v4-0003-Added-SQL-support-for-ALTER-ROLE-to-manage-two-pa.patch

From cd811b0fb1318fc47eeba7aa0beb4538bd1a966e Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 11:54:11 -0700
Subject: [PATCH v4 03/11] Added SQL support for ALTER ROLE to manage two
 passwords

Disallow roles to have different types of passwords; when setting or
adding a password, ensure that it is of the same type as the type of the
other existing password, if any.
---
 src/backend/commands/user.c                   | 348 +++++++++++++++++-
 src/backend/parser/gram.y                     |  53 ++-
 .../regress/expected/password_rollover.out    | 161 ++++++++
 src/test/regress/parallel_schedule            |   5 +
 src/test/regress/sql/password_rollover.sql    | 130 +++++++
 5 files changed, 684 insertions(+), 13 deletions(-)
 create mode 100644 src/test/regress/expected/password_rollover.out
 create mode 100644 src/test/regress/sql/password_rollover.sql

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 9ad02e4092..01bcb7e7f2 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -721,11 +721,16 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	ListCell   *option;
 	char	   *rolename;
 	char	   *password = NULL;	/* user password */
+	char	   *second_password = NULL;	/* user's second password */
 	int			connlimit = -1; /* maximum connections allowed */
-	char	   *validUntil = NULL;	/* time the login is valid until */
-	Datum		validUntil_datum;	/* same, as timestamptz Datum */
+	char	   *validUntil = NULL;	/* time the password is valid until */
+	Datum		validUntil_datum;	/* validUntil, as timestamptz Datum */
 	bool		validUntil_null;
+	char	   *secondValidUntil = NULL;/* time the second password is valid until */
+	Datum		secondValidUntil_datum;	/* secondValidUntil, as timestamptz Datum */
+	bool		secondValidUntil_null;
 	DefElem    *dpassword = NULL;
+	DefElem    *dsecondpassword = NULL;
 	DefElem    *dissuper = NULL;
 	DefElem    *dinherit = NULL;
 	DefElem    *dcreaterole = NULL;
@@ -735,10 +740,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	DefElem    *dconnlimit = NULL;
 	DefElem    *drolemembers = NULL;
 	DefElem    *dvalidUntil = NULL;
+	DefElem    *dfirstValidUntil = NULL;
+	DefElem    *dsecondValidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
 	Oid			roleid;
 	Oid			currentUserId = GetUserId();
 	GrantRoleOptions popt;
+	bool		overwriteFirstPassword = false;
+	bool		addFirstPassword = false;
+	bool		addSecondPassword = false;
+	bool		dropFirstPassword = false;
+	bool		dropSecondPassword = false;
+	bool		dropAllPasswords = false;
 
 	check_rolespec_name(stmt->role,
 						_("Cannot alter reserved roles."));
