Обсуждение: pg_dump issue with renamed system schemas

Поиск
Список
Период
Сортировка

pg_dump issue with renamed system schemas

От
"Bossart, Nathan"
Дата:
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


Вложения

Re: pg_dump issue with renamed system schemas

От
Tom Lane
Дата:
"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

https://www.postgresql.org/message-id/flat/f85991ad-bbd4-ad57-fde4-e12f0661dbf0@postgrespro.ru

AFAICT we didn't think we'd found a satisfactory solution yet.

            regards, tom lane