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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: psql: Add role's membership options to the \du+ command
Дата
Msg-id CAKFQuwb-d1Hc745qLPvgABfDkU+kragtts_8byQ1Ck7MYzyO5g@mail.gmail.com
обсуждение исходный текст
Ответ на psql: Add role's membership options to the \du+ command  (Pavel Luzanov <p.luzanov@postgrespro.ru>)
Ответы Re: psql: Add role's membership options to the \du+ command  (Pavel Luzanov <p.luzanov@postgrespro.ru>)
Список pgsql-hackers
On Mon, Jan 9, 2023 at 9:09 AM Pavel Luzanov <p.luzanov@postgrespro.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.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Nathan Bossart
Дата:
Сообщение: Re: wake up logical workers after ALTER SUBSCRIPTION
Следующее
От: Andrew Dunstan
Дата:
Сообщение: Re: Non-superuser subscription owners