@@ -750,9 +763,95 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 
 		if (strcmp(defel->defname, "password") == 0)
 		{
-			if (dpassword)
+			if (overwriteFirstPassword || addFirstPassword)
 				errorConflictingDefElem(defel, pstate);
 			dpassword = defel;
+			overwriteFirstPassword = true;
+
+			if (dpassword->arg != NULL)
+			{
+				/* PASSWORD 'sometext' syntax was used */
+
+				/*
+				 * Adding and dropping passwords in the same command is not
+				 * supported.
+				 */
+				if (dropFirstPassword || dropSecondPassword || dropAllPasswords)
+					errorConflictingDefElem(defel, pstate);
+			}
+			else
+			{
+				/* PASSWORD NULL syntax was used */
+
+				if (dropFirstPassword)
+					errorConflictingDefElem(defel, pstate);
+
+				/*
+				 * Adding and dropping passwords in the same command is not
+				 * supported.
+				 */
+				if (addFirstPassword || addSecondPassword)
+					errorConflictingDefElem(defel, pstate);
+
+				dropFirstPassword = true;
+			}
+		}
+		else if (strcmp(defel->defname, "add-first-password") == 0)
+		{
+			if (addFirstPassword || overwriteFirstPassword)
+				errorConflictingDefElem(defel, pstate);
+			dpassword = defel;
+			addFirstPassword = true;
+
+			/*
+			 * Adding and dropping passwords in the same command is not
+			 * supported.
+			 */
+			if (dropFirstPassword || dropSecondPassword || dropAllPasswords)
+				errorConflictingDefElem(defel, pstate);
+		}
+		else if (strcmp(defel->defname, "add-second-password") == 0)
+		{
+			if (dsecondpassword)
+				errorConflictingDefElem(defel, pstate);
+			dsecondpassword = defel;
+			addSecondPassword = true;
+			/*
+			 * Adding and dropping passwords in the same command is not
+			 * supported.
+			 */
+			if (dropFirstPassword || dropSecondPassword || dropAllPasswords)
+				errorConflictingDefElem(defel, pstate);
+		}
+		else if (strcmp(defel->defname, "drop-password") == 0)
+		{
+			char *which = strVal(defel->arg);
+
+			if (strcmp(which, "first") == 0)
+			{
+				if (dropFirstPassword || dropAllPasswords)
+					errorConflictingDefElem(defel, pstate);
+				dropFirstPassword = true;
+			}
+			else if (strcmp(which, "second") == 0)
+			{
+				if (dropSecondPassword || dropAllPasswords)
+					errorConflictingDefElem(defel, pstate);
+				dropSecondPassword = true;
+			}
+			else
+			{
+				if (dropAllPasswords || dropFirstPassword || dropSecondPassword)
+					errorConflictingDefElem(defel, pstate);
+				dropAllPasswords = true;
+			}
+
+			/*
+			 * Adding and dropping passwords in the same command is not
+			 * supported.
+			 */
+			if (addFirstPassword || addSecondPassword || overwriteFirstPassword)
+				errorConflictingDefElem(defel, pstate);
 		}
 		else if (strcmp(defel->defname, "superuser") == 0)
 		{
@@ -809,6 +908,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 				errorConflictingDefElem(defel, pstate);
 			dvalidUntil = defel;
 		}
+		else if (strcmp(defel->defname, "first-password-valid-until") == 0)
+		{
+			if (dfirstValidUntil)
+				errorConflictingDefElem(defel, pstate);
+			dfirstValidUntil = defel;
+		}
+		else if (strcmp(defel->defname, "second-password-valid-until") == 0)
+		{
+			if (dsecondValidUntil)
+				errorConflictingDefElem(defel, pstate);
+			dsecondValidUntil = defel;
+		}
 		else if (strcmp(defel->defname, "bypassrls") == 0)
 		{
 			if (dbypassRLS)
@@ -822,6 +933,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 
 	if (dpassword && dpassword->arg)
 		password = strVal(dpassword->arg);
+	if (dsecondpassword)
+		second_password = strVal(dsecondpassword->arg);
 	if (dconnlimit)
 	{
 		connlimit = intVal(dconnlimit->arg);
@@ -830,8 +943,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid connection limit: %d", connlimit)));
 	}
+
+	/*
+	 * Disallow mixing VALID UNTIL with ADD FIRST/SECOND PASSWORD.
+	 *
+	 * VALID UNTIL and FIRST PASSWORD VALID UNTIL are functionally identical,
+	 * but we track them separately to prevent the confusing invocation like the
+	 * following.
+	 *
+	 * ALTER ROLE x ADD SECOND PASSWORD 'y' VALID UNTIL '2020/01/01';
+	 *
+	 * In the above command the user may expect the expiration of the _second_
+	 * password to be set to '2020/01/01', but it will lead to second password's
+	 * expiration set to NULL and first password's expiration set to
+	 * '2020/01/01', because a plain VALIF UNTIL applies to the _first_
+	 * password.
+	 */
+	if (dvalidUntil && (addFirstPassword || addSecondPassword))
+		errorConflictingDefElem(dvalidUntil, pstate);
+	dvalidUntil = dfirstValidUntil;
+
 	if (dvalidUntil)
 		validUntil = strVal(dvalidUntil->arg);
+	if (dsecondValidUntil)
+		secondValidUntil = strVal(dsecondValidUntil->arg);
 
 	/*
 	 * Scan the pg_authid relation to be certain the user exists.
@@ -867,7 +1002,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	{
 		/* things an unprivileged user certainly can't do */
 		if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit ||
-			dvalidUntil || disreplication || dbypassRLS)
+			dvalidUntil || dsecondValidUntil || disreplication || dbypassRLS)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to alter role"),
@@ -875,7 +1010,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 							   "CREATEROLE", "ADMIN", rolename)));
 
 		/* an unprivileged user can change their own password */
