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

On Mon, Jan 9, 2023 at 9:09 AM Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>
wrote:

> When you include one role in another, you can specify three options:
> ADMIN, INHERIT (added in e3ce2de0) and SET (3d14e171).
>
> For example.
>
> CREATE ROLE alice LOGIN;
>
> GRANT pg_read_all_settings TO alice WITH ADMIN TRUE, INHERIT TRUE, SET
> TRUE;
> GRANT pg_stat_scan_tables TO alice WITH ADMIN FALSE, INHERIT FALSE, SET
> FALSE;
> GRANT pg_read_all_stats TO alice WITH ADMIN FALSE, INHERIT TRUE, SET FALSE;
>
> For information about the options, you need to look in the pg_auth_members:
>
> SELECT roleid::regrole, admin_option, inherit_option, set_option
> FROM pg_auth_members
> WHERE member = 'alice'::regrole;
> roleid | admin_option | inherit_option | set_option
> ----------------------+--------------+----------------+------------
> pg_read_all_settings | t | t | t
> pg_stat_scan_tables | f | f | f
> pg_read_all_stats | f | t | f
> (3 rows)
>
> I think it would be useful to be able to get this information with a
> psql command
> like \du (and \dg). With proposed patch the \du command still only lists
> the roles of which alice is a member:
>
> \du alice
> List of roles
> Role name | Attributes | Member of
>
> -----------+------------+--------------------------------------------------------------
> alice | |
> {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
>
> But the \du+ command adds information about the selected ADMIN, INHERIT
> and SET options:
>
> \du+ alice
> List of roles
> Role name | Attributes | Member of
> | Description
>
> -----------+------------+-----------------------------------------------+-------------
> alice | | pg_read_all_settings WITH ADMIN, INHERIT, SET+|
> | | pg_read_all_stats WITH INHERIT +|
> | | pg_stat_scan_tables |
>
> One more change. The roles in the "Member of" column are sorted for both
> \du+ and \du for consistent output.
>
> Any comments are welcome.
>
>
Yeah, I noticed the lack too, then went a bit too far afield with trying to
compose a graph of the roles. I'm still working on that but at this point
it probably won't be something I try to get committed to psql. Something
more limited like this does need to be included.

One thing I did was name the situation where none of the grants are true -
EMPTY. So: pg_stat_scan_tables WITH EMPTY.

I'm not too keen on the idea of converting the existing array into a
newline separated string. I would try hard to make the modification here
purely additional. If users really want to build up queries on their own
they should be using the system catalog. So concise human readability
should be the goal here. Keeping those two things in mind I would add a
new text[] column to the views with the following possible values in each
cell the meaning of which should be self-evident or this probably isn't a
good approach...

ais
ai
as
a
is
i
s
empty

That said, I do find the newline based output to be quite useful in the
graph query I'm writing and so wouldn't be disappointed if we changed over
to that. I'd probably stick with abbreviations though. Another thing I
did with the graph was have both "member" and "memberof" columns in the
output. In short, every grant row in pg_auth_members appears twice, once
in each column, so the role being granted membership and the role into
which membership is granted both have visibility when you filter on them.
For the role graph I took this idea and extended out to an entire chain of
roles (and also broke out user and group separately) but I think doing the
direct-grant only here would still be a big improvement.

postgres=# \dgS+ pg_read_all_settings
List of roles
Role name | Attributes | Member of | Members | Description
----------------------+--------------+-----------+-------------
pg_read_all_settings | Cannot login | {} | { pg_monitor } |

David J.