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. [1] ERROR: permission denied to terminate process DETAIL: Only roles with the SUPERUSER attribute may terminate processes of roles with the SUPERUSER attribute. -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
From c4fb4499a8156830d9636191422ee3e7b89d5b7c Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Sat, 26 Sep 2026 22:38:08 +0000 Subject: [PATCH v1] Fix DROP DATABASE FORCE failing on parallel autovacuum workers. Both an autovacuum worker and the parallel workers it launches 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. An 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. As a result, a role with pg_signal_backend that is not a superuser got "permission denied to terminate process" from DROP DATABASE WITH (FORCE) while a parallel autovacuum on that database was 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. 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. Fix this by treating a process whose lock group leader is an autovacuum worker the way the autovacuum worker itself is treated. Add a test to the test_autovacuum module, which holds the parallel workers at a new injection point. Oversight in commit 1ff3180ca01. Backpatch to 19, where parallel autovacuum was introduced. Reported-by: Claude Code Author: Bharath Rupireddy <[email protected]> Discussion: https://postgr.es/m/<<message-id>> Backpatch-through: 19 --- src/backend/commands/vacuumparallel.c | 4 ++ src/backend/storage/ipc/procarray.c | 23 +++++++- .../t/001_parallel_autovacuum.pl | 52 +++++++++++++++++++ 3 files changed, 77 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c index 4532da60c84..67630236f81 100644 --- a/src/backend/commands/vacuumparallel.c +++ b/src/backend/commands/vacuumparallel.c @@ -46,6 +46,7 @@ #include "storage/bufmgr.h" #include "storage/proc.h" #include "tcop/tcopprot.h" +#include "utils/injection_point.h" #include "utils/lsyscache.h" #include "utils/rel.h" @@ -1316,6 +1317,9 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc) /* Prepare to track buffer usage during parallel execution */ InstrStartParallelQuery(); + /* Used by tests to pause a worker while it is attached to the leader */ + INJECTION_POINT("parallel-vacuum-worker-start", NULL); + /* Process indexes to perform vacuum/cleanup */ parallel_vacuum_process_safe_indexes(&pvs); diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index b7e03134ed8..5836ed4d1cf 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -3900,14 +3900,33 @@ TerminateOtherDBBackends(Oid databaseId) if (proc != NULL) { - if (superuser_arg(proc->roleId) && !superuser()) + PGPROC *leader = proc->lockGroupLeader; + Oid roleId = proc->roleId; + + /* + * An autovacuum worker and the parallel workers it launches + * all run as the bootstrap superuser, but only the workers + * publish that role here. An autovacuum worker never goes + * through SetAuthenticatedUserId(), so its roleId stays + * InvalidOid, while a parallel worker calls it with the + * authenticated user of its leader. The checks below would + * then refuse a worker whose leader they accept, so check + * such a worker the way its leader is checked. The leader's + * PGPROC is not recycled until its last member has exited, so + * the pointer read here is the real leader. + */ + if (leader != NULL && leader != proc && + leader->backendType == B_AUTOVAC_WORKER) + roleId = InvalidOid; + + if (superuser_arg(roleId) && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to terminate process"), errdetail("Only roles with the %s attribute may terminate processes of roles with the %s attribute.", "SUPERUSER", "SUPERUSER"))); - if (!has_privs_of_role(GetUserId(), proc->roleId) && + if (!has_privs_of_role(GetUserId(), roleId) && !has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), diff --git a/src/test/modules/test_autovacuum/t/001_parallel_autovacuum.pl b/src/test/modules/test_autovacuum/t/001_parallel_autovacuum.pl index 33c86bbdc94..d1dfce8ad9b 100644 --- a/src/test/modules/test_autovacuum/t/001_parallel_autovacuum.pl +++ b/src/test/modules/test_autovacuum/t/001_parallel_autovacuum.pl @@ -253,5 +253,57 @@ $node->safe_psql('postgres', $node->safe_psql('postgres', "SELECT injection_points_detach('autovacuum-worker-cost-balanced')"); +# Test 4: +# Check that DROP DATABASE WITH (FORCE) can terminate the parallel workers of +# an autovacuum. They publish the bootstrap superuser as their role while +# their leader publishes none, so a non-superuser owner of the database was +# refused for the whole index phase of the vacuum. + +# Leave both worker slots to the parallel autovacuum below. +$node->safe_psql('postgres', + 'ALTER TABLE test_autovac SET (autovacuum_enabled = false)'); +$node->safe_psql('regress_db2', + 'ALTER TABLE filler SET (autovacuum_enabled = false)'); + +# Hand regress_db2 to a non-superuser that may terminate other sessions. +$node->safe_psql( + 'postgres', qq{ + CREATE ROLE regress_dbowner LOGIN; + GRANT pg_signal_backend TO regress_dbowner; + ALTER DATABASE regress_db2 OWNER TO regress_dbowner; +}); + +# Hold the parallel workers while they are attached to their leader. +$node->safe_psql('postgres', + "SELECT injection_points_attach('parallel-vacuum-worker-start', 'wait')"); + +# A table whose autovacuum vacuums indexes in parallel. +$node->safe_psql( + 'regress_db2', qq{ + CREATE TABLE dropdb_force (a int, b int, c int) + WITH (autovacuum_parallel_workers = 2, + autovacuum_vacuum_threshold = 1, + autovacuum_vacuum_scale_factor = 0); + INSERT INTO dropdb_force SELECT g, g, g FROM generate_series(1, 1000) g; + CREATE INDEX ON dropdb_force (a); + CREATE INDEX ON dropdb_force (b); + CREATE INDEX ON dropdb_force (c); + DELETE FROM dropdb_force; +}); + +$node->wait_for_event('parallel worker', 'parallel-vacuum-worker-start'); + +my ($ret, $out, $err) = $node->psql( + 'postgres', + 'DROP DATABASE regress_db2 WITH (FORCE)', + connstr => $node->connstr('postgres') . ' user=regress_dbowner'); + +is($ret, 0, 'DROP DATABASE WITH (FORCE) ends parallel autovacuum workers'); +is($err, '', 'no error from DROP DATABASE WITH (FORCE)'); + +# The command ended the held workers, so there is nothing left to wake up. +$node->safe_psql('postgres', + "SELECT injection_points_detach('parallel-vacuum-worker-start')"); + $node->stop; done_testing(); -- 2.47.3
