After playing with the \du command, I found that we can't avoid translation.
All attributes are translatable. Also, two of nine attributes shows in
new line separated format (connection limit and password valid until).
$ LANGUAGE=fr psql -c "ALTER ROLE postgres CONNECTION LIMIT 3 VALID
UNTIL 'infinity'" -c '\du'
ALTER ROLE
Liste des rôles
Nom du rôle | Attributs | Membre de
-------------+---------------------------------------------------------------------------------+-----------
postgres | Superutilisateur, Créer un rôle, Créer une base,
Réplication, Contournement RLS+| {}
| 3 connexions +|
| Mot de passe valide jusqu'à
infinity |
So I decided to keep the format suggested by David, but without
abbreviations and only for extended mode.
$ psql -c '\duS+'
List of roles
Role name | Attributes
| Member of | Description
-----------------------------+-------------------------------+---------------------------------------------------+-------------
pg_checkpoint | Cannot login
| |
pg_create_subscription | Cannot login
| |
pg_database_owner | Cannot login
| |
pg_execute_server_program | Cannot login
| |
pg_maintain | Cannot login
| |
pg_monitor | Cannot login |
pg_read_all_settings from postgres (inherit, set)+|
| |
pg_read_all_stats from postgres (inherit, set) +|
| |
pg_stat_scan_tables from postgres (inherit, set) |
pg_read_all_data | Cannot login
| |
pg_read_all_settings | Cannot login
| |
pg_read_all_stats | Cannot login
| |
pg_read_server_files | Cannot login
| |
pg_signal_backend | Cannot login
| |
pg_stat_scan_tables | Cannot login
| |
pg_use_reserved_connections | Cannot login
| |
pg_write_all_data | Cannot login
| |
pg_write_server_files | Cannot login
| |
postgres | Superuser
+| |
| Create role
+| |
| Create DB
+| |
| Replication
+| |
| Bypass RLS
+| |
| 3 connections
+| |
| Password valid until infinity
| |
Please look at new version. I understand that this is a compromise choice.
I am ready to change it if a better solution is offered.
P.S. If no objections I plan to add this patch to Open Items for v16
https://wiki.postgresql.org/wiki/PostgreSQL_16_Open_Items
On 05.04.2023 17:24, David G. Johnston wrote:
On Wed, Apr 5, 2023 at 6:58 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
Pavel Luzanov <p.luza...@postgrespro.ru> writes:
> What if this long output will be available only for \du+, and
for \du
> just show distinct (without duplicates)
> roles in the current array format? For those, who don't care
about these
> new membership options, nothing will change.
> Those, who need details will use the + modifier.
> ?
I kind of like that. Would we change to newlines in the Attributes
field in both \du and \du+? (I'm +1 for that, but maybe others
aren't.)
If we don't change the \du "Member of" column display (aside from
removing duplicates) I'm disinclined to change the Attributes column.
I too am partial to only exposing this detail on the extended (+) display.
David J.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
From 1f9433696e41a8f37cfd4c0514e136fedd50939e Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Thu, 13 Apr 2023 15:09:57 +0300
Subject: [PATCH v7] psql: show membership options in the \du command
---
doc/src/sgml/ref/psql-ref.sgml | 40 +++++++++++--
src/bin/psql/describe.c | 92 ++++++++++++++++++++++--------
src/test/regress/expected/psql.out | 49 ++++++++++++++++
src/test/regress/sql/psql.sql | 30 ++++++++++
4 files changed, 182 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc422373d6..f174bddcb0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1727,9 +1727,23 @@ 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.
+ </para>
+ <para>
+ If the form <literal>\dg</literal> is used, a list of distinct roles
+ of which it is a member is shown for each role in array format.
+ </para>
+ <para>
+ If the form <literal>\dg+</literal> is used, the memberships granted
+ to the role shown within each row, in newline-separated format.
+ The presentation includes both the name of the grantor as well as
+ the membership permissions: <literal>admin</literal>,
+ <literal>inherit</literal>, <literal>set</literal>.
+ The word <literal>empty</literal> is printed in the case that none of those
+ permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ Also, if the form <literal>\dg+</literal> is used, the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
@@ -1969,9 +1983,23 @@ 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.
+ </para>
+ <para>
+ If the form <literal>\du</literal> is used, a list of distinct roles
+ of which it is a member is shown for each role in array format.
+ </para>
+ <para>
+ If the form <literal>\du+</literal> is used, the memberships granted
+ to the role shown within each row, in newline-separated format.
+ The presentation includes both the name of the grantor as well as
+ the membership permissions: <literal>admin</literal>,
+ <literal>inherit</literal>, <literal>set</literal>.
+ The word <literal>empty</literal> is printed in the case that none of those
+ permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ Also, if the form <literal>\du+</literal> is used, the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 83a37ee601..afdffb6932 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -36,7 +36,9 @@ static bool describeOneTableDetails(const char *schemaname,
bool verbose);
static void add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline);
-static void add_role_attribute(PQExpBuffer buf, const char *const str);
+static void add_role_attribute(PQExpBuffer buf,
+ const char *const str,
+ bool verbose);
static bool listTSParsersVerbose(const char *pattern);
static bool describeOneTSParser(const char *oid, const char *nspname,
const char *prsname);
@@ -3631,24 +3633,56 @@ 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, r.rolreplication,\n");
- if (verbose)
+ if (pset.sversion >= 160000)
{
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%%I from %%I (%%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN '%s' END,\n"
+ " CASE WHEN m.inherit_option THEN '%s' END,\n"
+ " CASE WHEN m.set_option THEN '%s' END),\n"
+ " '^$', '%s')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\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",
+ gettext_noop("admin"),
+ gettext_noop("inherit"),
+ gettext_noop("set"),
+ gettext_noop("empty"));
+ else
+ appendPQExpBufferStr(&buf,
+ " 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\n"
+ " ORDER BY 1) AS memberof");
}
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ 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) as memberof");
if (pset.sversion >= 90500)
{
appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
}
+ if (verbose)
+ {
+ appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
+ ncols++;
+ }
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
if (!showSystem && !pattern)
@@ -3687,26 +3721,26 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
resetPQExpBuffer(&buf);
if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
- add_role_attribute(&buf, _("Superuser"));
+ add_role_attribute(&buf, _("Superuser"), verbose);
if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
- add_role_attribute(&buf, _("No inheritance"));
+ add_role_attribute(&buf, _("No inheritance"), verbose);
if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
- add_role_attribute(&buf, _("Create role"));
+ add_role_attribute(&buf, _("Create role"), verbose);
if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
- add_role_attribute(&buf, _("Create DB"));
+ add_role_attribute(&buf, _("Create DB"), verbose);
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
- add_role_attribute(&buf, _("Cannot login"));
+ add_role_attribute(&buf, _("Cannot login"), verbose);
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
- add_role_attribute(&buf, _("Replication"));
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"), verbose);
if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
+ if (strcmp(PQgetvalue(res, i, 10), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"), verbose);
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
@@ -3735,10 +3769,15 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ {
+ if (pset.sversion >= 90500)
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
+ else
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
+ }
}
termPQExpBuffer(&buf);
@@ -3754,10 +3793,17 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
}
static void
-add_role_attribute(PQExpBuffer buf, const char *const str)
+add_role_attribute(PQExpBuffer buf,
+ const char *const str,
+ bool verbose)
{
if (buf->len > 0)
- appendPQExpBufferStr(buf, ", ");
+ {
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(buf, verbose ? "\n" : ", ");
+ else
+ appendPQExpBufferStr(buf, ", ");
+ }
appendPQExpBufferStr(buf, str);
}
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 956e475447..396eaa8495 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6607,3 +6607,52 @@ 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_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2 LOGIN CREATEROLE BYPASSRLS REPLICATION CREATEDB NOINHERIT CONNECTION LIMIT 3 VALID UNTIL 'infinity';
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for 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;
+\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 | No inheritance, Create role, Create DB, Replication, Bypass RLS+| {regress_du_role0,regress_du_role1}
+ | 3 connections +|
+ | Password valid until infinity |
+
+\du+ regress_du_role*
+ List of roles
+ Role name | Attributes | Member of | Description
+------------------+-------------------------------+--------------------------------------------------------------+----------------------------------
+ regress_du_role0 | Cannot login | | Description for regress_du_role0
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (admin, inherit, set)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (inherit) +|
+ | | regress_du_role0 from regress_du_role2 (set) |
+ regress_du_role2 | No inheritance +| regress_du_role0 from regress_du_admin (admin) +| Description for regress_du_role2
+ | Create role +| regress_du_role0 from regress_du_role1 (inherit, set) +|
+ | Create DB +| regress_du_role0 from regress_du_role2 (empty) +|
+ | Replication +| regress_du_role1 from regress_du_admin (admin, set) |
+ | Bypass RLS +| |
+ | 3 connections +| |
+ | Password valid until infinity | |
+
+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 630f638f02..5ef5e40e46 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1815,3 +1815,33 @@ 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_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2 LOGIN CREATEROLE BYPASSRLS REPLICATION CREATEDB NOINHERIT CONNECTION LIMIT 3 VALID UNTIL 'infinity';
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for 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;
+
+\du 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;
--
2.34.1
