## 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 ~]$