On 22.02.2023 00:34, David G. Johnston wrote:
This is the format I've gone for (more-or-less) in my RoleGraph view
(I'll be sharing it publicly in the near future).
bob from grantor (a, s, i) \n
adam from postgres (a, s, i) \n
emily from postgres (empty)
I think this is a good compromise.
Based upon prior comments going for something like the following is
undesirable: bob=asi/grantor
Agree. Membership options are not the ACL (although they have
similarities). Therefore, showing them as a ACL-like column will be
confusing.
So, please find attached the second version of the patch. It implements
suggested display format and small refactoring of existing code for \du
command.
As a non-native writer, I have doubts about the documentation part.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
From 70489a605687a325287bad109e2741dd7d08cea3 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Mon, 27 Feb 2023 22:35:29 +0300
Subject: [PATCH v2] psql: \du shows membership options
---
doc/src/sgml/ref/psql-ref.sgml | 12 ++++++++
src/bin/psql/describe.c | 45 +++++++++++++++++++-----------
src/test/regress/expected/psql.out | 45 ++++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 25 +++++++++++++++++
4 files changed, 111 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11..c94a2287f0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1724,6 +1724,12 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\dg+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
@@ -1966,6 +1972,12 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\du+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c8a0bb7b3a..27a8680ddf 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3623,22 +3623,36 @@ 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"
- " 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");
+ " r.rolreplication, r.rolbypassrls,\n");
+
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%I from %I (%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\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");
+ else
+ appendPQExpBufferStr(&buf,
+ " ARRAY(SELECT 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");
if (verbose)
{
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
-
- if (pset.sversion >= 90500)
- {
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
- }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3692,12 +3706,11 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
add_role_attribute(&buf, _("Replication"));
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
+ if (strcmp(PQgetvalue(res, i, 9), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
@@ -3726,10 +3739,10 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 8fc62cebd2..a9e47f1ebf 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6578,3 +6578,48 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+-- check \du
+CREATE ROLE regress_du0;
+CREATE ROLE regress_du1 LOGIN;
+CREATE ROLE regress_du2 LOGIN;
+COMMENT ON ROLE regress_du0 IS 'Description for regress_du0';
+COMMENT ON ROLE regress_du1 IS 'Description for regress_du1';
+COMMENT ON ROLE regress_du2 IS 'Description for regress_du2';
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
+GRANT regress_du0 TO regress_du2 WITH ADMIN TRUE, INHERIT FALSE, SET TRUE;
+GRANT pg_monitor TO regress_du1;
+GRANT regress_du0 TO regress_du1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du2;
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du2;
+\du regress*
+ List of roles
+ Role name | Attributes | Member of
+-------------+--------------+----------------------------------------
+ regress_du0 | Cannot login |
+ regress_du1 | | pg_monitor from pal (i, s) +
+ | | regress_du0 from pal (a) +
+ | | regress_du0 from regress_du1 (i) +
+ | | regress_du0 from regress_du2 (a, i, s)
+ regress_du2 | | regress_du0 from pal (a, s) +
+ | | regress_du0 from regress_du1 (empty) +
+ | | regress_du0 from regress_du2 (s)
+
+\du+ regress*
+ List of roles
+ Role name | Attributes | Member of | Description
+-------------+--------------+----------------------------------------+-----------------------------
+ regress_du0 | Cannot login | | Description for regress_du0
+ regress_du1 | | pg_monitor from pal (i, s) +| Description for regress_du1
+ | | regress_du0 from pal (a) +|
+ | | regress_du0 from regress_du1 (i) +|
+ | | regress_du0 from regress_du2 (a, i, s) |
+ regress_du2 | | regress_du0 from pal (a, s) +| Description for regress_du2
+ | | regress_du0 from regress_du1 (empty) +|
+ | | regress_du0 from regress_du2 (s) |
+
+REVOKE regress_du0 FROM regress_du1 CASCADE;
+REVOKE regress_du0 FROM regress_du2 CASCADE;
+DROP ROLE regress_du2;
+DROP ROLE regress_du1;
+DROP ROLE regress_du0;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2da9665a19..dca1ede0ab 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1791,3 +1791,28 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+
+-- check \du
+CREATE ROLE regress_du0;
+CREATE ROLE regress_du1 LOGIN;
+CREATE ROLE regress_du2 LOGIN;
+COMMENT ON ROLE regress_du0 IS 'Description for regress_du0';
+COMMENT ON ROLE regress_du1 IS 'Description for regress_du1';
+COMMENT ON ROLE regress_du2 IS 'Description for regress_du2';
+
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
+GRANT regress_du0 TO regress_du2 WITH ADMIN TRUE, INHERIT FALSE, SET TRUE;
+GRANT pg_monitor TO regress_du1;
+GRANT regress_du0 TO regress_du1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du2;
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du2;
+
+\du regress*
+\du+ regress*
+
+REVOKE regress_du0 FROM regress_du1 CASCADE;
+REVOKE regress_du0 FROM regress_du2 CASCADE;
+DROP ROLE regress_du2;
+DROP ROLE regress_du1;
+DROP ROLE regress_du0;
--
2.34.1
