public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Luzanov <[email protected]>
To: David G. Johnston <[email protected]>
To: Tom Lane <[email protected]>
Cc: Jonathan S. Katz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: David Zhang <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: psql: Add role's membership options to the \du+ command
Date: Sat, 24 Jun 2023 18:11:31 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwZxCUs=o-jWRUaHOoxBxpB1OTTVSvbz_0ivoSs4UM6KiA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAKFQuwanYpV_0quPhRMpVoyUew4-4to0u1E0uXMNY4=ODPZHWQ@mail.gmail.com>
<[email protected]>
<CAKFQuwZXQP7ox0jsa+AqKa-4MkTpd2VypJm9dkXsk+6wKEQ2Gg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKFQuwZiomZFLd5zK=feCjY7Weefvce1MR+k38wyBMbaP2ASsg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAKFQuwa7EDaN6wzK4TSym8KrN3oSXJmYNEDyWNGRd5qw1pyUFQ@mail.gmail.com>
<[email protected]>
<CAKFQuwZ7RDkUMAtBE1hkcpsbEfnRQA__Rii6cW-2fipsHKF6gg@mail.gmail.com>
<[email protected]>
<CAKFQuwZxCUs=o-jWRUaHOoxBxpB1OTTVSvbz_0ivoSs4UM6KiA@mail.gmail.com>
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
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: psql: Add role's membership options to the \du+ command
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox