Parallel autovacuum: DROP DATABASE WITH (FORCE) fails on the parallel workers

Hi,

On Sun, Sep 27, 2026 at 5:00 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

AI review found a bug in parallel autovacuum (1ff3180ca01). I checked
it myself and it reproduces on HEAD and PG19. Patch with a test
attached.

A role with pg_signal_backend that is not a superuser gets "permission
denied to terminate process" [1] from DROP DATABASE WITH (FORCE) while
a parallel autovacuum on that database is in its index phase, which on
a large table is where the vacuum spends its time. The same role can
terminate the autovacuum worker itself, and DROP DATABASE without
FORCE succeeds with the same vacuum running.

Both the autovacuum worker and its parallel workers run as the
bootstrap superuser, so that is not what separates them.
TerminateOtherDBBackends() looks at the role a process published in
its PGPROC, and only the workers published one. The autovacuum worker
gets its user from InitializeSessionUserIdStandalone(), which sets
AuthenticatedUserId directly and never calls SetAuthenticatedUserId(),
so MyProc->roleId stays InvalidOid and superuser_arg() on it is false.
Its parallel workers take the same user through ParallelWorkerMain(),
which does call SetAuthenticatedUserId(), so they publish the
bootstrap superuser and the check refuses them.

A parallel worker of a VACUUM command is not affected, since its
leader is a user session and the worker publishes the same role as its
leader. Autovacuum is the only leader that publishes no role while its
workers publish one.

The fix treats a process whose lock group leader is an autovacuum
worker the way the autovacuum worker itself is treated. The patch adds
a test to the test_autovacuum module that fails with this error
without the fix.

Thank you for the report and the patch!

IIUC the issue stems from the fact that the leader and its workers
advertise different roleIds (InvalidOid and BOOTSTRAP_SUPERUSERID). I
think the same issue can be reproduced in other cases. For instance,
suppose that a bgworker connecting to the database via
BackgroundWorkerInitializeConnection(dbname, NULL, 0) runs a parallel
query, the leader's roleId is InvalidOid whereas the parallel query
workers have BOOTSTRAP_SUPERUSERID. I think we should fix it as well
and backpatch the fix to 14.

I have some review comments on the proposed patch:

+               if (leader != NULL && leader != proc &&
+                   leader->backendType == B_AUTOVAC_WORKER)
+                   roleId = InvalidOid;

I think we should check a lock group member with its leader's roleId
instead of unconditionally using InvalidOid. That would
straightforwardly fix the inconsistency between the leader and the
workers.

if (leader != NULL && leader != proc &&
leader->databaseId == databaseId)
roleId = leader->roleId;

To fix this issue not only in autovacuum cases, the backendType check
should be removed.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi,

On Mon, Sep 28, 2026 at 11:39 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Thank you for the report and the patch!

Thanks for taking a look at it.

IIUC the issue stems from the fact that the leader and its workers
advertise different roleIds (InvalidOid and BOOTSTRAP_SUPERUSERID). I
think the same issue can be reproduced in other cases. For instance,
suppose that a bgworker connecting to the database via
BackgroundWorkerInitializeConnection(dbname, NULL, 0) runs a parallel
query, the leader's roleId is InvalidOid whereas the parallel query
workers have BOOTSTRAP_SUPERUSERID. I think we should fix it as well
and backpatch the fix to 14.

Good catch. Yes, it happens there too. I verified it locally with a
simple test module that launches a background worker and starts a
parallel worker to run a query. I agree the fix needs to be
back-patched through 14.

I have some review comments on the proposed patch:

+               if (leader != NULL && leader != proc &&
+                   leader->backendType == B_AUTOVAC_WORKER)
+                   roleId = InvalidOid;

I think we should check a lock group member with its leader's roleId
instead of unconditionally using InvalidOid. That would
straightforwardly fix the inconsistency between the leader and the
workers.

if (leader != NULL && leader != proc &&
leader->databaseId == databaseId)
roleId = leader->roleId;

To fix this issue not only in autovacuum cases, the backendType check
should be removed.

Agreed, that is better. Checking a parallel worker against its
leader's roleId is the right choice here, since both are members of
the same lock group. It gives the expected behavior, terminating both
the leader and the workers for DROP DATABASE FORCE, which both run as
the bootstrap superuser.