-		if (dpassword && roleid != currentUserId)
+		if ((dpassword || dsecondpassword) && roleid != currentUserId)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to alter role"),
@@ -934,15 +1069,42 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 										   &validUntil_null);
 	}
 
+	/* Convert secondvaliduntil to internal form */
+	if (dsecondValidUntil)
+	{
+		secondValidUntil_datum = DirectFunctionCall3(timestamptz_in,
+											   CStringGetDatum(secondValidUntil),
+											   ObjectIdGetDatum(InvalidOid),
+											   Int32GetDatum(-1));
+		secondValidUntil_null = false;
+	}
+	else
+	{
+		/* fetch existing setting in case hook needs it */
+		secondValidUntil_datum = SysCacheGetAttr(AUTHNAME, tuple,
+										   Anum_pg_authid_rolsecondvaliduntil,
+										   &secondValidUntil_null);
+	}
+
 	/*
 	 * Call the password checking hook if there is one defined
 	 */
-	if (check_password_hook && password)
-		(*check_password_hook) (rolename,
-								password,
-								get_password_type(password),
-								validUntil_datum,
-								validUntil_null);
+	if (check_password_hook)
+	{
+		if (password)
+			(*check_password_hook) (rolename,
+									password,
+									get_password_type(password),
+									validUntil_datum,
+									validUntil_null);
+
+		if (second_password)
+			(*check_password_hook) (rolename,
+									second_password,
+									get_password_type(second_password),
+									secondValidUntil_datum,
+									secondValidUntil_null);
+	}
 
 	/*
 	 * Build an updated tuple, perusing the information just obtained
@@ -1008,6 +1170,20 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 		char	   *shadow_pass;
 		const char *logdetail = NULL;
 
+		if (addFirstPassword)
+		{
+			bool	firstPassword_null;
+
+			SysCacheGetAttr(AUTHNAME, tuple,
+							Anum_pg_authid_rolpassword,
+							&firstPassword_null);
+
+			if (!firstPassword_null)
+				ereport(ERROR,
+						(errmsg("first password is already in use"),
+						errdetail("Use ALTER ROLE DROP FIRST PASSWORD.")));
+		}
+
 		/* Like in CREATE USER, don't allow an empty password. */
 		if (password[0] == '\0' ||
 			plain_crypt_verify(rolename, password, "", &logdetail) == STATUS_OK)
@@ -1034,17 +1210,153 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 		new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
 	}
 
