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

On Fri, Mar 3, 2023 at 4:01 AM Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>
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.