v1-0001-psql-Rethinking-of-du-command.patch

From 78caf093e68d6166477baf59934d53f50103836a Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
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