Please find the attached v2 patch. I plan to share patches for the
back branches without the TAP test for branches < PG19 unless there
are any comments.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi Bharath,

v2 looks right. The new test fails without your change
and passes with it. Attached v3 is only a rebase over head.

A superuser's parallel worker is still refused, other role cases
did not change.

The check also covers the REPACK decoding worker, which connects as
the table owner. DROP DATABASE fails on its slot first, so that looks
fine.

I did not test the bgworker case. The check does not look at the
backend type, and such a bgworker leaves roleId unset like autovacuum
does, so I think it works too.

Thanks,
Shihao

On Tue, Sep 29, 2026 at 7:46 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Mon, Sep 28, 2026 at 11:39 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Thank you for the report and the patch!

Thanks for taking a look at it.

IIUC the issue stems from the fact that the leader and its workers
advertise different roleIds (InvalidOid and BOOTSTRAP_SUPERUSERID). I
think the same issue can be reproduced in other cases. For instance,
suppose that a bgworker connecting to the database via
BackgroundWorkerInitializeConnection(dbname, NULL, 0) runs a parallel
query, the leader's roleId is InvalidOid whereas the parallel query
workers have BOOTSTRAP_SUPERUSERID. I think we should fix it as well
and backpatch the fix to 14.

Good catch. Yes, it happens there too. I verified it locally with a
simple test module that launches a background worker and starts a
parallel worker to run a query. I agree the fix needs to be
back-patched through 14.

I have some review comments on the proposed patch:

+               if (leader != NULL && leader != proc &&
+                   leader->backendType == B_AUTOVAC_WORKER)
+                   roleId = InvalidOid;

I think we should check a lock group member with its leader's roleId
instead of unconditionally using InvalidOid. That would
straightforwardly fix the inconsistency between the leader and the
workers.

if (leader != NULL && leader != proc &&
leader->databaseId == databaseId)
roleId = leader->roleId;

To fix this issue not only in autovacuum cases, the backendType check
should be removed.

Agreed, that is better. Checking a parallel worker against its
leader's roleId is the right choice here, since both are members of
the same lock group. It gives the expected behavior, terminating both
the leader and the workers for DROP DATABASE FORCE, which both run as
the bootstrap superuser.

Please find the attached v2 patch. I plan to share patches for the
back branches without the TAP test for branches < PG19 unless there
are any comments.

I've reviewed the patch and have one comment:

+               PGPROC     *leader = proc->lockGroupLeader;
+               Oid         roleId = proc->roleId;
+
...
+                */
+               if (leader != NULL && leader != proc &&
+                   leader->databaseId == databaseId)
+                   roleId = leader->roleId;

We read another backend's lockGroupLeader and dereference it without
holding the lock that protects the lock group fields, which seems
incorrect to me. If the worker exits after we read the pointer, the
leader's PGPROC could be recycled and we could check an unrelated
backend's role. I think that we can acquire the leader's partition
lock and recheck if the leader is still the leader we're looking for,
like BecomeLockGroupMember() does. It would require one more LWLock
acquisition per lock group member but probably we can live with it as
DROP DATABASE FORCE isn't a hot path. What do you think?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Sun, Oct 4, 2026 at 8:35 PM shihao zhong <zhong950419@gmail.com> wrote:

Hi Bharath,

v2 looks right. The new test fails without your change
and passes with it. Attached v3 is only a rebase over head.

A superuser's parallel worker is still refused, other role cases
did not change.

The check also covers the REPACK decoding worker, which connects as
the table owner.

Right. A similar issue could happen also in REPACK workers; if DROP
DATABASE FORCE sees the repack worker and a SIGTERM arrives there
before it creates a logical slot, we would see different roles in the
leader and the worker and possibly fail. But it's unlikely to happen
in practice as it's a very short window. I'm personally okay with
covering this case but others think differently that this fix should
deal with parallel autovacuum and bgworker cases. If so, we should add
!OidIsValid(leader->roleId) to the check.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi Masahiko,

Attached v4 takes the partition lock and rechecks the leader, as you
suggested. The databaseId test is gone, since the recheck makes it
redundant. The new test and the role matrix give the same results as
v2.

I did not add the !OidIsValid(leader->roleId) check. DROP DATABASE
fails on the REPACK slot first anyway.

Thanks,
shihao zhong

On Mon, Oct 5, 2026 at 5:27 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

