Date: Sat, 11 Feb 2017 17:06:17 -0500
From: 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
Message-ID: <20170211220617.GG9812@tamriel.snowman.net>
References: <3534542.o3cNaKiDID@techfox>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="pGTW/KQBa/3AO71v"
Content-Disposition: inline
In-Reply-To: <3534542.o3cNaKiDID@techfox>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org


--pGTW/KQBa/3AO71v
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Frank van Vugt (ftm.van.vugt@foxi.nl) wrote:
> I noticed the following and wondered whether this is intentional or an=20
> oversight in pg_dump's '-c' option?
>=20
> The clean option causes the public schema to be dropped and recreated, bu=
t=20
> this is done with the default schema priviliges, which are not the same a=
s the=20
> 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
=66rom 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'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

--pGTW/KQBa/3AO71v
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

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

iQIcBAEBCgAGBQJYn4rZAAoJEO1sijiDR2RV1eYQALjajUgXdkmza7NucAkM1QuJ
uAb3ox0O4L56oNX6+SqY6voLcviYMQvIIfT1gsgOhOjkUguNuaV875Toq/xrfYLt
7LUDNAVeaKH6ska/vG1aRPTQWqqWcnZfR0TBpAToDj5LLMRnte1N0clMDasXKXBL
ff8iDPVWGMtqjBNy13KpdvDR3/S+Tm9hj3VfTKhGVfFcHSx8Jx3QzIHdBzrMyZkg
9OVzKCMrZwtp+VLOj2mot8YS2vnVRg4zS7qSn9ScWMnyejGClkZSKmxAnFMUk1DC
Lf5Wft4R+OwOICCUo9YaAmuq6T3OcmOn+Nt8AH+cGI4TtaXfjReYMNS4I+4bwfRo
rvcGHRD55OcP+X2nlX7LEp/fybD7mqX3JPEVYD1BAcW7FVyVJcYoJl5q3g6YYcYt
zYymqr+v2ig0XgEDNdn949AVmJ3CVTWzd5IvrWXTMnXE3S+1XyriA2oaDOJW1eo1
XmTcFyGWaSPfYEOhbFaSg23T3g643uXfViwyb9jW8QwkN5vwDXmKg/XdH4s7mB8X
9oOdzTY3VKL30l5tbOHtLiBchWJcBnsmaF0EfNTdXrZFAs2XRW4r3ac2tbADoVYI
3ZS3T50/6enzE00Yz+XaXl8P0fB0qeUec/TawwcFksISK+FMkaKUscF9W/FtuQcw
2uELnamEHThio8iCC5WT
=Vp8d
-----END PGP SIGNATURE-----

--pGTW/KQBa/3AO71v--