On 03.01.2024 02:37, Jim Nasby wrote:
Some attributes are arguably important enough to warrant their own
column. The most obvious is NOLOGIN, since those roles are generally
used for a very different purpose than LOGIN roles. SUPERUSER might be
another candidate (though, I much prefer a dedicated "sudo role" than
explicit SU on roles).
I like this idea.
But what if all the attributes are moved to separate columns?
This solves all issues except the wide output. Less significant attributes
can be moved to extended mode. Here's what it might look like:
postgres@postgres(17.0)=# \du
List of roles
Role name | Login | Superuser | Create role | Create DB | Replication
-----------+-------+-----------+-------------+-----------+-------------
admin | no | no | no | no | no
alice | yes | yes | no | no | no
bob | yes | no | no | yes | yes
charlie | yes | no | yes | no | no
postgres | yes | yes | yes | yes | yes
(5 rows)
postgres@postgres(17.0)=# \du+
List of roles
Role name | Login | Superuser | Create role | Create DB | Replication | Bypass
RLS | Inheritance | Password | Valid until | Connection limit |
Description
-----------+-------+-----------+-------------+-----------+-------------+------------+-------------+----------+------------------------+------------------+-------------------------------------------------------------
admin | no | no | no | no | no | no
| yes | no | | -1 |
Group role without login
alice | yes | yes | no | no | no | no
| no | yes | infinity | 5 |
Superuser but with connection limit and with no inheritance
bob | yes | no | no | yes | yes | yes
| yes | no | 2022-01-01 00:00:00+03 | -1 | No
password but with expire time
charlie | yes | no | yes | no | no | no
| yes | yes | | 0 | No
connections allowed
postgres | yes | yes | yes | yes | yes | yes
| yes | yes | | -1 |
(5 rows)
postgres@postgres(17.0)=# \x \du+ bob
Expanded display is on.
List of roles
-[ RECORD 1 ]----+---------------------------------
Role name | bob
Login | yes
Superuser | no
Create role | no
Create DB | yes
Replication | yes
Bypass RLS | yes
Inheritance | yes
Password | no
Valid until | 2022-01-01 00:00:00+03
Connection limit | -1
Description | No password but with expire time
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
From 2ac990e66ce91efa319fce970ea37e98c6e89fa2 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Tue, 9 Jan 2024 23:46:31 +0300
Subject: [PATCH v2] psql: Rethinking of \du command
In this version, all attributes have been moved to separate columns
---
src/backend/catalog/system_views.sql | 6 +-
src/bin/psql/describe.c | 144 +++++++--------------------
2 files changed, 41 insertions(+), 109 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 058fc47c91..fed221f787 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -24,10 +24,10 @@ CREATE VIEW pg_roles AS
rolcanlogin,
rolreplication,
rolconnlimit,
- '********'::text as rolpassword,
+ CASE WHEN rolpassword IS NOT NULL THEN '********'::text END AS rolpassword,
rolvaliduntil,
rolbypassrls,
- setconfig as rolconfig,
+ setconfig AS rolconfig,
pg_authid.oid
FROM pg_authid LEFT JOIN pg_db_role_setting s
ON (pg_authid.oid = setrole AND setdatabase = 0);
@@ -65,7 +65,7 @@ CREATE VIEW pg_user AS
usesuper,
userepl,
usebypassrls,
- '********'::text as passwd,
+ CASE WHEN passwd IS NOT NULL THEN '********'::text END AS passwd,
valuntil,
useconfig
FROM pg_shadow;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 5077e7b358..c79cfd0b97 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);
@@ -3654,34 +3653,47 @@ 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");
+ "SELECT r.rolname AS \"%s\",\n"
+ " CASE WHEN r.rolcanlogin THEN '%s' ELSE '%s' END AS \"%s\",\n"
+ " CASE WHEN r.rolsuper THEN '%s' ELSE '%s' END AS \"%s\",\n"
+ " CASE WHEN r.rolcreaterole THEN '%s' ELSE '%s' END AS \"%s\",\n"
+ " CASE WHEN r.rolcreatedb THEN '%s' ELSE '%s' END AS \"%s\",\n"
+ " CASE WHEN r.rolreplication THEN '%s' ELSE '%s' END AS \"%s\"",
+ gettext_noop("Role name"),
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Login"),
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Superuser"),
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Create role"),
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Create DB"),
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Replication"));
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)
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolbypassrls THEN '%s' ELSE '%s' END AS \"%s\"",
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Bypass RLS"));
- if (pset.sversion >= 90500)
- {
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolinherit THEN '%s' ELSE '%s' END AS \"%s\"",
+ gettext_noop("yes"), gettext_noop("no"), gettext_noop("Inheritance"));
+
+ if (pset.sversion >= 170000)
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolpassword IS NULL THEN '%s' ELSE '%s' END AS \"%s\"",
+ gettext_noop("no"), gettext_noop("yes"),
+ gettext_noop("Password"));
+
+ appendPQExpBuffer(&buf,
+ ",\n r.rolvaliduntil AS \"%s\",\n"
+ " r.rolconnlimit AS \"%s\",\n"
+ " pg_catalog.shobj_description(r.oid, 'pg_authid') AS \"%s\"",
+ gettext_noop("Valid until"),
+ gettext_noop("Connection limit"),
+ gettext_noop("Description"));
}
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3700,99 +3712,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
*/
--
2.34.1
