Date: Mon, 13 Feb 2017 09:02:07 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
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: <20170213140207.GH9812@tamriel.snowman.net>
References: <3534542.o3cNaKiDID@techfox>
 <20170211220617.GG9812@tamriel.snowman.net>
 <3026.1486855735@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="0yIEv4ciajEKQOWx"
Content-Disposition: inline
In-Reply-To: <3026.1486855735@sss.pgh.pa.us>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org


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

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > I'm not seeing a very simple answer for this, unfortunately.
>=20
> I'm inclined to argue that it was a mistake to include any non-pinned
> objects in pg_init_privs.  The reason initdb leaves some objects unpinned
> is exactly because they can be dropped and recreated, and that means
> that their "initial" privileges are not static system properties.
>=20
> We might need to fix pg_dump too, but I think these entries in
> pg_init_privs should simply not be there.

Wouldn't that mean we then need to drop and recreate all of those
non-pinned objects, with their ACLs, on a -c?

Or are you thinking we would just dump out whatever ACLs they have that
are different from default object creation, meaning that we'd have ACLs
for nearly all the views in pg_catalog and information_schema included
in regular pg_dump output?  Just dumping out the ACLs wouldn't
necessairly get us back to the same state as the database which was
dumped out though as the existing objects might already have some GRANTs
performed on them.

pg_init_privs also handles the initial privileges for extension objects,
though I'm guessing you aren't including those.  The initdb-time objects
are certainly similar though, in some ways, to extensions, the main
difference being that you can't really drop/recreate all of the initdb
time objects.

It seems to me that the oddball here is the public schema, and
specifically, the public schema when the user has been using the
=66rom-initdb public schema (which has initdb-time privileges which are
recorded into pg_init_privs).  Had the user dropped and recreated the
public schema, the initdb-time privs for the original public schema in
pg_init_privs would have been removed, and the new public schema
wouldn't have had any entries in pg_init_privs.  The reason it's an
oddball is that it's the only from-initdb time object which is dropped
and recreated as part of pg_dump -c.  If we dropped all non-pinned
objects and recreated them when running pg_dump -c then perhaps what
you're suggesting would work, but we don't and I don't think that's
really what you were thinking we would do.

While it's a bit grotty, I tend to think the best approach is to
continue to assume that the only from-initdb-time object that the user
actually wants us to drop/recreate on a pg_dump -c is the public schema,
which means we need to handle things a bit differently when working in
-c mode for the public schema when pulling ACLs.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYobxfAAoJEO1sijiDR2RVwy8P/in5dBhqtDWgVeMy+b8UAYPd
p400679YIZnD8G7xQBllTQFXuwJ3pwI6279xd1N1EYRS+9LSlLHDMzJZX8KfasmD
+1HfSyjlVwphMHJm+LS4lUgscTl9AxXS6u4Mcg/u1bYqqxTR27/7QC/qGG9X0KAo
Ya6ntdrfwRKwoi1fh4YWMhJLcym1va3DacLgAoQVyRCeR8q3pf/KTIiY5TvhqKEw
1Tf13RZZgO5cbDTBErCriyqQIFGrOJq9qscTwzrf0Ll1eBR6vW/lb08hI7GzhuZp
cJ12BwzyXe+HvLkN+K/g7YR111dSVxdalielqt88viE+FznsfQsWdzBGVwJu9bC5
iHdRyNzi9wTnFJ381Nx5O13Ffqo4cm4/shlgWibkhHWe4Pq6WQX03lMPDmADGkSV
5jrbyYU+tcooLj5B1pO/7svZ081DkiHABLtI80Kz8WO5v9JG324H/xSLqr7GqxzY
gbkDPEHMrxNZRekzynTZmZ7WHahLPx1fZlaWs5aFqezWWTq2RWJn0HbyezhPavqI
DJCvZSf5eUUqD5oAqxdN7mlNvonm2nXigBZcJ/CGFqW/MxYu8dyP4+UezBCfZ9K/
Qo9maqZgsmuJGNL4zhPAnE9UmzLYKnwXIgWW0ZS4CzurRywid4E5UX3z1NHebcVF
PaAaZEkHM1PqRwz8PDoq
=zT8k
-----END PGP SIGNATURE-----

--0yIEv4ciajEKQOWx--