Re: intentional or oversight? pg_dump -c does not restore default priviliges on schema public

Frank,

* Frank van Vugt (ftm(dot)van(dot)vugt(at)foxi(dot)nl) wrote:
> Op zaterdag 11 februari 2017 15:28:55 schreef Tom Lane:
> > I'm inclined to argue that it was a mistake to include any non-pinned
> > objects in pg_init_privs.
> <cut>
> > We might need to fix pg_dump too, but I think these entries in
> > pg_init_privs should simply not be there.
>
> Thanks for picking this up, I'll probably see this subject pop up on hackers
> and/or committers at some point ;)

We should be able to get it addressed shortly.

> Allow me to emphasize that this issue basically means that for v9.6 after
> restoring a dump created with the '-c' option one ends up in a situation that
> might be quite confusing for users that didn't have to pay much attention yet
> to handling priviliges... i.e. trying even a plain select on table_a in the
> public schema as a non-system user returns something like:
> ERROR: relation "table_a" does not exist

Yes, it's unfortunate that many users aren't really familiar with
schema-level privileges.

For your specific case, if you drop/recreate the public schema in the
system that you're dumping the data from, and then set the ACLs to what
you want, they should be dumped out, even with a pg_dump -c. It's only
when you're using -c with the initdb-time public schema, and initdb-time
ACLs, that the issue arises.

Thanks!

Stephen