On 13.07.2023 11:26, Pavel Luzanov wrote:
And for versions <16 I forget to simplify expression for 'Options'
column after removing LEFT JOIN on pam:
SELECT m.rolname AS "Role name", r.rolname AS "Member of",
pg_catalog.concat_ws(', ',
CASE WHEN pam.admin_option THEN 'ADMIN' END,
CASE WHEN pam.roleid IS NOT NULL AND m.rolinherit THEN 'INHERIT' END,
CASE WHEN pam.roleid IS NOT NULL THEN 'SET' END
) AS "Options",
g.rolname AS "Grantor"
FROM pg_catalog.pg_roles m
JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)
LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE m.rolname !~ '^pg_'
ORDER BY 1, 2, 4;
I plan to replace it to:
pg_catalog.concat_ws(', ',
CASE WHEN pam.admin_option THEN 'ADMIN' END,
CASE WHEN m.rolinherit THEN 'INHERIT' END,
'SET'
) AS "Options",
The new version contains only this change.
--
-----
Pavel Luzanov
From d25743f87462ef89f9c098f0b907ac1fff473a99 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Fri, 14 Jul 2023 00:08:03 +0300
Subject: [PATCH v10] psql: Add \drg command to show role memberships
New command shows assigned privileges for each membership
ADMIN, INHERIT (added by e3ce2de0) or SET (3d14e171) and
who is grantor. This is important to know
how privileges can be used and who can revoke membership.
Without this command you need to query pg_auth_members directly.
This patch also removes the "Member of" column from the \du & \dg
commands, as it does not provide enough information about membership.
---
doc/src/sgml/ref/psql-ref.sgml | 21 ++++++++
src/bin/psql/command.c | 2 +
src/bin/psql/describe.c | 87 +++++++++++++++++++++++++-----
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 | 46 ++++++++++++++--
src/test/regress/sql/psql.sql | 26 +++++++++
8 files changed, 174 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 35aec6d3ce..e09fd06105 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1883,6 +1883,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>
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..722ba95420 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3617,7 +3617,7 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
PGresult *res;
printTableContent cont;
printTableOpt myopt = pset.popt.topt;
- int ncols = 3;
+ int ncols = 2;
int nrows = 0;
int i;
int conns;
@@ -3631,11 +3631,7 @@ 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\n");
if (verbose)
{
@@ -3675,8 +3671,6 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
- /* ignores implicit memberships from superuser & pg_database_owner */
- printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
if (verbose)
printTableAddHeader(&cont, gettext_noop("Description"), true, align);
@@ -3701,11 +3695,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, (verbose ? 9 : 8)), "t") == 0)
add_role_attribute(&buf, _("Replication"));
if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
+ if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
add_role_attribute(&buf, _("Bypass RLS"));
conns = atoi(PQgetvalue(res, i, 6));
@@ -3735,10 +3729,8 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
-
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
}
termPQExpBuffer(&buf);
@@ -3753,6 +3745,75 @@ 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)
+ appendPQExpBufferStr(&buf,
+ " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
+ " CASE WHEN pam.inherit_option THEN 'INHERIT' END,\n"
+ " CASE WHEN pam.set_option THEN 'SET' END\n");
+ else
+ appendPQExpBufferStr(&buf,
+ " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
+ " CASE WHEN m.rolinherit THEN 'INHERIT' END,\n"
+ " 'SET'\n");
+
+ 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"
+ " 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 0ff595e7ee..b2b749d69a 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -280,6 +280,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dp[S] [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 e9fddd91eb..779fdc90cb 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1713,7 +1713,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",
@@ -4760,7 +4760,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..7cd0c27cca 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6187,9 +6187,9 @@ List of text search templates
(0 rows)
\dg "no.such.role"
- List of roles
- Role name | Attributes | Member of
------------+------------+-----------
+ List of roles
+ Role name | Attributes
+-----------+------------
\dL "no.such.language"
List of languages
@@ -6618,3 +6618,43 @@ 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_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
+(7 rows)
+
+\du regress_du_role*
+ List of roles
+ Role name | Attributes
+------------------+--------------
+ regress_du_role0 | Cannot login
+ regress_du_role1 | Cannot login
+ regress_du_role2 | Cannot login
+
+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
