Date: Mon, 13 Feb 2017 10:52:24 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Frank van Vugt <ftm.van.vugt@foxi.nl>, pgsql-general@postgresql.org
Subject: Re: intentional or oversight? pg_dump -c does not restore
 default priviliges on schema public
Message-ID: <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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="GMq1OpGuLUulEJi3"
Content-Disposition: inline
In-Reply-To: <05965a0f-6ee0-07ed-cf96-2868c5d8cd6e@aklaver.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org


--GMq1OpGuLUulEJi3
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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.
>=20
> 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.

Thanks!

Stephen

--GMq1OpGuLUulEJi3
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYodY4AAoJEO1sijiDR2RVFN4P/jChoHZD2nNY+ppPFJ1ul45D
abeprAHgZ2PnIw5eZpr4VcDTbdUU5RGRZDS/ceiBu5hokQQyw9yDFbC30xtg7eT2
Zy/zixobsFppN68z0xwSblmKXrYd5WQPSzxL24rq8tB/NRkS4ZvVWpbijqZNMAKN
XX0foc1kzhw+RDuASWPVE/C2/VwQQNryzqIa4g+4jZteYzeYcSeSqs5S30DCiDiW
5nQWwHu6L5grOuvVyoDpgemO3bI7pJXXnvR12C+zX4blrrpEXsqfISTBis1RCHQ7
ZRmoOD4MXz0hx/JN/UwCFTtGRMb1ys5kKYOQD2hzN0vT2blYpNbmf7lOmhqqIpuB
mgUQa3R1MhU1KoV9mhWJ+dZw/NJC1esr80Dqcq7l3hromleB1staTqRRSp4InV05
2iIMmhEo/uJm0gJUjf3dUUNcHu43cjHTMNYb6uC8jrBjLwoU07O+kfY4Xc8eUsk1
2krIUafdJtsjo0qP0eRbW3QQ6lqjtzKNgWUaNBQh0Yq170WLtmIdhgkGQlbOSQGj
eZmq0nKZFqEv5LjrzlgKI5KFx0gH6sW26aDWy0kwygMM+T/SCaQ6gDk4VVs80iUv
5w4vJ5N/cQX9TJ4Iw8lH18FrH/bKamScibJXV7Gn5PwQ11+D7Nc6Hzi/XUS4MEL+
nHE6xOyHhCNsUFFISOhF
=PHVU
-----END PGP SIGNATURE-----

--GMq1OpGuLUulEJi3--