dump cannot be restored if schema permissions revoked
От | Richard Yen |
---|---|
Тема | dump cannot be restored if schema permissions revoked |
Дата | |
Msg-id | CAKH4vDgKvcKHmzj0=omYaaxCwbA5sMO-1P1+k0LLLL4bTsWAAg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: dump cannot be restored if schema permissions revoked
|
Список | 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).
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
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$
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
Вложения
В списке pgsql-hackers по дате отправления: