Skip to content
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Commit d65ddac

Browse files
tglsfdctglsfdc
committedJul 19, 2023
Add psql \drg command to display role grants.
With the addition of INHERIT and SET options for role grants, the historical display of role memberships in \du/\dg is woefully inadequate. Besides those options, there are pre-existing shortcomings that you can't see the ADMIN option nor the grantor. To fix this, remove the "Member of" column from \du/\dg altogether (making that output usefully narrower), and invent a new meta-command "\drg" that is specifically for displaying role memberships. It shows one row for each role granted to the selected role(s), with the grant options and grantor. We would not normally back-patch such a feature addition post feature freeze, but in this case the change is mainly driven by v16 changes in the server, so it seems appropriate to include it in v16. Pavel Luzanov, with bikeshedding and review from a lot of people, but particularly David Johnston Discussion: https://postgr.es/m/b9be2d0e-a9bc-0a30-492f-a4f68e4f7740@postgrespro.ru
1 parent 15c68cd commit d65ddac

File tree

8 files changed

+175
-18
lines changed

8 files changed

+175
-18
lines changed
 

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1883,6 +1883,7 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
18831883
</listitem>
18841884
</varlistentry>
18851885

1886+
18861887
<varlistentry id="app-psql-meta-command-drds">
18871888
<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>
18881889
<listitem>
@@ -1905,6 +1906,27 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
19051906
</listitem>
19061907
</varlistentry>
19071908

1909+
1910+
<varlistentry id="app-psql-meta-command-drg">
1911+
<term><literal>\drg[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
1912+
<listitem>
1913+
<para>
1914+
Lists information about each granted role membership, including
1915+
assigned options (<literal>ADMIN</literal>,
1916+
<literal>INHERIT</literal> and/or <literal>SET</literal>) and grantor.
1917+
See the <link linkend="sql-grant"><command>GRANT</command></link>
1918+
command for information about role memberships.
1919+
</para>
1920+
<para>
1921+
By default, only grants to user-created roles are shown; supply the
1922+
<literal>S</literal> modifier to include system roles.
1923+
If <replaceable class="parameter">pattern</replaceable> is specified,
1924+
only grants to those roles whose names match the pattern are listed.
1925+
</para>
1926+
</listitem>
1927+
</varlistentry>
1928+
1929+
19081930
<varlistentry id="app-psql-meta-command-drp">
19091931
<term><literal>\dRp[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
19101932
<listitem>

‎src/bin/psql/command.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -918,6 +918,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
918918

919919
free(pattern2);
920920
}
921+
else if (cmd[2] == 'g')
922+
success = describeRoleGrants(pattern, show_system);
921923
else
922924
status = PSQL_CMD_UNKNOWN;
923925
break;

‎src/bin/psql/describe.c

Lines changed: 74 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -3617,7 +3617,7 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
36173617
PGresult *res;
36183618
printTableContent cont;
36193619
printTableOpt myopt = pset.popt.topt;
3620-
int ncols = 3;
3620+
int ncols = 2;
36213621
int nrows = 0;
36223622
int i;
36233623
int conns;
@@ -3631,11 +3631,7 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
36313631
printfPQExpBuffer(&buf,
36323632
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
36333633
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
3634-
" r.rolconnlimit, r.rolvaliduntil,\n"
3635-
" ARRAY(SELECT b.rolname\n"
3636-
" FROM pg_catalog.pg_auth_members m\n"
3637-
" JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
3638-
" WHERE m.member = r.oid) as memberof");
3634+
" r.rolconnlimit, r.rolvaliduntil");
36393635

36403636
if (verbose)
36413637
{
@@ -3675,8 +3671,6 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
36753671

36763672
printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
36773673
printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
3678-
/* ignores implicit memberships from superuser & pg_database_owner */
3679-
printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
36803674

36813675
if (verbose)
36823676
printTableAddHeader(&cont, gettext_noop("Description"), true, align);
@@ -3701,11 +3695,11 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
37013695
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
37023696
add_role_attribute(&buf, _("Cannot login"));
37033697

3704-
if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
3698+
if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), "t") == 0)
37053699
add_role_attribute(&buf, _("Replication"));
37063700