On Tue, Sep 29, 2026 at 7:46 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Mon, Sep 28, 2026 at 11:39 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Thank you for the report and the patch!

Thanks for taking a look at it.

IIUC the issue stems from the fact that the leader and its workers
advertise different roleIds (InvalidOid and BOOTSTRAP_SUPERUSERID). I
think the same issue can be reproduced in other cases. For instance,
suppose that a bgworker connecting to the database via
BackgroundWorkerInitializeConnection(dbname, NULL, 0) runs a parallel
query, the leader's roleId is InvalidOid whereas the parallel query
workers have BOOTSTRAP_SUPERUSERID. I think we should fix it as well
and backpatch the fix to 14.

Good catch. Yes, it happens there too. I verified it locally with a
simple test module that launches a background worker and starts a
parallel worker to run a query. I agree the fix needs to be
back-patched through 14.

I have some review comments on the proposed patch:

+               if (leader != NULL && leader != proc &&
+                   leader->backendType == B_AUTOVAC_WORKER)
+                   roleId = InvalidOid;

I think we should check a lock group member with its leader's roleId
instead of unconditionally using InvalidOid. That would
straightforwardly fix the inconsistency between the leader and the
workers.

if (leader != NULL && leader != proc &&
leader->databaseId == databaseId)
roleId = leader->roleId;

To fix this issue not only in autovacuum cases, the backendType check
should be removed.

Agreed, that is better. Checking a parallel worker against its
leader's roleId is the right choice here, since both are members of
the same lock group. It gives the expected behavior, terminating both
the leader and the workers for DROP DATABASE FORCE, which both run as
the bootstrap superuser.

Please find the attached v2 patch. I plan to share patches for the
back branches without the TAP test for branches < PG19 unless there
are any comments.

I've reviewed the patch and have one comment:

+               PGPROC     *leader = proc->lockGroupLeader;
+               Oid         roleId = proc->roleId;
+
...
+                */
+               if (leader != NULL && leader != proc &&
+                   leader->databaseId == databaseId)
+                   roleId = leader->roleId;

We read another backend's lockGroupLeader and dereference it without
holding the lock that protects the lock group fields, which seems
incorrect to me. If the worker exits after we read the pointer, the
leader's PGPROC could be recycled and we could check an unrelated
backend's role. I think that we can acquire the leader's partition
lock and recheck if the leader is still the leader we're looking for,
like BecomeLockGroupMember() does. It would require one more LWLock
acquisition per lock group member but probably we can live with it as
DROP DATABASE FORCE isn't a hot path. What do you think?

On second thought, I think we don't need to acquire the leader's
partition lock here. The leader's PGPROC can be recycled only after
the last member has exited, so if we read an unrelated backend's role
that way, the worker we're checking has already gone. That's no
different from the existing race mentioned in the comment in
TerminateOtherDBBackends(), where a process can exit before we send
the signal. Acquiring the lock would not close that race either. So I
think it's enough to add a comment explaining why it's okay to read
these fields without the lock. Sorry for the noise.

Also, I think it's better to add the !OidIsValid(leader->roleId) check
to the if statement (and set roleId = InvalidOid directly). That way,
the change would affect only the specific case where the leader
doesn't advertise its roleId while its workers do, which seems better
to me for backpatching than creating a generic rule "if the leader and
the worker have different roles, the worker inherits the leader's
roleId only when being terminated".

For the record, I've considered alternative solutions but the current
approach is reasonable to me.

A. Set parallel autovacuum worker's roleId to InvalidOid at its
startup time. While it's good that it affects only parallel autovacuum
cases, it would add another place to set the process's roleId. Also,
there is still a small window where its roleId is
BOOTSTRAP_SUPERUSERID. To remove the window completely, we need to
change parallel query code so that workers advertise the same roleId
as their leader, which is too invasive.

B. Ignore parallel workers in TerminateOtherDBBackends(). I think it's
the second-best idea but I'm concerned that it would affect the
existing behavior that terminates parallel workers.

When it comes to the current idea, it changes
TerminateOtherDBBackends() but the change would affect only the cases
where the leader doesn't advertise its roleId, i.e., parallel
autovacuum and parallel queries run by a bgworker connected without a
user, and doesn't change the existing behavior of terminating parallel
workers.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi Sawada-san,