+	/* second password */
+	if (second_password)
+	{
+		char	   *shadow_pass;
+		const char *logdetail = NULL;
+		bool		secondPassword_null;
+
+		SysCacheGetAttr(AUTHNAME, tuple,
+						Anum_pg_authid_rolsecondpassword,
+						&secondPassword_null);
+
+		if (!secondPassword_null)
+			ereport(ERROR,
+					(errmsg("second password is already in use"),
+					errdetail("Use ALTER ROLE DROP SECOND PASSWORD")));
+
+		/* Like in CREATE USER, don't allow an empty password. */
+		if (second_password[0] == '\0' ||
+			plain_crypt_verify(rolename, second_password, "", &logdetail) == STATUS_OK)
+		{
+			ereport(NOTICE,
+					(errmsg("empty string is not a valid password, clearing password")));
+			new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] = true;
+		}
+		else
+		{
+			char	   *salt;
+
+			if (!get_salt(rolename, &salt, &logdetail))
+				ereport(ERROR,
+						(errcode(ERRCODE_INTERNAL_ERROR),
+						errmsg("could not get a valid salt for password"),
+						errdetail("%s", logdetail)));
+
+			/* Encrypt the password to the requested format. */
+			shadow_pass = encrypt_password(Password_encryption, salt, second_password);
+			new_record[Anum_pg_authid_rolsecondpassword - 1] =
+				CStringGetTextDatum(shadow_pass);
+		}
+		new_record_repl[Anum_pg_authid_rolsecondpassword - 1] = true;
+	}
+
+	/*
+	 * Disallow more than one type of passwords for a role. If a role has an md5
+	 * password, then allow only md5 passwords; similarly for scram-sha-256
+	 * passwords. Having all passwords of the same type helps the server pick
+	 * the correponding authentication method during connection attempt.
+	 */
+	if (overwriteFirstPassword || addFirstPassword || addSecondPassword)
+	{
+		bool	firstPassword_null;
+		bool	secondPassword_null;
+		Datum	firstPassword_datum;
+		Datum	secondPassword_datum;
+		char   *roleFirstPassword = NULL;
+		char   *roleSecondPassword = NULL;
+		PasswordType roleFirstPasswordType = -1; /* silence the compiler */
+		PasswordType roleSecondPasswordType = -1; /* silence the compiler */
+
+		firstPassword_datum = SysCacheGetAttr(AUTHNAME, tuple,
+												Anum_pg_authid_rolpassword,
+												&firstPassword_null);
+		secondPassword_datum = SysCacheGetAttr(AUTHNAME, tuple,
+												Anum_pg_authid_rolsecondpassword,
+												&secondPassword_null);
+		if (!firstPassword_null)
+		{
+			roleFirstPassword = TextDatumGetCString(firstPassword_datum);
+			roleFirstPasswordType = get_password_type(roleFirstPassword);
+		}
+
+		if (!secondPassword_null)
+		{
+			roleSecondPassword = TextDatumGetCString(secondPassword_datum);
+			roleSecondPasswordType = get_password_type(roleSecondPassword);
+		}
+
+			/* if the user requested setting the first password ... */
+		if ((overwriteFirstPassword || addFirstPassword) &&
+			/* and we have decided to honor their request */
+			new_record_repl[Anum_pg_authid_rolpassword - 1] == true &&
+			/* and the resulting password hash about to be stored is not null */
+			new_record_nulls[Anum_pg_authid_rolpassword - 1] == false &&
+			/* and the algorithm used doesn't match existing password's algorithm */
+			roleSecondPassword != NULL &&
+			roleSecondPasswordType != Password_encryption)
+		{
+			if (roleSecondPasswordType == PASSWORD_TYPE_MD5)
+				ereport(ERROR,
+						(errmsg("role has an md5 password"),
+						errdetail("The new password must also use md5.")));
+			else if (roleSecondPasswordType == PASSWORD_TYPE_SCRAM_SHA_256)
+				ereport(ERROR,
+						(errmsg("role has a scram-sha-256 password"),
+						errdetail("The new password must also use scram-sha-256.")));
+			else
+				ereport(ERROR,
+						(errmsg("role has a plaintext password"),
+						errdetail("The new password must also use plaintext.")));
+		}
+
+			/* if the user requested setting the second password ... */
+		if (addSecondPassword &&
+			/* and we have decided to honor their request */
+			new_record_repl[Anum_pg_authid_rolsecondpassword - 1] == true &&
+			/* and the resulting password hash about to be stored is not null */
+			new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] == false &&
+			/* and the algorithm used doesn't match existing password's algorithm */
+			roleFirstPassword != NULL &&
+			roleFirstPasswordType != Password_encryption)
+		{
+			if (roleFirstPasswordType == PASSWORD_TYPE_MD5)
+				ereport(ERROR,
+						(errmsg("role has an md5 password"),
+						errdetail("The new password must also use md5.")));
+			else if (roleFirstPasswordType == PASSWORD_TYPE_SCRAM_SHA_256)
+				ereport(ERROR,
+						(errmsg("role has a scram-sha-256 password"),
+						errdetail("The new password must also use scram-sha-256.")));
+			else
+				ereport(ERROR,
+						(errmsg("role has a plaintext password"),
+						errdetail("The new password must also use plaintext.")));
+		}
+	}
+
 	/* unset password */
