v5-0002-psql-Rethinking-of-du-command.patch
text/x-patch
Filename: v5-0002-psql-Rethinking-of-du-command.patch
Type: text/x-patch
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v5-0002
Subject: psql: Rethinking of \du command
| File | + | − |
|---|---|---|
| src/bin/psql/describe.c | 58 | 107 |
| src/test/regress/expected/psql.out | 86 | 20 |
| src/test/regress/sql/psql.sql | 27 | 10 |
From 6301b9f5df0a71e8cf054567cbb7b55c66c8463a Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
Date: Sat, 17 Feb 2024 20:47:35 +0300
Subject: [PATCH v5 2/2] psql: Rethinking of \du command
The Attributes column includes only the enabled logical attributes.
The attribute names correspond to the keywords of the CREATE ROLE
command. The attributes are listed in the same order as in
the documentation. "Login", "Connection limit" and "Valid until" attributes
are placed in separate columns. The "Password?" column has been added.
---
src/bin/psql/describe.c | 165 ++++++++++-------------------
src/test/regress/expected/psql.out | 106 ++++++++++++++----
src/test/regress/sql/psql.sql | 37 +++++--
3 files changed, 171 insertions(+), 137 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b6a4eb1d56..6e0f895475 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -36,7 +36,6 @@ 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 bool listTSParsersVerbose(const char *pattern);
static bool describeOneTSParser(const char *oid, const char *nspname,
const char *prsname);
@@ -3662,34 +3661,66 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
- printTableContent cont;
- printTableOpt myopt = pset.popt.topt;
- int ncols = 2;
- int nrows = 0;
- int i;
- int conns;
- const char align = 'l';
- char **attr;
-
- myopt.default_footer = false;
+ printQueryOpt myopt = pset.popt;
initPQExpBuffer(&buf);
-
printfPQExpBuffer(&buf,
- "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
- " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
- " r.rolconnlimit, r.rolvaliduntil");
-
- if (verbose)
- {
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
- }
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ "SELECT r.rolname AS \"%s\",\n"
+ " CASE WHEN r.rolcanlogin THEN '%s' ELSE '%s' END \"%s\",\n"
+ " pg_catalog.concat_ws(E'\\n',\n"
+ " CASE WHEN r.rolsuper THEN '%s' END,\n"
+ " CASE WHEN r.rolcreatedb THEN '%s' END,\n"
+ " CASE WHEN r.rolcreaterole THEN '%s' END,\n"
+ " CASE WHEN r.rolinherit THEN '%s' END,\n"
+ " CASE WHEN r.rolreplication THEN '%s' END",
+ gettext_noop("Role name"),
+ gettext_noop("yes"), gettext_noop("no"),
+ gettext_noop("Login"),
+ gettext_noop("Superuser"),
+ gettext_noop("Create DB"),
+ gettext_noop("Create role"),
+ gettext_noop("Inherit"),
+ gettext_noop("Replication"));
if (pset.sversion >= 90500)
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolbypassrls THEN '%s' END",
+ gettext_noop("Bypass RLS"));
+
+ appendPQExpBuffer(&buf, "\n ) \"%s\"", gettext_noop("Attributes"));
+
+ if (pset.sversion >= 170000)
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolpassword IS NULL THEN '%s'\n"
+ " WHEN r.rolpassword = '********' THEN '%s'\n"
+ " END \"%s\"",
+ gettext_noop("no"), gettext_noop("yes"),
+ gettext_noop("Password?"));
+
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolvaliduntil IS NOT NULL and r.rolpassword IS NULL\n"
+ " THEN '%s'\n"
+ " ELSE r.rolvaliduntil::pg_catalog.text\n"
+ " END \"%s\",\n"
+ " CASE\n"
+ " WHEN r.rolconnlimit >= 0 AND r.rolsuper THEN '%s'\n"
+ " WHEN r.rolconnlimit >= 0 AND NOT r.rolcanlogin THEN '%s'\n"
+ " WHEN r.rolconnlimit = 0 THEN '%s'\n"
+ " WHEN r.rolconnlimit = -1 THEN ''::pg_catalog.text\n"
+ " ELSE r.rolconnlimit::pg_catalog.text\n"
+ " END \"%s\"",
+ gettext_noop("(irrelevant)"),
+ gettext_noop("Valid until"),
+ gettext_noop("(irrelevant)"),
+ gettext_noop("(irrelevant)"),
+ gettext_noop("(not allowed)"),
+ gettext_noop("Connection limit"));
+
+ if (verbose)
{
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.shobj_description(r.oid, 'pg_authid') AS \"%s\"",
+ gettext_noop("Description"));
}
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3708,99 +3739,19 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
if (!res)
return false;
- nrows = PQntuples(res);
- attr = pg_malloc0((nrows + 1) * sizeof(*attr));
-
- printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
-
- printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
- printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
-
- if (verbose)
- printTableAddHeader(&cont, gettext_noop("Description"), true, align);
-
- for (i = 0; i < nrows; i++)
- {
- printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
-
- resetPQExpBuffer(&buf);
- if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
- add_role_attribute(&buf, _("Superuser"));
-
- if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
- add_role_attribute(&buf, _("No inheritance"));
-
- if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
- add_role_attribute(&buf, _("Create role"));
-
- if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
- add_role_attribute(&buf, _("Create DB"));
-
- if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
- add_role_attribute(&buf, _("Cannot login"));
-
- if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), "t") == 0)
- add_role_attribute(&buf, _("Replication"));
-
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
-
- conns = atoi(PQgetvalue(res, i, 6));
- if (conns >= 0)
- {
- if (buf.len > 0)
- appendPQExpBufferChar(&buf, '\n');
-
- if (conns == 0)
- appendPQExpBufferStr(&buf, _("No connections"));
- else
- appendPQExpBuffer(&buf, ngettext("%d connection",
- "%d connections",
- conns),
- conns);
- }
-
- if (strcmp(PQgetvalue(res, i, 7), "") != 0)
- {
- if (buf.len > 0)
- appendPQExpBufferChar(&buf, '\n');
- appendPQExpBufferStr(&buf, _("Password valid until "));
- appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
- }
-
- attr[i] = pg_strdup(buf.data);
-
- printTableAddCell(&cont, attr[i], false, false);
-
- if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
- }
- termPQExpBuffer(&buf);
-
- printTable(&cont, pset.queryFout, false, pset.logfile);
- printTableCleanup(&cont);
+ myopt.title = _("List of roles");
+ myopt.translate_header = true;
- for (i = 0; i < nrows; i++)
- free(attr[i]);
- free(attr);
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
PQclear(res);
return true;
}
-static void
-add_role_attribute(PQExpBuffer buf, const char *const str)
-{
- if (buf->len > 0)
- appendPQExpBufferStr(buf, ", ");
-
- appendPQExpBufferStr(buf, str);
-}
-
/*
* \drds
*/
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index ad02772562..822e3e09db 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6198,9 +6198,10 @@ List of text search templates
(0 rows)
\dg "no.such.role"
- List of roles
- Role name | Attributes
------------+------------
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit
+-----------+-------+------------+-----------+-------------+------------------
+(0 rows)
\dL "no.such.language"
List of languages
@@ -6630,16 +6631,23 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
\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;
+CREATE ROLE regress_du_su LOGIN SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS PASSWORD '123' CONNECTION LIMIT 3;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE PASSWORD '123' VALID UNTIL 'infinity';
+CREATE ROLE regress_du_role0 LOGIN REPLICATION BYPASSRLS CREATEDB password '123' VALID UNTIL '2024-12-31';
+CREATE ROLE regress_du_role1 VALID UNTIL '2024-12-31' CONNECTION LIMIT 50;
+CREATE ROLE regress_du_role2 LOGIN PASSWORD '123' CONNECTION LIMIT 0;
+CREATE ROLE regress_du_role3 LOGIN NOINHERIT PASSWORD '123' CONNECTION LIMIT 10;
+COMMENT ON ROLE regress_du_su IS 'Superuser but with connection limit';
+COMMENT ON ROLE regress_du_admin IS 'User createrole attribute';
+COMMENT ON ROLE regress_du_role1 IS 'Group role without password but with valid until and conn limit';
+COMMENT ON ROLE regress_du_role2 IS 'No connections allowed';
+COMMENT ON ROLE regress_du_role3 IS 'User without attributes';
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_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;
@@ -6657,18 +6665,76 @@ GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET
regress_du_role2 | regress_du_role1 | ADMIN, SET | regress_du_admin
(7 rows)
-\du regress_du_role*
- List of roles
- Role name | Attributes
-------------------+--------------
- regress_du_role0 | Cannot login
- regress_du_role1 | Cannot login
- regress_du_role2 | Cannot login
-
-DROP ROLE regress_du_role0;
-DROP ROLE regress_du_role1;
-DROP ROLE regress_du_role2;
-DROP ROLE regress_du_admin;
+-- run as superuser
+\du+ regress_du_*
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit | Description
+------------------+-------+-------------+-----------+------------------------------+------------------+-----------------------------------------------------------------
+ regress_du_admin | yes | Create role+| yes | infinity | | User createrole attribute
+ | | Inherit | | | |
+ regress_du_role0 | yes | Create DB +| yes | Tue Dec 31 00:00:00 2024 PST | |
+ | | Inherit +| | | |
+ | | Replication+| | | |
+ | | Bypass RLS | | | |
+ regress_du_role1 | no | Inherit | no | (irrelevant) | (irrelevant) | Group role without password but with valid until and conn limit
+ regress_du_role2 | yes | Inherit | yes | | (not allowed) | No connections allowed
+ regress_du_role3 | yes | | yes | | 10 | User without attributes
+ regress_du_su | yes | Superuser +| yes | | (irrelevant) | Superuser but with connection limit
+ | | Create DB +| | | |
+ | | Create role+| | | |
+ | | Inherit +| | | |
+ | | Replication+| | | |
+ | | Bypass RLS | | | |
+(6 rows)
+
+-- run as user with createrole attribute
+SET ROLE regress_du_admin;
+\du regress_du_*
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit
+------------------+-------+-------------+-----------+------------------------------+------------------
+ regress_du_admin | yes | Create role+| | infinity |
+ | | Inherit | | |
+ regress_du_role0 | yes | Create DB +| yes | Tue Dec 31 00:00:00 2024 PST |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+ regress_du_role1 | no | Inherit | no | (irrelevant) | (irrelevant)
+ regress_du_role2 | yes | Inherit | yes | | (not allowed)
+ regress_du_role3 | yes | | | | 10
+ regress_du_su | yes | Superuser +| | | (irrelevant)
+ | | Create DB +| | |
+ | | Create role+| | |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+(6 rows)
+
+-- run as unprivileged user
+SET ROLE regress_du_role0;
+\du regress_du_*
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit
+------------------+-------+-------------+-----------+------------------------------+------------------
+ regress_du_admin | yes | Create role+| | infinity |
+ | | Inherit | | |
+ regress_du_role0 | yes | Create DB +| | Tue Dec 31 00:00:00 2024 PST |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+ regress_du_role1 | no | Inherit | | Tue Dec 31 00:00:00 2024 PST | (irrelevant)
+ regress_du_role2 | yes | Inherit | | | (not allowed)
+ regress_du_role3 | yes | | | | 10
+ regress_du_su | yes | Superuser +| | | (irrelevant)
+ | | Create DB +| | |
+ | | Create role+| | |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+(6 rows)
+
+RESET ROLE;
+DROP ROLE regress_du_role0, regress_du_role1, regress_du_role2, regress_du_role3, regress_du_admin;
-- Test display of empty privileges.
BEGIN;
-- Create an owner for tested objects because output contains owner name.
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 129f853353..5853717aec 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1831,10 +1831,18 @@ DROP FUNCTION psql_error;
\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;
+CREATE ROLE regress_du_su LOGIN SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS PASSWORD '123' CONNECTION LIMIT 3;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE PASSWORD '123' VALID UNTIL 'infinity';
+CREATE ROLE regress_du_role0 LOGIN REPLICATION BYPASSRLS CREATEDB password '123' VALID UNTIL '2024-12-31';
+CREATE ROLE regress_du_role1 VALID UNTIL '2024-12-31' CONNECTION LIMIT 50;
+CREATE ROLE regress_du_role2 LOGIN PASSWORD '123' CONNECTION LIMIT 0;
+CREATE ROLE regress_du_role3 LOGIN NOINHERIT PASSWORD '123' CONNECTION LIMIT 10;
+
+COMMENT ON ROLE regress_du_su IS 'Superuser but with connection limit';
+COMMENT ON ROLE regress_du_admin IS 'User createrole attribute';
+COMMENT ON ROLE regress_du_role1 IS 'Group role without password but with valid until and conn limit';
+COMMENT ON ROLE regress_du_role2 IS 'No connections allowed';
+COMMENT ON ROLE regress_du_role3 IS 'User without attributes';
GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
@@ -1842,19 +1850,28 @@ 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_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;
+-- run as superuser
+\du+ regress_du_*
+
+-- run as user with createrole attribute
+SET ROLE regress_du_admin;
+\du regress_du_*
+
+-- run as unprivileged user
+SET ROLE regress_du_role0;
+\du regress_du_*
+
+RESET ROLE;
+
+DROP ROLE regress_du_role0, regress_du_role1, regress_du_role2, regress_du_role3, regress_du_admin;
-- Test display of empty privileges.
BEGIN;
--
2.34.1