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 1pY8A2-0003e4-L1 for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Mar 2023 16:21:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pY8A1-0002p7-BD for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Mar 2023 16:21:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pY8A0-0002oy-ME for pgsql-hackers@lists.postgresql.org; Fri, 03 Mar 2023 16:21:33 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pY89x-0007Da-Gu for pgsql-hackers@lists.postgresql.org; Fri, 03 Mar 2023 16:21:31 +0000 Received: by mail-ed1-x531.google.com with SMTP id cw28so12476642edb.5 for ; Fri, 03 Mar 2023 08:21:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677860488; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=aqQMH3gEbhi6/v9Qv75hk5xJWeDiftwQVgICpYEkpO4=; b=DnRpbtFD1u2tCWoXK9F0vsdclbXCkwwg+tCp3FLeymTguKvQ4E6HDLWstyMCbIsajd aT5AFB0MId7QoizwoerYe8YherycGGkxYJ33mcNIV+GZaSByR2W2tgsIZ5iX9CAuUCQJ t7jN+MHI6SOH+WRgPtRmQMQBG78LUQ78WKWFMEU/Z1CfT4PlttwqGay3uRhJXAzvmJhy ibi4QxQh4GPKyBruVZgE8xSy4r+YIfAiq5bj7IrB1YtGQOp7fHClqOoTB63oRQym+0T8 JBM94q1/jbVsYdfs5x8vjDPAxmPJexVjlEZzp8Y7gSyNsm4yQgWXZwgPhlo1Vz4Ho/ca qknQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677860488; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=aqQMH3gEbhi6/v9Qv75hk5xJWeDiftwQVgICpYEkpO4=; b=zx44Tp6FSCRiLn7PVC9Mxn2oSz4msnxFdkIdm3H1ermdRn9G7aovLwICfYK9zvMQNY q0IVZvGCZ5nvV2CwF+0ML4e+/h/7vQeBTns7RUkvvd+KeUuz639Ls6QNuMlNJFw6ubV2 InnM/OIY9hYDCtUmGmwGsf3rs7l+bgTkT+rqJT2RwRlfoAFXUgKlDexwd0AGtsUhtUKu UcJp4OLlX16rb/FJkw+KspAYKaScfVqnqZVzhQgfwo5pxk6wRw7KEOiHEReHOq+VEDsG FmvGfnAwtYQ6TkcaQ9N/yLtTiSY62sCi8QuklTjpSl1rH7qxnIH5ypp9iNqNtlfRNCTx 0kdw== X-Gm-Message-State: AO0yUKWMKIFdh+eaI8lUPXrjIq7aMrHxtQHTHxaUZzMYrpPogMT2msPQ riemrftCYidI6QpdbhtPEKeJ58apJNhVGL8dUdY= X-Google-Smtp-Source: AK7set/cBolI+5HxleMMq1vB4rkMcJa5cH3OTIUMNb441Hd7eODAmH1VsTG3XuA4ei7hdWsuRP9e2wTRJKW9jSY/q/E= X-Received: by 2002:a17:906:a14:b0:8b2:d30:e728 with SMTP id w20-20020a1709060a1400b008b20d30e728mr1151200ejf.1.1677860487793; Fri, 03 Mar 2023 08:21:27 -0800 (PST) MIME-Version: 1.0 References: <01620d6b-9efe-b199-b619-8450d865ec83@postgrespro.ru> <94ce7392-fa90-bc1c-4dc2-677161bbff58@postgrespro.ru> <20824093-d94f-e5d6-b611-8cec7fc6e95e@postgrespro.ru> In-Reply-To: <20824093-d94f-e5d6-b611-8cec7fc6e95e@postgrespro.ru> From: "David G. Johnston" Date: Fri, 3 Mar 2023 09:21:11 -0700 Message-ID: Subject: Re: psql: Add role's membership options to the \du+ command To: Pavel Luzanov Cc: David Zhang , "pgsql-hackers@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000005b8dbb05f60156fc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005b8dbb05f60156fc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Mar 3, 2023 at 4:01=E2=80=AFAM Pavel Luzanov wrote: > Hello, > > On 22.02.2023 00:34, David G. Johnston wrote: > > I didn't even know this function existed. But I see that it was changed i= n > 3d14e171 with updated documentation: > > https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-= ACCESS > Maybe that's enough. > > > I think that should probably have ADMIN as one of the options as well. > Also curious what it reports for an empty membership. > > > I've been experimenting for a few days and I want to admit that this is a > very difficult and not obvious topic. > I'll try to summarize what I think. > > 1. > About ADMIN value for pg_has_role. > Implementation of ADMIN value will be different from USAGE and SET. > To be True, USAGE value requires the full chain of memberships to have > INHERIT option. > Similar with SET: the full chain of memberships must have SET option. > But for ADMIN, only last member in the chain must have ADMIN option and > all previous members > must have INHERIT (to administer directly) or SET option (to switch to > role, last in the chain). > Therefore, it is not obvious to me that the function needs the ADMIN valu= e. > Or you can SET to some role that then has an unbroken INHERIT chain to the administered role. ADMIN basically implies SET/USAGE but it doesn't work the other way around. I'd be fine with "pg_can_admin_role" being a newly created function that provides this true/false answer but it seems indisputable that today there is no core-provided means to answer the question "can one role get ADMIN rights on another role". Modifying \du to show this seems out-of-scope but the pg_has_role function already provides that question for INHERIT and SET so it is at least plausible to extend it to include ADMIN, even if the phrase "has role" seems a bit of a misnomer. I do cover this aspect with the Role Graph pseudo-extension but given the presence and ease-of-use of a boolean-returning function this seems like a natural addition. We've also survived quite long without it - this isn't a new concept in v16, just a bit refined. > > 2. > pg_has_role function description starts with: Does user have privilege fo= r > role? > - This is not exact: function works not only with users, but with > NOLOGIN roles too. > - Term "privilege": this term used for ACL columns, such usage may be > confusing, > especially after adding INHERIT and SET in addition to ADMIN option= . > Yes, it missed the whole "there are only roles now" memo. I don't have an issue with using privilege here though - you have to use the GRANT command which "defines access privileges". Otherwise "membership option" or maybe just "option" would need to be explored. > > 3. > It is possible to grant membership with all three options turned off: > grant a to b with admin false, inherit false, set false; > But such membership is completely useless (if i didn't miss something). > May be such grants must be prohibited. At least this may be documented in > the GRANT command. > I have no issue with prohibiting the "empty membership" if someone wants to code that up. > 4. > Since v16 it is possible to grant membership from one role to another > several times with different grantors. > And only grantor can revoke membership. > - This is not documented anywhere. > Yeah, a pass over the GRANTED BY actual operation versus documentation seems warranted. > - Current behavior of \du command with duplicated roles in "Member of= " > column strongly confusing. > This is one of the goals of the discussion patch. > This indeed needs to be fixed, one way (include grantor) or the other (du-duplicate), with the current proposal of including grantor getting my vote. > > I think to write about this to pgsql-docs additionally to this topic. > I wouldn't bother starting yet another thread in this area right now, this one can absorb some related changes as well as the subject line item. David J. --0000000000005b8dbb05f60156fc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Mar 3, 2023 at 4:01=E2=80=AFAM Pavel Luzanov <<= a href=3D"mailto:p.luzanov@postgrespro.ru">p.luzanov@postgrespro.ru>= wrote:
=20 =20 =20
Hello,
On 22.02.2023 00:34, David G. Johnston wrote:
=20 I didn't even know this function existed. But I see that it was changed in 3d14e171 with updated documentation:

I think tha= t should probably have ADMIN as one of the options as well.=C2=A0 Also curious what it reports for an empty membership.

I've been experimenting for a few days and I want to admit that this is a very difficult and not obvious topic.
I'll try to summarize what I think.

1.
About ADMIN value for pg_has_role.
Implementation of ADMIN value will be different from USAGE and SET.
To be True, USAGE value requires the full chain of memberships to have INHERIT option.
Similar with SET: the full chain of memberships must have SET option.
But for ADMIN, only last member in the chain must have ADMIN option and all previous members
must have INHERIT (to administer directly) or SET option (to switch to role, last in the chain).
Therefore, it is not obvious to me that the function needs the ADMIN value.

Or you= can SET to some role that then has an unbroken INHERIT chain to the admini= stered role.

ADMIN basically implies SET/USAGE but it = doesn't work the other way around.

I'd be fine= with "pg_can_admin_role" being a newly created function that pro= vides this true/false answer but it seems indisputable that today there is = no core-provided means to answer the question "can one role get ADMIN = rights on another role".=C2=A0 Modifying \du to show this seems out-of= -scope but the pg_has_role function already provides that question for INHE= RIT and SET so it is at least plausible to extend it to include ADMIN, even= if the phrase "has role" seems a bit of a misnomer.=C2=A0 I do c= over this aspect with the Role Graph pseudo-extension but given the presenc= e and ease-of-use of a boolean-returning function this seems like a natural= addition.=C2=A0 We've also survived quite long without it - this isn&#= 39;t a new concept in v16, just a bit refined.
=C2=A0
=

2.
pg_has_role function description starts with: Does user have privilege for role?
=C2=A0=C2=A0 =C2=A0- This is not exact: function works not onl= y with users, but with NOLOGIN roles too.
=C2=A0=C2=A0 =C2=A0- Term "privilege": this term use= d for ACL columns, such usage may be confusing,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 especially after adding INHERIT and SE= T in addition to ADMIN option.

Yes, it missed = the whole "there are only roles now" memo.=C2=A0 I don't have= an issue with using privilege here though - you have to use the GRANT comm= and which "defines access privileges".=C2=A0 Otherwise "memb= ership option" or maybe just "option" would need to be explo= red.
=C2=A0
=C2=A0=C2=A0 =C2=A0
3.
It is possible to grant membership with all three options turned off:
=C2=A0=C2=A0=C2=A0 grant a to b with admin false, inherit false, set = false;
But such membership is completely useless (if i didn't mis= s something).
May be such grants must be prohibited. At least this may be documented in the GRANT command.

I have no issue with prohibiting the "empty membership"= if someone wants to code that up.


4.
Since v16 it is possible to grant membership from one role to another several times with different grantors.
And only grantor can revoke membership.
=C2=A0=C2=A0 =C2=A0- This is not documented anywhere.

Yeah, a pass over the GRANTED BY actu= al operation versus documentation seems warranted.


=C2=A0=C2=A0 =C2=A0- Current behavior of \du command with dupl= icated roles in "Member of" column strongly confusing.
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 This is one of the goals of the discus= sion patch.

This indeed needs= to be fixed, one way (include grantor) or the other (du-duplicate), with t= he current proposal of including grantor getting my vote.
= =C2=A0
=C2= =A0=C2=A0 =C2=A0
I think to write about this to pgsql-docs additionally to this topic.

I wouldn't b= other starting yet another thread in this area right now, this one can abso= rb some related changes as well as the subject line item.

David J.

--0000000000005b8dbb05f60156fc--