pg_dump issue with renamed system schemas

Started by Nathan Bossartabout 6 years ago2 messageshackers
Jump to latest
#1Nathan Bossart
nathandbossart@gmail.com

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
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nathan Bossart (#1)
Re: pg_dump issue with renamed system schemas

"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