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

Thanks a lot for the improvement, and it will definitely help provide
more very useful information.

I noticed the document psql-ref.sgml has been updated for both `du+` and
`dg+`, but only `du` and `\du+` are covered in regression test. Is that
because `dg+` is treated exactly the same as `du+` from testing point of
view?

The reason I am asking this question is that I notice that `pg_monitor`
also has the detailed information, so not sure if more test cases required.

postgres=# \duS+
List of roles
          Role name          | Attributes                        
|                   Member of                   | Description
-----------------------------+------------------------------------------------------------+-----------------------------------------------+-------------
 alice |                                                            |
pg_read_all_settings WITH ADMIN, INHERIT, SET |
 pg_checkpoint               | Cannot login
|                                               |
 pg_database_owner           | Cannot login
|                                               |
 pg_execute_server_program   | Cannot login
|                                               |
 pg_maintain                 | Cannot login
|                                               |
 pg_monitor                  | Cannot
login                                               |
pg_read_all_settings WITH INHERIT, SET       +|
|                                                            |
pg_read_all_stats WITH INHERIT, SET          +|
|                                                            |
pg_stat_scan_tables WITH INHERIT, SET         |

Best regards,

David

On 2023-01-09 8:09 a.m., Pavel Luzanov 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.
>