When you include one role in another, you can specify three options:
ADMIN, INHERIT (added in e3ce2de0) and SET (3d14e171).
For example.
CREATE ROLE alice LOGIN;
GRANT pg_read_all_settings TO alice WITH ADMIN TRUE, INHERIT TRUE, SET TRUE;
GRANT pg_stat_scan_tables TO alice WITH ADMIN FALSE, INHERIT FALSE, SET
FALSE;
GRANT pg_read_all_stats TO alice WITH ADMIN FALSE, INHERIT TRUE, SET FALSE;
For information about the options, you need to look in the pg_auth_members:
SELECT roleid::regrole, admin_option, inherit_option, set_option
FROM pg_auth_members
WHERE member = 'alice'::regrole;
roleid | admin_option | inherit_option | set_option
----------------------+--------------+----------------+------------
pg_read_all_settings | t | t | t
pg_stat_scan_tables | f | f | f
pg_read_all_stats | f | t | f
(3 rows)
I think it would be useful to be able to get this information with a
psql command
like \du (and \dg). With proposed patch the \du command still only lists
the roles of which alice is a member:
\du alice
List of roles
Role name | Attributes | Member of
-----------+------------+--------------------------------------------------------------
alice | |
{pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
But the \du+ command adds information about the selected ADMIN, INHERIT
and SET options:
\du+ alice
List of roles
Role name | Attributes | Member of
| Description
-----------+------------+-----------------------------------------------+-------------
alice | | pg_read_all_settings WITH ADMIN, INHERIT, SET+|
| | pg_read_all_stats WITH INHERIT +|
| | pg_stat_scan_tables |
One more change. The roles in the "Member of" column are sorted for both
\du+ and \du for consistent output.
Any comments are welcome.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 8a5285da9a..ef3e87fa32 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1724,9 +1724,8 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<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.
- If the form <literal>\dg+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ If the form <literal>\dg+</literal> is used, each role is listed
+ with its associated description and options for each role's membership.
</para>
</listitem>
</varlistentry>
@@ -1964,9 +1963,8 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<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.
- If the form <literal>\du+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ If the form <literal>\du+</literal> is used, each role is listed
+ with its associated description and options for each role's membership.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index df166365e8..c7b10f96f3 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3624,11 +3624,29 @@ 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)
+ {
+ appendPQExpBufferStr(&buf,
+ " (SELECT string_agg (quote_ident(b.rolname) || regexp_replace(\n"
+ " CASE WHEN m.admin_option THEN ', ADMIN' ELSE '' END ||\n"
+ " CASE WHEN m.inherit_option THEN ', INHERIT' ELSE '' END ||\n"
+ " CASE WHEN m.set_option THEN ', SET' ELSE '' END, \n"
+ " '^,', ' WITH', 1), E'\\n' ORDER BY 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");
+ }
+ else
+ {
+ appendPQExpBufferStr(&buf,
+ " 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\n"
+ " ORDER BY b.rolname) as memberof");
+ }
if (verbose)
{
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 8fc62cebd2..3a2eb65850 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6578,3 +6578,24 @@ 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 \du
+CREATE ROLE regress_du LOGIN;
+COMMENT ON ROLE regress_du IS 'Description for regress_du role';
+GRANT pg_read_all_settings TO regress_du WITH ADMIN TRUE, INHERIT TRUE, SET TRUE;
+GRANT pg_stat_scan_tables TO regress_du WITH ADMIN FALSE, INHERIT FALSE, SET FALSE;
+GRANT pg_read_all_stats TO regress_du WITH ADMIN FALSE, INHERIT TRUE, SET FALSE;
+\du regress_du
+ List of roles
+ Role name | Attributes | Member of
+------------+------------+--------------------------------------------------------------
+ regress_du | | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
+
+\du+ regress_du
+ List of roles
+ Role name | Attributes | Member of | Description
+------------+------------+-----------------------------------------------+---------------------------------
+ regress_du | | pg_read_all_settings WITH ADMIN, INHERIT, SET+| Description for regress_du role
+ | | pg_read_all_stats WITH INHERIT +|
+ | | pg_stat_scan_tables |
+
+DROP ROLE regress_du;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2da9665a19..6a5a57ab72 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1791,3 +1791,14 @@ 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 \du
+CREATE ROLE regress_du LOGIN;
+COMMENT ON ROLE regress_du IS 'Description for regress_du role';
+GRANT pg_read_all_settings TO regress_du WITH ADMIN TRUE, INHERIT TRUE, SET TRUE;
+GRANT pg_stat_scan_tables TO regress_du WITH ADMIN FALSE, INHERIT FALSE, SET FALSE;
+GRANT pg_read_all_stats TO regress_du WITH ADMIN FALSE, INHERIT TRUE, SET FALSE;
+
+\du regress_du
+\du+ regress_du
+DROP ROLE regress_du;
