Subject: Re: psql: Add role's membership options to the \du+ command

From: Pavel Luzanov <[email-masked]>
Date: Thu, 18 May 2023 16:07:22 +0300

On 18.05.2023 05:42, Jonathan S. Katz wrote:

> That said, from a readability standpoint, it was easier for me to 
> follow the tabular form vs. the sentence form.

May be possible to reach a agreement on the sentence form. Similar 
descriptions used for referential constraints in the \d command:

create table t1 (id int primary key);create table t2 (id int references 
t1(id));\d t2                 Table "public.t2" Column |  Type   | 
Collation | Nullable | Default 
--------+---------+-----------+----------+--------- id     | integer 
|           |          | Foreign-key constraints:    "t2_id_fkey" 
FOREIGN KEY (id) REFERENCES t1(id)As for tabular form it looks more 
natural to have a separate psql command for pg_auth_members system 
catalog. Something based on this query:SELECT r.rolname role, m.rolname 
member,       admin_option admin, inherit_option inherit, set_option 
set,       g.rolname grantorFROM pg_catalog.pg_auth_members pam     JOIN 
pg_catalog.pg_roles r ON (pam.roleid = r.oid)     JOIN 
pg_catalog.pg_roles m ON (pam.member = m.oid)     JOIN 
pg_catalog.pg_roles g ON (pam.grantor = g.oid)WHERE r.rolname !~ 
'^pg_'ORDER BY role, member, grantor;       role       |      
member      | admin | inherit | set |     grantor 
------------------+------------------+-------+---------+-----+------------------ regress_du_role0 
| regress_du_admin | t     | t       | t   | postgres regress_du_role0 | 
regress_du_role1 | t     | t       | t   | 
regress_du_admin regress_du_role0 | regress_du_role1 | f     | t       | 
f   | regress_du_role1 regress_du_role0 | regress_du_role1 | f     | 
f       | t   | regress_du_role2 regress_du_role0 | regress_du_role2 | 
t     | f       | f   | regress_du_admin regress_du_role0 | 
regress_du_role2 | f     | t       | t   | 
regress_du_role1 regress_du_role0 | regress_du_role2 | f     | f       | 
f   | regress_du_role2 regress_du_role1 | regress_du_admin | t     | 
t       | t   | postgres regress_du_role1 | regress_du_role2 | t     | 
f       | t   | regress_du_admin regress_du_role2 | regress_du_admin | 
t     | t       | t   | postgres(10 rows)But is it worth inventing a new 
psql command for this?

-----
Pavel Luzanov

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 15 Jun 2023 11:47:56 -0700

Robert - can you please comment on what you are willing to commit in order
to close out your open item here.  My take is that the design for this, the
tabular form a couple of emails ago (copied here), is ready-to-commit, just
needing the actual (trivial) code changes to be made to accomplish it.

Tabular form

     rolname      |     memberof     |       options       |
grantor      ------------------+------------------+---------------------+------------------
postgres         |                  |                     |
regress_du_admin | regress_du_role0+| admin, inherit, set+| postgres
     +                  | regress_du_role1+| admin, inherit, set+|
postgres        +                  | regress_du_role2 | admin,
inherit, set | postgres regress_du_role0 |                  |
           |  regress_du_role1 | regress_du_role0+| admin, inherit,
set+| regress_du_admin+                  | regress_du_role0+| inherit
          +| regress_du_role1+                  | regress_du_role0 |
set                 | regress_du_role2 regress_du_role2 |
regress_du_role0+| admin              +| regress_du_admin+
     | regress_du_role0+| inherit, set       +| regress_du_role1+
            | regress_du_role0+| empty              +|
regress_du_role2+                  | regress_du_role1 | admin, set
     | regress_du_admin(5 rows)

On Thu, May 18, 2023 at 6:07 AM Pavel Luzanov <[email-masked]>
wrote:

> That said, from a readability standpoint, it was easier for me to follow
> the tabular form vs. the sentence form.
>
> May be possible to reach a agreement on the sentence form. Similar descriptions used
> for referential constraints in the \d command:
>
> I think we should consider the tabular form with translatable headers to
be the acceptable choice here.  I don't see enough value in the sentence
form to make it worth trying to overcome the i18n objection there.

> As for tabular form it looks more natural to have a separate psql command
> for pg_auth_members system catalog. Something based on this query:But is it worth inventing a new psql command for this?
>
>
IMO, no.  I'd much rather read "admin, inherit, set" than deal with
true/false in columns.  I think the newlines are better compared to
repetition of the rolname as well.

I'm also strongly in favor of explicitly writing out the word "empty"
instead of leaving the column blank for the case that no options are marked
true.  But it isn't a show-stopper for me.

David J.

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Mon, 19 Jun 2023 09:31:14 -0400