-	if (dpassword && dpassword->arg == NULL)
+	if (dropFirstPassword || dropAllPasswords)
 	{
 		new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
 		new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
 	}
 
+	if (dropSecondPassword || dropAllPasswords)
+	{
+		new_record_repl[Anum_pg_authid_rolsecondpassword - 1] = true;
+		new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] = true;
+	}
+
 	/* valid until */
 	new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
 	new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
 	new_record_repl[Anum_pg_authid_rolvaliduntil - 1] = true;
+	/* second password valid until */
+	new_record[Anum_pg_authid_rolsecondvaliduntil - 1] = secondValidUntil_datum;
+	new_record_nulls[Anum_pg_authid_rolsecondvaliduntil - 1] = secondValidUntil_null;
+	new_record_repl[Anum_pg_authid_rolsecondvaliduntil - 1] = true;
 
 	if (dbypassRLS)
 	{
@@ -1559,6 +1871,18 @@ RenameRole(const char *oldname, const char *newname)
 				(errmsg("MD5 password cleared because of role rename")));
 	}
 
+	datum = heap_getattr(oldtuple, Anum_pg_authid_rolsecondpassword, dsc, &isnull);
+
+	if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
+	{
+		/* MD5 uses the username as salt, so just clear it on a rename */
+		repl_repl[Anum_pg_authid_rolsecondpassword - 1] = true;
+		repl_null[Anum_pg_authid_rolsecondpassword - 1] = true;
+
+		ereport(NOTICE,
+				(errmsg("MD5 password cleared because of role rename")));
+	}
+
 	newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
 	CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e56cbe77cb..6447ac4056 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -361,7 +361,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival>	opt_nowait_or_skip
 
 %type <list>	OptRoleList AlterOptRoleList
-%type <defelt>	CreateOptRoleElem AlterOptRoleElem
+%type <defelt>	CreateOptRoleElem AlterOptRoleElem AlterOnlyOptRoleElem
+%type <boolean>	OptFirstOrSecond
 
 %type <str>		opt_type
 %type <str>		foreign_server_version opt_foreign_server_version
@@ -1168,6 +1169,7 @@ OptRoleList:
 
 AlterOptRoleList:
 			AlterOptRoleList AlterOptRoleElem		{ $$ = lappend($1, $2); }
+			| AlterOptRoleList AlterOnlyOptRoleElem	{ $$ = lappend($1, $2); }
 			| /* EMPTY */							{ $$ = NIL; }
 		;
 
@@ -1263,6 +1265,55 @@ AlterOptRoleElem:
 				}
 		;
 
