Remove duplicates of membership from results of \du
Hi, hackers
When executing \du, you can see duplicates of the same role in 'member of'.
This happens when admin | inherit | set options are granted by another role.
---
postgres=# create role role_a login createrole;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
role_a | Create role
| {}
shinya | Superuser, Create role, Create DB, Replication, Bypass RLS
| {}
postgres=# set role role_a;
SET
postgres=> create role role_b;
CREATE ROLE
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
role_a | Create role
| {role_b}
role_b | Cannot login
| {}
shinya | Superuser, Create role, Create DB, Replication, Bypass RLS
| {}
postgres=> grant role_b to role_a;
GRANT ROLE
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------------
role_a | Create role
| {role_b,role_b}
role_b | Cannot login
| {}
shinya | Superuser, Create role, Create DB, Replication, Bypass RLS
| {}
postgres=> select rolname, oid from pg_roles where rolname = 'role_b';
rolname | oid
---------+-------
role_b | 16401
(1 row)
postgres=> select * from pg_auth_members where roleid = 16401;
oid | roleid | member | grantor | admin_option | inherit_option |
set_option
-------+--------+--------+---------+--------------+----------------+------------
16402 | 16401 | 16400 | 10 | t | f | f
16403 | 16401 | 16400 | 16400 | f | t | t
(2 rows)
---
Attached patch resolves this issue.
Do you think?
Regards,
Shinya Kato
Attachments:
v1_0001_remove_duplicates_membership.patchtext/x-patch; charset=UTF-8; name=v1_0001_remove_duplicates_membership.patchDownload
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 058e41e749..8aeb669100 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3632,7 +3632,7 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
" r.rolconnlimit, r.rolvaliduntil,\n"
- " ARRAY(SELECT b.rolname\n"
+ " ARRAY(SELECT DISTINCT b.rolname\n"
" FROM pg_catalog.pg_auth_members m\n"
" JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
" WHERE m.member = r.oid) as memberof");
On Sat, May 6, 2023 at 6:37 AM Shinya Kato <Shinya11.Kato@oss.nttdata.com>
wrote:
Hi, hackers
When executing \du, you can see duplicates of the same role in 'member of'.
This happens when admin | inherit | set options are granted by another
role.
There is already an ongoing patch discussing the needed changes to psql \du
because of this change in tracking membership grant attributes.
/messages/by-id/b9be2d0e-a9bc-0a30-492f-a4f68e4f7740@postgrespro.ru
David J.