v3-0001-Add-new-columns-to-pg_authid.patch

application/x-patch
Filename: v3-0001-Add-new-columns-to-pg_authid.patch
Type: application/x-patch
Part: 0
Message: Re: [PoC/RFC] Multiple passwords, interval expirations
Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: format-patch
Series: patch v3-0001
Subject: Add new columns to pg_authid
File	+	−
src/backend/commands/user.c	4	0
src/include/catalog/pg_authid.dat	30	15
src/include/catalog/pg_authid.h	2	0
From bc7c35e53e421157c9425c198bc2557ad118a650 Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 11:36:05 -0700
Subject: [PATCH v3 1/3] Add new columns to pg_authid

Add two columns to pg_authid, namely rolsecondpassword and
rolsecondvaliduntil. These columns are added in preparation for the
password-rollover feature. These columns will store the hash and the
expiration time, repspectively, of a second password that the role can
use for login authentication.
---
 src/backend/commands/user.c       |  4 +++
 src/include/catalog/pg_authid.dat | 45 ++++++++++++++++++++-----------
 src/include/catalog/pg_authid.h   |  2 ++
 3 files changed, 36 insertions(+), 15 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index ce77a055e5..0afaf74865 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -452,9 +452,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	else
 		new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
 
+	new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] = true;
+
 	new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
 	new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
 
+	new_record_nulls[Anum_pg_authid_rolsecondvaliduntil - 1] = true;
+
 	new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls);
 
 	/*
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 6b4a0aaaad..b326e48376 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -23,76 +23,91 @@
   rolname => 'POSTGRES', rolsuper => 't', rolinherit => 't',
   rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't',
   rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '6171', oid_symbol => 'ROLE_PG_DATABASE_OWNER',
   rolname => 'pg_database_owner', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '6181', oid_symbol => 'ROLE_PG_READ_ALL_DATA',
   rolname => 'pg_read_all_data', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '6182', oid_symbol => 'ROLE_PG_WRITE_ALL_DATA',
   rolname => 'pg_write_all_data', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '3373', oid_symbol => 'ROLE_PG_MONITOR',
   rolname => 'pg_monitor', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '3374', oid_symbol => 'ROLE_PG_READ_ALL_SETTINGS',
   rolname => 'pg_read_all_settings', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '3375', oid_symbol => 'ROLE_PG_READ_ALL_STATS',
   rolname => 'pg_read_all_stats', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '3377', oid_symbol => 'ROLE_PG_STAT_SCAN_TABLES',
   rolname => 'pg_stat_scan_tables', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '4569', oid_symbol => 'ROLE_PG_READ_SERVER_FILES',
   rolname => 'pg_read_server_files', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '4570', oid_symbol => 'ROLE_PG_WRITE_SERVER_FILES',
   rolname => 'pg_write_server_files', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '4571', oid_symbol => 'ROLE_PG_EXECUTE_SERVER_PROGRAM',
   rolname => 'pg_execute_server_program', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '4200', oid_symbol => 'ROLE_PG_SIGNAL_BACKEND',
   rolname => 'pg_signal_backend', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINT',
   rolname => 'pg_checkpoint', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '4550', oid_symbol => 'ROLE_PG_USE_RESERVED_CONNECTIONS',
   rolname => 'pg_use_reserved_connections', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 { oid => '6304', oid_symbol => 'ROLE_PG_CREATE_SUBSCRIPTION',
   rolname => 'pg_create_subscription', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolpassword => '_null_', rolvaliduntil => '_null_',
+  rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
 
 ]
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 0e7ddc56ea..2a27ae3e10 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -45,6 +45,8 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
 #ifdef CATALOG_VARLEN			/* variable-length fields start here */
 	text		rolpassword;	/* password, if any */
 	timestamptz rolvaliduntil;	/* password expiration time, if any */
+	text		rolsecondpassword;	/* second password, if any */
+	timestamptz rolsecondvaliduntil;	/* second password expiration time, if any */
 #endif
 } FormData_pg_authid;
 
-- 
2.41.0