In the previous version, I didn't notice (unlike cfbot) the compiler
warning. Fixed in version 6.
-----
Pavel Luzanov
From 1b8b5743df23637b70e8d4ad0df0e1f892c595f3 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Wed, 22 Mar 2023 20:54:41 +0300
Subject: [PATCH v6] psql: show membership options in the \du command
Format for the "Member of" column completely redesigned.
Shown within each row, in newline-separated format, are the memberships granted to
the role. The presentation includes both the name of the grantor
as well as the membership permissions (in an abbreviated format:
a for admin option, i for inherit option, s for set option.)
The word 'empty' is printed in the case that none of those permissions are granted.
---
doc/src/sgml/ref/psql-ref.sgml | 30 +++++++++++---
src/bin/psql/describe.c | 63 ++++++++++++++++++++----------
src/test/regress/expected/psql.out | 49 +++++++++++++++++++++++
src/test/regress/sql/psql.sql | 30 ++++++++++++++
4 files changed, 146 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7b8ae9fac3..03e7da93de 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1727,9 +1727,18 @@ 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.
- If the form <literal>\dg+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\dg+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
@@ -1969,9 +1978,18 @@ 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.
- If the form <literal>\du+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\du+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 99e28f607e..7f2b7c9363 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3631,24 +3631,42 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printfPQExpBuffer(&buf,
"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.rolconnlimit, r.rolvaliduntil, r.rolreplication,\n");
- if (verbose)
- {
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
- }
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ 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 (pset.sversion >= 90500)
{
appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
}
+ if (verbose)
+ {
+ appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
+ ncols++;
+ }
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
if (!showSystem && !pattern)
@@ -3701,13 +3719,6 @@ 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)
- 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"));
-
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
@@ -3735,10 +3746,22 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+
+ if (pset.sversion >= 90500)
+ if (strcmp(PQgetvalue(res, i, 10), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ {
+ if (pset.sversion >= 90500)
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
+ else
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
+ }
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c00e28361c..d5f31b2082 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6607,3 +6607,52 @@ 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_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+\du regress_du_role*
+ List of roles
+ Role name | Attributes | Member of
+------------------+--------------+--------------------------------------------------
+ regress_du_role0 | Cannot login |
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+
+ | | regress_du_role0 from regress_du_role1 (i) +
+ | | regress_du_role0 from regress_du_role2 (s)
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +
+ | | regress_du_role0 from regress_du_role1 (i, s) +
+ | | regress_du_role0 from regress_du_role2 (empty) +
+ | | regress_du_role1 from regress_du_admin (a, s)
+
+\du+ regress_du_role*
+ List of roles
+ Role name | Attributes | Member of | Description
+------------------+--------------+--------------------------------------------------+----------------------------------
+ regress_du_role0 | Cannot login | | Description for regress_du_role0
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (i) +|
+ | | regress_du_role0 from regress_du_role2 (s) |
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +| Description for regress_du_role2
+ | | regress_du_role0 from regress_du_role1 (i, s) +|
+ | | regress_du_role0 from regress_du_role2 (empty) +|
+ | | regress_du_role1 from regress_du_admin (a, s) |
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 961783d6ea..1e8d46882b 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1815,3 +1815,33 @@ 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_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+
+\du regress_du_role*
+\du+ regress_du_role*
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
--
2.34.1