Attached v5 drops the lock and adds a comment on why the read is safe.
It also adds the !OidIsValid(leader->roleId) check and sets roleId to
InvalidOid directly, so only a leader with no advertised role changes
anything.

Thanks,
Shihao

On Tue, Oct 6, 2026 at 9:00 PM shihao zhong <zhong950419@gmail.com> wrote:

Hi Sawada-san,

Attached v5 drops the lock and adds a comment on why the read is safe.
It also adds the !OidIsValid(leader->roleId) check and sets roleId to
InvalidOid directly, so only a leader with no advertised role changes
anything.

Thank you for updating the patch. Could you also share your thoughts
on the points I raised in my previous email?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi Sawada-san,

I agree with you on both A and B, and I would keep the current approach.

For A, a parallel worker gets its roleId in InitializeSessionUserId(),
so any reset has to come after that and the window stays. It also would
not cover the bgworker case.

I did not try B. I share your concern that it changes how DROP DATABASE
FORCE treats parallel workers today, which I would not want in a
backpatch.

Thanks,
Shihao

Hi,

On Tue, Oct 6, 2026 at 5:54 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

I've reviewed the patch and have one comment:

Thanks for reviewing the v2 patch.

+ PGPROC *leader = proc->lockGroupLeader;

We read another backend's lockGroupLeader and dereference it without
holding the lock that protects the lock group fields, which seems
incorrect to me. If the worker exits after we read the pointer, the
leader's PGPROC could be recycled and we could check an unrelated
backend's role....

On second thought, I think we don't need to acquire the leader's
partition lock here.

So I
think it's enough to add a comment explaining why it's okay to read
these fields without the lock. Sorry for the noise.

Please also note that pg_terminate_backend() doesn't take a lock
either when it reads PGPROC.roleId. I don't think that recycling
window is a big issue in practice.

For the record, I've considered alternative solutions but the current
approach is reasonable to me.

A. Set parallel autovacuum worker's roleId to InvalidOid at its
startup time. While it's good that it affects only parallel autovacuum
cases, it would add another place to set the process's roleId. Also,
there is still a small window where its roleId is
BOOTSTRAP_SUPERUSERID. To remove the window completely, we need to
change parallel query code so that workers advertise the same roleId
as their leader, which is too invasive.

I respectfully disagree with this option, for a couple of reasons.
It's invasive, of course, and resetting something to invalid outside
of the parallel workers infrastructure seems off to me. The recycling
window also seems to be accepted behavior (see my argument above).

B. Ignore parallel workers in TerminateOtherDBBackends(). I think it's
the second-best idea but I'm concerned that it would affect the
existing behavior that terminates parallel workers.

I respectfully disagree with this option as well. Ignoring them is not
a good solution, IMHO.

When it comes to the current idea, it changes
TerminateOtherDBBackends() but the change would affect only the cases
where the leader doesn't advertise its roleId, i.e., parallel
autovacuum and parallel queries run by a bgworker connected without a
user, and doesn't change the existing behavior of terminating parallel
workers.

I took a closer look today, and here's what I have. More than the fix
itself, I would like to agree on the behavior first, since I think
this is a sensitive area. Please feel free to correct me.

Autovacuum workers (including slot sync workers and background
workers) that connect with no user name run as the bootstrap
superuser, but they leave PGPROC.roleId unset. Parallel workers
launched by them also run as the superuser, inherited from the leader,
but they do set PGPROC.roleId.

I looked at pg_terminate_backend() [1]https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL first, because I think DROP
DATABASE FORCE has to agree with it per docs [2]https://www.postgresql.org/docs/devel/sql-dropdatabase.html. It lets only
superusers terminate superuser backends, except that
pg_signal_autovacuum_worker may terminate autovacuum workers. Whether
or not the predefined role includes the parallel workers launched by
autovacuum workers defines the scope of the fix.

Case 1 is a non-superuser with just pg_signal_backend. Case 2 is the
same role with pg_signal_autovacuum_worker.

pg_terminate_backend() on HEAD:
Case 1: autovacuum worker errors out, needs
pg_signal_autovacuum_worker. Parallel worker errors out, needs
SUPERUSER.
Case 2: autovacuum worker is terminated. Parallel worker errors out,
needs SUPERUSER.