+OptFirstOrSecond:
+			FIRST_P 			{ $$ = true; }
+			| SECOND_P 			{ $$ = false; }
+		;
+
+/*
+ * AlterOnlyOptRoleElem is separate from AlterOptRoleElem because these options
+ * are not available to the CREATE ROLE command.
+ */
+AlterOnlyOptRoleElem:
+			ADD_P OptFirstOrSecond PASSWORD Sconst
+				{
+					bool first = $2;
+
+					if (first)
+						$$ = makeDefElem("add-first-password",
+										(Node *) makeString($4), @1);
+					else
+						$$ = makeDefElem("add-second-password",
+										(Node *) makeString($4), @1);
+				}
+			| DROP OptFirstOrSecond PASSWORD
+				{
+					bool first = $2;
+
+					if (first)
+						$$ = makeDefElem("drop-password",
+										(Node *) makeString("first"), @1);
+					else
+						$$ = makeDefElem("drop-password",
+										(Node *) makeString("second"), @1);
+				}
+			| DROP ALL PASSWORD
+				{
+					$$ = makeDefElem("drop-all-password", (Node *) NULL, @1);
+				}
+			| OptFirstOrSecond PASSWORD VALID UNTIL Sconst
+				{
+					bool first = $1;
+
+					if (first)
+						$$ = makeDefElem("first-password-valid-until",
+										(Node *) makeString($5), @1);
+					else
+						$$ = makeDefElem("second-password-valid-until",
+										(Node *) makeString($5), @1);
+				}
+		;
+
 CreateOptRoleElem:
 			AlterOptRoleElem			{ $$ = $1; }
 			/* The following are not supported by ALTER ROLE/USER/GROUP */