On 6/15/23 2:47 PM, David G. Johnston wrote:
> Robert - can you please comment on what you are willing to commit in 
> order to close out your open item here.  My take is that the design for 
> this, the tabular form a couple of emails ago (copied here), is 
> ready-to-commit, just needing the actual (trivial) code changes to be 
> made to accomplish it.
> 
> Tabular form
> 
> grantor 
> ------------------+------------------+---------------------+------------------ postgres         |                  |                     |  regress_du_admin | regress_du_role0+| admin, inherit, set+| postgres        +                  | regress_du_role1+| admin, inherit, set+| postgres        +                  | regress_du_role2 | admin, inherit, set | postgres regress_du_role0 |                  |                     |  regress_du_role1 | regress_du_role0+| admin, inherit, set+| regress_du_admin+                  | regress_du_role0+| inherit            +| regress_du_role1+                  | regress_du_role0 | set                 | regress_du_role2 regress_du_role2 | regress_du_role0+| admin              +| regress_du_admin+                  | regress_du_role0+| inherit, set       +| regress_du_role1+                  | regress_du_role0+| empty              +| regress_du_role2+                  | regress_du_role1 | admin, set          | regress_du_admin(5 rows)
> 

[RMT hat]

Can we resolve this before Beta 2?[1] The RMT originally advised to try 
to resolve before Beta 1[2], and this seems to be lingering.

Thanks,

Jonathan

[1] 
https://www.postgresql.org/message-id/460ae02a-3123-16a3-f2d7-ccd79778819b%40postgresql.org
[2] 
https://www.postgresql.org/message-id/d61db38b-29d9-81cc-55b3-8a5c704bb969%40postgresql.org

---

From: Tom Lane <[email-masked]>
Date: Thu, 22 Jun 2023 20:08:46 -0400

> to resolve before Beta 1[2], and this seems to be lingering.

At this point I kinda doubt that we can get this done before beta2
either, but I'll put in my two cents anyway:

* I agree that the "tabular" format looks nicer and has fewer i18n
issues than the other proposals.

* Personally I could do without the "empty" business, but that seems
unnecessary in the tabular format; an empty column will serve fine.

* I also agree with Pavel's comment that we'd be better off taking
this out of \du altogether and inventing a separate \d command.
Maybe "\drg" for "display role grants"?

* Parenthetically, the "Attributes" column of \du is a complete
disaster, lacking not only conceptual but even notational consistency.
(Who decided that some items belonged on their own line and others
not?)  I suppose it's way too late to redesign that for v16.  But
I think we'd have more of a free hand to clean that up if we weren't
trying to shoehorn role grants into the same display.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 23 Jun 2023 08:52:34 -0700

> issues than the other proposals.

As you are on board with a separate command please clarify whether you mean
the tabular format but still with newlines, one row per grantee, or the
table with one row per grantor-grantee pair.

I still like using newlines here even in the separate meta-command.

> unnecessary in the tabular format; an empty column will serve fine.

I disagree, but not strongly.

I kinda expected you to be on the side of "why are we discussing a
situation that should just be prohibited" though.

> Maybe "\drg" for "display role grants"?

Just to be clear, the open item fix proposal is to remove the presently
broken (due to it showing duplicates without any context) "member of" array
in \du and make a simple table report output in \drg instead.

I'm good with \drg as a new meta-command.

> disaster
I hadn't thought about this in detail but did get the same impression.

David J.

---

From: Tom Lane <[email-masked]>
Date: Fri, 23 Jun 2023 12:16:48 -0400

> table with one row per grantor-grantee pair.

I'd lean towards a straight table with a row per grantee/grantor.
I tend to think that faking table layout with some newlines is
a poor idea.  I'm not dead set on that approach though.

			regards, tom lane

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Fri, 23 Jun 2023 17:18:59 -0400

On 6/23/23 11:52 AM, David G. Johnston wrote:
> On Thu, Jun 22, 2023 at 5:08 PM Tom Lane <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     "Jonathan S. Katz" <[email-masked]
>     <mailto:[email-masked]>> writes:

[RMT Hat]

Well, the probability of completing this before the beta 2 freeze is 
effectively zero now. This is a bit disappointing as there was ample 
time since the first RMT nudge on the issue. But let's move forward and 
resolve it before Beta 3.

> As you are on board with a separate command please clarify whether you 
> mean the tabular format but still with newlines, one row per grantee, or 
> the table with one row per grantor-grantee pair.
> 

(I'll save for the downthread comment).

> situation that should just be prohibited" though.

[Personal hat]

I'm still not a fan of "empty" but perhaps the formatting around the 
"separate command" will help drive a conclusion on this.

> broken (due to it showing duplicates without any context) "member of" 
> array in \du and make a simple table report output in \drg instead.
> 

[Personal hat]

+1 for a new command. The proposal above seems reasonable.

Thanks,

Jonathan

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Fri, 23 Jun 2023 17:20:22 -0400

[Personal hat]

Generally, I find the tabular view w/o newlines is easier to read, and 
makes it simpler to join to other data (though that may not be 
applicable here).

Again, I'm not the target user of this feature (until I need to use it), 
so my opinion comes with a few grains of salt.

Thanks,

Jonathan

---

From: Tom Lane <[email-masked]>
Date: Fri, 23 Jun 2023 20:12:11 -0400

> situation that should just be prohibited" though.

I haven't formed an opinion yet on whether it should be prohibited.
But even if we do that going forward, won't psql need to deal with
such cases when examining old servers?

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 23 Jun 2023 18:28:26 -0700

> such cases when examining old servers?
I haven't given enough thought to that.  My first reaction is that using
blank for old servers would be desirable and then, if allowed in v16+
server, "empty" for those.

That said, the entire grantor premise that motivated this doesn't exist on
those servers so maybe \drg just shouldn't work against pre-v16 servers -
and we keep the existing \du query as-is for those as well while removing
the "member of" column when \du is executed against a v16+ server.

David J.

---

From: Pavel Luzanov <[email-masked]>
Date: Sat, 24 Jun 2023 18:11:31 +0300

Thank you for all valuable comments. I can now continue working on the 
patch.
Here's what I plan to do in the next version.

Changes for \du & \dg commands
* showing distinct roles in the "Member of" column
* explicit order for list of roles
* no changes for extended mode (\du+)

New meta-command \drg
* showing info from pg_auth_members based on a query:

SELECT r.rolname role, m.rolname member,
        pg_catalog.concat_ws(', ',
            CASE WHEN pam.admin_option THEN 'ADMIN' END,
            CASE WHEN pam.inherit_option THEN 'INHERIT' END,
            CASE WHEN pam.set_option THEN 'SET' END
        ) AS options,
        g.rolname grantor
FROM pg_catalog.pg_auth_members pam
      JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
      JOIN pg_catalog.pg_roles m ON (pam.member = m.oid)
      JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE r.rolname !~ '^pg_'
ORDER BY role, member, grantor;
        role       |      member      |       options |     grantor
------------------+------------------+---------------------+------------------
  regress_du_role0 | regress_du_admin | ADMIN, INHERIT, SET | postgres
  regress_du_role0 | regress_du_role1 | ADMIN, INHERIT, SET | 
regress_du_admin
  regress_du_role0 | regress_du_role1 | INHERIT | regress_du_role1
  regress_du_role0 | regress_du_role1 | SET | regress_du_role2
  regress_du_role0 | regress_du_role2 | ADMIN | regress_du_admin
  regress_du_role0 | regress_du_role2 | INHERIT, SET | regress_du_role1
  regress_du_role0 | regress_du_role2 | | regress_du_role2
  regress_du_role1 | regress_du_admin | ADMIN, INHERIT, SET | postgres
  regress_du_role1 | regress_du_role2 | ADMIN, SET | regress_du_admin
  regress_du_role2 | regress_du_admin | ADMIN, INHERIT, SET | postgres
(10 rows)

Notes
* The name of the new command. It's a good name, if not for the history.
There are two commands showing the same information about roles: \du and 
\dr.
The addition of \drg may be misinterpreted: if there is \drg, then there 
is also \dug.
Maybe it's time to think about deprecating of the \du command and leave 
only \dg in the next versions?

* 'empty'. I suggest thinking about forbidding the situation with empty 
options.
If we prohibit them, the issue will be resolved automatically.

* The new meta-command will also make sense for versions <16.
The ADMIN OPTION is available in all supported versions.

* The new meta-command will not show all roles. It will only show the 
roles included in other roles.
To show all roles you need to add an outer join between pg_roles and 
pg_auth_members.
But all columns except "role" will be left blank. Is it worth doing this?

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 24 Jun 2023 08:57:23 -0700

> only \dg in the next versions?

I would add \dr as the new official command to complement adding \drg and
deprecate both \du and \dg.  Though actual removal and de-documenting
doesn't seem like a good idea.  But if we ever did assign something
non-role to \dr it would be very confusing.

> The ADMIN OPTION is available in all supported versions.

Doesn't every role pre-16 gain SET permission?  We can also deduce whether
the grant provides INHERIT based upon the attribute of the role in question.

> But all columns except "role" will be left blank. Is it worth doing this?
I'm inclined to want this.  I would be good when specifying a role to
filter upon that all rows that do exist matching that filter end up in the
output regardless if they are standalone or not.

David J.

---

From: Pavel Luzanov <[email-masked]>
Date: Sun, 25 Jun 2023 17:44:51 +0300

On 24.06.2023 18:57, David G. Johnston wrote:
> On Sat, Jun 24, 2023 at 8:11 AM Pavel Luzanov 
> <[email-masked]> wrote:
>
> I would add \dr as the new official command to complement adding \drg 
> and deprecate both \du and \dg.  Though actual removal and 
> de-documenting doesn't seem like a good idea. But if we ever did 
> assign something non-role to \dr it would be very confusing.

It's my mistake and inattention. I was thinking about '\du' and '\dg', 
and wrote about '\du' and '\dr'.
I agree that \dr and \drg the best names.
So, now concentrating on implementing \drg.

> Doesn't every role pre-16 gain SET permission?  We can also deduce 
> whether the grant provides INHERIT based upon the attribute of the 
> role in question.

Indeed! I will do so.

> filter upon that all rows that do exist matching that filter end up in 
> the output regardless if they are standalone or not.

Ok

-- 
Pavel Luzanov
Postgres Professional:https://postgrespro.com

---

From: Pavel Luzanov <[email-masked]>
Date: Mon, 26 Jun 2023 22:29:46 +0300

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 <[email-masked]>
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