DROP DATABASE FORCE on HEAD:
Case 1: autovacuum worker is terminated and the database drops.
Parallel worker errors out, needs SUPERUSER.
Case 2: same as case 1.

Do we want the forced database drop to match the behavior of
pg_terminate_backend()?

Do we want pg_signal_autovacuum_worker to cover the parallel workers
launched by autovacuum workers?

If yes to both, we need fixes in both places. I prefer this, unless I
am missing something.

If yes to the first and no to the second, we fix the forced database
drop and adjust the docs for pg_terminate_backend().

Thoughts?

[1]: https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL
[2]: https://www.postgresql.org/docs/devel/sql-dropdatabase.html

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Wed, Oct 7, 2026 at 7:47 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Tue, Oct 6, 2026 at 5:54 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

I've reviewed the patch and have one comment:

Thanks for reviewing the v2 patch.

+ PGPROC *leader = proc->lockGroupLeader;

We read another backend's lockGroupLeader and dereference it without
holding the lock that protects the lock group fields, which seems
incorrect to me. If the worker exits after we read the pointer, the
leader's PGPROC could be recycled and we could check an unrelated
backend's role....

On second thought, I think we don't need to acquire the leader's
partition lock here.

So I
think it's enough to add a comment explaining why it's okay to read
these fields without the lock. Sorry for the noise.

Please also note that pg_terminate_backend() doesn't take a lock
either when it reads PGPROC.roleId. I don't think that recycling
window is a big issue in practice.

For the record, I've considered alternative solutions but the current
approach is reasonable to me.

A. Set parallel autovacuum worker's roleId to InvalidOid at its
startup time. While it's good that it affects only parallel autovacuum
cases, it would add another place to set the process's roleId. Also,
there is still a small window where its roleId is
BOOTSTRAP_SUPERUSERID. To remove the window completely, we need to
change parallel query code so that workers advertise the same roleId
as their leader, which is too invasive.

I respectfully disagree with this option, for a couple of reasons.
It's invasive, of course, and resetting something to invalid outside
of the parallel workers infrastructure seems off to me. The recycling
window also seems to be accepted behavior (see my argument above).

B. Ignore parallel workers in TerminateOtherDBBackends(). I think it's
the second-best idea but I'm concerned that it would affect the
existing behavior that terminates parallel workers.

I respectfully disagree with this option as well. Ignoring them is not
a good solution, IMHO.

When it comes to the current idea, it changes
TerminateOtherDBBackends() but the change would affect only the cases
where the leader doesn't advertise its roleId, i.e., parallel
autovacuum and parallel queries run by a bgworker connected without a
user, and doesn't change the existing behavior of terminating parallel
workers.

I took a closer look today, and here's what I have. More than the fix
itself, I would like to agree on the behavior first, since I think
this is a sensitive area. Please feel free to correct me.

Autovacuum workers (including slot sync workers and background
workers) that connect with no user name run as the bootstrap
superuser, but they leave PGPROC.roleId unset. Parallel workers
launched by them also run as the superuser, inherited from the leader,
but they do set PGPROC.roleId.

I looked at pg_terminate_backend() [1] first, because I think DROP
DATABASE FORCE has to agree with it per docs [2]. It lets only
superusers terminate superuser backends, except that
pg_signal_autovacuum_worker may terminate autovacuum workers. Whether
or not the predefined role includes the parallel workers launched by
autovacuum workers defines the scope of the fix.

Case 1 is a non-superuser with just pg_signal_backend. Case 2 is the
same role with pg_signal_autovacuum_worker.

pg_terminate_backend() on HEAD:
Case 1: autovacuum worker errors out, needs
pg_signal_autovacuum_worker. Parallel worker errors out, needs
SUPERUSER.
Case 2: autovacuum worker is terminated. Parallel worker errors out,
needs SUPERUSER.

DROP DATABASE FORCE on HEAD:
Case 1: autovacuum worker is terminated and the database drops.
Parallel worker errors out, needs SUPERUSER.
Case 2: same as case 1.

These analyses match mine.

Do we want the forced database drop to match the behavior of
pg_terminate_backend()?

No. DROP DATABASE FORCE and pg_terminate_backend() purposefully work
differently for different purposes. See the comments in
TerminateOtherDBBackends():

