From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Stephen Frost <sfrost@snowman.net>
To: 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: Sat, 11 Feb 2017 15:38:09 -0800
Message-ID: <9f29583d-4a82-55e7-619a-5a774dbabd8d@aklaver.com> (raw)
In-Reply-To: <20170211220617.GG9812@tamriel.snowman.net>
References: <3534542.o3cNaKiDID@techfox>
<20170211220617.GG9812@tamriel.snowman.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
On 02/11/2017 02:06 PM, Stephen Frost wrote:
> Greetings,
>
> * Frank van Vugt (ftm.van.vugt@foxi.nl) wrote:
>> I noticed the following and wondered whether this is intentional or an
>> oversight in pg_dump's '-c' option?
>>
>> The clean option causes the public schema to be dropped and recreated, but
>> this is done with the default schema priviliges, which are not the same as the
>> ones assigned during create database:
>
> Interesting. The reason this happens is that the privileges for the
> public schema aren't dumped when they are the same as what you would get
> from a default install in 9.6+, but using -c will end up dropping and
> recreating it, which, as you note, will end up having different
> privileges than the default install because they'll be the regular
> default privilegs of "nothing" for schemas.
>
> This is happening just for the public schema due to how it's handled in
> a special way in pg_dump_archive.c:_printTocEntry(). This only impacts
> ACLs because those are the only things which are different for the
> public schema vs. it's initdb settings (there's no SECURITY LABEL, for
> example, on the initdb'd public schema).
>
> Due to how the public schema is (and always has been) handled in this
> special way, this is a bug which needs to be fixed by having the default
> ACLs for the public schema included in the dump output if -c is being
> used.
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.
>
> I'm not seeing a very simple answer for this, unfortunately. I'm
> thinking we're going to need to pull the public schema's permissions
> differently if we're in clean mode (by comparing to the default schema
> privileges) vs. when we're not (in which case we should be comparing to
> *public*'s initdb-time privileges, as we do now). One option would be
> to handle that by hacking up buildACLQueries() to take a flag which
> basically means "we are dropping the public schema, do not consider its
> pg_init_privs settings" but that strikes me as awful grotty. Another
> option would be to change getNamespaces() to run a special query
> (perhaps as a UNION-ALL combination with the existing query) that is
> just to get the info for the 'public' schema (and exclude the 'public'
> schema from the first half of the query, of course).
>
> Thanks for the report!
>
> 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: <9f29583d-4a82-55e7-619a-5a774dbabd8d@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