dump cannot be restored if schema permissions revoked

From: Richard Yen <richyen3(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: dump cannot be restored if schema permissions revoked
Date: 2021-04-07 17:13:30
Message-ID: CAKH4vDgKvcKHmzj0=omYaaxCwbA5sMO-1P1+k0LLLL4bTsWAAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
permissions_test.sh text/x-sh 2.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jehan-Guillaume de Rorthais 2021-04-07 17:19:21 Re: multi-install PostgresNode fails with older postgres versions
Previous Message Andres Freund 2021-04-07 17:09:54 Re: Minimal logical decoding on standbys