Hello hackers,
I noticed that in some situations involving the use of REVOKE ON SCHEMA,
pg_dump
can produce a dump that cannot be restored. This prevents successful
pg_restore (and by corollary, pg_upgrade).
An example shell script to recreate this problem is attached. The error
output appears at the end like this:
<snippet>
+ pg_restore -d postgres /tmp/foo.dmp
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2748; 0 0 ACL TABLE
mytable owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: permission
denied for schema private
Command was: GRANT SELECT ON TABLE private.mytable TO privileged WITH
GRANT OPTION;
SET SESSION AUTHORIZATION privileged;
GRANT SELECT ON TABLE private.mytable TO enduser WITH GRANT OPTION;
RESET SESSION AUTHORIZATION;
WARNING: errors ignored on restore: 1
-bash-4.2$
</snippet>
Note that `privileged` user needs to grant permissions to `enduser`, but
can no longer do so because `privileged` no longer has access to the
`private` schema (it was revoked).
How might we fix up pg_dump to handle these sorts of situations? It seems
like pg_dump might need extra logic to GRANT the schema permissions to the
`privileged` user and then REVOKE them later on?
Thanks for looking,
--Richard
#!/bin/bash
set -x
# Clean
pg_ctl -D /tmp/data -mi stop
pg_ctl -D /tmp/data2 -mi stop
rm -rf /tmp/data
rm -rf /tmp/data2
# Initdb
initdb -D /tmp/data
# Start
pg_ctl -D /tmp/data -l /tmp/logfile start
# Create database
createdb foo
# Create users
psql -c "create role owneruser with login" foo
psql -c "create role privileged with login" foo
psql -c "create role enduser with login" foo
# Grant schema permission
psql -c "create schema private" foo
psql -c "alter schema private owner to owneruser" foo
psql -U owneruser -c "grant usage, create on schema private to privileged with grant option" foo
psql -U privileged -c "grant usage, create on schema private to enduser with grant option" foo
# Create table
psql -U owneruser -c "create table private.mytable (id int, name text)" foo
# Grant table permission
psql -U owneruser -c "grant select on private.mytable to privileged with grant option" foo
psql -U privileged -c "grant select on private.mytable to enduser with grant option" foo
# Revoke schema permission
psql -c "revoke usage, create on schema private from privileged cascade" foo
# Dump globals
pg_dumpall -g > /tmp/globals.dmp
# Dump database
pg_dump -Fc -C foo > /tmp/foo.dmp
# Shut down
pg_ctl -D /tmp/data -mf stop
# Init new cluster
initdb -D /tmp/data2
# Start new cluster
pg_ctl -D /tmp/data2 -l /tmp/logfile2 start
# Load globals
psql -f /tmp/globals.dmp
# Restore database
pg_restore -d postgres /tmp/foo.dmp
#### Failure output looks like this ####
## + pg_restore -d postgres /tmp/foo.dmp
## pg_restore: [archiver (db)] Error while PROCESSING TOC:
## pg_restore: [archiver (db)] Error from TOC entry 2748; 0 0 ACL TABLE mytable owneruser
## pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for schema private
## Command was: GRANT SELECT ON TABLE private.mytable TO privileged WITH GRANT OPTION;
## SET SESSION AUTHORIZATION privileged;
## GRANT SELECT ON TABLE private.mytable TO enduser WITH GRANT OPTION;
## RESET SESSION AUTHORIZATION;
##
## WARNING: errors ignored on restore: 1
## -bash-4.2$
#### End snippet ####
