public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Luzanov <[email protected]>
To: David G. Johnston <[email protected]>
Cc: David Zhang <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: psql: Add role's membership options to the \du+ command
Date: Wed, 1 Mar 2023 13:55:13 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAKFQuwYt3ENZoYgY2RBh-U4id_=vntT9uJ83PvxPSsPQ0Cc_Jw@mail.gmail.com>
<[email protected]>
<CAKFQuwbmAFfDw9ScCiOxK4UGf8Z8ny1nDgAn41gOJCeJ76QC4Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKFQuwYZ3S_A1ZKdnmOv-LueO1683UZf7AocNFTDjwEMLVfMOA@mail.gmail.com>
<[email protected]>
<CAKFQuwbkywHVFPL6TrJiHFd0KgEvdH0LZTSFxAdK4wzP-u8m-A@mail.gmail.com>
<[email protected]>
Next version (v3) addresses complains from cfbot. Changed only tests.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
Attachments:
[text/x-patch] v3-0001-psql-du-shows-memberships-options.patch (11.6K, ../[email protected]/2-v3-0001-psql-du-shows-memberships-options.patch)
download | inline diff:
From 6db62993d4b7afbcbce3e63ce3fbe3946ec50cff Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <[email protected]>
Date: Wed, 1 Mar 2023 13:29:10 +0300
Subject: [PATCH v3] psql: \du shows memberships options
---
doc/src/sgml/ref/psql-ref.sgml | 12 ++++++++
src/bin/psql/describe.c | 45 ++++++++++++++++++----------
src/test/regress/expected/psql.out | 48 ++++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 29 ++++++++++++++++++
4 files changed, 118 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11..c94a2287f0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1724,6 +1724,12 @@ 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.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\dg+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
@@ -1966,6 +1972,12 @@ 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.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\du+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c8a0bb7b3a..27a8680ddf 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3623,22 +3623,36 @@ 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"
- " 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.rolreplication, r.rolbypassrls,\n");
+
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&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 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\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");
+ 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 (verbose)
{
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
-
- if (pset.sversion >= 90500)
- {
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
- }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3692,12 +3706,11 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
add_role_attribute(&buf, _("Replication"));
- 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, 9), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
@@ -3726,10 +3739,10 @@ 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, 10), false, false);
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 8fc62cebd2..584d644261 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6578,3 +6578,51 @@ 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;
+CREATE ROLE regress_du_admin;
+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';
+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 from regress_du_admin (a, i, s)+
+ | | regress_du_role0 from regress_du_role1 (i) +
+ | | regress_du_role0 from regress_du_role2 (s)
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +
+ | | regress_du_role0 from regress_du_role1 (i, s) +
+ | | regress_du_role0 from regress_du_role2 (empty) +
+ | | regress_du_role1 from regress_du_admin (a, s)
+
+\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 (a, i, s)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (i) +|
+ | | regress_du_role0 from regress_du_role2 (s) |
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +| Description for regress_du_role2
+ | | regress_du_role0 from regress_du_role1 (i, s) +|
+ | | regress_du_role0 from regress_du_role2 (empty) +|
+ | | regress_du_role1 from regress_du_admin (a, s) |
+
+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 2da9665a19..e54a94c96a 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1791,3 +1791,32 @@ 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;
+CREATE ROLE regress_du_admin;
+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';
+
+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
view thread (29+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: psql: Add role's membership options to the \du+ command
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox