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

On 17.02.2023 19:53, David G. Johnston wrote:
> On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov
> <p(dot)luzanov(at)postgrespro(dot)ru> wrote:
>
>                                    List of roles
>  Role name | Attributes                         | Member of
> -----------+------------------------------------------------------------+-----------
>  admin     | Create
> role                                                | {bob,bob}
>  bob | | {}
>  postgres  | Superuser, Create role, Create DB, Replication,
> Bypass RLS | {}
>
> First 'grant bob to admin' command issued immediately after
> creating role bob by superuser(grantor=10). Second command issues
> by admin role and set membership options SET and INHERIT.
>
> If we don't ready to display membership options with \du+ may be
> at least we must group records in 'Member of' column for \du command?
>
>
> I agree that these views should GROUP BY roleid and use
> bool_or(*_option) to produce their result.

Ok, I'll try in the next few days. But what presentation format to use?

1. bob(admin_option=t inherit_option=t set_option=f) -- it seems very long
2. bob(ai) -- short, but will it be clear?
3. something else?

> Their purpose is to communicate the current effective state to the
> user, not facilitate full inspection of the configuration, possibly to
> aid in issuing GRANT and REVOKE commands.

This can help in issuing GRANT command, but not REVOKE. Revoking a
role's membership is now very similar to revoking privileges. Only the
role that granted membership can revoke that membership. So for REVOKE
you need to know who granted membership, but this information will not
be available after grouping.

> One thing I found, and I plan to bring this up independently once I've
> collected my thoughts, is that pg_has_role() uses the terminology
> "USAGE" and "MEMBER" for "INHERIT" and "SET" respectively.
>
> It's annoying that "member" has been overloaded here.  And the choice
> of USAGE just seems arbitrary (though I haven't researched it) given
> the related syntax.
>
> https://www.postgresql.org/docs/15/functions-info.html
>

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.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com