v5-0004-Updated-pg_dumpall-to-support-exporting-a-role-s-.patch

From 4e3603b98c689d88670d6cf454cbe988aa0134cc Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 13:49:03 -0700
Subject: [PATCH v5 4/9] Updated pg_dumpall to support exporting a role's
 second password

---
 src/bin/pg_dump/pg_dumpall.c | 43 +++++++++++++++++++++++++++++++-----
 1 file changed, 37 insertions(+), 6 deletions(-)

diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 73337f3392..3d2c450649 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -761,6 +761,8 @@ dumpRoles(PGconn *conn)
 				i_rolconnlimit,
 				i_rolpassword,
 				i_rolvaliduntil,
+				i_rolsecondpassword,
+				i_rolsecondvaliduntil,
 				i_rolreplication,
 				i_rolbypassrls,
 				i_rolcomment,
@@ -771,7 +773,20 @@ dumpRoles(PGconn *conn)
 	 * Notes: rolconfig is dumped later, and pg_authid must be used for
 	 * extracting rolcomment regardless of role_catalog.
 	 */
-	if (server_version >= 90600)
+	if (server_version >= 180000)
+		printfPQExpBuffer(buf,
+						  "SELECT oid, rolname, rolsuper, rolinherit, "
+						  "rolcreaterole, rolcreatedb, "
+						  "rolcanlogin, rolconnlimit, rolpassword, "
+						  "rolvaliduntil, "
+						  "rolsecondpassword, rolsecondvaliduntil, "
+						  "rolreplication, rolbypassrls, "
+						  "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
+						  "rolname = current_user AS is_current_user "
+						  "FROM %s "
+						  "WHERE rolname !~ '^pg_' "
+						  "ORDER BY 2", role_catalog);
+	else if (server_version >= 90600)
 		printfPQExpBuffer(buf,
 						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, "
@@ -787,7 +802,9 @@ dumpRoles(PGconn *conn)
 						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, "
 						  "rolcanlogin, rolconnlimit, rolpassword, "
-						  "rolvaliduntil, rolreplication, rolbypassrls, "
+						  "rolvaliduntil, "
+						  "null as rolsecondpassword, null as rolsecodnvaliduntil, "
+						  "rolreplication, rolbypassrls, "
 						  "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
 						  "rolname = current_user AS is_current_user "
 						  "FROM %s "
@@ -796,8 +813,10 @@ dumpRoles(PGconn *conn)
 		printfPQExpBuffer(buf,
 						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, "
-						  "rolcanlogin, rolconnlimit, rolpassword, "
-						  "rolvaliduntil, rolreplication, "
+						  "rolcanlogin, rolconnlimit, "
+						  "rolpassword, rolvaliduntil, "
+						  "null as rolsecondpassword, null as rolsecondvaliduntil, "
+						  "rolreplication, "
 						  "false as rolbypassrls, "
 						  "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
 						  "rolname = current_user AS is_current_user "
@@ -816,6 +835,8 @@ dumpRoles(PGconn *conn)
 	i_rolconnlimit = PQfnumber(res, "rolconnlimit");
 	i_rolpassword = PQfnumber(res, "rolpassword");
 	i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
+	i_rolsecondpassword = PQfnumber(res, "rolsecondpassword");
+	i_rolsecondvaliduntil = PQfnumber(res, "rolsecondvaliduntil");
 	i_rolreplication = PQfnumber(res, "rolreplication");
 	i_rolbypassrls = PQfnumber(res, "rolbypassrls");
 	i_rolcomment = PQfnumber(res, "rolcomment");
@@ -904,12 +925,22 @@ dumpRoles(PGconn *conn)
 
 		if (!PQgetisnull(res, i, i_rolpassword) && !no_role_passwords)
 		{
-			appendPQExpBufferStr(buf, " PASSWORD ");
+			appendPQExpBufferStr(buf, " ADD FIRST PASSWORD ");
 			appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn);
 		}
 
 		if (!PQgetisnull(res, i, i_rolvaliduntil))
-			appendPQExpBuffer(buf, " VALID UNTIL '%s'",
+			appendPQExpBuffer(buf, " FIRST PASSWORD VALID UNTIL '%s'",
+							  PQgetvalue(res, i, i_rolvaliduntil));
+
+		if (!PQgetisnull(res, i, i_rolsecondpassword) && !no_role_passwords)
+		{
+			appendPQExpBufferStr(buf, " ADD SECOND PASSWORD ");
+			appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolsecondpassword), conn);
+		}
+
+		if (!PQgetisnull(res, i, i_rolsecondvaliduntil))
+			appendPQExpBuffer(buf, " SECOND PASSWORD VALID UNTIL '%s'",
 							  PQgetvalue(res, i, i_rolvaliduntil));
 
 		appendPQExpBufferStr(buf, ";\n");
-- 
2.25.1