pg_dump issue with renamed system schemas

From: "Bossart, Nathan" <bossartn(at)amazon(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_dump issue with renamed system schemas
Date: 2020-04-10 20:14:58
Message-ID: CC85E9B1-BBE2-4F49-ADCB-172CF7D1DE4E@amazon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Do-not-consider-pg_init_privs-entries-when-dumpin.patch application/octet-stream 2.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-04-10 20:19:56 Re: Properly mark NULL returns in numeric aggregates
Previous Message Tom Lane 2020-04-10 20:13:18 Re: pg_validatebackup -> pg_verifybackup?