Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izBcZ-0007Gf-U7 for pgsql-bugs@arkaria.postgresql.org; Wed, 05 Feb 2020 03:45:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1izBcW-0007Hh-QQ for pgsql-bugs@arkaria.postgresql.org; Wed, 05 Feb 2020 03:44:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izBcW-0007D2-Ht for pgsql-bugs@lists.postgresql.org; Wed, 05 Feb 2020 03:44:56 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1izBcS-0002fn-Ux for pgsql-bugs@lists.postgresql.org; Wed, 05 Feb 2020 03:44:55 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 77CBD5F7A7; Tue, 4 Feb 2020 22:44:54 -0500 (EST) Date: Tue, 4 Feb 2020 22:44:54 -0500 From: Stephen Frost To: Tom Lane Cc: Alvaro Herrera , Ferdi Smit , Floris Van Nee , "pgsql-bugs@lists.postgresql.org" Subject: Re: incorrect pg_dump output due to not handling dropped roles correctly Message-ID: <20200205034454.GU3195@tamriel.snowman.net> References: <20191203202745.GR6962@tamriel.snowman.net> <20200117212624.GA7872@alvherre.pgsql> <32560.1580327205@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="TNi7gLfRnzrB+FaZ" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --TNi7gLfRnzrB+FaZ Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Stephen Frost (sfrost@snowman.net) wrote: > On Wed, Jan 29, 2020 at 14:46 Tom Lane wrote: > > Stephen Frost writes: > > > In particular- should we really be setting default privileges for obj= ects > > > that are being created by an extension, at all? The more I think abo= ut > > it, > > > the less I like it, particularly when we start to think about the tru= sted > > > extension work that Tom=E2=80=99s doing. > > > > > In short, I=E2=80=99m leaning towards the argument that this was a mi= ssing check > > in > > > the default ACL code to explicitly *not* add default privileges on > > objects > > > that are being created by an extension. With such a check, the entire > > > wouldn=E2=80=99t end up in pg_init_privs and the issue that started t= his thread > > > wouldn=E2=80=99t happen. > > > > Hmm. I think we'd still wish pg_upgrade to preserve whatever privileges > > have been granted on extension objects, but that doesn't necessarily > > have to happen through pg_init_privs. I've forgotten the details --- > > would the change you suggest be enough to make upgrade work correctly, > > or would we need additional hacking? >=20 > Err, privileges on upgrade *are* preserved, even with this. Guess I wasn= =E2=80=99t > clear. >=20 > Right now: >=20 > If someone sets up default privileges for a user, and that user then > creates an extension, the default privs are applied to all of the > individual objects that are created by the extension, and then get record= ed > in pg_init_privs, as if it was the extension setting up those privileges. >=20 > Having those privileges end up in pg_init_privs is pretty clearly wrong > because only those privileges that are installed by the extension should > end up in init privs, because all OTHER privileges are what will be dumped > out. >=20 > However, I=E2=80=99m not sure that those objects, which are inside the ex= tension, > should really be having default privs applied to them in the first place! > If I have default privs set up to grant EXECUTE to some user on functions, > and then I install pg adminpack, is it really right for all the functions > in pg adminpack to now have execute rights granted to whomever this user > is- even when the extension revoked the grant to public because the > function is unsafe for general users to have access to..? >=20 > I feel like default privs should apply when the user is EXPLICITLY creati= ng > a specific object, not when it=E2=80=99s being created as part of an exte= nsion, and > therefore we should have a check in the creation system, where the default > privs are handled, to basically make the default privs code be a no-op wh= en > the object is being created by an extension. Users will have to go grant > access to those objects in the extension explicitly instead, but that see= ms > perfectly reasonable to me. >=20 > Currently traveling and so not actually looking at the code, but that=E2= =80=99s my > recollection of things. Back from said travel now and reviewed the thread and discussion and this looks like exactly what's happening- so, does someone want to comment on this, or..? Specifically, if one does: postgres=3D# create role test; CREATE ROLE postgres=3D# alter default privileges in schema public grant all privileges= on tables to test; ALTER DEFAULT PRIVILEGES postgres=3D# create extension pg_stat_statements; CREATE EXTENSION Should the pg_stat_statements view, that's actually created by the extension as part of its install, end up with the default privileges of "grant all privileges on tables to test"? At this point, my vote is 'no, that is a bug that we should fix.', but it's also clearly a behavior change and maybe others have a different opinion..? If not, it should at least be a relatively easy fix.. Thanks, Stephen --TNi7gLfRnzrB+FaZ Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJeOjo2AAoJEO1sijiDR2RV8gIP/j2Sz5n8fQ/CfeK55/GrOhwL ocTXttu7scmJldZEWQlmK12w2y/wgpqc70FmqHin18bskEO4JmLk59oCOEWpSsdJ ccLBgY3BjNQBf8QMq05ceHhQOaEwjMNC/ZmruY9+TQLxt+kbIQRETmG+2eEeOOHq sNMcSfSFgHadni/oxqll49XwAJGDzotAxozqfQCSR1OhuPSpyTx1dLk+fV30w/JN VGHPLkVKZc3/QyKQcFV6Yxtgysm5XFyhSfkCZ9vl9LYStTJRFTzfCimwWwBRQCWH KkkSrJPg8yC/dY+NVFylFXoy4K2WNwo4Ne8MMoTEPaTWH+lxv7J8KyDW8QM8zvJy Y3tN16/mrqW8aZ2HVwhvEb5HLtnkuRY5mpBKEk1SBvVToxFDmZGx85FnxbRE8H8x Df/assd6uWY1Ig6P0HB5DB9ArQLeYyxxixohfxXM65PfP8r0fVHwpeBB/lA1eB8q ouHSy+cbQ2bXgrvuiecTTD06OY9GN7NSUnRVd5j/7loO0ZzGgJPpvfWPPV2Kaj1O S9NeCgWzWHS/ulH4klzUQHMn5ilseR4cTVZ08zBIl4lpe8yUQvop5kn/Da2ocMz1 sCD4UIpr4N0DIz7UjN6K1Jo/liF7MM3takFvwmAZjG2njz3QNCOYlFK0ai7+zRKx BEfGu0PDynHK10z0CfE/ =0INu -----END PGP SIGNATURE----- --TNi7gLfRnzrB+FaZ--