Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nR0IB-0002lR-Lu for pgsql-hackers@arkaria.postgresql.org; Sun, 06 Mar 2022 23:27:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nR0IA-0003VJ-4R for pgsql-hackers@arkaria.postgresql.org; Sun, 06 Mar 2022 23:27:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nR0I9-0003VA-R9 for pgsql-hackers@lists.postgresql.org; Sun, 06 Mar 2022 23:27:57 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nR0I3-0002ir-BT for pgsql-hackers@postgresql.org; Sun, 06 Mar 2022 23:27:57 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 226NRhJT092486; Sun, 6 Mar 2022 18:27:43 -0500 From: Tom Lane To: Mark Dilger cc: Joshua Brindle , Andrew Dunstan , Robert Haas , Jeff Davis , PostgreSQL-development , Joe Conway Subject: Re: Granting SET and ALTER SYSTE privileges for GUCs In-reply-to: <78889A65-CA7A-4015-866D-33460967071D@enterprisedb.com> References: <3D691E20-C1D5-4B80-8BA5-6BEB63AF3029@enterprisedb.com> <9DB8DABC-0E1E-4AB0-BB11-9BC48E4C71BE@enterprisedb.com> <2D6C1081-DB7D-4260-8987-5B4912E95917@enterprisedb.com> <1A6DA47B-2D5F-427E-AD72-1D8BD23BF94C@enterprisedb.com> <79685.1646604824@sss.pgh.pa.us> <83814.1646607430@sss.pgh.pa.us> <78889A65-CA7A-4015-866D-33460967071D@enterprisedb.com> Comments: In-reply-to Mark Dilger message dated "Sun, 06 Mar 2022 15:06:43 -0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <92484.1646609263.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sun, 06 Mar 2022 18:27:43 -0500 Message-ID: <92485.1646609263@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Mark Dilger writes: > On Mar 6, 2022, at 2:57 PM, Tom Lane wrote: >> I don't think this is materially different from what we do with >> permissions on (say) functions. If you want to revoke the public >> SET privilege on some USERSET variable, you instantiate the default >> and then revoke. You end up with an empty ACL stored in pg_setting_acl= , >> and voila. > I assume you mean the implementation of REVOKE does this, not that the u= ser needs to do both a grant and a revoke. Right. Again, look at what happens when you create a function and then revoke its default PUBLIC EXECUTE permission. >> It'd likely be necessary to refuse to record a grant/revoke on >> an unknown GUC, since if we don't know the GUC then we can't know >> what the relevant default ACL ought to be. But I bet your existing >> patch has some dubious behavior in that case too. > The existing patch allows grants on unknown gucs, because it can't know = what guc an upgrade script will introduce, and the grant statement may nee= d to execute before the guc exists. Yeah, that's the problematic case. It might mostly work to assume that an unknown GUC has an empty default ACL. This could fail to retain the default PUBLIC SET permission if it later turns out the GUC is USERSET ... but I suspect in most cases anybody who's messing with the permissions would've started out by revoking that anyway. We could make this definitely work in pg_dump if we teach pg_dump to explicitly grant or revoke the PUBLIC SET permission anytime it's emitting anything for a GUC, even if it thinks that would be the default state anyway. Extension scripts that want to modify permissions for their GUCs could follow that same principle to be sure. regards, tom lane