Re: psql: Add role's membership options to the \du+ command - Mailing list pgsql-hackers

From Pavel Luzanov
Subject Re: psql: Add role's membership options to the \du+ command
Date
Msg-id b8624fab-ad13-7933-8afc-976ab106b13a@postgrespro.ru
Whole thread Raw
In response to Re: psql: Add role's membership options to the \du+ command  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: psql: Add role's membership options to the \du+ command
List pgsql-hackers
Thank you for all valuable comments. I can now continue working on the 
patch.
Here's what I plan to do in the next version.

Changes for \du & \dg commands
* showing distinct roles in the "Member of" column
* explicit order for list of roles
* no changes for extended mode (\du+)

New meta-command \drg
* showing info from pg_auth_members based on a query:

SELECT r.rolname role, m.rolname member,
        pg_catalog.concat_ws(', ',
            CASE WHEN pam.admin_option THEN 'ADMIN' END,
            CASE WHEN pam.inherit_option THEN 'INHERIT' END,
            CASE WHEN pam.set_option THEN 'SET' END
        ) AS options,
        g.rolname grantor
FROM pg_catalog.pg_auth_members pam
      JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
      JOIN pg_catalog.pg_roles m ON (pam.member = m.oid)
      JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE r.rolname !~ '^pg_'
ORDER BY role, member, grantor;
        role       |      member      |       options |     grantor
------------------+------------------+---------------------+------------------
  regress_du_role0 | regress_du_admin | ADMIN, INHERIT, SET | postgres
  regress_du_role0 | regress_du_role1 | ADMIN, INHERIT, SET | 
regress_du_admin
  regress_du_role0 | regress_du_role1 | INHERIT | regress_du_role1
  regress_du_role0 | regress_du_role1 | SET | regress_du_role2
  regress_du_role0 | regress_du_role2 | ADMIN | regress_du_admin
  regress_du_role0 | regress_du_role2 | INHERIT, SET | regress_du_role1
  regress_du_role0 | regress_du_role2 | | regress_du_role2
  regress_du_role1 | regress_du_admin | ADMIN, INHERIT, SET | postgres
  regress_du_role1 | regress_du_role2 | ADMIN, SET | regress_du_admin
  regress_du_role2 | regress_du_admin | ADMIN, INHERIT, SET | postgres
(10 rows)

Notes
* The name of the new command. It's a good name, if not for the history.
There are two commands showing the same information about roles: \du and 
\dr.
The addition of \drg may be misinterpreted: if there is \drg, then there 
is also \dug.
Maybe it's time to think about deprecating of the \du command and leave 
only \dg in the next versions?

* 'empty'. I suggest thinking about forbidding the situation with empty 
options.
If we prohibit them, the issue will be resolved automatically.

* The new meta-command will also make sense for versions <16.
The ADMIN OPTION is available in all supported versions.

* The new meta-command will not show all roles. It will only show the 
roles included in other roles.
To show all roles you need to add an outer join between pg_roles and 
pg_auth_members.
But all columns except "role" will be left blank. Is it worth doing this?

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




pgsql-hackers by date:

Previous
From: Joe Conway
Date:
Subject: Re: BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG
Next
From: Pavel Luzanov
Date:
Subject: Re: Things I don't like about \du's "Attributes" column