Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf565e2d

Browse files
tglsfdcpull[bot]
authored andcommitted
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 woefullyinadequate. Besides those options, there are pre-existingshortcomings 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. Itshows one row for each role granted to the selected role(s), withthe grant options and grantor.We would not normally back-patch such a feature addition postfeature freeze, but in this case the change is mainly driven byv16 changes in the server, so it seems appropriate to include itin v16.Pavel Luzanov, with bikeshedding and review from a lot of people,but particularly David JohnstonDiscussion:https://postgr.es/m/b9be2d0e-a9bc-0a30-492f-a4f68e4f7740@postgrespro.ru
1 parent3215572 commitf565e2d

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+
elseif (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
printTableContentcont;
36193619
printTableOptmyopt=pset.popt.topt;
3620-
intncols=3;
3620+
intncols=2;
36213621
intnrows=0;
36223622
inti;
36233623
intconns;
@@ -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(constchar*pattern,boolshowSystem)
3832+
{
3833+
PQExpBufferDatabuf;
3834+
PGresult*res;
3835+
printQueryOptmyopt=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
externboollistDbRoleSettings(constchar*pattern,constchar*pattern2);
3939

40+
/* \drg */
41+
externbooldescribeRoleGrants(constchar*pattern,boolshowSystem);
42+
4043
/* \z (or \dp) */
4144
externboolpermissionsList(constchar*pattern,boolshowSystem);
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
elseif (TailMatchesCS("\\dT*"))
47624762
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
4763-
elseif (TailMatchesCS("\\du*")||TailMatchesCS("\\dg*"))
4763+
elseif (TailMatchesCS("\\du*")||
4764+
TailMatchesCS("\\dg*")||
4765+
TailMatchesCS("\\drg*"))
47644766
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
47654767
elseif (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)
[8]ページ先頭
©2009-2025 Movatter.jp