v4-0005-Update-system-views-pg_roles-and-pg_shadow.patch

From 443eed143edcaf7a770dddbae1e254f374a21dbf Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 21:17:12 -0700
Subject: [PATCH v4 05/11] Update system views pg_roles and pg_shadow

---
 doc/src/sgml/system-views.sgml       | 39 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql |  4 +++
 src/test/regress/expected/rules.out  |  4 +++
 3 files changed, 47 insertions(+)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 2b35c2f91b..28776fc268 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2662,6 +2662,25 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rolsecondpassword</structfield> <type>text</type>
+      </para>
+      <para>
+       Not the second password (always reads as <literal>********</literal>)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rolsecondvaliduntil</structfield> <type>timestamptz</type>
+      </para>
+      <para>
+       Second password's expiry time (only used for password authentication);
+       null if no expiration
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>rolbypassrls</structfield> <type>bool</type>
@@ -3493,6 +3512,26 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>secondpasswd</structfield> <type>text</type>
+      </para>
+      <para>
+       Second password (possibly encrypted); null if none.  See
+       <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>
+       for details of how encrypted passwords are stored.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>secondvaluntil</structfield> <type>timestamptz</type>
+      </para>
+      <para>
+       Second password's expiry time (only used for password authentication)
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>useconfig</structfield> <type>text[]</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fcb14976c0..ba8a2393b2 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -26,6 +26,8 @@ CREATE VIEW pg_roles AS
         rolconnlimit,
         '********'::text as rolpassword,
         rolvaliduntil,
+        '********'::text as rolsecondpassword,
+        rolsecondvaliduntil,
         rolbypassrls,
         setconfig as rolconfig,
         pg_authid.oid
@@ -42,6 +44,8 @@ CREATE VIEW pg_shadow AS
         rolbypassrls AS usebypassrls,
         rolpassword AS passwd,
         rolvaliduntil AS valuntil,
+        rolsecondpassword AS secondpasswd,
+        rolsecondvaliduntil AS secondvaluntil,
         setconfig AS useconfig
     FROM pg_authid LEFT JOIN pg_db_role_setting s
     ON (pg_authid.oid = setrole AND setdatabase = 0)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2c60400ade..6b87e70766 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1486,6 +1486,8 @@ pg_roles| SELECT pg_authid.rolname,
     pg_authid.rolconnlimit,
     '********'::text AS rolpassword,
     pg_authid.rolvaliduntil,
+    '********'::text AS rolsecondpassword,
+    pg_authid.rolsecondvaliduntil,
     pg_authid.rolbypassrls,
     s.setconfig AS rolconfig,
     pg_authid.oid
@@ -1729,6 +1731,8 @@ pg_shadow| SELECT pg_authid.rolname AS usename,
     pg_authid.rolbypassrls AS usebypassrls,
     pg_authid.rolpassword AS passwd,
     pg_authid.rolvaliduntil AS valuntil,
+    pg_authid.rolsecondpassword AS secondpasswd,
+    pg_authid.rolsecondvaliduntil AS secondvaluntil,
     s.setconfig AS useconfig
    FROM (pg_authid
      LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
-- 
2.41.0