diff --git a/src/test/regress/expected/password_rollover.out b/src/test/regress/expected/password_rollover.out
new file mode 100644
index 0000000000..44522987f8
--- /dev/null
+++ b/src/test/regress/expected/password_rollover.out
@@ -0,0 +1,161 @@
+--
+-- Tests for password rollovers
+--
+SET password_encryption = 'md5';
+-- Create a role, as usual
+CREATE ROLE regress_password_rollover1 PASSWORD 'p1' LOGIN;
+-- the rolpassword field should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           |             rolpassword             | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil 
+----------------------------+-------------------------------------+---------------+-------------------+---------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 |               |                   | 
+(1 row)
+
+-- Add another password that the role can use for authentication.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p2';
+-- the rolpassword and rolsecondpassword fields should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           |             rolpassword             | rolvaliduntil |          rolsecondpassword          | rolsecondvaliduntil 
+----------------------------+-------------------------------------+---------------+-------------------------------------+---------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 |               | md5c72e860974ea678511e200ded12780b6 | 
+(1 row)
+
+-- Set second password's expiration time.
+ALTER ROLE regress_password_rollover1 SECOND PASSWORD VALID UNTIL '2021/01/01';
+-- the rolvaliduntil field should be null, and other should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           |             rolpassword             | rolvaliduntil |          rolsecondpassword          |     rolsecondvaliduntil      
+----------------------------+-------------------------------------+---------------+-------------------------------------+------------------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 |               | md5c72e860974ea678511e200ded12780b6 | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+ALTER ROLE regress_password_rollover1 FIRST PASSWORD VALID UNTIL '2022/01/01';
+-- All fields should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           |             rolpassword             |        rolvaliduntil         |          rolsecondpassword          |     rolsecondvaliduntil      
+----------------------------+-------------------------------------+------------------------------+-------------------------------------+------------------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 | Sat Jan 01 00:00:00 2022 PST | md5c72e860974ea678511e200ded12780b6 | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+-- Setting a password to null does not set its expiration time to null
+ALTER ROLE regress_password_rollover1 PASSWORD NULL;
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           | rolpassword |        rolvaliduntil         |          rolsecondpassword          |     rolsecondvaliduntil      
+----------------------------+-------------+------------------------------+-------------------------------------+------------------------------
+ regress_password_rollover1 |             | Sat Jan 01 00:00:00 2022 PST | md5c72e860974ea678511e200ded12780b6 | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+-- If, for some reason, the role wants to get rid of the latest password added.
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+-- the rolpassword and rolsecondpassword fields should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           | rolpassword |        rolvaliduntil         | rolsecondpassword |     rolsecondvaliduntil      
+----------------------------+-------------+------------------------------+-------------------+------------------------------
+ regress_password_rollover1 |             | Sat Jan 01 00:00:00 2022 PST |                   | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+-- Add a new password in 'second' slot
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p3' SECOND PASSWORD VALID UNTIL '2023/01/01';
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           | rolpassword |        rolvaliduntil         |          rolsecondpassword          |     rolsecondvaliduntil      
+----------------------------+-------------+------------------------------+-------------------------------------+------------------------------
+ regress_password_rollover1 |             | Sat Jan 01 00:00:00 2022 PST | md53dff5d9eee2beb63399f1900a2371fcb | Sun Jan 01 00:00:00 2023 PST
+(1 row)
+
+-- VALID UNTIL must not be allowed when ADDing a password, to avoid the
+-- confusing invocation where the command may seem to do one thing but actually
+-- does something else. The following may seem like it will add a 'second'
+-- password with a new expiration, but, if allowed, this will set the expiration
+-- time on the _first_ password.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p4' VALID UNTIL '2023/01/01';
+ERROR:  conflicting or redundant options
+LINE 1: ...gress_password_rollover1 ADD SECOND PASSWORD 'p4' VALID UNTI...
+                                                             ^
+-- Even though both, the password and the expiration, refer to the first
+-- password, we disallow it to be consistent with the previous command's
+-- behaviour.
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p4' VALID UNTIL '2023/01/01';
+ERROR:  conflicting or redundant options
+LINE 1: ...egress_password_rollover1 ADD FIRST PASSWORD 'p4' VALID UNTI...
+                                                             ^
+-- Set the first password
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p5';
+-- Attempting to add a password while the respective slot is occupied
+-- results in error
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p6';
+ERROR:  first password is already in use
+DETAIL:  Use ALTER ROLE DROP FIRST PASSWORD.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p6';
+ERROR:  second password is already in use
+DETAIL:  Use ALTER ROLE DROP SECOND PASSWORD
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+-- The rolsecondpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           |             rolpassword             |        rolvaliduntil         | rolsecondpassword |     rolsecondvaliduntil      
+----------------------------+-------------------------------------+------------------------------+-------------------+------------------------------
+ regress_password_rollover1 | md5cc8c5dac5560a2fead71cfba4625a2c7 | Sat Jan 01 00:00:00 2022 PST |                   | Sun Jan 01 00:00:00 2023 PST
+(1 row)
+
+-- Use scram-sha-256 for password storage
+SET password_encryption = 'scram-sha-256';
+-- Trying to add a scram-sha-256 based password, while the other password uses
+-- md5, should raise an error.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p7'
+    SECOND PASSWORD VALID UNTIL 'Infinity';
+ERROR:  role has an md5 password
+DETAIL:  The new password must also use md5.
+-- Drop the first password
+ALTER ROLE regress_password_rollover1 DROP FIRST PASSWORD;
+-- Adding a scram-sha-256 based password should now be allowed.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p7'
+    SECOND PASSWORD VALID UNTIL 'Infinity';
+-- The rolsecondpassword field should now contain a SCRAM secret, and the rolpassword field should now be null.
+SELECT rolname, rolpassword, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           | rolpassword |        rolvaliduntil         |             rolsecondpassword_masked              | rolsecondvaliduntil 
+----------------------------+-------------+------------------------------+---------------------------------------------------+---------------------
+ regress_password_rollover1 |             | Sat Jan 01 00:00:00 2022 PST | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> | infinity
+(1 row)
+
+-- Adding another scram-sha-256 based password should also be allowed.
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p8'
+    FIRST PASSWORD VALID UNTIL 'Infinity';
+-- The rolpassword and rolsecondpassword field should now contain a SCRAM secret
+SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+          rolname           |                rolpassword_masked                 | rolvaliduntil |             rolsecondpassword_masked              | rolsecondvaliduntil 
+----------------------------+---------------------------------------------------+---------------+---------------------------------------------------+---------------------
+ regress_password_rollover1 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> | infinity      | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> | infinity
+(1 row)
+
+-- Switch back to md5 for password storage
+SET password_encryption = 'md5';
+-- Ensure the second password is empty, before we populate it
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+-- Trying to add an md5 based password, while the other password uses
+-- scram-sha-256, should raise an error.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p9'
+    SECOND PASSWORD VALID UNTIL 'Infinity';
+ERROR:  role has a scram-sha-256 password
+DETAIL:  The new password must also use scram-sha-256.
+DROP ROLE regress_password_rollover1;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..5efad7f3ad 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -68,6 +68,11 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
 # ----------
 test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated join_hash
 