37073701
if (pset.sversion >= 90500)
3708-
if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
3702+
if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
37093703
add_role_attribute(&buf, _("Bypass RLS"));
37103704

37113705
conns = atoi(PQgetvalue(res, i, 6));
@@ -3735,10 +3729,8 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
37353729

37363730
printTableAddCell(&cont, attr[i], false, false);
37373731

3738-
printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
3739-
37403732
if (verbose)
3741-
printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
3733+
printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
37423734
}
37433735
termPQExpBuffer(&buf);
37443736

@@ -3831,6 +3823,75 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
38313823
return false;
38323824
}
38333825

3826+
/*
3827+
* \drg
3828+
* Describes role grants.
3829+
*/
3830+
bool
3831+
describeRoleGrants(const char *pattern, bool showSystem)
3832+
{
3833+
PQExpBufferData buf;
3834+
PGresult *res;
3835+
printQueryOpt myopt = pset.popt;
3836+
3837+
initPQExpBuffer(&buf);
3838+
printfPQExpBuffer(&buf,
3839+
"SELECT m.rolname AS \"%s\", r.rolname AS \"%s\",\n"
3840+
" pg_catalog.concat_ws(', ',\n",
3841+
gettext_noop("Role name"),
3842+
gettext_noop("Member of"));
3843+
3844+
if (pset.sversion >= 160000)
3845+
appendPQExpBufferStr(&buf,
3846+
" CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3847+
" CASE WHEN pam.inherit_option THEN 'INHERIT' END,\n"
3848+
" CASE WHEN pam.set_option THEN 'SET' END\n");
3849+
else
3850+
appendPQExpBufferStr(&buf,
3851+
" CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3852+
" CASE WHEN m.rolinherit THEN 'INHERIT' END,\n"
3853+
" 'SET'\n");
3854+
3855+
appendPQExpBuffer(&buf,
3856+
" ) AS \"%s\",\n"
3857+
" g.rolname AS \"%s\"\n",
3858+
gettext_noop("Options"),
3859+
gettext_noop("Grantor"));
3860+
3861+
appendPQExpBufferStr(&buf,
3862+
"FROM pg_catalog.pg_roles m\n"
3863+
" JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)\n"
3864+
" LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)\n"
3865+
" LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)\n");
3866+
3867+
if (!showSystem && !pattern)
3868+
appendPQExpBufferStr(&buf, "WHERE m.rolname !~ '^pg_'\n");
3869+
3870+
if (!validateSQLNamePattern(&buf, pattern, false, false,
3871+
NULL, "m.rolname", NULL, NULL,
3872+
NULL, 1))
3873+
{
3874+
termPQExpBuffer(&buf);
3875+
return false;
3876+
}
3877+
3878+
appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;\n");
3879+
3880+
res = PSQLexec(buf.data);
3881+
termPQExpBuffer(&buf);
3882+
if (!res)
3883+
return false;
3884+
3885+
myopt.nullPrint = NULL;
3886+
myopt.title = _("List of role grants");
3887+
myopt.translate_header = true;
3888+
3889+
printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3890+
3891+
PQclear(res);
3892+
return true;
3893+
}
3894+
38343895

38353896
/*
38363897
* listTables()

‎src/bin/psql/describe.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,9 @@ extern bool describeRoles(const char *pattern, bool verbose, bool showSystem);
3737
/* \drds */
3838
extern bool listDbRoleSettings(const char *pattern, const char *pattern2);
3939

40+
/* \drg */
41+
extern bool describeRoleGrants(const char *pattern, bool showSystem);
42+
4043
/* \z (or \dp) */
4144
extern bool permissionsList(const char *pattern, bool showSystem);
4245

‎src/bin/psql/help.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -280,6 +280,7 @@ slashUsage(unsigned short int pager)
280280
HELP0(" \\dp[S] [PATTERN] list table, view, and sequence access privileges\n");
281281
HELP0(" \\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]\n");
282282
HELP0(" \\drds [ROLEPTRN [DBPTRN]] list per-database role settings\n");
283+
HELP0(" \\drg[S] [PATTERN] list role grants\n");
283284
HELP0(" \\dRp[+] [PATTERN] list replication publications\n");
284285
HELP0(" \\dRs[+] [PATTERN] list replication subscriptions\n");
285286
HELP0(" \\ds[S+] [PATTERN] list sequences\n");

