BUG #17718: Unexpected REVOKE ALL permissions in the backup script - Mailing list pgsql-bugs
From | PG Bug reporting form |
---|---|
Subject | BUG #17718: Unexpected REVOKE ALL permissions in the backup script |
Date | |
Msg-id | 17718-f2d102527fc03c7a@postgresql.org Whole thread Raw |
List | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17718 Logged by: Kirill Kravtsov Email address: kravtsov.k@gmail.com PostgreSQL version: 14.5 Operating system: Centos 7 Description: Hello, I’m facing an unexpected behaviour in pg_dump when used after issuing a REASSIGN OWNED statement in the database. For the problem to appear, one need to create an extension that creates database objects as a specific user and then issue REASSIGN OWNED from that user to another. There are two distinct problems that I observe when running pg_dump against that database: 1) The extension-generated objects now emit REVOKE ALL ... FROM "user" where user is the role that originally created the extension. This creates a dependency that shouldn't exist since all the permissions are supposed to be reassigned to a new user. 2) If the user is dropped, the statement now mentions the role OID instead of role name. Having those dependencies in the backup script breaks some of the automation by generating statements that cause a non-zero exit code when restoring the DB. I don't want to ignore the exit code in this scenario, and I don't have an ability to exclude those objects from the backup, so I would appreciate if someone could look into this. How to reproduce: # setup psql << EOF CREATE ROLE testuser; CREATE ROLE testsuper SUPERUSER; CREATE DATABASE db1 OWNER testuser TEMPLATE template0; EOF # creating an extension that creates system objects in the DB psql -d db1 -c "SET ROLE testsuper; CREATE EXTENSION pg_buffercache" # reassign owned from super to regular psql -d db1 -c "REASSIGN OWNED BY testsuper TO testuser" # observe references to the super still exist pg_dump -d db1 | grep 'REVOKE ALL' # display the OID of the super role psql -c "SELECT oid FROM pg_roles where rolname = 'testsuper'" # drop super role psql -c "DROP ROLE testsuper" # observe oid reference in the script pg_dump -d db1 | grep 'REVOKE ALL' # cleanup psql << EOF DROP DATABASE IF EXISTS db1; DROP ROLE IF EXISTS testuser; DROP ROLE IF EXISTS testsuper; EOF Sample output: $ # setup $ psql << EOF > CREATE ROLE testuser; > CREATE ROLE testsuper SUPERUSER; > CREATE DATABASE db1 OWNER testuser TEMPLATE template0; > EOF CREATE ROLE CREATE ROLE CREATE DATABASE $ # creating an extension that creates system objects in the DB $ psql -d db1 -c "SET ROLE testsuper; CREATE EXTENSION pg_buffercache" CREATE EXTENSION $ # reassign owned from super to regular $ psql -d db1 -c "REASSIGN OWNED BY testsuper TO testuser" REASSIGN OWNED $ # observe references to the super still exist $ pg_dump -d db1 | grep 'REVOKE ALL' REVOKE ALL ON FUNCTION public.pg_buffercache_pages() FROM testsuper; REVOKE ALL ON FUNCTION public.pg_buffercache_pages() FROM pg_monitor; REVOKE ALL ON TABLE public.pg_buffercache FROM testsuper; $ # display the OID of the super role $ psql -c "SELECT oid FROM pg_roles where rolname = 'testsuper'" oid -------- 258313 (1 row) $ $ # drop super role $ psql -c "DROP ROLE testsuper" DROP ROLE $ $ # observe oid reference in the script $ pg_dump -d db1 | grep 'REVOKE ALL' REVOKE ALL ON FUNCTION public.pg_buffercache_pages() FROM "258313"; REVOKE ALL ON FUNCTION public.pg_buffercache_pages() FROM pg_monitor; REVOKE ALL ON TABLE public.pg_buffercache FROM "258313"; $ $ # cleanup $ psql << EOF > DROP DATABASE IF EXISTS db1; > DROP ROLE IF EXISTS testuser; > DROP ROLE IF EXISTS testsuper; > EOF DROP DATABASE DROP ROLE NOTICE: role "testsuper" does not exist, skipping DROP ROLE
pgsql-bugs by date: