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 1pFRjj-0005mH-NW for pgsql-hackers@arkaria.postgresql.org; Wed, 11 Jan 2023 03:25:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFRjh-0001IC-Ja for pgsql-hackers@arkaria.postgresql.org; Wed, 11 Jan 2023 03:25:09 +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 1pFRjh-0001I2-AP for pgsql-hackers@lists.postgresql.org; Wed, 11 Jan 2023 03:25:09 +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 1pFRje-0007Uj-SH for pgsql-hackers@postgresql.org; Wed, 11 Jan 2023 03:25:08 +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 30B3P4Yu408090; Tue, 10 Jan 2023 22:25:04 -0500 From: Tom Lane To: Robert Haas cc: "pgsql-hackers@postgresql.org" Subject: Re: pgsql: Add new GUC createrole_self_grant. In-reply-to: References: <399118.1673401630@sss.pgh.pa.us> <404209.1673404807@sss.pgh.pa.us> Comments: In-reply-to Robert Haas message dated "Tue, 10 Jan 2023 22:10:31 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <408088.1673407504.1@sss.pgh.pa.us> Date: Tue, 10 Jan 2023 22:25:04 -0500 Message-ID: <408089.1673407504@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Robert Haas writes: > On Tue, Jan 10, 2023 at 9:40 PM Tom Lane wrote: >> The scenario I'm worried about could be closed, mostly, if we were willing >> to invent an intermediate GUC privilege level "can be set interactively >> but only by CREATEROLE holders" ("PGC_CRSET"?). > Of course, if it's possible for a non-CREATEROLE user to set the value > that a CREATEROLE user experiences, that'd be more of a problem -- That's exactly the case I'm worried about, and it's completely reachable if a CREATEROLE user makes a SECURITY DEFINER function that executes an affected GRANT and is callable by an unprivileged user. Now, that probably isn't terribly likely, and it's unclear that there'd be any serious security consequence even if the GRANT did do something different than the author of the SECURITY DEFINER function was expecting. Nonetheless, I'm feeling itchy about this chain of assumptions. > To answer your question directly, though, I don't know of any other > setting where that would be a useful level. Yeah, I didn't think of one either. Also, even if we invented PGC_CRSET, it can't stop one CREATEROLE user from attacking another one, assuming that there is some interesting attack that can be constructed here. I think the whole point of your recent patches is to not assume that CREATEROLE users are mutually trusting, so that's bad. Bottom line is that a GUC doesn't feel like the right mechanism to use. What do you think about inventing a role property, or a grantable role that controls this behavior? regards, tom lane