Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-general-owner+M228756=pgsql-general-arkari@postgresql.org>)
	id 1cdHNS-0007iM-8v
	for pgsql-general@arkaria.postgresql.org; Mon, 13 Feb 2017 14:13:14 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-general-owner+M228756=pgsql-general-arkari@postgresql.org>)
	id 1cdHNR-0002Iq-SH
	for pgsql-general@arkaria.postgresql.org; Mon, 13 Feb 2017 14:13:13 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <sfrost@tamriel.snowman.net>)
	id 1cdHL7-0006ZQ-VY
	for pgsql-general@postgresql.org; Mon, 13 Feb 2017 14:10:50 +0000
Received: from tamriel.snowman.net ([2001:4830:167b::11])
	by makus.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <sfrost@tamriel.snowman.net>)
	id 1cdHL1-0004qw-86
	for pgsql-general@postgresql.org; Mon, 13 Feb 2017 14:10:48 +0000
Received: by tamriel.snowman.net (Postfix, from userid 1000)
	id D22BD5F794; Mon, 13 Feb 2017 09:10:42 -0500 (EST)
Date: Mon, 13 Feb 2017 09:10:42 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Frank van Vugt <ftm.van.vugt@foxi.nl>
Cc: pgsql-general@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>,
	Adrian Klaver <adrian.klaver@aklaver.com>
Subject: Re: intentional or oversight? pg_dump -c does not restore
 default priviliges on schema public
Message-ID: <20170213141042.GJ9812@tamriel.snowman.net>
References: <3534542.o3cNaKiDID@techfox>
 <20170211220617.GG9812@tamriel.snowman.net>
 <3026.1486855735@sss.pgh.pa.us>
 <23252987.YLsmlhlehZ@techfox>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="YlOmdgDTFxtsaaW3"
Content-Disposition: inline
In-Reply-To: <23252987.YLsmlhlehZ@techfox>
X-Editor: Vim http://www.vim.org/
X-Info: http://www.snowman.net
X-Operating-System: Linux/3.13.0-91-generic (x86_64)
X-Uptime: 09:06:43 up 219 days, 15:31, 11 users,  load average: 0.36, 0.16,
 0.14
User-Agent: Mutt/1.5.21 (2010-09-15)
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org


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

Frank,

* Frank van Vugt (ftm.van.vugt@foxi.nl) wrote:
> Op zaterdag 11 februari 2017 15:28:55 schreef Tom Lane:
> > I'm inclined to argue that it was a mistake to include any non-pinned
> > objects in pg_init_privs.
> <cut>
> > We might need to fix pg_dump too, but I think these entries in
> > pg_init_privs should simply not be there.
>=20
> Thanks for picking this up, I'll probably see this subject pop up on hack=
ers=20
> and/or committers at some point ;)

We should be able to get it addressed shortly.

> Allow me to emphasize that this issue basically means that for v9.6 after=
=20
> restoring a dump created with the '-c' option one ends up in a situation =
that=20
> might be quite confusing for users that didn't have to pay much attention=
 yet=20
> to handling priviliges... i.e. trying even a plain select on table_a in t=
he=20
> public schema as a non-system user returns something like:
> 	ERROR:  relation "table_a" does not exist

Yes, it's unfortunate that many users aren't really familiar with
schema-level privileges.

For your specific case, if you drop/recreate the public schema in the
system that you're dumping the data from, and then set the ACLs to what
you want, they should be dumped out, even with a pg_dump -c.  It's only
when you're using -c with the initdb-time public schema, and initdb-time
ACLs, that the issue arises.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYob5iAAoJEO1sijiDR2RV00EP/1yMHKTvwynx0yOe6ZvydfgS
6MNLR/A+WQq7lCi5gToHML+ywkTvj39nEVBGrqlmy8kG89L+n6Di1VGoaJ1OEZsb
ypMTfGCKXnjZFV06sKOlv5+WUAvY7FTpdW0sFxwxD7YwbRTrblW6z74mpGGjsvZ4
rWjYN4ZhjuqYnwkaoRz2wqdR58uxd1+7fDY4IUEL/q8R1iZvnWj4A9hMEOcbVvDS
9qYM8Y/WRfxkigFWkOlc/p3rq8SF4cPUxa7UT3GkRNDTWhn9NLfxqaXxVV1YVR5t
u5Y6VRUI2tNnlEdP4cb8BtVV5dbX8hj7z/3Vw8APgIr6xWmfpoa/ibX1MglfM3lZ
Rn050/K0J71fJPJKHk2sWLMSv3Fhx4peTmpcoD1cSx30RV31hZsPp25oTTc5jEcL
PmDQvMkIKr3C60TQaWs7ehL8xPRmRg/2wmwCg0TxOO4QYgIxfQkMSC8uVHTIZa+f
dlUYgN5r/ENNpan06MIY59Z4wOLm8Dc2ZVD3p1zfxKz56QS5JqHrOdm0CnWKqxQy
MgmGq4kkHCC+t7+18DFFfXxWLqZDMO4teXi6qwC5CpbsB6uPXn4Nope4tXwIKvlI
487XJQL+/hCWFCKBRZofZMH1Me4MdUpGA2rAsLXhTNsCBIggGJ5FWsg/ozGc5ewZ
/q/U0wzCeNpBzOAvmluG
=h6Ad
-----END PGP SIGNATURE-----

--YlOmdgDTFxtsaaW3--