+# ----------
+# Another group of parallel tests
+# ----------
+test: password_rollover
+
 # ----------
 # Additional BRIN tests
 # ----------
diff --git a/src/test/regress/sql/password_rollover.sql b/src/test/regress/sql/password_rollover.sql
new file mode 100644
index 0000000000..f630da30c2
--- /dev/null
+++ b/src/test/regress/sql/password_rollover.sql
@@ -0,0 +1,130 @@
+--
+-- Tests for password rollovers
+--
+
+SET password_encryption = 'md5';
+
+-- Create a role, as usual
+CREATE ROLE regress_password_rollover1 PASSWORD 'p1' LOGIN;
+
+-- the rolpassword field should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Add another password that the role can use for authentication.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p2';
+
+-- the rolpassword and rolsecondpassword fields should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Set second password's expiration time.
+ALTER ROLE regress_password_rollover1 SECOND PASSWORD VALID UNTIL '2021/01/01';
+
+-- the rolvaliduntil field should be null, and other should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+ALTER ROLE regress_password_rollover1 FIRST PASSWORD VALID UNTIL '2022/01/01';
+
+-- All fields should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Setting a password to null does not set its expiration time to null
+ALTER ROLE regress_password_rollover1 PASSWORD NULL;
+
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- If, for some reason, the role wants to get rid of the latest password added.
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+
+-- the rolpassword and rolsecondpassword fields should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Add a new password in 'second' slot
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p3' SECOND PASSWORD VALID UNTIL '2023/01/01';
+
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- VALID UNTIL must not be allowed when ADDing a password, to avoid the
+-- confusing invocation where the command may seem to do one thing but actually
+-- does something else. The following may seem like it will add a 'second'
+-- password with a new expiration, but, if allowed, this will set the expiration
+-- time on the _first_ password.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p4' VALID UNTIL '2023/01/01';
+
+-- Even though both, the password and the expiration, refer to the first
+-- password, we disallow it to be consistent with the previous command's
+-- behaviour.
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p4' VALID UNTIL '2023/01/01';
+
+-- Set the first password
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p5';
+
+-- Attempting to add a password while the respective slot is occupied
+-- results in error
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p6';
+
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p6';
+
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+
+-- The rolsecondpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Use scram-sha-256 for password storage
+SET password_encryption = 'scram-sha-256';
+
+-- Trying to add a scram-sha-256 based password, while the other password uses
+-- md5, should raise an error.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p7'
+    SECOND PASSWORD VALID UNTIL 'Infinity';
+
+-- Drop the first password
+ALTER ROLE regress_password_rollover1 DROP FIRST PASSWORD;
+
+-- Adding a scram-sha-256 based password should now be allowed.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p7'
+    SECOND PASSWORD VALID UNTIL 'Infinity';
+
+-- The rolsecondpassword field should now contain a SCRAM secret, and the rolpassword field should now be null.
+SELECT rolname, rolpassword, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Adding another scram-sha-256 based password should also be allowed.
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p8'
+    FIRST PASSWORD VALID UNTIL 'Infinity';
+
+-- The rolpassword and rolsecondpassword field should now contain a SCRAM secret
+SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+    FROM pg_authid
+    WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Switch back to md5 for password storage
+SET password_encryption = 'md5';
+
+-- Ensure the second password is empty, before we populate it
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+
+-- Trying to add an md5 based password, while the other password uses
+-- scram-sha-256, should raise an error.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p9'
+    SECOND PASSWORD VALID UNTIL 'Infinity';
+
+DROP ROLE regress_password_rollover1;
-- 
2.41.0