Next version (v3) addresses complains from cfbot. Changed only tests.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
From 6db62993d4b7afbcbce3e63ce3fbe3946ec50cff Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Wed, 1 Mar 2023 13:29:10 +0300
Subject: [PATCH v3] psql: \du shows memberships options
---
doc/src/sgml/ref/psql-ref.sgml | 12 ++++++++
src/bin/psql/describe.c | 45 ++++++++++++++++++----------
src/test/regress/expected/psql.out | 48 ++++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 29 ++++++++++++++++++
4 files changed, 118 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..584d644261 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6578,3 +6578,51 @@ 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;
+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';
+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 2da9665a19..e54a94c96a 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1791,3 +1791,32 @@ 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;
+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';
+
+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
