From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Frank van Vugt <ftm.van.vugt@foxi.nl>
Cc: pgsql-general@postgresql.org
Subject: Re: intentional or oversight? pg_dump -c does not restore default priviliges on schema public
Date: Mon, 13 Feb 2017 08:25:01 -0800
Message-ID: <c5b4b677-83e3-81a0-67bf-6f31420fe2d2@aklaver.com> (raw)
In-Reply-To: <20170213155224.GO9812@tamriel.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, sfrost@snowman.net, ftm.van.vugt@foxi.nl
Subject: Re: intentional or oversight? pg_dump -c does not restore default priviliges on schema public
In-Reply-To: <c5b4b677-83e3-81a0-67bf-6f31420fe2d2@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox