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

From 8b18b5003ee6f77769c91952aa231802b5069513 Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 21:17:12 -0700
Subject: [PATCH v5 5/9] 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 7ed617170f..452edae0be 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2739,6 +2739,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>
@@ -3570,6 +3589,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 2e61f6d74e..e55364c3bd 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 f4a0f36377..7af39be2ac 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1490,6 +1490,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
@@ -1733,6 +1735,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.25.1