pg_dump issue with renamed system schemas
Hi hackers,
I think I've found a small bug in pg_dump that could cause some schema
privileges to be missed. In short, if you've renamed a schema that
has an entry in pg_init_privs, pg_dump will skip dumping the initial
ACL for the schema. This results in missing privileges on restore.
I've attached a small patch with a test case to handle this. This
patch fixes the problem by adjusting the LEFT JOIN on pg_init_privs to
only match for schemas that match the default system names. I've only
included 'public' and 'pg_catalog' for now, since AFAICT those are the
only two system schemas with corresponding pg_init_privs entries for
which pg_dump dumps ACLs. Also, I haven't attempted to handle the
case where an extension schema with a pg_init_privs entry has been
renamed. Perhaps a sturdier approach would be to adjust the way
pg_init_privs is maintained, but that might be too invasive.
Even with this patch, I think there are still some interesting corner
cases involving the 'public' schema (e.g. recreating it, changing its
ownership). I don't know if it's worth trying to address all these
corner cases with special system schemas, but the first one I
mentioned seemed simple enough to fix.
Nathan
Attachments:
v1-0001-Do-not-consider-pg_init_privs-entries-when-dumpin.patchapplication/octet-stream; name=v1-0001-Do-not-consider-pg_init_privs-entries-when-dumpin.patchDownload+21-2
"Bossart, Nathan" <bossartn@amazon.com> writes:
I think I've found a small bug in pg_dump that could cause some schema
privileges to be missed. In short, if you've renamed a schema that
has an entry in pg_init_privs, pg_dump will skip dumping the initial
ACL for the schema. This results in missing privileges on restore.
This seems like a special case of the issue discussed in
/messages/by-id/f85991ad-bbd4-ad57-fde4-e12f0661dbf0@postgrespro.ru
AFAICT we didn't think we'd found a satisfactory solution yet.
regards, tom lane