Re: psql: Add role's membership options to the \du+ command

public inbox for [email protected]  
help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Pavel Luzanov <[email protected]>
Cc: David Zhang <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: psql: Add role's membership options to the \du+ command
Date: Fri, 3 Mar 2023 09:21:11 -0700
Message-ID: <CAKFQuwY9DtC8B-eCpHe39yK+y9LardAyaEz0ccVa_pQ+xBgr2Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<CAKFQuwYt3ENZoYgY2RBh-U4id_=vntT9uJ83PvxPSsPQ0Cc_Jw@mail.gmail.com>
	<[email protected]>
	<CAKFQuwbmAFfDw9ScCiOxK4UGf8Z8ny1nDgAn41gOJCeJ76QC4Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAKFQuwYZ3S_A1ZKdnmOv-LueO1683UZf7AocNFTDjwEMLVfMOA@mail.gmail.com>
	<[email protected]>
	<CAKFQuwbkywHVFPL6TrJiHFd0KgEvdH0LZTSFxAdK4wzP-u8m-A@mail.gmail.com>
	<[email protected]>

On Fri, Mar 3, 2023 at 4:01 AM Pavel Luzanov <[email protected]>
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 in
> 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 value.
>

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 for
> 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.

view thread (29+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: psql: Add role's membership options to the \du+ command
  In-Reply-To: <CAKFQuwY9DtC8B-eCpHe39yK+y9LardAyaEz0ccVa_pQ+xBgr2Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox