Subject: Re: intentional or oversight? pg_dump -c does not restore
 default priviliges on schema public
To: Stephen Frost <sfrost@snowman.net>
References: <3534542.o3cNaKiDID@techfox>
 <20170211220617.GG9812@tamriel.snowman.net>
 <9f29583d-4a82-55e7-619a-5a774dbabd8d@aklaver.com>
 <20170213140452.GI9812@tamriel.snowman.net>
 <05965a0f-6ee0-07ed-cf96-2868c5d8cd6e@aklaver.com>
 <20170213155224.GO9812@tamriel.snowman.net>
Cc: Frank van Vugt <ftm.van.vugt@foxi.nl>, pgsql-general@postgresql.org
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <c5b4b677-83e3-81a0-67bf-6f31420fe2d2@aklaver.com>
Date: Mon, 13 Feb 2017 08:25:01 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.7.0
MIME-Version: 1.0
In-Reply-To: <20170213155224.GO9812@tamriel.snowman.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

On 02/13/2017 07:52 AM, Stephen Frost wrote:
> Greetings,
>
> * Adrian Klaver (adrian.klaver@aklaver.com) wrote:
>> On 02/13/2017 06:04 AM, Stephen Frost wrote:
>>> * Adrian Klaver (adrian.klaver@aklaver.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.klaver@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general