‎src/bin/psql/tab-complete.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1713,7 +1713,7 @@ psql_completion(const char *text, int start, int end)
17131713
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
17141714
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
17151715
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
1716-
"\\drds", "\\dRs", "\\dRp", "\\ds",
1716+
"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
17171717
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
17181718
"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
17191719
"\\endif", "\\errverbose", "\\ev",
@@ -4760,7 +4760,9 @@ psql_completion(const char *text, int start, int end)
47604760
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
47614761
else if (TailMatchesCS("\\dT*"))
47624762
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
4763-
else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*"))
4763+
else if (TailMatchesCS("\\du*") ||
4764+
TailMatchesCS("\\dg*") ||
4765+
TailMatchesCS("\\drg*"))
47644766
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
47654767
else if (TailMatchesCS("\\dv*"))
47664768
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views);

‎src/test/regress/expected/psql.out

Lines changed: 43 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6187,9 +6187,9 @@ List of text search templates
61876187
(0 rows)
61886188

61896189
\dg "no.such.role"
6190-
List of roles
6191-
Role name | Attributes | Member of
6192-
-----------+------------+-----------
6190+
List of roles
6191+
Role name | Attributes
6192+
-----------+------------
61936193

61946194
\dL "no.such.language"
61956195
List of languages
@@ -6618,3 +6618,43 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
66186618
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
66196619
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
66206620
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
6621+
-- check \drg and \du
6622+
CREATE ROLE regress_du_role0;
6623+
CREATE ROLE regress_du_role1;
6624+
CREATE ROLE regress_du_role2;
6625+
CREATE ROLE regress_du_admin;
6626+
GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
6627+
GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
6628+
GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
6629+
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
6630+
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
6631+
GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
6632+
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
6633+
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
6634+
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
6635+
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
6636+
\drg regress_du_role*
6637+
List of role grants
6638+
Role name | Member of | Options | Grantor
6639+
------------------+------------------+---------------------+------------------
6640+
regress_du_role1 | regress_du_role0 | ADMIN, INHERIT, SET | regress_du_admin
6641+
regress_du_role1 | regress_du_role0 | INHERIT | regress_du_role1
6642+
regress_du_role1 | regress_du_role0 | SET | regress_du_role2
6643+
regress_du_role2 | regress_du_role0 | ADMIN | regress_du_admin
6644+
regress_du_role2 | regress_du_role0 | INHERIT, SET | regress_du_role1
6645+
regress_du_role2 | regress_du_role0 | | regress_du_role2
6646+
regress_du_role2 | regress_du_role1 | ADMIN, SET | regress_du_admin
6647+
(7 rows)
6648+
6649+
\du regress_du_role*
6650+
List of roles
6651+
Role name | Attributes
6652+
------------------+--------------
6653+
regress_du_role0 | Cannot login
6654+
regress_du_role1 | Cannot login
6655+
regress_du_role2 | Cannot login
6656+
6657+
DROP ROLE regress_du_role0;
6658+
DROP ROLE regress_du_role1;
6659+
DROP ROLE regress_du_role2;
6660+
DROP ROLE regress_du_admin;

‎src/test/regress/sql/psql.sql

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1823,3 +1823,29 @@ DROP FUNCTION psql_error;
18231823
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
18241824
\dT "no.such.database"."no.such.schema"."no.such.data.type"
18251825
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
1826+
1827+
-- check \drg and \du
1828+
CREATE ROLE regress_du_role0;
1829+
CREATE ROLE regress_du_role1;
1830+
CREATE ROLE regress_du_role2;
1831+
CREATE ROLE regress_du_admin;
1832+
1833+
GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
1834+
GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
1835+
GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
1836+
1837+
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
1838+
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
1839+
GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
1840+
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
1841+
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
1842+
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
1843+
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
1844+
1845+
\drg regress_du_role*
1846+
\du regress_du_role*
1847+
1848+
DROP ROLE regress_du_role0;
1849+
DROP ROLE regress_du_role1;
1850+
DROP ROLE regress_du_role2;
1851+
DROP ROLE regress_du_admin;

0 commit comments

Comments
 (0)
Failed to load comments.