v5-0001-Show-password-presence-in-pg_roles.patch

From 80d7038e72d36659fc8453567ce38660fc3a6364 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
Date: Sat, 17 Feb 2024 20:46:32 +0300
Subject: [PATCH v5 1/2] Show password presence in pg_roles

Keeping with the changes made in v16 it does seem worthwhile modifying
pg_roles to be sensitive to the role querying the view having both
createrole and admin membership on the role being displayed.  With now
three possible outcomes: NULL if no password is in use, ********* if a
password is in use and the user has the ability to alter role, or
<insufficient privileges>.
---
 src/backend/catalog/system_views.sql | 43 ++++++++++++++++++----------
 src/test/regress/expected/rules.out  | 37 +++++++++++++++---------
 2 files changed, 51 insertions(+), 29 deletions(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 04227a72d1..acb6668e58 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -16,21 +16,34 @@
 
 CREATE VIEW pg_roles AS
     SELECT
-        rolname,
-        rolsuper,
-        rolinherit,
-        rolcreaterole,
-        rolcreatedb,
-        rolcanlogin,
-        rolreplication,
-        rolconnlimit,
-        '********'::text as rolpassword,
-        rolvaliduntil,
-        rolbypassrls,
-        setconfig as rolconfig,
-        pg_authid.oid
-    FROM pg_authid LEFT JOIN pg_db_role_setting s
-    ON (pg_authid.oid = setrole AND setdatabase = 0);
+        r.rolname,
+        r.rolsuper,
+        r.rolinherit,
+        r.rolcreaterole,
+        r.rolcreatedb,
+        r.rolcanlogin,
+        r.rolreplication,
+        r.rolconnlimit,
+        CASE WHEN curr_user.rolsuper OR
+                 (curr_user.rolcreaterole AND m.admin_option)
+             THEN
+                  CASE WHEN r.rolpassword IS NULL
+                       THEN NULL::pg_catalog.text
+                       ELSE '********'::pg_catalog.text
+                  END
+             ELSE '<insufficient privileges>'::pg_catalog.text
+        END rolpassword,
+        r.rolvaliduntil,
+        r.rolbypassrls,
+        s.setconfig AS rolconfig,
+        r.oid
+    FROM pg_catalog.pg_authid r
+    JOIN pg_catalog.pg_authid curr_user
+        ON (curr_user.rolname = CURRENT_USER)
+    LEFT JOIN pg_catalog.pg_auth_members m
+        ON (curr_user.oid = m.member AND r.oid = m.roleid AND m.admin_option)
+    LEFT JOIN pg_catalog.pg_db_role_setting s
+        ON (r.oid = s.setrole AND s.setdatabase = 0);
 
 CREATE VIEW pg_shadow AS
     SELECT
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b7488d760e..9cce3818ce 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1478,21 +1478,30 @@ pg_replication_slots| SELECT l.slot_name,
     l.synced
    FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, temporary, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size, two_phase, conflict_reason, failover, synced)
      LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
-pg_roles| SELECT pg_authid.rolname,
-    pg_authid.rolsuper,
-    pg_authid.rolinherit,
-    pg_authid.rolcreaterole,
-    pg_authid.rolcreatedb,
-    pg_authid.rolcanlogin,
-    pg_authid.rolreplication,
-    pg_authid.rolconnlimit,
-    '********'::text AS rolpassword,
-    pg_authid.rolvaliduntil,
-    pg_authid.rolbypassrls,
+pg_roles| SELECT r.rolname,
+    r.rolsuper,
+    r.rolinherit,
+    r.rolcreaterole,
+    r.rolcreatedb,
+    r.rolcanlogin,
+    r.rolreplication,
+    r.rolconnlimit,
+        CASE
+            WHEN (curr_user.rolsuper OR (curr_user.rolcreaterole AND m.admin_option)) THEN
+            CASE
+                WHEN (r.rolpassword IS NULL) THEN NULL::text
+                ELSE '********'::text
+            END
+            ELSE '<insufficient privileges>'::text
+        END AS rolpassword,
+    r.rolvaliduntil,
+    r.rolbypassrls,
     s.setconfig AS rolconfig,
-    pg_authid.oid
-   FROM (pg_authid
-     LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
+    r.oid
+   FROM (((pg_authid r
+     JOIN pg_authid curr_user ON ((curr_user.rolname = CURRENT_USER)))
+     LEFT JOIN pg_auth_members m ON (((curr_user.oid = m.member) AND (r.oid = m.roleid) AND m.admin_option)))
+     LEFT JOIN pg_db_role_setting s ON (((r.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
 pg_rules| SELECT n.nspname AS schemaname,
     c.relname AS tablename,
     r.rulename,
-- 
2.34.1