From 78caf093e68d6166477baf59934d53f50103836a Mon Sep 17 00:00:00 2001 From: Pavel Luzanov Date: Sat, 30 Dec 2023 15:48:18 +0300 Subject: [PATCH v1] psql: Rethinking of \du command Changes: * login attribute moved from "Attributes" column to separate column "Can login?" with yes/no values. * connlimit attribute moved from "Attributes" column to separate column "Max connections" in extended mode(+). * 'valid until' attribute moved from "Attributes" column to separate column "Password expire time" in extended mode. * Added new column "Has password?" in extended mode based on changed pg_roles.rolpassword. The rolpassword column of the pg_roles view modified so that it is easy to identify the presence of a password. The same changes made for pg_user.passwd for consistency. * describeRoles function rewritten for the convenience of printing the whole query result. All the magic of building "Attributes" column moved to SELECT statement for easy viewing by users via ECHO_HIDDEN variable. Per suggestions from Tom Lane. Author: Pavel Luzanov Discussion: https://www.postgresql.org/message-id/4133242.1687481416%40sss.pgh.pa.us --- src/backend/catalog/system_views.sql | 6 +- src/bin/psql/describe.c | 146 ++++++++------------------- 2 files changed, 43 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..b6ccb013aa 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,49 @@ 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" + " pg_catalog.concat_ws(', ',\n" + " CASE WHEN r.rolsuper THEN '%s' END,\n" + " CASE WHEN NOT r.rolinherit THEN '%s' END,\n" + " CASE WHEN r.rolcreaterole THEN '%s' END,\n" + " CASE WHEN r.rolcreatedb THEN '%s' END,\n" + " CASE WHEN r.rolreplication THEN '%s' END", + gettext_noop("Role name"), + gettext_noop("yes"), gettext_noop("no"), + gettext_noop("Can login?"), + gettext_noop("Superuser"), + gettext_noop("No inheritance"), + gettext_noop("Create role"), + gettext_noop("Create DB"), + gettext_noop("Replication")); + + if (pset.sversion >= 90500) + appendPQExpBuffer(&buf, + ",\n CASE WHEN r.rolbypassrls THEN '%s' END", + gettext_noop("Bypass RLS")); + + appendPQExpBuffer(&buf, "\n ) AS \"%s\"", gettext_noop("Attributes")); if (verbose) { - appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description"); - ncols++; - } - appendPQExpBufferStr(&buf, "\n, r.rolreplication"); + 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("Has password?")); - if (pset.sversion >= 90500) - { - appendPQExpBufferStr(&buf, "\n, r.rolbypassrls"); + 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("Password expire time"), + gettext_noop("Max connections"), + gettext_noop("Description")); } appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n"); @@ -3700,99 +3714,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