public inbox for [email protected]
help / color / mirror / Atom feedFrom: Imseih (AWS), Sami <[email protected]>
To: [email protected] <[email protected]>
Subject: [BUG] autovacuum may skip tables when session_authorization/role is set on database
Date: Wed, 13 Dec 2023 20:42:45 +0000
Message-ID: <[email protected]> (raw)
Hi,
A recent case in the field in which a database session_authorization is
altered to a non-superuser, non-owner of tables via alter database .. set session_authorization ..
caused autovacuum to skip tables.
The issue was discovered on 13.10, and the logs show such messages:
warning: skipping "table1" --- only table or database owner can vacuum it
In HEAD, I can repro, but the message is now a bit different due to [1].
WARNING: permission denied to vacuum "table1”, skipping it
It seems to me we should force an autovacuum worker to set the session userid to
a superuser.
Attached is a repro and a patch which sets the session user to the BOOTSTRAP superuser
at the start of the autovac worker.
Thoughts?
Regards,
Sami
Amazon Web Services (AWS)
[1] https://postgr.es/m/[email protected]
## make autovac trigger often for the test
psql<<EOF
alter system set autovacuum_naptime = "1s";
EOF
pg_ctl stop -mf; pg_ctl start;
## create pgbench tables owned by testuser, and give
## testuser2 DML/read permissions on the tables.
psql<<EOF
create database testdb;
create user testuser password 'password';
create user testuser2 password 'password';
grant create on database testdb to testuser;
grant create on database testdb to testuser2;
\c testdb
grant all on schema public to testuser;
\! pgbench -U testuser -d testdb -i -s10
alter table public.pgbench_accounts set ( autovacuum_vacuum_threshold = 5 );
alter table public.pgbench_accounts set ( autovacuum_vacuum_scale_factor = 0);
grant select, insert, update, delete on pgbench_branches to testuser2;
grant select, insert, update, delete on pgbench_accounts to testuser2;
grant select, insert, update, delete on pgbench_history to testuser2;
grant select, insert, update, delete on pgbench_tellers to testuser2;
EOF
## set the session_authorization on the database and start a workload.
## because we gave testuser2 DML/read permissions, the pgbench will succeed
psql<<EOF
alter database testdb set session_authorization = testuser2;
\! pgbench -U testuser -d testdb -c50 -T360
EOF
## but the autovacuums will fail because testuser2 is not the owner of the tables, and testuser2 is the
## session authorization
2023-12-13 19:25:34.901 UTC [1159218] WARNING: permission denied to vacuum "pgbench_history", skipping it
2023-12-13 19:25:34.901 UTC [1159218] WARNING: permission denied to vacuum "pgbench_tellers", skipping it
2023-12-13 19:25:36.001 UTC [1159223] WARNING: permission denied to vacuum "pgbench_branches", skipping it
2023-12-13 19:25:36.001 UTC [1159223] WARNING: permission denied to vacuum "pgbench_accounts", skipping it
2023-12-13 19:25:36.001 UTC [1159223] WARNING: permission denied to vacuum "pgbench_history", skipping it
2023-12-13 19:25:36.002 UTC [1159223] WARNING: permission denied to vacuum "pgbench_tellers", skipping it
[ec2-user@ip-172-31-22-197 ~]$
Attachments:
[application/octet-stream] 0001-v1-Force-autovacuum-to-use-bootstrap-superuser.patch (2.2K, ../[email protected]/3-0001-v1-Force-autovacuum-to-use-bootstrap-superuser.patch)
download | inline diff:
From d9702ce17f2f78d4c5139a241a69c432823f8233 Mon Sep 17 00:00:00 2001
From: EC2 Default User <[email protected]>
Date: Wed, 13 Dec 2023 19:20:12 +0000
Subject: [PATCH 1/1] Force autovacuum to use bootstrap superuser
---
src/backend/postmaster/autovacuum.c | 7 +++++++
src/backend/utils/init/miscinit.c | 2 +-
src/include/miscadmin.h | 1 +
3 files changed, 9 insertions(+), 1 deletion(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index b04fcfc8c8..f468661916 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -75,6 +75,7 @@
#include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "commands/dbcommands.h"
#include "commands/vacuum.h"
@@ -2025,6 +2026,12 @@ do_autovacuum(void)
ALLOCSET_DEFAULT_SIZES);
MemoryContextSwitchTo(AutovacMemCxt);
+ /*
+ * Autovacuum must be run as superuser, so set the session userid to the
+ * bootstrap superuser id.
+ */
+ SetSessionUserId(BOOTSTRAP_SUPERUSERID, true);
+
/* Start a transaction so our commands have one to play into. */
StartTransactionCommand();
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 819936ec02..10bc2add2a 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -546,7 +546,7 @@ GetSessionUserId(void)
}
-static void
+void
SetSessionUserId(Oid userid, bool is_superuser)
{
Assert(SecurityRestrictionContext == 0);
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 1043a4d782..4f56c85333 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -354,6 +354,7 @@ extern char *GetUserNameFromId(Oid roleid, bool noerr);
extern Oid GetUserId(void);
extern Oid GetOuterUserId(void);
extern Oid GetSessionUserId(void);
+extern void SetSessionUserId(Oid userid, bool is_superuser);
extern Oid GetAuthenticatedUserId(void);
extern void GetUserIdAndSecContext(Oid *userid, int *sec_context);
extern void SetUserIdAndSecContext(Oid userid, int sec_context);
--
2.40.1
[text/plain] repro.txt (2.0K, ../[email protected]/4-repro.txt)
download | inline:
## make autovac trigger often for the test
psql<<EOF
alter system set autovacuum_naptime = "1s";
EOF
pg_ctl stop -mf; pg_ctl start;
## create pgbench tables owned by testuser, and give
## testuser2 DML/read permissions on the tables.
psql<<EOF
create database testdb;
create user testuser password 'password';
create user testuser2 password 'password';
grant create on database testdb to testuser;
grant create on database testdb to testuser2;
\c testdb
grant all on schema public to testuser;
\! pgbench -U testuser -d testdb -i -s10
alter table public.pgbench_accounts set ( autovacuum_vacuum_threshold = 5 );
alter table public.pgbench_accounts set ( autovacuum_vacuum_scale_factor = 0);
grant select, insert, update, delete on pgbench_branches to testuser2;
grant select, insert, update, delete on pgbench_accounts to testuser2;
grant select, insert, update, delete on pgbench_history to testuser2;
grant select, insert, update, delete on pgbench_tellers to testuser2;
EOF
## set the session_authorization on the database and start a workload.
## because we gave testuser2 DML/read permissions, the pgbench will succeed
psql<<EOF
alter database testdb set session_authorization = testuser2;
\! pgbench -U testuser -d testdb -c50 -T360
EOF
## but the autovacuums will fail because testuser2 is not the owner of the tables, and testuser2 is the
## session authorization
2023-12-13 19:25:34.901 UTC [1159218] WARNING: permission denied to vacuum "pgbench_history", skipping it
2023-12-13 19:25:34.901 UTC [1159218] WARNING: permission denied to vacuum "pgbench_tellers", skipping it
2023-12-13 19:25:36.001 UTC [1159223] WARNING: permission denied to vacuum "pgbench_branches", skipping it
2023-12-13 19:25:36.001 UTC [1159223] WARNING: permission denied to vacuum "pgbench_accounts", skipping it
2023-12-13 19:25:36.001 UTC [1159223] WARNING: permission denied to vacuum "pgbench_history", skipping it
2023-12-13 19:25:36.002 UTC [1159223] WARNING: permission denied to vacuum "pgbench_tellers", skipping it
[ec2-user@ip-172-31-22-197 ~]$
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [BUG] autovacuum may skip tables when session_authorization/role is set on database
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox