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

On 02/13/2017 07:52 AM, Stephen Frost wrote:
> Greetings,
>
> * Adrian Klaver (adrian(dot)klaver(at)aklaver(dot)com) wrote:
>> On 02/13/2017 06:04 AM, Stephen Frost wrote:
>>> * Adrian Klaver (adrian(dot)klaver(at)aklaver(dot)com) wrote:
>>>> I am following this up to the point of not understanding what
>>>> exactly changed between 9.5 and 9.6. Namely 9.5 does include the
>>>> default ACL's in the dump output and 9.6 does not.
>>>
>>> Quite a bit in pg_dump changed, but the relevant bit here is that we now
>>> try to include in the pg_dump output any ACLs which have been changed
>> >from their initdb-time settings for initdb-time objects. What that
>>> means is that if you don't change the privileges for the public schema
>> >from what they're set to at initdb-time, then we don't dump out any ACL
>>> commands for the public schema. That ends up being incorrect in '-c'
>>> mode because we drop the public schema in that mode and recreate it, in
>>> which case we need to re-implement the ACLs which existed for the public
>>> schema at initdb-time.
>>
>> Thanks for the explanation in this post and your previous one. If I
>> am following pg_init_privs is the initial state of objects ACLs and
>> if that changes then those entries are removed.
>
> No, if the object is *dropped* then the entry is removed from
> pg_init_privs. Otherwise, the entries in pg_init_privs aren't changed.
>
>> So would not the
>> general case be, on recreating an object use the ACLs in
>> pg_init_privs if they exist otherwise use the ACLs as they exist
>> wherever they go to on change away from pg_init_privs?
>
> pg_init_privs doesn't track the object's name, so this isn't actually
> possible. Even if we did track the name of the object, I don't think
> we'd actually want to set the privileges to what they were set to at
> initdb time. If you drop the public schema and then recreate it, are
> you really expecting it to get the initdb-time privileges it had..?
> How would you reconsile that with default privileges (which we don't
> have for schemas right now, but it's been proposed...).
>
> This case is about a pg_dump, which is a very different case in that we
> want to recreate the state of the system as it existed at the time of
> the dump.
>
>> I gather that
>> is what you are proposing as a special case for the public schema.
>> Just wondering why it should not be the general case?
>
> Not quite.. This is about what pg_dump does when a -c is used.
> Unfortunately, it's *already* doing something special with the public
> schema (if it wasn't, then this wouldn't really be an issue..). This is
> just about making it do the right thing in that already-existing
> special-case.

Alright I see now, thanks.

>
> Thanks!
>
> Stephen
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com