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/20220726.104712.912995710251150228.horikyota....@gmail.com
0001-v1-Force-autovacuum-to-use-bootstrap-superuser.patch
Description: 0001-v1-Force-autovacuum-to-use-bootstrap-superuser.patch
## 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 ~]$