/*
* Permissions checks relax the pg_terminate_backend checks in two
* ways, both by omitting the !OidIsValid(proc->roleId) check:
*
* - Accept terminating autovacuum workers, since DROP DATABASE
* without FORCE terminates them.
*
* - Accept terminating bgworkers. For bgworker authors, it's
* convenient to be able to recommend FORCE if a worker is blocking
* DROP DATABASE unexpectedly.
*
* Unlike pg_terminate_backend, we don't raise some warnings - like
* "PID %d is not a PostgreSQL server process", because for us already
* finished session is not a problem.
*/

If we had DROP DATABASE FORCE match the pg_terminate_backend(), DROP
DATABASE FORCE would also require pg_signal_autovacuum_worker to
terminate autovacuum workers. On the other hand, if we had
pg_terminate_backend() match DROP DATABASE FORCE, users with
pg_signal_backend would be able to terminate autovacuum workers,
making pg_signal_autovacuum_worker nonsense. Neither outcome makes
sense to me.

The consistency we need to maintain is between DROP DATABASE with
FORCE and without FORCE, and parallel autovacuum breaks it in v19,
which is what we need to fix for v19.

Do we want pg_signal_autovacuum_worker to cover the parallel workers
launched by autovacuum workers?

Not sure, but I think it's a separate topic for v20. Parallel workers
are not autovacuum worker processes. They are bgworkers internally and
shown as "parallel worker" in pg_stat_activity. So the current
behavior doesn't contradict the docs, and users can terminate the
autovacuum worker, which stops its parallel workers too.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi,

On Wed, Oct 7, 2026 at 10:49 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Do we want the forced database drop to match the behavior of
pg_terminate_backend()?

No. DROP DATABASE FORCE and pg_terminate_backend() purposefully work
differently for different purposes. See the comments in
TerminateOtherDBBackends():

My bad. I missed that. Thanks for pointing to that.

/*
* Permissions checks relax the pg_terminate_backend checks in two
* ways, both by omitting the !OidIsValid(proc->roleId) check:
*
* - Accept terminating autovacuum workers, since DROP DATABASE
* without FORCE terminates them.

I read the commit 372700cf3067, re-read the docs, and found the hidden
note. We took exception from pg_terminate_backend()'s behavior for
autovacuum workers (of course for bg workers as well) allowing the
non-superuser role to terminate the autovacuum workers and force drop
the database. So, it now needs to also include the parallel workers
started by autovacuum workers. I will add a comment about this in the
hidden docs and the comments in TerminateOtherDBBackends().

When it comes to the current idea, it changes
TerminateOtherDBBackends() but the change would affect only the cases
where the leader doesn't advertise its roleId, i.e., parallel
autovacuum and parallel queries run by a bgworker connected without a
user, and doesn't change the existing behavior of terminating parallel
workers.

Yes, that's correct with the v2 patch. If we were to fix it for
parallel workers started by any bg worker (which I think we should),
the fix also needs to be back-patched. If agreed, I will check how far
back it needs to be back-patched and prepare patches.

Also, I think it's better to add the !OidIsValid(leader->roleId) check
to the if statement (and set roleId = InvalidOid directly). That way,
the change would affect only the specific case where the leader
doesn't advertise its roleId while its workers do, which seems better
to me for backpatching than creating a generic rule "if the leader and
the worker have different roles, the worker inherits the leader's
roleId only when being terminated".

Does the following work? I still think setting roleId with
leader->roleId is good for readability purposes (it avoids a line in
the comment).

@@ -3900,14 +3900,30 @@ TerminateOtherDBBackends(Oid databaseId)

                        if (proc != NULL)
                        {
-                               if (superuser_arg(proc->roleId) && !superuser())
+                               PGPROC     *leader = proc->lockGroupLeader;
+                               Oid                     roleId = proc->roleId;
+
+                               /*
+                                * An autovacuum worker and a
background worker with no user
+                                * name advertise no role and they run
as bootstrap superuser (see
+                                * InitializeSessionUserIdStandalone()
callsites in
+                                * postinit.c). While the parallel
workers they launch inherit
+                                * the same superuser and advertise it
in their PGPROC. Accept
+                                * such parallel workers as well.
+                                */
+                               if (leader != NULL && leader != proc &&
+                                       !OidIsValid(leader->roleId) &&
+                                       leader->databaseId == databaseId)
+                                       roleId = leader->roleId;
+
+                               if (superuser_arg(roleId) && !superuser())

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Thu, Oct 8, 2026 at 10:27 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Wed, Oct 7, 2026 at 10:49 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Do we want the forced database drop to match the behavior of
pg_terminate_backend()?

No. DROP DATABASE FORCE and pg_terminate_backend() purposefully work
differently for different purposes. See the comments in
TerminateOtherDBBackends():

My bad. I missed that. Thanks for pointing to that.

/*
* Permissions checks relax the pg_terminate_backend checks in two
* ways, both by omitting the !OidIsValid(proc->roleId) check:
*
* - Accept terminating autovacuum workers, since DROP DATABASE
* without FORCE terminates them.

I read the commit 372700cf3067, re-read the docs, and found the hidden
note. We took exception from pg_terminate_backend()'s behavior for
autovacuum workers (of course for bg workers as well) allowing the
non-superuser role to terminate the autovacuum workers and force drop
the database. So, it now needs to also include the parallel workers
started by autovacuum workers. I will add a comment about this in the
hidden docs and the comments in TerminateOtherDBBackends().

When it comes to the current idea, it changes
TerminateOtherDBBackends() but the change would affect only the cases
where the leader doesn't advertise its roleId, i.e., parallel
autovacuum and parallel queries run by a bgworker connected without a
user, and doesn't change the existing behavior of terminating parallel
workers.

Yes, that's correct with the v2 patch. If we were to fix it for
parallel workers started by any bg worker (which I think we should),
the fix also needs to be back-patched. If agreed, I will check how far
back it needs to be back-patched and prepare patches.

I'd fix it only in v19 for now. While the problem exists in the back
branches too, it has never been reported in the field. Also the next
minor release will be the final one for v14, there would be no chance
to fix it there if this change turned out to be wrong. I think that If
we get a report in the future, we can backpatch it.

Also, I think it's better to add the !OidIsValid(leader->roleId) check
to the if statement (and set roleId = InvalidOid directly). That way,
the change would affect only the specific case where the leader
doesn't advertise its roleId while its workers do, which seems better
to me for backpatching than creating a generic rule "if the leader and
the worker have different roles, the worker inherits the leader's
roleId only when being terminated".

Does the following work? I still think setting roleId with
leader->roleId is good for readability purposes (it avoids a line in
the comment).

@@ -3900,14 +3900,30 @@ TerminateOtherDBBackends(Oid databaseId)

if (proc != NULL)
{
-                               if (superuser_arg(proc->roleId) && !superuser())
+                               PGPROC     *leader = proc->lockGroupLeader;
+                               Oid                     roleId = proc->roleId;
+
+                               /*
+                                * An autovacuum worker and a
background worker with no user
+                                * name advertise no role and they run
as bootstrap superuser (see
+                                * InitializeSessionUserIdStandalone()
callsites in
+                                * postinit.c). While the parallel
workers they launch inherit
+                                * the same superuser and advertise it
in their PGPROC. Accept
+                                * such parallel workers as well.
+                                */
+                               if (leader != NULL && leader != proc &&
+                                       !OidIsValid(leader->roleId) &&
+                                       leader->databaseId == databaseId)
+                                       roleId = leader->roleId;
+
+                               if (superuser_arg(roleId) && !superuser())

Fine with me. But probably can we remove leader->databaseId ==
databaseId check? It seems redundant for parallel workers.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi,

On Thu, Oct 8, 2026 at 10:52 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Yes, that's correct with the v2 patch. If we were to fix it for
parallel workers started by any bg worker (which I think we should),
the fix also needs to be back-patched. If agreed, I will check how far
back it needs to be back-patched and prepare patches.

I'd fix it only in v19 for now. While the problem exists in the back
branches too, it has never been reported in the field. Also the next
minor release will be the final one for v14, there would be no chance
to fix it there if this change turned out to be wrong. I think that If
we get a report in the future, we can backpatch it.

Agreed.

Fine with me. But probably can we remove leader->databaseId ==
databaseId check? It seems redundant for parallel workers.

That's correct, but I would like to be more cautious here, so I
retained it. I also added a check that the parallel worker's
advertised role is the bootstrap superuser, again to be more cautious.

