Re: dump cannot be restored if schema permissions revoked

On Wed, Apr 07, 2021 at 10:13:30AM -0700, Richard Yen wrote:
> 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?

I would approach this by allowing GRANT to take a grantor role name. Then,
we'd remove the SET SESSION AUTHORIZATION, and the user running the restore
would set the grantor. "GRANT SELECT ON TABLE foo TO bob GRANTED BY alice;"
looks reasonable to me, though one would need to check if SQL requires that to
have some different behavior.

> It seems
> like pg_dump might need extra logic to GRANT the schema permissions to the
> `privileged` user and then REVOKE them later on?

That could work, but I would avoid it for a couple of reasons. In some
"pg_restore --use-list" partial restores, the schema privilege may already
exist, and this design may surprise the DBA by removing the existing
privilege. When running a restore as a non-superuser, the additional
GRANT/REVOKE could be a source of permission denied failures.