Please find attached new patch version.
It implements \drg command and hides duplicates in \du & \dg commands.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
From a117f13fd497bf6ff8a504bcda6cb10d34dd22a7 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Mon, 26 Jun 2023 22:10:31 +0300
Subject: [PATCH v8] psql: add \drg command to show role memberships and grants
information.
New command shows assigned privileges for each membership (ADMIN, INHERIT
or SET) and who is grantor. This is important to know which privileges can
be used and how to revoke membership. Without this command you need to query
pg_auth_members directly.
Since v16 it is possible to include one role to another several times with
different grantors. Therefore, the 'Member of' column of the \du
and \dg commands now shows a sorted array of distinct roles to hide duplicates.
---
doc/src/sgml/ref/psql-ref.sgml | 27 +++++++++-
src/bin/psql/command.c | 2 +
src/bin/psql/describe.c | 80 +++++++++++++++++++++++++++++-
src/bin/psql/describe.h | 3 ++
src/bin/psql/help.c | 1 +
src/bin/psql/tab-complete.c | 6 ++-
src/test/regress/expected/psql.out | 41 +++++++++++++++
src/test/regress/sql/psql.sql | 26 ++++++++++
8 files changed, 180 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 35aec6d3ce..2d2e394a84 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1722,7 +1722,8 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
Lists database roles.
(Since the concepts of <quote>users</quote> and <quote>groups</quote> have been
unified into <quote>roles</quote>, this command is now equivalent to
- <literal>\du</literal>.)
+ <literal>\du</literal>.) For each role a sorted list of distinct roles
+ of which it is a member is shown in array format.
By default, only user-created roles are shown; supply the
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
@@ -1883,6 +1884,27 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
</listitem>
</varlistentry>
+
+ <varlistentry id="app-psql-meta-command-drg">
+ <term><literal>\drg[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists detailed information about each role membership, including
+ assigned options (<literal>ADMIN</literal>,
+ <literal>INHERIT</literal> or <literal>SET</literal>) and grantor.
+ See the <link linkend="sql-grant"><command>GRANT</command></link>
+ command for their meaning.
+ </para>
+ <para>
+ By default, only user-created roles are shown; supply the
+ <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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry id="app-psql-meta-command-drds">
<term><literal>\drds [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term>
<listitem>
@@ -1957,7 +1979,8 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
Lists database roles.
(Since the concepts of <quote>users</quote> and <quote>groups</quote> have been
unified into <quote>roles</quote>, this command is now equivalent to
- <literal>\dg</literal>.)
+ <literal>\dg</literal>.) For each role a sorted list of distinct roles
+ of which it is a member is shown in array format.
By default, only user-created roles are shown; supply the
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 511debbe81..88a653a709 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -918,6 +918,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
free(pattern2);
}
+ else if (strncmp(cmd, "drg", 3) == 0)
+ success = describeRoleGrants(pattern, show_system);
else
status = PSQL_CMD_UNKNOWN;
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 9325a46b8f..e4101fe36b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3632,10 +3632,11 @@ 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");
+ " WHERE m.member = r.oid\n"
+ " ORDER BY 1) as memberof");
if (verbose)
{
@@ -3753,6 +3754,81 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
return true;
}
+/*
+ * \drg
+ * Describes role grants.
+ */
+bool
+describeRoleGrants(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT m.rolname AS \"%s\", r.rolname AS \"%s\",\n"
+ " pg_catalog.concat_ws(', ',\n",
+ gettext_noop("Role name"),
+ gettext_noop("Member of"));
+
+ if (pset.sversion >= 160000)
+ appendPQExpBuffer(&buf,
+ " CASE WHEN pam.admin_option THEN '%s' END,\n"
+ " CASE WHEN pam.inherit_option THEN '%s' END,\n"
+ " CASE WHEN pam.set_option THEN '%s' END\n",
+ gettext_noop("ADMIN"),
+ gettext_noop("INHERIT"),
+ gettext_noop("SET"));
+ else
+ appendPQExpBuffer(&buf,
+ " CASE WHEN pam.admin_option THEN '%s' END,\n"
+ " CASE WHEN pam.roleid IS NOT NULL AND m.rolinherit THEN '%s' END,\n"
+ " CASE WHEN pam.roleid IS NOT NULL THEN '%s' END\n",
+ gettext_noop("ADMIN"),
+ gettext_noop("INHERIT"),
+ gettext_noop("SET"));
+
+ appendPQExpBuffer(&buf,
+ " ) AS \"%s\",\n"
+ " g.rolname AS \"%s\"\n",
+ gettext_noop("Options"),
+ gettext_noop("Grantor"));
+
+ appendPQExpBufferStr(&buf,
+ "FROM pg_catalog.pg_roles m\n"
+ " LEFT JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)\n"
+ " LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)\n"
+ " LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)\n");
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf, "WHERE m.rolname !~ '^pg_'\n");
+
+ if (!validateSQLNamePattern(&buf, pattern, false, false,
+ NULL, "m.rolname", NULL, NULL,
+ NULL, 1))
+ {
+ termPQExpBuffer(&buf);
+ return false;
+ }
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;\n");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of role grants");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
static void
add_role_attribute(PQExpBuffer buf, const char *const str)
{
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 554fe86725..a7ae2db1d7 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -34,6 +34,9 @@ extern bool describeOperators(const char *oper_pattern,
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose, bool showSystem);
+/* \drg */
+extern bool describeRoleGrants(const char *pattern, bool showSystem);
+
/* \drds */
extern bool listDbRoleSettings(const char *pattern, const char *pattern2);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index c6478ed559..7741ccc26b 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -280,6 +280,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dp [PATTERN] list table, view, and sequence access privileges\n");
HELP0(" \\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]\n");
HELP0(" \\drds [ROLEPTRN [DBPTRN]] list per-database role settings\n");
+ HELP0(" \\drg[S] [PATTERN] list role grants\n");
HELP0(" \\dRp[+] [PATTERN] list replication publications\n");
HELP0(" \\dRs[+] [PATTERN] list replication subscriptions\n");
HELP0(" \\ds[S+] [PATTERN] list sequences\n");
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 677847e434..97664ec1d2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1709,7 +1709,7 @@ psql_completion(const char *text, int start, int end)
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
- "\\drds", "\\dRs", "\\dRp", "\\ds",
+ "\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
"\\endif", "\\errverbose", "\\ev",
@@ -4731,7 +4731,9 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (TailMatchesCS("\\dT*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
- else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*"))
+ else if (TailMatchesCS("\\du*") ||
+ TailMatchesCS("\\dg*") ||
+ TailMatchesCS("\\drg*"))
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
else if (TailMatchesCS("\\dv*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c062c3dc7b..a4042a71f6 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6618,3 +6618,44 @@ 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 \drg and \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE 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;
+\drg regress_du_role*
+ List of role grants
+ Role name | Member of | Options | Grantor
+------------------+------------------+---------------------+------------------
+ regress_du_role0 | | |
+ regress_du_role1 | regress_du_role0 | ADMIN, INHERIT, SET | regress_du_admin
+ regress_du_role1 | regress_du_role0 | INHERIT | regress_du_role1
+ regress_du_role1 | regress_du_role0 | SET | regress_du_role2
+ regress_du_role2 | regress_du_role0 | ADMIN | regress_du_admin
+ regress_du_role2 | regress_du_role0 | INHERIT, SET | regress_du_role1
+ regress_du_role2 | regress_du_role0 | | regress_du_role2
+ regress_du_role2 | regress_du_role1 | ADMIN, SET | regress_du_admin
+(8 rows)
+
+\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}
+ regress_du_role2 | Cannot login | {regress_du_role0,regress_du_role1}
+
+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 965021fd84..30f19c8e26 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1823,3 +1823,29 @@ 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 \drg and \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE 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;
+
+\drg 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;
\ No newline at end of file
--
2.34.1