Please find the attached v3 patches. I split the TAP test into 0002.
Although I reviewed it closely, I don't have a strong opinion on
taking it (unless anyone thinks otherwise), I'm fine to leave it here
in the thread for the records.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Fri, Oct 9, 2026 at 10:44 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Thu, Oct 8, 2026 at 10:52 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Yes, that's correct with the v2 patch. If we were to fix it for
parallel workers started by any bg worker (which I think we should),
the fix also needs to be back-patched. If agreed, I will check how far
back it needs to be back-patched and prepare patches.

I'd fix it only in v19 for now. While the problem exists in the back
branches too, it has never been reported in the field. Also the next
minor release will be the final one for v14, there would be no chance
to fix it there if this change turned out to be wrong. I think that If
we get a report in the future, we can backpatch it.

Agreed.

Fine with me. But probably can we remove leader->databaseId ==
databaseId check? It seems redundant for parallel workers.

That's correct, but I would like to be more cautious here, so I
retained it. I also added a check that the parallel worker's
advertised role is the bootstrap superuser, again to be more cautious.

Hmm, I don't think these checks narrow the case. When the leader
advertises no role, it goes through
InitializeSessionUserIdStandalone(), so its parallel workers always
run as the bootstrap superuser and connect to the same database. So
both checks are always true once !OidIsValid(leader->roleId) holds.
Even if a worker advertised another role, I think judging it by its
leader would still be fine, since we accept terminating the leader and
the worker exits with it anyway. So I'd prefer to remove both checks,
as they make readers think when they could be false.

Also, regarding the following documentation change:

-      This terminates background worker connections and connections that the
-      current user has permission to terminate
+      This terminates background worker connections, the parallel workers they
+      launch, and connections that the current user has permission to terminate

I think we don't need this change as parallel workers are background
workers, no?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi,

On Tue, Oct 13, 2026 at 4:17 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Fine with me. But probably can we remove leader->databaseId ==
databaseId check? It seems redundant for parallel workers.

That's correct, but I would like to be more cautious here, so I
retained it. I also added a check that the parallel worker's
advertised role is the bootstrap superuser, again to be more cautious.

Hmm, I don't think these checks narrow the case. When the leader
advertises no role, it goes through
InitializeSessionUserIdStandalone(), so its parallel workers always
run as the bootstrap superuser and connect to the same database. So
both checks are always true once !OidIsValid(leader->roleId) holds.
Even if a worker advertised another role, I think judging it by its
leader would still be fine, since we accept terminating the leader and
the worker exits with it anyway. So I'd prefer to remove both checks,
as they make readers think when they could be false.

Agreed, let me not be over-cautious. We likely don't, and never will,
change InitializeSessionUserIdStandalone() for such bg/parallel
workers. I will remove both the database OID and bootstrap superuser
checks in the next version.

Also, regarding the following documentation change:

-      This terminates background worker connections and connections that the
-      current user has permission to terminate
+      This terminates background worker connections, the parallel workers they
+      launch, and connections that the current user has permission to terminate

I think we don't need this change as parallel workers are background
workers, no?

I would like to keep it for clarity, especially since not every
parallel worker is exempted here, only the ones started by autovacuum
workers or bg workers.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Tue, Oct 13, 2026 at 9:01 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Also, regarding the following documentation change:

-      This terminates background worker connections and connections that the
-      current user has permission to terminate
+      This terminates background worker connections, the parallel workers they
+      launch, and connections that the current user has permission to terminate

I think we don't need this change as parallel workers are background
workers, no?

I would like to keep it for clarity, especially since not every
parallel worker is exempted here, only the ones started by autovacuum
workers or bg workers.

I think that distinction is not visible to users. A parallel worker
always connects to the same database as its leader, so the leader is
checked too, with the same role. If the current user cannot terminate
the leader, the command fails whether or not the worker is exempted.
So I think the existing sentence is already accurate for parallel
workers: if the command succeeds, all parallel workers in the database
have been terminated. Also, I think the new text could be misleading.
Since parallel workers are background workers too, "the parallel
workers they launch" is either redundant with the first item
"background worker connections", or reads as if other parallel workers
are not terminated, which is not the case.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi,

On Wed, Oct 14, 2026 at 12:03 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

I think that distinction is not visible to users. A parallel worker
always connects to the same database as its leader, so the leader is
checked too, with the same role.

Since the code comments already specify that, I think it is fine not
to have anything in the docs. So I removed the doc changes and
addressed the other comments as well. Please find the attached v4
patches.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com