Subject: Re: [PoC/RFC] Multiple passwords, interval expirations

From: Joshua Brindle <[email-masked]>
Date: Wed, 2 Mar 2022 13:02:18 -0500

On Wed, Mar 2, 2022 at 10:35 AM Joshua Brindle
<[email-masked]> wrote:
>
> On Wed, Mar 2, 2022 at 9:58 AM Joshua Brindle
> <[email-masked]> wrote:
> >
> > This is not intended for PG15.
> >
> > Attached are a proof of concept patchset to implement multiple valid
> > passwords, which have independent expirations, set by a GUC or SQL
> > using an interval.
> >
>
> <snip>
>
> > postgres=# select * from pg_auth_password ;
> >  roleid |  name   |
> >            password
> >                     |          expiration
> > --------+---------+-------------------------------------------------------------------------------------------------------------------
> > --------------------+-------------------------------
> >      10 | __def__ |
> > SCRAM-SHA-256$4096:yGiHIYPwc2az7xj/7TIyTA==$OQL/AEcEY1yOCNbrZEj4zDvNnOLpIqltOW1uQvosLvc=:9VRRppuIkSrwhiBN5ePy8wB1y
> > zDa/2uX0WUx6gXi93E= |
> >   16384 | __def__ |
> > SCRAM-SHA-256$4096:AAAAAAAAAAAAAAAAAAAAAA==$1Ivp4d+vAWxowpuGEn05KR9lxyGOms3yy85k3D7XpBg=:k8xUjU6xrJG17PMGa/Zya6pAE
> > /M7pEDaoIFmWvNIEUg= | 2022-03-02 06:52:31.217193-08
> >   16384 | newone  |
> > SCRAM-SHA-256$4096:AAAAAAAAAAAAAAAAAAAAAA==$WK3+41CCGDognSnZrtpHhv00z9LuVUjHR1hWq8T1+iE=:w2C5GuhgiEB7wXqPxYfxBKB+e
> > hm4h6Oeif1uzpPIFVk= | 2022-03-03 06:47:53.728159-08
> > (3 rows)
>
> There's obviously a salt problem here that I'll need to fix that
> apparently snuck in at the last rebase, but this brings up one aspect
> of the patchset I didn't mention in the original email:
>

Attached are fixed patches rebased against the lastest master.

> For the SCRAM protocol to work as is with existing clients the salt
> for each password must be the same. Right now ALTER USER will find and
> reuse the salt, but a user passing in a pre-computed SCRAM secret
> currently has no way to get the salt.
>
> for \password (we'll need a new one that takes a password name) I was
> thinking libpq could hold onto the salt that was used to log in, but
> for outside computation we'll need some way for the client to request
> it.
>
> None of that is done yet.

---

From: Joshua Brindle <[email-masked]>
Date: Fri, 8 Apr 2022 13:04:22 -0400

> > None of that is done yet.

Now that the commitfest is over these are rebased on master.

It's unclear if I will be able to continue working on this featureset,
this email address will be inactive after today.

---

From: Jacob Champion <[email-masked]>
Date: Wed, 29 Jun 2022 14:21:53 -0700

> this email address will be inactive after today.

I'm assuming the answer to this was "no". Is there any interest out
there to pick this up for the July CF?

--Jacob

---

From: Stephen Frost <[email-masked]>
Date: Wed, 29 Jun 2022 17:27:37 -0400

Greetings,

> there to pick this up for the July CF?

Short answer to that is yes, I’m interested in continuing this (though
certainly would welcome it if there are others who are also interested, and
may be able to bring someone else to help work on it too but that might be
more August / September time frame).

Thanks,

Stephen

---

From: Gurjeet Singh <[email-masked]>
Date: Thu, 30 Jun 2022 16:53:58 -0700

I am planning on picking it up next week; right now picking up steam,
and reviewing a different, smaller patch.

At his behest, I had a conversation with Joshua (OP), and have his
support to pick up and continue working on this patch. I have a some
ideas of my own, on what this patch should do, but since I haven't
fully reviewed the (bulky) patch, I'll reserve my proposals until I
wrap my head around it.

Please expect some activity on this patch towards the end of next week.

BCC: Joshua's new work email.

Best regards,
Gurjeet
http://Gurje.et

On Wed, Jun 29, 2022 at 2:27 PM Stephen Frost <[email-masked]> wrote:
>
> Greetings,
>
> On Wed, Jun 29, 2022 at 17:22 Jacob Champion <[email-masked]> wrote:
>>
> Short answer to that is yes, I’m interested in continuing this (though certainly would welcome it if there are others who are also interested, and may be able to bring someone else to help work on it too but that might be more August / September time frame).
>
> Thanks,
>
> Stephen

---

From: Stephen Frost <[email-masked]>
Date: Thu, 30 Jun 2022 20:20:34 -0400

Greetings,

> and reviewing a different, smaller patch.

Great!  Glad that others are interested in this.

> fully reviewed the (bulky) patch, I'll reserve my proposals until I

I'd be curious as to your thought as to what the patch should be doing.
Joshua and I had discussed it at some length as he was working on it.

> Please expect some activity on this patch towards the end of next week.

I've gone ahead and updated it, cleaned up a couple things, and make it
so that check-world actually passes with it.  Attached is an updated
version and I'll add it to the July commitfest.

Thanks!

Stephen
From 1203b8a92e4e6c51c7e5aa244d8ee9bbd2f74e66 Mon Sep 17 00:00:00 2001
From: Stephen Frost <[email-masked]>
Date: Thu, 30 Jun 2022 18:15:52 -0400
Subject: [PATCH 1/3] Move rolpassword out of pg_authid into a new table

In preparation for more flexibility in password
management the rolpassword column needs to be
moved into a new table.

Author: Joshua Brindle
---
 src/backend/catalog/Makefile                 |   2 +-
 src/backend/catalog/catalog.c                |   8 +-
 src/backend/catalog/system_views.sql         |   5 +-
 src/backend/commands/user.c                  | 226 ++++++++++++----
 src/backend/libpq/auth-sasl.c                |   2 +-
 src/backend/libpq/auth-scram.c               |   4 +-
 src/backend/libpq/auth.c                     |  16 ++
 src/backend/libpq/crypt.c                    |  40 ++-
 src/backend/utils/cache/catcache.c           |   1 +
 src/backend/utils/cache/relcache.c           |  13 +-
 src/backend/utils/cache/syscache.c           |  12 +
 src/bin/initdb/initdb.c                      |   2 +-
 src/bin/pg_dump/pg_dumpall.c                 |  13 +-
 src/common/scram-common.c                    |   2 +-
 src/include/catalog/pg_auth_password.h       |  50 ++++
 src/include/catalog/pg_authid.dat            |  26 +-
 src/include/catalog/pg_authid.h              |   7 +-
 src/include/commands/user.h                  |   1 +
 src/include/libpq/crypt.h                    |   2 +-
 src/include/utils/syscache.h                 |   1 +
 src/test/regress/expected/create_index.out   |  14 +-
 src/test/regress/expected/oidjoins.out       |   1 +
 src/test/regress/expected/password.out       |  44 ++--
 src/test/regress/expected/roleattributes.out | 256 +++++++++----------
 src/test/regress/expected/rules.out          |   5 +-
 src/test/regress/expected/tablespace.out     |  12 +-
 src/test/regress/sql/create_index.sql        |  10 +-
 src/test/regress/sql/password.sql            |  32 ++-
 src/test/regress/sql/roleattributes.sql      |  64 ++---
 src/test/regress/sql/tablespace.sql          |   8 +-
 30 files changed, 563 insertions(+), 316 deletions(-)
 create mode 100644 src/include/catalog/pg_auth_password.h

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 89a0221ec9..450cfd9a58 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -63,7 +63,7 @@ CATALOG_HEADERS := \
 	pg_rewrite.h pg_trigger.h pg_event_trigger.h pg_description.h \
 	pg_cast.h pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
 	pg_database.h pg_db_role_setting.h pg_tablespace.h \
-	pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
+	pg_authid.h pg_auth_members.h pg_auth_password.h pg_shdepend.h pg_shdescription.h \
 	pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
 	pg_ts_parser.h pg_ts_template.h pg_extension.h \
 	pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index e784538aae..6a32e44c39 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -28,6 +28,7 @@
 #include "catalog/catalog.h"
 #include "catalog/namespace.h"
 #include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_password.h"
 #include "catalog/pg_authid.h"
 #include "catalog/pg_database.h"
 #include "catalog/pg_db_role_setting.h"
@@ -246,6 +247,7 @@ IsSharedRelation(Oid relationId)
 {
 	/* These are the shared catalogs (look for BKI_SHARED_RELATION) */
 	if (relationId == AuthIdRelationId ||
+		relationId == AuthPasswordRelationId ||
 		relationId == AuthMemRelationId ||
 		relationId == DatabaseRelationId ||
 		relationId == DbRoleSettingRelationId ||
@@ -260,6 +262,8 @@ IsSharedRelation(Oid relationId)
 	/* These are their indexes */
 	if (relationId == AuthIdOidIndexId ||
 		relationId == AuthIdRolnameIndexId ||
+		relationId == AuthPasswordRoleOidIndexId ||
+		relationId == AuthMemRoleMemIndexId ||
 		relationId == AuthMemMemRoleIndexId ||
 		relationId == AuthMemRoleMemIndexId ||
 		relationId == DatabaseNameIndexId ||
@@ -279,8 +283,8 @@ IsSharedRelation(Oid relationId)
 		relationId == TablespaceOidIndexId)
 		return true;
 	/* These are their toast tables and toast indexes */
-	if (relationId == PgAuthidToastTable ||
-		relationId == PgAuthidToastIndex ||
+	if (relationId == PgAuthPasswordToastTable ||
+		relationId == PgAuthPasswordToastIndex ||
 		relationId == PgDatabaseToastTable ||
 		relationId == PgDatabaseToastIndex ||
 		relationId == PgDbRoleSettingToastTable ||
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fedaed533b..6989594022 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -40,11 +40,14 @@ CREATE VIEW pg_shadow AS
         rolsuper AS usesuper,
         rolreplication AS userepl,
         rolbypassrls AS usebypassrls,
-        rolpassword AS passwd,
+        p.password AS passwd,
         rolvaliduntil AS valuntil,
         setconfig AS useconfig
     FROM pg_authid LEFT JOIN pg_db_role_setting s
     ON (pg_authid.oid = setrole AND setdatabase = 0)
+    LEFT JOIN pg_auth_password p
+    ON p.roleid = pg_authid.oid
+
     WHERE rolcanlogin;
 
 REVOKE ALL ON pg_shadow FROM public;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 984305ba31..b224b4a2e9 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -23,6 +23,7 @@
 #include "catalog/objectaccess.h"
 #include "catalog/pg_auth_members.h"
 #include "catalog/pg_authid.h"
+#include "catalog/pg_auth_password.h"
 #include "catalog/pg_database.h"
 #include "catalog/pg_db_role_setting.h"
 #include "commands/comment.h"
@@ -64,6 +65,42 @@ have_createrole_privilege(void)
 	return has_createrole_privilege(GetUserId());
 }
 
+/*
+ * Is the role able to log in
+*/
+bool
+is_role_valid(const char *rolename, char **logdetail)
+{
+	HeapTuple	roleTup;
+	Datum		datum;
+	bool		isnull;
+	TimestampTz vuntil = 0;
+
+	/* Get role info from pg_authid */
+	roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(rolename));
+	if (!HeapTupleIsValid(roleTup))
+	{
+		*logdetail = psprintf(_("Role \"%s\" does not exist."),
+							  rolename);
+		return false;			/* no such user */
+	}
+
+	datum = SysCacheGetAttr(AUTHNAME, roleTup,
+							Anum_pg_authid_rolvaliduntil, &isnull);
+	ReleaseSysCache(roleTup);
+
+	if (!isnull)
+		vuntil = DatumGetTimestampTz(datum);
+
+	if (!isnull && vuntil < GetCurrentTimestamp())
+	{
+		*logdetail = psprintf(_("User \"%s\" has an expired password."), rolename);
+		return false;
+	}
+
+	return true;
+}
+
 
 /*
  * CREATE ROLE
@@ -351,43 +388,6 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
 	new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
 	new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
-
-	if (password)
-	{
-		char	   *shadow_pass;
-		const char *logdetail = NULL;
-
-		/*
-		 * Don't allow an empty password. Libpq treats an empty password the
-		 * same as no password at all, and won't even try to authenticate. But
-		 * other clients might, so allowing it would be confusing. By clearing
-		 * the password when an empty string is specified, the account is
-		 * consistently locked for all clients.
-		 *
-		 * Note that this only covers passwords stored in the database itself.
-		 * There are also checks in the authentication code, to forbid an
-		 * empty password from being used with authentication methods that
-		 * fetch the password from an external system, like LDAP or PAM.
-		 */
-		if (password[0] == '\0' ||
-			plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK)
-		{
-			ereport(NOTICE,
-					(errmsg("empty string is not a valid password, clearing password")));
-			new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
-		}
-		else
-		{
-			/* Encrypt the password to the requested format. */
-			shadow_pass = encrypt_password(Password_encryption, stmt->role,
-										   password);
-			new_record[Anum_pg_authid_rolpassword - 1] =
-				CStringGetTextDatum(shadow_pass);
-		}
-	}
-	else
-		new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
-
 	new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
 	new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
 
@@ -422,6 +422,60 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	 */
 	CatalogTupleInsert(pg_authid_rel, tuple);
 
+	if (password)
+	{
+		char	   *shadow_pass;
+		char	   *logdetail;
+		Datum		new_password_record[Natts_pg_auth_password];
+		bool		new_password_record_nulls[Natts_pg_auth_password];
+		Relation	pg_auth_password_rel;
+		TupleDesc	pg_auth_password_dsc;
+		HeapTuple	new_tuple;
+
+		/*
+		 * Don't allow an empty password. Libpq treats an empty password the
+		 * same as no password at all, and won't even try to authenticate. But
+		 * other clients might, so allowing it would be confusing. By clearing
+		 * the password when an empty string is specified, the account is
+		 * consistently locked for all clients.
+		 *
+		 * Note that this only covers passwords stored in the database itself.
+		 * There are also checks in the authentication code, to forbid an
+		 * empty password from being used with authentication methods that
+		 * fetch the password from an external system, like LDAP or PAM.
+		 */
+		if (password[0] == '\0' ||
+			plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK)
+		{
+			ereport(NOTICE,
+					(errmsg("empty string is not a valid password, clearing password")));
+		}
+		else
+		{
+			/* Encrypt the password to the requested format. */
+			shadow_pass = encrypt_password(Password_encryption, stmt->role,
+										   password);
+
+			MemSet(new_password_record, 0, sizeof(new_password_record));
+			MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
+
+			/* open password table and insert it. */
+			pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+			pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
+
+			new_password_record[Anum_pg_auth_password_password - 1] =
+				CStringGetTextDatum(shadow_pass);
+			new_password_record[Anum_pg_auth_password_roleid - 1] = roleid;
+
+			new_tuple = heap_form_tuple(pg_auth_password_dsc,
+										new_password_record, new_password_record_nulls);
+			CatalogTupleInsert(pg_auth_password_rel, new_tuple);
+			heap_freetuple(new_tuple);
+			table_close(pg_auth_password_rel, NoLock);
+
+		}
+	}
+
 	/*
 	 * Advance command counter so we can see new record; else tests in
 	 * AddRoleMems may fail.
@@ -495,6 +549,9 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	Datum		new_record[Natts_pg_authid];
 	bool		new_record_nulls[Natts_pg_authid];
 	bool		new_record_repl[Natts_pg_authid];
+	Datum		new_password_record[Natts_pg_auth_password];
+	bool		new_password_record_nulls[Natts_pg_auth_password];
+	bool		new_password_record_repl[Natts_pg_auth_password];
 	Relation	pg_authid_rel;
 	TupleDesc	pg_authid_dsc;
 	HeapTuple	tuple,
@@ -624,6 +681,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	rolename = pstrdup(NameStr(authform->rolname));
 	roleid = authform->oid;
 
+
 	/*
 	 * To mess with a superuser or replication role in any way you gotta be
 	 * superuser.  We also insist on superuser to change the BYPASSRLS
@@ -694,6 +752,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	MemSet(new_record, 0, sizeof(new_record));
 	MemSet(new_record_nulls, false, sizeof(new_record_nulls));
 	MemSet(new_record_repl, false, sizeof(new_record_repl));
+	MemSet(new_password_record, 0, sizeof(new_password_record));
+	MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
+	MemSet(new_password_record_repl, false, sizeof(new_password_record_repl));
+
 
 	/*
 	 * issuper/createrole/etc
@@ -752,24 +814,24 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 		{
 			ereport(NOTICE,
 					(errmsg("empty string is not a valid password, clearing password")));
-			new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
+			new_password_record_nulls[Anum_pg_auth_password_password - 1] = true;
 		}
 		else
 		{
 			/* Encrypt the password to the requested format. */
 			shadow_pass = encrypt_password(Password_encryption, rolename,
 										   password);
-			new_record[Anum_pg_authid_rolpassword - 1] =
+			new_password_record[Anum_pg_auth_password_password - 1] =
 				CStringGetTextDatum(shadow_pass);
 		}
-		new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
+		new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
 	}
 
 	/* unset password */
 	if (dpassword && dpassword->arg == NULL)
 	{
-		new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
-		new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
+		new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
+		new_password_record_nulls[Anum_pg_auth_password_password - 1] = true;
 	}
 
 	/* valid until */
@@ -786,12 +848,48 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record,
 								  new_record_nulls, new_record_repl);
 	CatalogTupleUpdate(pg_authid_rel, &tuple->t_self, new_tuple);
-
-	InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
-
 	ReleaseSysCache(tuple);
 	heap_freetuple(new_tuple);
 
+	if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true)
+	{
+		Relation	pg_auth_password_rel;
+		TupleDesc	pg_auth_password_dsc;
+		HeapTuple   password_tuple;
+
+		pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+		pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
+		password_tuple = SearchSysCache1(AUTHPASSWORD, ObjectIdGetDatum(roleid));
+
+		if (new_password_record_nulls[Anum_pg_auth_password_password - 1] == true) /* delete existing password */
+		{
+			if (HeapTupleIsValid(password_tuple)) {
+				CatalogTupleDelete(pg_auth_password_rel, &password_tuple->t_self);
+				ReleaseSysCache(password_tuple);
+			}
+		}
+		else if (HeapTupleIsValid(password_tuple)) /* update existing password */
+		{
+			new_tuple = heap_modify_tuple(password_tuple, pg_auth_password_dsc, new_password_record,
+										new_password_record_nulls, new_password_record_repl);
+			CatalogTupleUpdate(pg_auth_password_rel, &password_tuple->t_self, new_tuple);
+			ReleaseSysCache(password_tuple);
+			heap_freetuple(new_tuple);
+		}
+		else /* insert new password */
+		{
+			new_password_record[Anum_pg_auth_password_roleid - 1] = roleid;
+
+			new_tuple = heap_form_tuple(pg_auth_password_dsc,
+										new_password_record, new_password_record_nulls);
+			CatalogTupleInsert(pg_auth_password_rel, new_tuple);
+			heap_freetuple(new_tuple);
+		}
+
+		table_close(pg_auth_password_rel, NoLock);
+	}
+	InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
+
 	/*
 	 * Advance command counter so we can see new record; else tests in
 	 * AddRoleMems may fail.
@@ -901,7 +999,6 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
 	return roleid;
 }
 
-
 /*
  * DROP ROLE
  */
@@ -909,7 +1006,8 @@ void
 DropRole(DropRoleStmt *stmt)
 {
 	Relation	pg_authid_rel,
-				pg_auth_members_rel;
+				pg_auth_members_rel,
+				pg_auth_password_rel;
 	ListCell   *item;
 
 	if (!have_createrole_privilege())
@@ -923,6 +1021,8 @@ DropRole(DropRoleStmt *stmt)
 	 */
 	pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
 	pg_auth_members_rel = table_open(AuthMemRelationId, RowExclusiveLock);
+	pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+
 
 	foreach(item, stmt->roles)
 	{
@@ -1056,6 +1156,15 @@ DropRole(DropRoleStmt *stmt)
 		DeleteSharedComments(roleid, AuthIdRelationId);
 		DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
 
+		/*
+		 * Drop password
+		 */
+		tuple = SearchSysCache1(AUTHPASSWORD, roleid);
+		if (HeapTupleIsValid(tuple)) {
+			CatalogTupleDelete(pg_auth_password_rel, &tuple->t_self);
+			ReleaseSysCache(tuple);
+		}
+
 		/*
 		 * Remove settings for this role.
 		 */
@@ -1077,7 +1186,9 @@ DropRole(DropRoleStmt *stmt)
 	 * Now we can clean up; but keep locks until commit.
 	 */
 	table_close(pg_auth_members_rel, NoLock);
+	table_close(pg_auth_password_rel, NoLock);
 	table_close(pg_authid_rel, NoLock);
+
 }
 
 /*
@@ -1087,11 +1198,12 @@ ObjectAddress
 RenameRole(const char *oldname, const char *newname)
 {
 	HeapTuple	oldtuple,
-				newtuple;
+				newtuple,
+				passtuple;
 	TupleDesc	dsc;
 	Relation	rel;
 	Datum		datum;
-	bool		isnull;
+	bool		isnull = true;
 	Datum		repl_val[Natts_pg_authid];
 	bool		repl_null[Natts_pg_authid];
 	bool		repl_repl[Natts_pg_authid];
@@ -1189,14 +1301,24 @@ RenameRole(const char *oldname, const char *newname)
 															   CStringGetDatum(newname));
 	repl_null[Anum_pg_authid_rolname - 1] = false;
 
-	datum = heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull);
+	passtuple = SearchSysCache1(AUTHPASSWORD, roleid);
+
+	if (HeapTupleIsValid(passtuple))
+		datum = SysCacheGetAttr(AUTHPASSWORD, passtuple,
+								Anum_pg_auth_password_password, &isnull);
 
 	if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
 	{
+		Relation	pg_auth_password_rel;
+
 		/* MD5 uses the username as salt, so just clear it on a rename */
-		repl_repl[Anum_pg_authid_rolpassword - 1] = true;
-		repl_null[Anum_pg_authid_rolpassword - 1] = true;
+		pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
 
+		if (HeapTupleIsValid(passtuple)) {
+			CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self);
+			ReleaseSysCache(passtuple);
+		}
+		table_close(pg_auth_password_rel, NoLock);
 		ereport(NOTICE,
 				(errmsg("MD5 password cleared because of role rename")));
 	}
diff --git a/src/backend/libpq/auth-sasl.c b/src/backend/libpq/auth-sasl.c
index a1d7dbb6d5..805b3695b7 100644
--- a/src/backend/libpq/auth-sasl.c
+++ b/src/backend/libpq/auth-sasl.c
@@ -33,7 +33,7 @@
  * implementation.
  *
  * shadow_pass is an optional pointer to the stored secret of the role
- * authenticated, from pg_authid.rolpassword.  For mechanisms that use
+ * authenticated, from pg_auth_password.password.  For mechanisms that use
  * shadowed passwords, a NULL pointer here means that an entry could not
  * be found for the role (or the user does not exist), and the mechanism
  * should fail the authentication exchange.
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index ee7f52218a..795f1cba55 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -220,7 +220,7 @@ scram_get_mechanisms(Port *port, StringInfo buf)
  * It should be one of the mechanisms that we support, as returned by
  * scram_get_mechanisms().
  *
- * 'shadow_pass' is the role's stored secret, from pg_authid.rolpassword.
+ * 'shadow_pass' is the role's stored secret, from pg_auth_password.password.
  * The username was provided by the client in the startup message, and is
  * available in port->user_name.  If 'shadow_pass' is NULL, we still perform
  * an authentication exchange, but it will fail, as if an incorrect password
@@ -454,7 +454,7 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 }
 
 /*
- * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
+ * Construct a SCRAM secret, for storing in pg_auth_password.password.
  *
  * The result is palloc'd, so caller is responsible for freeing it.
  */
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index efc53f3135..269cf286ad 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -592,10 +592,26 @@ ClientAuthentication(Port *port)
 
 		case uaMD5:
 		case uaSCRAM:
+			/*
+			 * check to be sure we are not past rolvaliduntil
+			 */
+			if (!is_role_valid(port->user_name, &logdetail)) {
+				status = STATUS_ERROR;
+				break;
+			}
+
 			status = CheckPWChallengeAuth(port, &logdetail);
 			break;
 
 		case uaPassword:
+			/*
+			 * check to be sure we are not past rolvaliduntil
+			 */
+			if (!is_role_valid(port->user_name, &logdetail)) {
+				status = STATUS_ERROR;
+				break;
+			}
+
 			status = CheckPasswordAuth(port, &logdetail);
 			break;
 
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 1ff8b0507d..745e61034c 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -2,7 +2,7 @@
  *
  * crypt.c
  *	  Functions for dealing with encrypted passwords stored in
- *	  pg_authid.rolpassword.
+ *	  pg_auth_password.password.
  *
  * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
@@ -16,6 +16,7 @@
 #include <unistd.h>
 
 #include "catalog/pg_authid.h"
+#include "catalog/pg_auth_password.h"
 #include "common/md5.h"
 #include "common/scram-common.h"
 #include "libpq/crypt.h"
@@ -36,8 +37,7 @@
 char *
 get_role_password(const char *role, const char **logdetail)
 {
-	TimestampTz vuntil = 0;
-	HeapTuple	roleTup;
+	HeapTuple	roleTup, passTup;
 	Datum		datum;
 	bool		isnull;
 	char	   *shadow_pass;
@@ -51,33 +51,29 @@ get_role_password(const char *role, const char **logdetail)
 		return NULL;			/* no such user */
 	}
 
-	datum = SysCacheGetAttr(AUTHNAME, roleTup,
-							Anum_pg_authid_rolpassword, &isnull);
-	if (isnull)
+	datum = SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull);
+	
+	passTup = SearchSysCache1(AUTHPASSWORD, datum);
+
+	if (!HeapTupleIsValid(passTup))
 	{
-		ReleaseSysCache(roleTup);
 		*logdetail = psprintf(_("User \"%s\" has no password assigned."),
 							  role);
 		return NULL;			/* user has no password */
 	}
-	shadow_pass = TextDatumGetCString(datum);
-
-	datum = SysCacheGetAttr(AUTHNAME, roleTup,
-							Anum_pg_authid_rolvaliduntil, &isnull);
-	if (!isnull)
-		vuntil = DatumGetTimestampTz(datum);
+	datum = SysCacheGetAttr(AUTHPASSWORD, passTup,
+							Anum_pg_auth_password_password, &isnull);
 
 	ReleaseSysCache(roleTup);
-
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (isnull) /* this should not happen any more but just in case */
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
+		ReleaseSysCache(passTup);
+		*logdetail = psprintf(_("User \"%s\" has no password assigned."),
 							  role);
-		return NULL;
+		return NULL;			/* user has no password */
 	}
+	shadow_pass = TextDatumGetCString(datum);
+	ReleaseSysCache(passTup);
 
 	return shadow_pass;
 }
@@ -156,7 +152,7 @@ encrypt_password(PasswordType target_type, const char *role,
  * Check MD5 authentication response, and return STATUS_OK or STATUS_ERROR.
  *
  * 'shadow_pass' is the user's correct password or password hash, as stored
- * in pg_authid.rolpassword.
+ * in pg_auth_password.password.
  * 'client_pass' is the response given by the remote user to the MD5 challenge.
  * 'md5_salt' is the salt used in the MD5 authentication challenge.
  *
@@ -211,7 +207,7 @@ md5_crypt_verify(const char *role, const char *shadow_pass,
  * Check given password for given user, and return STATUS_OK or STATUS_ERROR.
  *
  * 'shadow_pass' is the user's correct password hash, as stored in
- * pg_authid.rolpassword.
+ * pg_auth_password.password.
  * 'client_pass' is the password given by the remote user.
  *
  * In the error case, store a string at *logdetail that will be sent to the
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 6ae7c1f50b..7c8f864a80 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1104,6 +1104,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
 
 		case AUTHNAME:
 		case AUTHOID:
+		case AUTHPASSWORD:
 		case AUTHMEMMEMROLE:
 		case DATABASEOID:
 
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index f502df91dc..b2ca82d1ab 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -50,6 +50,7 @@
 #include "catalog/pg_attrdef.h"
 #include "catalog/pg_auth_members.h"
 #include "catalog/pg_authid.h"
+#include "catalog/pg_auth_password.h"
 #include "catalog/pg_constraint.h"
 #include "catalog/pg_database.h"
 #include "catalog/pg_namespace.h"
@@ -113,6 +114,7 @@ static const FormData_pg_attribute Desc_pg_proc[Natts_pg_proc] = {Schema_pg_proc
 static const FormData_pg_attribute Desc_pg_type[Natts_pg_type] = {Schema_pg_type};
 static const FormData_pg_attribute Desc_pg_database[Natts_pg_database] = {Schema_pg_database};
 static const FormData_pg_attribute Desc_pg_authid[Natts_pg_authid] = {Schema_pg_authid};
+static const FormData_pg_attribute Desc_pg_auth_password[Natts_pg_auth_password] = {Schema_pg_auth_password};
 static const FormData_pg_attribute Desc_pg_auth_members[Natts_pg_auth_members] = {Schema_pg_auth_members};
 static const FormData_pg_attribute Desc_pg_index[Natts_pg_index] = {Schema_pg_index};
 static const FormData_pg_attribute Desc_pg_shseclabel[Natts_pg_shseclabel] = {Schema_pg_shseclabel};
@@ -3485,6 +3487,7 @@ RelationBuildLocalRelation(const char *relname,
 	{
 		case DatabaseRelationId:
 		case AuthIdRelationId:
+		case AuthPasswordRelationId:
 		case AuthMemRelationId:
 		case RelationRelationId:
 		case AttributeRelationId:
@@ -3905,7 +3908,7 @@ RelationCacheInitialize(void)
  *		RelationCacheInitializePhase2
  *
  *		This is called to prepare for access to shared catalogs during startup.
- *		We must at least set up nailed reldescs for pg_database, pg_authid,
+ *		We must at least set up nailed reldescs for pg_database, pg_authid, pg_auth_password,
  *		pg_auth_members, and pg_shseclabel. Ideally we'd like to have reldescs
  *		for their indexes, too.  We attempt to load this information from the
  *		shared relcache init file.  If that's missing or broken, just make
@@ -3950,8 +3953,10 @@ RelationCacheInitializePhase2(void)
 				  Natts_pg_shseclabel, Desc_pg_shseclabel);
 		formrdesc("pg_subscription", SubscriptionRelation_Rowtype_Id, true,
 				  Natts_pg_subscription, Desc_pg_subscription);
+		formrdesc("pg_auth_password", AuthPasswordRelation_Rowtype_Id, true,
+				  Natts_pg_auth_password, Desc_pg_auth_password);
 
-#define NUM_CRITICAL_SHARED_RELS	5	/* fix if you change list above */
+#define NUM_CRITICAL_SHARED_RELS	6	/* fix if you change list above */
 	}
 
 	MemoryContextSwitchTo(oldcxt);
@@ -4090,8 +4095,10 @@ RelationCacheInitializePhase3(void)
 							AuthMemRelationId);
 		load_critical_index(SharedSecLabelObjectIndexId,
 							SharedSecLabelRelationId);
+		load_critical_index(AuthPasswordRoleOidIndexId,
+							AuthPasswordRelationId);
 
-#define NUM_CRITICAL_SHARED_INDEXES 6	/* fix if you change list above */
+#define NUM_CRITICAL_SHARED_INDEXES 7	/* fix if you change list above */
 
 		criticalSharedRelcachesBuilt = true;
 	}
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 1912b12146..de2b3ec1eb 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -27,6 +27,7 @@
 #include "catalog/pg_amop.h"
 #include "catalog/pg_amproc.h"
 #include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_password.h"
 #include "catalog/pg_authid.h"
 #include "catalog/pg_cast.h"
 #include "catalog/pg_collation.h"
@@ -255,6 +256,17 @@ static const struct cachedesc cacheinfo[] = {
 		},
 		8
 	},
+	{AuthPasswordRelationId,			/* AUTHPASSWORD */
+		AuthPasswordRoleOidIndexId,
+		1,
+		{
+			Anum_pg_auth_password_roleid,
+			0,
+			0,
+			0
+		},
+		8
+	},
 	{
 		CastRelationId,			/* CASTSOURCETARGET */
 		CastSourceTargetIndexId,
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ed6de7ca94..7a39980e05 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1402,7 +1402,7 @@ setup_auth(FILE *cmdfd)
 		 * The authid table shouldn't be readable except through views, to
 		 * ensure passwords are not publicly visible.
 		 */
-		"REVOKE ALL ON pg_authid FROM public;\n\n",
+		"REVOKE ALL ON pg_auth_password FROM public;\n\n",
 		NULL
 	};
 
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index ae41a652d7..64f27aa3f2 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -737,7 +737,18 @@ dumpRoles(PGconn *conn)
 	int			i;
 
 	/* note: rolconfig is dumped later */
-	if (server_version >= 90600)
+	if (server_version >= 150000)
+		printfPQExpBuffer(buf,
+						  "SELECT oid, rolname, rolsuper, rolinherit, "
+						  "rolcreaterole, rolcreatedb, "
+						  "rolcanlogin, rolconnlimit, p.password as rolpassword, "
+						  "rolvaliduntil, rolreplication, rolbypassrls, "
+						  "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
+						  "rolname = current_user AS is_current_user "
+						  "FROM %s LEFT JOIN pg_auth_password p ON %s.oid = p.roleid "
+						  "WHERE rolname !~ '^pg_' "
+						  "ORDER BY 2", role_catalog, role_catalog, role_catalog);
+	else if (server_version >= 90600)
 		printfPQExpBuffer(buf,
 						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, "
diff --git a/src/common/scram-common.c b/src/common/scram-common.c
index 1268625929..d9d0cba737 100644
--- a/src/common/scram-common.c
+++ b/src/common/scram-common.c
@@ -181,7 +181,7 @@ scram_ServerKey(const uint8 *salted_password, uint8 *result,

 /*
- * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
+ * Construct a SCRAM secret, for storing in pg_auth_password.password.
  *
  * The password should already have been processed with SASLprep, if necessary!
  *
diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/pg_auth_password.h
new file mode 100644
index 0000000000..beaa2d40b9
--- /dev/null
+++ b/src/include/catalog/pg_auth_password.h
@@ -0,0 +1,50 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_auth_password.h
+ *	  definition of the "authorization identifier" system catalog (pg_auth_password)
+ *
+ * Portions Copyright (c) 2021, PostgreSQL Global Development Group
+ *
+ * src/include/catalog/pg_auth_password.h
+ *
+ * NOTES
+ *	  The Catalog.pm module reads this file and derives schema
+ *	  information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_AUTH_PASSWORD_H
+#define PG_AUTH_PASSWORD_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_auth_password_d.h"
+
+/* ----------------
+ *		pg_auth_password definition.  cpp turns this into
+ *		typedef struct FormData_pg_auth_password
+ * ----------------
+ */
+CATALOG(pg_auth_password,4548,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4549,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO
+{
+	Oid			roleid BKI_LOOKUP(pg_authid);	/* ID of a role */
+#ifdef CATALOG_VARLEN                /* variable-length fields start here */
+    text            password;        /* password */
+#endif
+} FormData_pg_auth_password;
+
+/* ----------------
+ *		Form_pg_auth_password corresponds to a pointer to a tuple with
+ *		the format of pg_auth_password relation.
+ * ----------------
+ */
+
+DECLARE_TOAST(pg_auth_password, 4175, 4176);
+#define PgAuthPasswordToastTable 4175
+#define PgAuthPasswordToastIndex 4176
+
+typedef FormData_pg_auth_password *Form_pg_auth_password;
+
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4550, AuthPasswordRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops));
+
+
+#endif							/* PG_AUTH_PASSWORD_H */
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 6c28119fa1..da36375a58 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -23,66 +23,66 @@
   rolname => 'POSTGRES', rolsuper => 't', rolinherit => 't',
   rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't',
   rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_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_' },
+  rolvaliduntil => '_null_' },
 { oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINTER',
   rolname => 'pg_checkpointer', rolsuper => 'f', rolinherit => 't',
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
-  rolpassword => '_null_', rolvaliduntil => '_null_' },
+  rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 3512601c80..0cb5675a33 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -42,9 +42,8 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
 	int32		rolconnlimit;	/* max connections allowed (-1=no limit) */
 
 	/* remaining fields may be null; use heap_getattr to read them! */
-#ifdef CATALOG_VARLEN			/* variable-length fields start here */
-	text		rolpassword;	/* password, if any */
-	timestamptz rolvaliduntil;	/* password expiration time, if any */
+#ifdef CATALOG_VARLEN
+	timestamptz rolvaliduntil BKI_FORCE_NULL;	/* role expiration time, if any */
 #endif
 } FormData_pg_authid;
 
@@ -55,8 +54,6 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
  */
 typedef FormData_pg_authid *Form_pg_authid;
 
-DECLARE_TOAST_WITH_MACRO(pg_authid, 4175, 4176, PgAuthidToastTable, PgAuthidToastIndex);
-
 DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, AuthIdRolnameIndexId, on pg_authid using btree(rolname name_ops));
 DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, AuthIdOidIndexId, on pg_authid using btree(oid oid_ops));
 
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index d3dd8303d2..3e1aae5785 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -24,6 +24,7 @@ typedef void (*check_password_hook_type) (const char *username, const char *shad
 
 extern PGDLLIMPORT check_password_hook_type check_password_hook;
 
+extern bool is_role_valid(const char *rolename, char **logdetail);
 extern Oid	CreateRole(ParseState *pstate, CreateRoleStmt *stmt);
 extern Oid	AlterRole(ParseState *pstate, AlterRoleStmt *stmt);
 extern Oid	AlterRoleSet(AlterRoleSetStmt *stmt);
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index 3238cf66d3..b8ff8ccb41 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -21,7 +21,7 @@
  * Plaintext passwords can be passed in by the user, in a CREATE/ALTER USER
  * command. They will be encrypted to MD5 or SCRAM-SHA-256 format, before
  * storing on-disk, so only MD5 and SCRAM-SHA-256 passwords should appear
- * in pg_authid.rolpassword. They are also the allowed values for the
+ * in pg_auth_password.password. They are also the allowed values for the
  * password_encryption GUC.
  */
 typedef enum PasswordType
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 4463ea66be..c7bc648f46 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -43,6 +43,7 @@ enum SysCacheIdentifier
 	AUTHMEMROLEMEM,
 	AUTHNAME,
 	AUTHOID,
+	AUTHPASSWORD,
 	CASTSOURCETARGET,
 	CLAAMNAMENSP,
 	CLAOID,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d55aec3a1d..9875b277f2 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2515,10 +2515,10 @@ REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
 ERROR:  cannot reindex system catalogs concurrently
 REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
 ERROR:  cannot reindex system catalogs concurrently
--- These are the toast table and index of pg_authid.
-REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
+-- These are the toast table and index of pg_database.
+REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table
 ERROR:  cannot reindex system catalogs concurrently
-REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
+REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index
 ERROR:  cannot reindex system catalogs concurrently
 REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
 ERROR:  cannot reindex system catalogs concurrently
@@ -2817,10 +2817,10 @@ ERROR:  must be owner of schema schema_to_reindex
 RESET ROLE;
 GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
 SET SESSION ROLE regress_reindexuser;
-REINDEX TABLE pg_toast.pg_toast_1260;
-ERROR:  must be owner of table pg_toast_1260
-REINDEX INDEX pg_toast.pg_toast_1260_index;
-ERROR:  must be owner of index pg_toast_1260_index
+REINDEX TABLE pg_toast.pg_toast_1262;
+ERROR:  must be owner of table pg_toast_1262
+REINDEX INDEX pg_toast.pg_toast_1262_index;
+ERROR:  must be owner of index pg_toast_1262_index
 -- Clean up
 RESET ROLE;
 REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser;
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 215eb899be..b69a2a72a7 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -197,6 +197,7 @@ NOTICE:  checking pg_tablespace {spcowner} => pg_authid {oid}
 NOTICE:  checking pg_auth_members {roleid} => pg_authid {oid}
 NOTICE:  checking pg_auth_members {member} => pg_authid {oid}
 NOTICE:  checking pg_auth_members {grantor} => pg_authid {oid}
+NOTICE:  checking pg_auth_password {roleid} => pg_authid {oid}
 NOTICE:  checking pg_shdepend {dbid} => pg_database {oid}
 NOTICE:  checking pg_shdepend {classid} => pg_class {oid}
 NOTICE:  checking pg_shdepend {refclassid} => pg_class {oid}
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
index 7c84c9da33..4ffc41a545 100644
--- a/src/test/regress/expected/password.out
+++ b/src/test/regress/expected/password.out
@@ -24,10 +24,12 @@ CREATE ROLE regress_passwd4 PASSWORD NULL;
 --
 -- Since the salt is random, the exact value stored will be different on every test
 -- run. Use a regular expression to mask the changing parts.
-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
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
     FROM pg_authid
+    LEFT JOIN pg_auth_password p 
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
-    ORDER BY rolname, rolpassword;
+    ORDER BY rolname, password;
      rolname     |                rolpassword_masked                 
 -----------------+---------------------------------------------------
  regress_passwd1 | md5783277baca28003b33453252be4dbb34
@@ -40,12 +42,14 @@ SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+
 ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
 NOTICE:  MD5 password cleared because of role rename
 -- md5 entry should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
     FROM pg_authid
+    LEFT JOIN pg_auth_password p 
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd2_new'
-    ORDER BY rolname, rolpassword;
-       rolname       | rolpassword 
----------------------+-------------
+    ORDER BY rolname, password;
+       rolname       | password 
+---------------------+----------
  regress_passwd2_new | 
 (1 row)
 
@@ -72,10 +76,12 @@ CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234';
 CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
 -- invalid length
 CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
-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
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
     FROM pg_authid
+    LEFT JOIN pg_auth_password p 
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
-    ORDER BY rolname, rolpassword;
+    ORDER BY rolname, password;
      rolname     |                rolpassword_masked                 
 -----------------+---------------------------------------------------
  regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70
@@ -95,9 +101,11 @@ ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
 NOTICE:  empty string is not a valid password, clearing password
 ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
 NOTICE:  empty string is not a valid password, clearing password
-SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
- rolpassword 
--------------
+SELECT password FROM pg_authid
+LEFT JOIN pg_auth_password p 
+ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty';
+ password 
+----------
  
 (1 row)
 
@@ -110,8 +118,10 @@ CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941
 CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=';
 -- Check that the invalid secrets were re-hashed. A re-hashed secret
 -- should not contain the original salt.
-SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
+SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
     FROM pg_authid
+    LEFT JOIN pg_auth_password p 
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd_sha_len%'
     ORDER BY rolname;
          rolname         | is_rolpassword_rehashed 
@@ -134,11 +144,13 @@ DROP ROLE regress_passwd_sha_len0;
 DROP ROLE regress_passwd_sha_len1;
 DROP ROLE regress_passwd_sha_len2;
 -- all entries should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
     FROM pg_authid
+    LEFT JOIN pg_auth_password p 
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
-    ORDER BY rolname, rolpassword;
- rolname | rolpassword 
----------+-------------
+    ORDER BY rolname, password;
+ rolname | password 
+---------+----------
 (0 rows)
 
diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regress/expected/roleattributes.out
index 5e6969b173..18a9dacadd 100644
--- a/src/test/regress/expected/roleattributes.out
+++ b/src/test/regress/expected/roleattributes.out
@@ -1,233 +1,233 @@
 -- default for superuser is false
 CREATE ROLE regress_test_def_superuser;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
-        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_superuser WITH NOSUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
-        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
-        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 -- default for inherit is true
 CREATE ROLE regress_test_def_inherit;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
-         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
+         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
-       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_inherit WITH INHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
-       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
-       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 -- default for create role is false
 CREATE ROLE regress_test_def_createrole;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
-           rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
+           rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
-         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_createrole WITH NOCREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
-         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
-         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 -- default for create database is false
 CREATE ROLE regress_test_def_createdb;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
-          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
+          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
-        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_createdb WITH NOCREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
-        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
-        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 | 
 (1 row)
 
 -- default for can login is false for role
 CREATE ROLE regress_test_def_role_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
-            rolname             | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
+            rolname             | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_role_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 | 
 (1 row)
 
 -- default for can login is true for user
 CREATE USER regress_test_def_user_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
-            rolname             | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
+            rolname             | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 | 
 (1 row)
 
 CREATE USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER USER regress_test_user_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 | 
 (1 row)
 
 ALTER USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 -- default for replication is false
 CREATE ROLE regress_test_def_replication;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
-           rolname            | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_replication | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
+           rolname            | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_replication | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
-         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_replication WITH NOREPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
-         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_replication | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_replication | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
-         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 | 
 (1 row)
 
 -- default for bypassrls is false
 CREATE ROLE regress_test_def_bypassrls;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
-          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
+          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 CREATE ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
-        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
-        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 (1 row)
 
 ALTER ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
-        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 |             | 
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil 
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 | 
 (1 row)
 
 -- clean up roles
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index fc3cde3226..231fec7fda 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1725,11 +1725,12 @@ pg_shadow| SELECT pg_authid.rolname AS usename,
     pg_authid.rolsuper AS usesuper,
     pg_authid.rolreplication AS userepl,
     pg_authid.rolbypassrls AS usebypassrls,
-    pg_authid.rolpassword AS passwd,
+    p.password AS passwd,
     pg_authid.rolvaliduntil AS valuntil,
     s.setconfig AS useconfig
-   FROM (pg_authid
+   FROM ((pg_authid
      LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
+     LEFT JOIN pg_auth_password p ON ((p.roleid = pg_authid.oid)))
   WHERE pg_authid.rolcanlogin;
 pg_shmem_allocations| SELECT pg_get_shmem_allocations.name,
     pg_get_shmem_allocations.off,
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index c52cf1cfcf..2ca2cd181f 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -53,13 +53,13 @@ ERROR:  cannot move system relation "pg_authid_rolname_index"
 REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
 ERROR:  cannot reindex system catalogs concurrently
 -- toast relations, fail
-REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index;
-ERROR:  cannot move system relation "pg_toast_1260_index"
-REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index;
+REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index;
+ERROR:  cannot move system relation "pg_toast_1262_index"
+REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index;
 ERROR:  cannot reindex system catalogs concurrently
-REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260;
-ERROR:  cannot move system relation "pg_toast_1260_index"
-REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260;
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262;
+ERROR:  cannot move system relation "pg_toast_1262_index"
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262;
 ERROR:  cannot reindex system catalogs concurrently
 -- system catalog, fail
 REINDEX (TABLESPACE pg_global) TABLE pg_authid;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d8fded3d93..8d649f2355 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1068,9 +1068,9 @@ REINDEX TABLE CONCURRENTLY concur_reindex_tab;
 COMMIT;
 REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
 REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
--- These are the toast table and index of pg_authid.
-REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
-REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
+-- These are the toast table and index of pg_database.
+REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table
+REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index
 REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
 -- Warns about catalog relations
 REINDEX SCHEMA CONCURRENTLY pg_catalog;
@@ -1243,8 +1243,8 @@ REINDEX SCHEMA schema_to_reindex;
 RESET ROLE;
 GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
 SET SESSION ROLE regress_reindexuser;
-REINDEX TABLE pg_toast.pg_toast_1260;
-REINDEX INDEX pg_toast.pg_toast_1260_index;
+REINDEX TABLE pg_toast.pg_toast_1262;
+REINDEX INDEX pg_toast.pg_toast_1262_index;
 
 -- Clean up
 RESET ROLE;
diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql
index 98f49916e5..f41d308fbb 100644
--- a/src/test/regress/sql/password.sql
+++ b/src/test/regress/sql/password.sql
@@ -23,18 +23,22 @@ CREATE ROLE regress_passwd4 PASSWORD NULL;
 --
 -- Since the salt is random, the exact value stored will be different on every test
 -- run. Use a regular expression to mask the changing parts.
-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
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
     FROM pg_authid
+    LEFT JOIN pg_auth_password p
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
-    ORDER BY rolname, rolpassword;
+    ORDER BY rolname, password;
 
 -- Rename a role
 ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
 -- md5 entry should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
     FROM pg_authid
+    LEFT JOIN pg_auth_password p
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd2_new'
-    ORDER BY rolname, rolpassword;
+    ORDER BY rolname, password;
 ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2;
 
 -- Change passwords with ALTER USER. With plaintext or already-encrypted
@@ -63,16 +67,20 @@ CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
 -- invalid length
 CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
 
-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
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
     FROM pg_authid
+    LEFT JOIN pg_auth_password p
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
-    ORDER BY rolname, rolpassword;
+    ORDER BY rolname, password;
 
 -- An empty password is not allowed, in any form
 CREATE ROLE regress_passwd_empty PASSWORD '';
 ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
 ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
-SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
+SELECT password FROM pg_authid
+LEFT JOIN pg_auth_password p
+ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty';
 
 -- Test with invalid stored and server keys.
 --
@@ -84,8 +92,10 @@ CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941
 
 -- Check that the invalid secrets were re-hashed. A re-hashed secret
 -- should not contain the original salt.
-SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
+SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
     FROM pg_authid
+    LEFT JOIN pg_auth_password p
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd_sha_len%'
     ORDER BY rolname;
 
@@ -103,7 +113,9 @@ DROP ROLE regress_passwd_sha_len1;
 DROP ROLE regress_passwd_sha_len2;
 
 -- all entries should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
     FROM pg_authid
+    LEFT JOIN pg_auth_password p
+    ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
-    ORDER BY rolname, rolpassword;
+    ORDER BY rolname, password;
diff --git a/src/test/regress/sql/roleattributes.sql b/src/test/regress/sql/roleattributes.sql
index c961b2d730..09505c6a3b 100644
--- a/src/test/regress/sql/roleattributes.sql
+++ b/src/test/regress/sql/roleattributes.sql
@@ -1,83 +1,83 @@
 -- default for superuser is false
 CREATE ROLE regress_test_def_superuser;
 
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
 CREATE ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
 ALTER ROLE regress_test_superuser WITH NOSUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
 ALTER ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
 
 -- default for inherit is true
 CREATE ROLE regress_test_def_inherit;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
 CREATE ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
 ALTER ROLE regress_test_inherit WITH INHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
 ALTER ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
 
 -- default for create role is false
 CREATE ROLE regress_test_def_createrole;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
 CREATE ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
 ALTER ROLE regress_test_createrole WITH NOCREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
 ALTER ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
 
 -- default for create database is false
 CREATE ROLE regress_test_def_createdb;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
 CREATE ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
 ALTER ROLE regress_test_createdb WITH NOCREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
 ALTER ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
 
 -- default for can login is false for role
 CREATE ROLE regress_test_def_role_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
 CREATE ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
 ALTER ROLE regress_test_role_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
 ALTER ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
 
 -- default for can login is true for user
 CREATE USER regress_test_def_user_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
 CREATE USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
 ALTER USER regress_test_user_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
 ALTER USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
 
 -- default for replication is false
 CREATE ROLE regress_test_def_replication;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
 CREATE ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
 ALTER ROLE regress_test_replication WITH NOREPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
 ALTER ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
 
 -- default for bypassrls is false
 CREATE ROLE regress_test_def_bypassrls;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
 CREATE ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
 ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
 ALTER ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
 
 -- clean up roles
 DROP ROLE regress_test_def_superuser;
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql
index 21db433f2a..d92135173f 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -42,10 +42,10 @@ REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am;
 REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid;
 REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
 -- toast relations, fail
-REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index;
-REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index;
-REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260;
-REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260;
+REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index;
+REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index;
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262;
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262;
 -- system catalog, fail
 REINDEX (TABLESPACE pg_global) TABLE pg_authid;
 REINDEX (TABLESPACE pg_global) TABLE CONCURRENTLY pg_authid;
-- 
2.34.1

From bfb15c69fa4367e92cabeef1a97eb6fb394b788f Mon Sep 17 00:00:00 2001
From: Stephen Frost <[email-masked]>
Date: Thu, 30 Jun 2022 18:16:52 -0400
Subject: [PATCH 2/3] multiple passwords work with scram and md5

also renaming roles, dropping roles, switching between
password_encryption

Author: Joshua Brindle
---
 src/backend/commands/user.c            | 198 ++++++++++++++++++++-----
 src/backend/libpq/auth-sasl.c          |   4 +-
 src/backend/libpq/auth-scram.c         | 181 ++++++++++++----------
 src/backend/libpq/auth.c               | 103 ++++++-------
 src/backend/libpq/crypt.c              |  51 ++++---
 src/backend/parser/gram.y              |  15 +-
 src/backend/utils/cache/catcache.c     |   2 +-
 src/backend/utils/cache/syscache.c     |   6 +-
 src/include/catalog/pg_auth_password.h |  10 +-
 src/include/commands/user.h            |   2 +-
 src/include/libpq/crypt.h              |   2 +-
 src/include/libpq/sasl.h               |   4 +-
 src/include/libpq/scram.h              |   2 +-
 src/include/parser/kwlist.h            |   1 +
 src/include/utils/syscache.h           |   2 +-
 src/test/regress/expected/password.out |  12 +-
 16 files changed, 385 insertions(+), 210 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index b224b4a2e9..94747d7c3c 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -31,11 +31,14 @@
 #include "commands/defrem.h"
 #include "commands/seclabel.h"
 #include "commands/user.h"
+#include "common/scram-common.h"
 #include "libpq/crypt.h"
+#include "libpq/scram.h"
 #include "miscadmin.h"
 #include "storage/lmgr.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
+#include "utils/catcache.h"
 #include "utils/fmgroids.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
@@ -47,6 +50,9 @@ Oid			binary_upgrade_next_pg_authid_oid = InvalidOid;
 /* GUC parameter */
 int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
 
+/* default password name */
+const char* default_passname = "__def__";
+
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
 
@@ -69,7 +75,7 @@ have_createrole_privilege(void)
  * Is the role able to log in
 */
 bool
-is_role_valid(const char *rolename, char **logdetail)
+is_role_valid(const char *rolename, const char **logdetail)
 {
 	HeapTuple	roleTup;
 	Datum		datum;
@@ -102,6 +108,64 @@ is_role_valid(const char *rolename, char **logdetail)
 }

+static
+bool
+validate_and_get_salt(char *rolename, char **salt, const char **logdetail)
+{
+	char	  **current_secrets;
+	char	   *salt1, *salt2 = NULL;
+	int			i, num;
+	PasswordType passtype;
+
+	if (Password_encryption == PASSWORD_TYPE_MD5)
+	{
+		*salt = rolename; /* md5 always uses role name, no need to look through the passwords */
+		return true;
+	}
+	else if (Password_encryption == PASSWORD_TYPE_PLAINTEXT)
+	{
+		*salt = NULL; /* Plaintext does not have a salt */
+		return true;
+	}
+
+	current_secrets = get_role_passwords(rolename, logdetail, &num);
+	if (num == 0)
+	{
+		*salt = NULL; /* No existing passwords, allow one to be generated */
+		return true;
+	}
+	for (i = 0; i < num; i++) {
+		passtype = get_password_type(current_secrets[i]);
+		if (passtype == PASSWORD_TYPE_MD5 || passtype == PASSWORD_TYPE_PLAINTEXT)
+			continue; /* md5 uses rolename as salt so it is always the same and plaintext has no salt */
+		else if (passtype == PASSWORD_TYPE_SCRAM_SHA_256) {
+				int			iterations;
+				uint8		stored_key[SCRAM_KEY_LEN];
+				uint8		server_key[SCRAM_KEY_LEN];
+
+				parse_scram_secret(current_secrets[i], &iterations, &salt1,
+									stored_key, server_key);
+
+				if (salt2 != NULL) {
+					if (strcmp(salt1, salt2)) {
+						*logdetail = psprintf(_("inconsistent salts, clearing password"));
+						*salt = NULL;
+						return false;
+					}
+				}
+				else
+					salt2 = salt1;
+		}
+
+	}
+	for (i = 0; i < num; i++)
+		pfree(current_secrets[i]);
+	pfree(current_secrets);
+	if (salt2)
+		*salt = pstrdup(salt2);
+	return true;
+}
+
 /*
  * CREATE ROLE
  */
@@ -117,6 +181,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	ListCell   *item;
 	ListCell   *option;
 	char	   *password = NULL;	/* user password */
+	char       *passname = NULL;    /* name of the password for managing multiple passwords */
 	bool		issuper = false;	/* Make the user a superuser? */
 	bool		inherit = true; /* Auto inherit privileges? */
 	bool		createrole = false; /* Can this user create roles? */
@@ -144,6 +209,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	DefElem    *dadminmembers = NULL;
 	DefElem    *dvalidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
+	DefElem    *dpassName = NULL;
+
 
 	/* The defaults can vary depending on the original statement type */
 	switch (stmt->stmt_type)
@@ -246,6 +313,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 				errorConflictingDefElem(defel, pstate);
 			dbypassRLS = defel;
 		}
+		else if (strcmp(defel->defname, "passname") == 0)
+		{
+			if (dpassName)
+				errorConflictingDefElem(defel, pstate);
+			dpassName = defel;
+		}
+
 		else
 			elog(ERROR, "option \"%s\" not recognized",
 				 defel->defname);
@@ -283,6 +357,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		validUntil = strVal(dvalidUntil->arg);
 	if (dbypassRLS)
 		bypassrls = boolVal(dbypassRLS->arg);
+	if (dpassName)
+		passname = strVal(dpassName->arg);
 
 	/* Check some permissions first */
 	if (issuper)
@@ -424,8 +500,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 
 	if (password)
 	{
-		char	   *shadow_pass;
-		char	   *logdetail;
+		char	   *shadow_pass, *salt;
+		const char	   *logdetail;
 		Datum		new_password_record[Natts_pg_auth_password];
 		bool		new_password_record_nulls[Natts_pg_auth_password];
 		Relation	pg_auth_password_rel;
@@ -453,12 +529,18 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		else
 		{
 			/* Encrypt the password to the requested format. */
-			shadow_pass = encrypt_password(Password_encryption, stmt->role,
+			validate_and_get_salt(stmt->role, &salt, &logdetail);
+			shadow_pass = encrypt_password(Password_encryption, salt,
 										   password);
 
 			MemSet(new_password_record, 0, sizeof(new_password_record));
 			MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
-
+			if (passname != NULL)
+				new_password_record[Anum_pg_auth_password_name - 1] =
+								DirectFunctionCall1(namein, CStringGetDatum(passname));
+			else
+				new_password_record[Anum_pg_auth_password_name - 1] =
+								DirectFunctionCall1(namein, CStringGetDatum(default_passname));
 			/* open password table and insert it. */
 			pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
 			pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
@@ -560,6 +642,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	ListCell   *option;
 	char	   *rolename;
 	char	   *password = NULL;	/* user password */
+	char       *passname = NULL;    /* password name for managing multiple passwords */
 	int			connlimit = -1; /* maximum connections allowed */
 	char	   *validUntil = NULL;	/* time the login is valid until */
 	Datum		validUntil_datum;	/* same, as timestamptz Datum */
@@ -575,6 +658,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	DefElem    *drolemembers = NULL;
 	DefElem    *dvalidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
+	DefElem    *dpassName = NULL;
 	Oid			roleid;
 
 	check_rolespec_name(stmt->role,
@@ -652,6 +736,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 				errorConflictingDefElem(defel, pstate);
 			dbypassRLS = defel;
 		}
+		else if (strcmp(defel->defname, "passname") == 0)
+		{
+			if (dpassName)
+				errorConflictingDefElem(defel, pstate);
+			dpassName = defel;
+		}
 		else
 			elog(ERROR, "option \"%s\" not recognized",
 				 defel->defname);
@@ -669,6 +759,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	}
 	if (dvalidUntil)
 		validUntil = strVal(dvalidUntil->arg);
+	if (dpassName)
+		passname = strVal(dpassName->arg);
 
 	/*
 	 * Scan the pg_authid relation to be certain the user exists.
@@ -805,8 +897,9 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	/* password */
 	if (password)
 	{
-		char	   *shadow_pass;
-		const char *logdetail = NULL;
+		char	*salt = NULL;
+		const char 	*logdetail = NULL;
+		char	*shadow_pass;
 
 		/* Like in CREATE USER, don't allow an empty password. */
 		if (password[0] == '\0' ||
@@ -818,13 +911,26 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 		}
 		else
 		{
-			/* Encrypt the password to the requested format. */
-			shadow_pass = encrypt_password(Password_encryption, rolename,
-										   password);
-			new_password_record[Anum_pg_auth_password_password - 1] =
-				CStringGetTextDatum(shadow_pass);
+			if (!validate_and_get_salt(rolename, &salt, &logdetail))
+				new_password_record_nulls[Anum_pg_auth_password_password - 1] = true;
+			else
+			{
+				/* Encrypt the password to the requested format. */
+				shadow_pass = encrypt_password(Password_encryption, salt,
+											password);
+				new_password_record[Anum_pg_auth_password_password - 1] =
+					CStringGetTextDatum(shadow_pass);
+
+				new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
+
+				if (passname)
+					new_password_record[Anum_pg_auth_password_name - 1] =
+						DirectFunctionCall1(namein, CStringGetDatum(passname));
+				else
+					new_password_record[Anum_pg_auth_password_name - 1] =
+					DirectFunctionCall1(namein, CStringGetDatum(default_passname));
+			}
 		}
-		new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
 	}
 
 	/* unset password */
@@ -859,7 +965,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 
 		pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
 		pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
-		password_tuple = SearchSysCache1(AUTHPASSWORD, ObjectIdGetDatum(roleid));
+		if (dpassName)
+			password_tuple = SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(roleid), CStringGetDatum(passname));
+		else
+			password_tuple = SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(roleid), CStringGetDatum(default_passname));
 
 		if (new_password_record_nulls[Anum_pg_auth_password_password - 1] == true) /* delete existing password */
 		{
@@ -1157,14 +1266,23 @@ DropRole(DropRoleStmt *stmt)
 		DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
 
 		/*
-		 * Drop password
+		 * Drop password(s)
 		 */
-		tuple = SearchSysCache1(AUTHPASSWORD, roleid);
-		if (HeapTupleIsValid(tuple)) {
-			CatalogTupleDelete(pg_auth_password_rel, &tuple->t_self);
-			ReleaseSysCache(tuple);
+		ScanKeyInit(&scankey,
+					Anum_pg_auth_password_roleid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(roleid));
+
+		sscan = systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIndexId,
+								   true, NULL, 1, &scankey);
+
+		while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
+		{
+			CatalogTupleDelete(pg_auth_password_rel, &tmp_tuple->t_self);
 		}
 
+		systable_endscan(sscan);
+
 		/*
 		 * Remove settings for this role.
 		 */
@@ -1201,6 +1319,9 @@ RenameRole(const char *oldname, const char *newname)
 				newtuple,
 				passtuple;
 	TupleDesc	dsc;
+	ScanKeyData scankey;
+	SysScanDesc sscan;
+
 	Relation	rel;
 	Datum		datum;
 	bool		isnull = true;
@@ -1211,6 +1332,7 @@ RenameRole(const char *oldname, const char *newname)
 	Oid			roleid;
 	ObjectAddress address;
 	Form_pg_authid authform;
+	Relation	pg_auth_password_rel;
 
 	rel = table_open(AuthIdRelationId, RowExclusiveLock);
 	dsc = RelationGetDescr(rel);
@@ -1301,28 +1423,38 @@ RenameRole(const char *oldname, const char *newname)
 															   CStringGetDatum(newname));
 	repl_null[Anum_pg_authid_rolname - 1] = false;
 
-	passtuple = SearchSysCache1(AUTHPASSWORD, roleid);
+	ScanKeyInit(&scankey,
+				Anum_pg_auth_password_roleid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(roleid));
+
+	pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
 
-	if (HeapTupleIsValid(passtuple))
-		datum = SysCacheGetAttr(AUTHPASSWORD, passtuple,
-								Anum_pg_auth_password_password, &isnull);
+	sscan = systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIndexId,
+								true, NULL, 1, &scankey);
 
-	if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
+	while (HeapTupleIsValid(passtuple = systable_getnext(sscan)))
 	{
-		Relation	pg_auth_password_rel;
+		datum = SysCacheGetAttr(AUTHPASSWORDNAME, passtuple,
+							Anum_pg_auth_password_password, &isnull);
 
-		/* MD5 uses the username as salt, so just clear it on a rename */
-		pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+		if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
+		{
 
-		if (HeapTupleIsValid(passtuple)) {
-			CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self);
-			ReleaseSysCache(passtuple);
+			/* MD5 uses the username as salt, so just clear it on a rename */
+
+			if (HeapTupleIsValid(passtuple)) {
+				CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self);
+				ereport(NOTICE,
+					(errmsg("MD5 password cleared because of role rename")));
+
+			}
 		}
-		table_close(pg_auth_password_rel, NoLock);
-		ereport(NOTICE,
-				(errmsg("MD5 password cleared because of role rename")));
 	}
 
+	systable_endscan(sscan);
+	table_close(pg_auth_password_rel, NoLock);
+
 	newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
 	CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple);
 
diff --git a/src/backend/libpq/auth-sasl.c b/src/backend/libpq/auth-sasl.c
index 805b3695b7..652c18fa94 100644
--- a/src/backend/libpq/auth-sasl.c
+++ b/src/backend/libpq/auth-sasl.c
@@ -49,7 +49,7 @@
  * should just pass NULL.
  */
 int
-CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, char *shadow_pass,
+CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, const char **passwords, int num,
 			  const char **logdetail)
 {
 	StringInfoData sasl_mechs;
@@ -136,7 +136,7 @@ CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, char *shadow_pass,
 			 * This is because we don't want to reveal to an attacker what
 			 * usernames are valid, nor which users have a valid password.
 			 */
-			opaq = mech->init(port, selected_mech, shadow_pass);
+			opaq = mech->init(port, selected_mech, passwords, num);
 
 			inputlen = pq_getmsgint(&buf, 4);
 			if (inputlen == -1)
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 795f1cba55..7dfc66b64b 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -109,7 +109,7 @@
 
 static void scram_get_mechanisms(Port *port, StringInfo buf);
 static void *scram_init(Port *port, const char *selected_mech,
-						const char *shadow_pass);
+						const char **secrets, const int num_secrets);
 static int	scram_exchange(void *opaq, const char *input, int inputlen,
 						   char **output, int *outputlen,
 						   const char **logdetail);
@@ -132,6 +132,13 @@ typedef enum
 	SCRAM_AUTH_FINISHED
 } scram_state_enum;
 
+typedef struct
+{
+	uint8		StoredKey[SCRAM_KEY_LEN];
+	uint8		ServerKey[SCRAM_KEY_LEN];
+} scram_secret;
+
+
 typedef struct
 {
 	scram_state_enum state;
@@ -141,10 +148,16 @@ typedef struct
 	Port	   *port;
 	bool		channel_binding_in_use;
 
+	/*
+	 * The salt and iterations must be the same for all
+	 * secrets since they are sent as part of the initial message
+	 */
 	int			iterations;
 	char	   *salt;			/* base64-encoded */
-	uint8		StoredKey[SCRAM_KEY_LEN];
-	uint8		ServerKey[SCRAM_KEY_LEN];
+	/* Array of possible secrets */
+	scram_secret *secrets;
+	int			num_secrets;
+	int			chosen_secret; /* secret chosen during final client message */
 
 	/* Fields of the first message from client */
 	char		cbind_flag;
@@ -220,17 +233,20 @@ scram_get_mechanisms(Port *port, StringInfo buf)
  * It should be one of the mechanisms that we support, as returned by
  * scram_get_mechanisms().
  *
- * 'shadow_pass' is the role's stored secret, from pg_auth_password.password.
+ * 'passwords' are the role's stored secrets, from pg_auth_password.password.
  * The username was provided by the client in the startup message, and is
  * available in port->user_name.  If 'shadow_pass' is NULL, we still perform
  * an authentication exchange, but it will fail, as if an incorrect password
  * was given.
  */
 static void *
-scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
+scram_init(Port *port, const char *selected_mech, const char **secrets, const int num_secrets)
 {
 	scram_state *state;
-	bool		got_secret;
+	bool		got_secret = false;
+	int			i;
+	int	iterations;
+	char *salt = NULL;			/* base64-encoded */
 
 	state = (scram_state *) palloc0(sizeof(scram_state));
 	state->port = port;
@@ -260,46 +276,47 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
 	/*
 	 * Parse the stored secret.
 	 */
-	if (shadow_pass)
+	if (secrets)
 	{
-		int			password_type = get_password_type(shadow_pass);
-
-		if (password_type == PASSWORD_TYPE_SCRAM_SHA_256)
+		state->secrets = palloc0(sizeof(scram_secret) * num_secrets);
+		state->num_secrets = num_secrets;
+		for (i = 0; i < num_secrets; i++)
 		{
-			if (parse_scram_secret(shadow_pass, &state->iterations, &state->salt,
-								   state->StoredKey, state->ServerKey))
-				got_secret = true;
-			else
+			int			password_type = get_password_type(secrets[i]);
+
+			if (password_type == PASSWORD_TYPE_SCRAM_SHA_256)
 			{
-				/*
-				 * The password looked like a SCRAM secret, but could not be
-				 * parsed.
-				 */
-				ereport(LOG,
-						(errmsg("invalid SCRAM secret for user \"%s\"",
-								state->port->user_name)));
-				got_secret = false;
+				if (parse_scram_secret(secrets[i], &state->iterations, &state->salt,
+									state->secrets[i].StoredKey, state->secrets[i].ServerKey))
+				{
+					if (salt) {
+						/* The stored iterations and salt must match or we cannot proceed, allow failure via mock */
+						if (strcmp(salt, state->salt) || iterations != state->iterations) {
+							ereport(WARNING, (errmsg("inconsistent salt or iterations for user \"%s\"",
+									state->port->user_name)));
+							got_secret = false; /* fail and allow mock creditials to be created */
+							break;
+						}
+					}
+					else
+					{
+						salt = state->salt;
+						iterations = state->iterations;
+						got_secret = true; /* We got at least one good SCRAM secret */
+					}
+				}
+				else
+				{
+					/*
+					* The password looked like a SCRAM secret, but could not be
+					* parsed.
+					*/
+					ereport(LOG,
+							(errmsg("invalid SCRAM secret for user \"%s\"",
+									state->port->user_name)));
+				}
 			}
 		}
-		else
-		{
-			/*
-			 * The user doesn't have SCRAM secret. (You cannot do SCRAM
-			 * authentication with an MD5 hash.)
-			 */
-			state->logdetail = psprintf(_("User \"%s\" does not have a valid SCRAM secret."),
-										state->port->user_name);
-			got_secret = false;
-		}
-	}
-	else
-	{
-		/*
-		 * The caller requested us to perform a dummy authentication.  This is
-		 * considered normal, since the caller requested it, so don't set log
-		 * detail.
-		 */
-		got_secret = false;
 	}
 
 	/*
@@ -310,8 +327,10 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
 	 */
 	if (!got_secret)
 	{
+		state->secrets = palloc0(sizeof(scram_secret));
+
 		mock_scram_secret(state->port->user_name, &state->iterations,
-						  &state->salt, state->StoredKey, state->ServerKey);
+						  &state->salt, state->secrets[0].StoredKey, state->secrets[0].ServerKey);
 		state->doomed = true;
 	}
 
@@ -459,7 +478,7 @@ scram_exchange(void *opaq, const char *input, int inputlen,
  * The result is palloc'd, so caller is responsible for freeing it.
  */
 char *
-pg_be_scram_build_secret(const char *password)
+pg_be_scram_build_secret(const char *password, const char *salt)
 {
 	char	   *prep_password;
 	pg_saslprep_rc rc;
@@ -476,11 +495,13 @@ pg_be_scram_build_secret(const char *password)
 	if (rc == SASLPREP_SUCCESS)
 		password = (const char *) prep_password;
 
-	/* Generate random salt */
-	if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
+	/* Use passed in salt or generate random salt */
+	if (!salt && !pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
 		ereport(ERROR,
 				(errcode(ERRCODE_INTERNAL_ERROR),
 				 errmsg("could not generate random salt")));
+	else if (salt)
+		pg_b64_decode(salt, strlen(salt), saltbuf, SCRAM_DEFAULT_SALT_LEN);
 
 	result = scram_build_secret(saltbuf, SCRAM_DEFAULT_SALT_LEN,
 								SCRAM_DEFAULT_ITERATIONS, password,
@@ -1114,47 +1135,57 @@ verify_client_proof(scram_state *state)
 	uint8		ClientSignature[SCRAM_KEY_LEN];
 	uint8		ClientKey[SCRAM_KEY_LEN];
 	uint8		client_StoredKey[SCRAM_KEY_LEN];
-	pg_hmac_ctx *ctx = pg_hmac_create(PG_SHA256);
-	int			i;
+	pg_hmac_ctx *ctx;
+	int			i, j;
 	const char *errstr = NULL;
-
 	/*
 	 * Calculate ClientSignature.  Note that we don't log directly a failure
 	 * here even when processing the calculations as this could involve a mock
 	 * authentication.
 	 */
-	if (pg_hmac_init(ctx, state->StoredKey, SCRAM_KEY_LEN) < 0 ||
-		pg_hmac_update(ctx,
-					   (uint8 *) state->client_first_message_bare,
-					   strlen(state->client_first_message_bare)) < 0 ||
-		pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
-		pg_hmac_update(ctx,
-					   (uint8 *) state->server_first_message,
-					   strlen(state->server_first_message)) < 0 ||
-		pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
-		pg_hmac_update(ctx,
-					   (uint8 *) state->client_final_message_without_proof,
-					   strlen(state->client_final_message_without_proof)) < 0 ||
-		pg_hmac_final(ctx, ClientSignature, sizeof(ClientSignature)) < 0)
+	for (j = 0; j < state->num_secrets; j++)
 	{
-		elog(ERROR, "could not calculate client signature: %s",
-			 pg_hmac_error(ctx));
-	}
+		ctx = pg_hmac_create(PG_SHA256);
+		elog(LOG, "Trying to verify password %d", j);
+
+		if (pg_hmac_init(ctx, state->secrets[j].StoredKey, SCRAM_KEY_LEN) < 0 ||
+			pg_hmac_update(ctx,
+						(uint8 *) state->client_first_message_bare,
+						strlen(state->client_first_message_bare)) < 0 ||
+			pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
+			pg_hmac_update(ctx,
+						(uint8 *) state->server_first_message,
+						strlen(state->server_first_message)) < 0 ||
+			pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
+			pg_hmac_update(ctx,
+						(uint8 *) state->client_final_message_without_proof,
+						strlen(state->client_final_message_without_proof)) < 0 ||
+			pg_hmac_final(ctx, ClientSignature, sizeof(ClientSignature)) < 0)
+		{
+			elog(LOG, "could not calculate client signature");
+			pg_hmac_free(ctx);
+			continue;
+		}
 
-	pg_hmac_free(ctx);
+		elog(LOG, "success on %d", j);
 
-	/* Extract the ClientKey that the client calculated from the proof */
-	for (i = 0; i < SCRAM_KEY_LEN; i++)
-		ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+		pg_hmac_free(ctx);
 
-	/* Hash it one more time, and compare with StoredKey */
-	if (scram_H(ClientKey, SCRAM_KEY_LEN, client_StoredKey, &errstr) < 0)
-		elog(ERROR, "could not hash stored key: %s", errstr);
+		for (i = 0; i < SCRAM_KEY_LEN; i++)
+			ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
 
-	if (memcmp(client_StoredKey, state->StoredKey, SCRAM_KEY_LEN) != 0)
-		return false;
+		/* Hash it one more time, and compare with StoredKey */
+		if (scram_H(ClientKey, SCRAM_KEY_LEN, client_StoredKey, &errstr) < 0)
+			elog(ERROR, "could not hash stored key: %s", errstr);
 
-	return true;
+		if (memcmp(client_StoredKey, state->secrets[j].StoredKey, SCRAM_KEY_LEN) == 0) {
+			elog(LOG, "Moving forward with Password %d", j);
+			state->chosen_secret = j;
+			return true;
+		}
+	}
+
+	return false;
 }
 
 /*
@@ -1380,7 +1411,7 @@ build_server_final_message(scram_state *state)
 	pg_hmac_ctx *ctx = pg_hmac_create(PG_SHA256);
 
 	/* calculate ServerSignature */
-	if (pg_hmac_init(ctx, state->ServerKey, SCRAM_KEY_LEN) < 0 ||
+	if (pg_hmac_init(ctx, state->secrets[state->chosen_secret].ServerKey, SCRAM_KEY_LEN) < 0 ||
 		pg_hmac_update(ctx,
 					   (uint8 *) state->client_first_message_bare,
 					   strlen(state->client_first_message_bare)) < 0 ||
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 269cf286ad..4cc59d39ce 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -58,8 +58,7 @@ static void set_authn_id(Port *port, const char *id);
 static int	CheckPasswordAuth(Port *port, const char **logdetail);
 static int	CheckPWChallengeAuth(Port *port, const char **logdetail);
 
-static int	CheckMD5Auth(Port *port, char *shadow_pass,
-						 const char **logdetail);
+static int	CheckMD5Auth(Port *port, const char **passwords, int num, const char **logdetail);

 /*----------------------------------------------------------------
@@ -790,8 +789,9 @@ static int
 CheckPasswordAuth(Port *port, const char **logdetail)
 {
 	char	   *passwd;
-	int			result;
-	char	   *shadow_pass;
+	int			result = STATUS_ERROR;
+	int			i, num;
+	char	   **passwords;
 
 	sendAuthRequest(port, AUTH_REQ_PASSWORD, NULL, 0);
 
@@ -799,17 +799,21 @@ CheckPasswordAuth(Port *port, const char **logdetail)
 	if (passwd == NULL)
 		return STATUS_EOF;		/* client wouldn't send password */
 
-	shadow_pass = get_role_password(port->user_name, logdetail);
-	if (shadow_pass)
-	{
-		result = plain_crypt_verify(port->user_name, shadow_pass, passwd,
-									logdetail);
+	passwords = get_role_passwords(port->user_name, logdetail, &num);
+	if (passwords != NULL) {
+		for (i = 0; i < num; i++)
+		{
+			result = plain_crypt_verify(port->user_name, passwords[i], passwd,
+										logdetail);
+			if (result == STATUS_OK)
+				break; /* Found a matching password, no need to try any others */
+		}
+		for (i = 0; i < num; i++)
+			pfree(passwords[i]);
+
+		pfree(passwords);
 	}
-	else
-		result = STATUS_ERROR;
 
-	if (shadow_pass)
-		pfree(shadow_pass);
 	pfree(passwd);
 
 	if (result == STATUS_OK)
@@ -824,57 +828,42 @@ CheckPasswordAuth(Port *port, const char **logdetail)
 static int
 CheckPWChallengeAuth(Port *port, const char **logdetail)
 {
-	int			auth_result;
-	char	   *shadow_pass;
-	PasswordType pwtype;
+	bool		scram_pw_avail = false;
+	int			auth_result = STATUS_ERROR;
+	int			i, num;
+	char	  **passwords;
 
 	Assert(port->hba->auth_method == uaSCRAM ||
 		   port->hba->auth_method == uaMD5);
 
-	/* First look up the user's password. */
-	shadow_pass = get_role_password(port->user_name, logdetail);
-
-	/*
-	 * If the user does not exist, or has no password or it's expired, we
-	 * still go through the motions of authentication, to avoid revealing to
-	 * the client that the user didn't exist.  If 'md5' is allowed, we choose
-	 * whether to use 'md5' or 'scram-sha-256' authentication based on current
-	 * password_encryption setting.  The idea is that most genuine users
-	 * probably have a password of that type, and if we pretend that this user
-	 * had a password of that type, too, it "blends in" best.
-	 */
-	if (!shadow_pass)
-		pwtype = Password_encryption;
-	else
-		pwtype = get_password_type(shadow_pass);
+	/* First look up the user's passwords. */
+	passwords = get_role_passwords(port->user_name, logdetail, &num);
 
 	/*
 	 * If 'md5' authentication is allowed, decide whether to perform 'md5' or
 	 * 'scram-sha-256' authentication based on the type of password the user
-	 * has.  If it's an MD5 hash, we must do MD5 authentication, and if it's a
-	 * SCRAM secret, we must do SCRAM authentication.
+	 * has.  If there's a SCRAM PW available then we'll do SCRAM, otherwise we
+	 * will fall back to trying to use MD5.
 	 *
 	 * If MD5 authentication is not allowed, always use SCRAM.  If the user
 	 * had an MD5 password, CheckSASLAuth() with the SCRAM mechanism will
 	 * fail.
 	 */
-	if (port->hba->auth_method == uaMD5 && pwtype == PASSWORD_TYPE_MD5)
-		auth_result = CheckMD5Auth(port, shadow_pass, logdetail);
-	else
-		auth_result = CheckSASLAuth(&pg_be_scram_mech, port, shadow_pass,
-									logdetail);
+	if (passwords != NULL) {
+		for (i = 0; i < num; i++)
+			if (get_password_type(passwords[i]) == PASSWORD_TYPE_SCRAM_SHA_256)
+				scram_pw_avail = true;
+
+		if (port->hba->auth_method == uaMD5 && !scram_pw_avail)
+			auth_result = CheckMD5Auth(port, (const char **) passwords, num, logdetail);
+		else
+			auth_result = CheckSASLAuth(&pg_be_scram_mech, port, (const char **) passwords, num,
+											logdetail);
 
-	if (shadow_pass)
-		pfree(shadow_pass);
+		for (i = 0; i < num; i++) 
+			pfree(passwords[i]);
 
-	/*
-	 * If get_role_password() returned error, return error, even if the
-	 * authentication succeeded.
-	 */
-	if (!shadow_pass)
-	{
-		Assert(auth_result != STATUS_OK);
-		return STATUS_ERROR;
+		pfree(passwords);
 	}
 
 	if (auth_result == STATUS_OK)
@@ -884,11 +873,12 @@ CheckPWChallengeAuth(Port *port, const char **logdetail)
 }
 
 static int
-CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail)
+CheckMD5Auth(Port *port, const char **passwords, int num, const char **logdetail)
 {
 	char		md5Salt[4];		/* Password salt */
 	char	   *passwd;
-	int			result;
+	int			result = STATUS_ERROR;
+	int			i;
 
 	if (Db_user_namespace)
 		ereport(FATAL,
@@ -909,12 +899,13 @@ CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail)
 	if (passwd == NULL)
 		return STATUS_EOF;		/* client wouldn't send password */
 
-	if (shadow_pass)
-		result = md5_crypt_verify(port->user_name, shadow_pass, passwd,
+	for (i = 0; i < num; i++)
+	{
+		result = md5_crypt_verify(port->user_name, passwords[i], passwd,
 								  md5Salt, 4, logdetail);
-	else
-		result = STATUS_ERROR;
-
+		if (result == STATUS_OK)
+			break;
+	}
 	pfree(passwd);
 
 	return result;
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 745e61034c..09b6bebf40 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -23,6 +23,7 @@
 #include "libpq/scram.h"
 #include "miscadmin.h"
 #include "utils/builtins.h"
+#include "utils/catcache.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
 
@@ -34,13 +35,15 @@
  * for the postmaster log, in *logdetail.  The error reason should *not* be
  * sent to the client, to avoid giving away user information!
  */
-char *
-get_role_password(const char *role, const char **logdetail)
+char **
+get_role_passwords(const char *role, const char **logdetail, int *num)
 {
-	HeapTuple	roleTup, passTup;
+	HeapTuple	roleTup;
 	Datum		datum;
 	bool		isnull;
-	char	   *shadow_pass;
+	char	   **passwords;
+	CatCList   *passlist;
+	int		    i;
 
 	/* Get role info from pg_authid */
 	roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
@@ -52,30 +55,34 @@ get_role_password(const char *role, const char **logdetail)
 	}
 
 	datum = SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull);
-	
-	passTup = SearchSysCache1(AUTHPASSWORD, datum);
+	ReleaseSysCache(roleTup);
+	/* Find any existing password that is not the one being updated to get the salt */
+	passlist = SearchSysCacheList1(AUTHPASSWORDNAME, datum);
+	*num = passlist->n_members;
 
-	if (!HeapTupleIsValid(passTup))
+	if (passlist->n_members == 0)
 	{
+		*num = 0;
 		*logdetail = psprintf(_("User \"%s\" has no password assigned."),
 							  role);
+		ReleaseCatCacheList(passlist);
 		return NULL;			/* user has no password */
 	}
-	datum = SysCacheGetAttr(AUTHPASSWORD, passTup,
-							Anum_pg_auth_password_password, &isnull);
 
-	ReleaseSysCache(roleTup);
-	if (isnull) /* this should not happen any more but just in case */
+	passwords = palloc0(sizeof(char *) * passlist->n_members); 
+
+	for (i = 0; i < passlist->n_members; i++)
 	{
-		ReleaseSysCache(passTup);
-		*logdetail = psprintf(_("User \"%s\" has no password assigned."),
-							  role);
-		return NULL;			/* user has no password */
+		HeapTuple	tup = &passlist->members[i]->tuple;
+
+		datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
+							Anum_pg_auth_password_password, &isnull);
+		passwords[i] = TextDatumGetCString(datum);
 	}
-	shadow_pass = TextDatumGetCString(datum);
-	ReleaseSysCache(passTup);
 
-	return shadow_pass;
+	ReleaseCatCacheList(passlist);
+
+	return passwords;
 }
 
 /*
@@ -107,7 +114,7 @@ get_password_type(const char *shadow_pass)
  * hash, so it is stored as it is regardless of the requested type.
  */
 char *
-encrypt_password(PasswordType target_type, const char *role,
+encrypt_password(PasswordType target_type, const char *salt,
 				 const char *password)
 {
 	PasswordType guessed_type = get_password_type(password);
@@ -128,13 +135,13 @@ encrypt_password(PasswordType target_type, const char *role,
 		case PASSWORD_TYPE_MD5:
 			encrypted_password = palloc(MD5_PASSWD_LEN + 1);
 
-			if (!pg_md5_encrypt(password, role, strlen(role),
+			if (!pg_md5_encrypt(password, salt, strlen(salt),
 								encrypted_password, &errstr))
-				elog(ERROR, "password encryption failed: %s", errstr);
+				elog(ERROR, "password encryption failed %s", errstr);
 			return encrypted_password;
 
 		case PASSWORD_TYPE_SCRAM_SHA_256:
-			return pg_be_scram_build_secret(password);
+			return pg_be_scram_build_secret(password, salt);
 
 		case PASSWORD_TYPE_PLAINTEXT:
 			elog(ERROR, "cannot encrypt password with 'plaintext'");
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 969c9c158f..7c1d023b7e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -826,8 +826,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	ORDER ORDINALITY OTHERS OUT_P OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
-	PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
-	PLACING PLAN PLANS POLICY
+	PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSNAME PASSWORD
+	PATH PLACING PLAN PLANS POLICY
 	POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
 
@@ -1261,6 +1261,15 @@ AlterOptRoleElem:
 							 errhint("Remove UNENCRYPTED to store the password in encrypted form instead."),
 							 parser_errposition(@1)));
 				}
+			| PASSNAME Sconst
+				{
+					$$ = makeDefElem("passname",
+									 (Node *)makeString($2), @1);
+				}
+			| VALID FOR Sconst
+				{
+					$$ = makeDefElem("validFor", (Node *)makeString($3), @1);
+				}
 			| INHERIT
 				{
 					$$ = makeDefElem("inherit", (Node *) makeBoolean(true), @1);
@@ -17863,6 +17872,7 @@ unreserved_keyword:
 			| PARTIAL
 			| PARTITION
 			| PASSING
+			| PASSNAME
 			| PASSWORD
 			| PATH
 			| PLAN
@@ -18481,6 +18491,7 @@ bare_label_keyword:
 			| PARTIAL
 			| PARTITION
 			| PASSING
+			| PASSNAME
 			| PASSWORD
 			| PATH
 			| PLACING
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 7c8f864a80..e487cef5b3 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1104,7 +1104,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
 
 		case AUTHNAME:
 		case AUTHOID:
-		case AUTHPASSWORD:
+		case AUTHPASSWORDNAME:
 		case AUTHMEMMEMROLE:
 		case DATABASEOID:
 
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index de2b3ec1eb..9879120bf7 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -256,12 +256,12 @@ static const struct cachedesc cacheinfo[] = {
 		},
 		8
 	},
-	{AuthPasswordRelationId,			/* AUTHPASSWORD */
+	{AuthPasswordRelationId,			/* AUTHPASSWORDNAME */
 		AuthPasswordRoleOidIndexId,
-		1,
+		2,
 		{
 			Anum_pg_auth_password_roleid,
-			0,
+			Anum_pg_auth_password_name,
 			0,
 			0
 		},
diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/pg_auth_password.h
index beaa2d40b9..2100cda758 100644
--- a/src/include/catalog/pg_auth_password.h
+++ b/src/include/catalog/pg_auth_password.h
@@ -24,11 +24,14 @@
  *		typedef struct FormData_pg_auth_password
  * ----------------
  */
-CATALOG(pg_auth_password,4548,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4549,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO
+CATALOG(pg_auth_password,4551,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4552,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO
 {
 	Oid			roleid BKI_LOOKUP(pg_authid);	/* ID of a role */
+	NameData    name;            /* name of password for multiple password support */
+
 #ifdef CATALOG_VARLEN                /* variable-length fields start here */
-    text            password;        /* password */
+	text            password BKI_FORCE_NOT_NULL;        /* password */
+	timestamptz     expiration BKI_FORCE_NULL;	        /* password expiration time, if any */
 #endif
 } FormData_pg_auth_password;
 
@@ -44,7 +47,6 @@ DECLARE_TOAST(pg_auth_password, 4175, 4176);
 
 typedef FormData_pg_auth_password *Form_pg_auth_password;
 
-DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4550, AuthPasswordRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops));
-
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4553, AuthPasswordRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops, name name_ops));
 
 #endif							/* PG_AUTH_PASSWORD_H */
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 3e1aae5785..1c14f60a05 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -24,7 +24,7 @@ typedef void (*check_password_hook_type) (const char *username, const char *shad
 
 extern PGDLLIMPORT check_password_hook_type check_password_hook;
 
-extern bool is_role_valid(const char *rolename, char **logdetail);
+extern bool is_role_valid(const char *rolename, const char **logdetail);
 extern Oid	CreateRole(ParseState *pstate, CreateRoleStmt *stmt);
 extern Oid	AlterRole(ParseState *pstate, AlterRoleStmt *stmt);
 extern Oid	AlterRoleSet(AlterRoleSetStmt *stmt);
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index b8ff8ccb41..84c9c4b4f4 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -35,7 +35,7 @@ extern PasswordType get_password_type(const char *shadow_pass);
 extern char *encrypt_password(PasswordType target_type, const char *role,
 							  const char *password);
 
-extern char *get_role_password(const char *role, const char **logdetail);
+extern char **get_role_passwords(const char *role, const char **logdetail, int *num);
 
 extern int	md5_crypt_verify(const char *role, const char *shadow_pass,
 							 const char *client_pass, const char *md5_salt,
diff --git a/src/include/libpq/sasl.h b/src/include/libpq/sasl.h
index 39ccf8f0e3..27b4e57033 100644
--- a/src/include/libpq/sasl.h
+++ b/src/include/libpq/sasl.h
@@ -77,7 +77,7 @@ typedef struct pg_be_sasl_mech
 	 *				 disclosing valid user names.
 	 *---------
 	 */
-	void	   *(*init) (Port *port, const char *mech, const char *shadow_pass);
+	void	   *(*init) (Port *port, const char *mech, const char **secrets, const int num_secrets);
 
 	/*---------
 	 * exchange()
@@ -131,6 +131,6 @@ typedef struct pg_be_sasl_mech
 
 /* Common implementation for auth.c */
 extern int	CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port,
-						  char *shadow_pass, const char **logdetail);
+						  const char **passwords, int num, const char **logdetail);
 
 #endif							/* PG_SASL_H */
diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h
index c51e848c24..70b01b511f 100644
--- a/src/include/libpq/scram.h
+++ b/src/include/libpq/scram.h
@@ -21,7 +21,7 @@
 extern PGDLLIMPORT const pg_be_sasl_mech pg_be_scram_mech;
 
 /* Routines to handle and check SCRAM-SHA-256 secret */
-extern char *pg_be_scram_build_secret(const char *password);
+extern char *pg_be_scram_build_secret(const char *password, const char *salt);
 extern bool parse_scram_secret(const char *secret, int *iterations, char **salt,
 							   uint8 *stored_key, uint8 *server_key);
 extern bool scram_verify_plain_password(const char *username,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ae35f03251..ec79fafabf 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -334,6 +334,7 @@ PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("passname", PASSNAME, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index c7bc648f46..a543c18188 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -43,7 +43,7 @@ enum SysCacheIdentifier
 	AUTHMEMROLEMEM,
 	AUTHNAME,
 	AUTHOID,
-	AUTHPASSWORD,
+	AUTHPASSWORDNAME,
 	CASTSOURCETARGET,
 	CLAAMNAMENSP,
 	CLAOID,
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
index 4ffc41a545..11fb244021 100644
--- a/src/test/regress/expected/password.out
+++ b/src/test/regress/expected/password.out
@@ -26,7 +26,7 @@ CREATE ROLE regress_passwd4 PASSWORD NULL;
 -- run. Use a regular expression to mask the changing parts.
 SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
     FROM pg_authid
-    LEFT JOIN pg_auth_password p 
+    LEFT JOIN pg_auth_password p
     ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
     ORDER BY rolname, password;
@@ -44,7 +44,7 @@ NOTICE:  MD5 password cleared because of role rename
 -- md5 entry should have been removed
 SELECT rolname, password
     FROM pg_authid
-    LEFT JOIN pg_auth_password p 
+    LEFT JOIN pg_auth_password p
     ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd2_new'
     ORDER BY rolname, password;
@@ -78,7 +78,7 @@ CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
 CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
 SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
     FROM pg_authid
-    LEFT JOIN pg_auth_password p 
+    LEFT JOIN pg_auth_password p
     ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
     ORDER BY rolname, password;
@@ -102,7 +102,7 @@ NOTICE:  empty string is not a valid password, clearing password
 ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
 NOTICE:  empty string is not a valid password, clearing password
 SELECT password FROM pg_authid
-LEFT JOIN pg_auth_password p 
+LEFT JOIN pg_auth_password p
 ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty';
  password 
 ----------
@@ -120,7 +120,7 @@ CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941
 -- should not contain the original salt.
 SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
     FROM pg_authid
-    LEFT JOIN pg_auth_password p 
+    LEFT JOIN pg_auth_password p
     ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd_sha_len%'
     ORDER BY rolname;
@@ -146,7 +146,7 @@ DROP ROLE regress_passwd_sha_len2;
 -- all entries should have been removed
 SELECT rolname, password
     FROM pg_authid
-    LEFT JOIN pg_auth_password p 
+    LEFT JOIN pg_auth_password p
     ON pg_authid.oid = p.roleid
     WHERE rolname LIKE 'regress_passwd%'
     ORDER BY rolname, password;
-- 
2.34.1

From 80f2b1948c299cbc799901d36a23d44bee36e00b Mon Sep 17 00:00:00 2001
From: Stephen Frost <[email-masked]>
Date: Thu, 30 Jun 2022 18:17:50 -0400
Subject: [PATCH 3/3] Per-password expiration

To build on the multi-password support this
adds per-password expiration, either passed in via ALTER ROLE/CREATE
ROLE with the grammar EXPIRES IN, or via a system-wide setting
called password_valid_duration

Author: Joshua Brindle
---
 src/backend/commands/user.c     | 121 ++++++++++++++++++++++++++++++--
 src/backend/commands/variable.c | 105 +++++++++++++++++++++++++++
 src/backend/libpq/auth.c        |   9 ++-
 src/backend/libpq/crypt.c       |  38 ++++++++--
 src/backend/parser/gram.y       |  10 ++-
 src/backend/utils/misc/guc.c    |  12 ++++
 src/include/commands/user.h     |   3 +
 src/include/commands/variable.h |   4 ++
 src/include/parser/kwlist.h     |   1 +
 9 files changed, 290 insertions(+), 13 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 94747d7c3c..9c46af5123 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -47,8 +47,9 @@
 Oid			binary_upgrade_next_pg_authid_oid = InvalidOid;

-/* GUC parameter */
+/* GUC parameters */
 int			Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
+Interval *default_password_duration = NULL;
 
 /* default password name */
 const char* default_passname = "__def__";
@@ -166,6 +167,84 @@ validate_and_get_salt(char *rolename, char **salt, const char **logdetail)
 	return true;
 }
 
+static
+Datum
+expires_in_datum(DefElem *passExpiresIn)
+{
+	Interval *interval;
+	Node	   *arg;
+	A_Const    *con;
+	TimestampTz now = GetCurrentTimestamp();
+	Datum		passExpiresIn_datum;
+	char *dateout;
+
+	if (default_password_duration != NULL)
+	{
+		/* The default duration GUC is set, use it if nothing came from SQL
+		 * or if something came from SQL, reject it if not from a superuser
+		 */
+
+		if (passExpiresIn != NULL)
+			if (!superuser())
+				ereport(ERROR,
+						(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+						 errmsg("must be superuser to override password_validity_duration GUC")));
+			else
+				goto bypass;
+		else
+		{
+			passExpiresIn_datum =  DirectFunctionCall2(timestamptz_pl_interval,
+										TimestampGetDatum(now),
+										 PointerGetDatum(default_password_duration));
+
+			dateout =
+				DatumGetCString(DirectFunctionCall1(timestamp_out,
+													passExpiresIn_datum));
+
+			ereport(NOTICE,
+				(errmsg("Password will expire at: \"%s\" (from GUC)", dateout)));
+
+			return passExpiresIn_datum;
+		}
+	}
+
+	if (passExpiresIn == NULL) 	/* No duration requested via SQL and no system default, no expiration */
+		return PointerGetDatum(NULL);
+
+bypass:
+	arg = (Node *)passExpiresIn->arg;
+	if (IsA(arg, TypeCast))
+	{
+		TypeCast   *tc = (TypeCast *) passExpiresIn->arg;
+		arg = tc->arg;
+	}
+
+	if (!IsA(arg, A_Const))
+	{
+		elog(ERROR, "unrecognized node type: %d", (int) nodeTag(arg));
+		return PointerGetDatum(NULL);
+	}
+	con = (A_Const *) arg;
+
+	interval = DatumGetIntervalP(DirectFunctionCall3(interval_in,
+								CStringGetDatum(strVal(&con->val)),
+								ObjectIdGetDatum(InvalidOid),
+								Int32GetDatum(-1)));
+
+	passExpiresIn_datum =  DirectFunctionCall2(timestamptz_pl_interval,
+										TimestampGetDatum(now),
+										PointerGetDatum(interval));
+
+	dateout =
+				DatumGetCString(DirectFunctionCall1(timestamp_out,
+													passExpiresIn_datum));
+
+	ereport(NOTICE,
+		(errmsg("Password will expire at: \"%s\" (from SQL)", dateout)));
+
+	return passExpiresIn_datum;
+}
+
 /*
  * CREATE ROLE
  */
@@ -210,7 +289,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 	DefElem    *dvalidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
 	DefElem    *dpassName = NULL;
-
+	DefElem    *dpassExpiresIn = NULL;
 
 	/* The defaults can vary depending on the original statement type */
 	switch (stmt->stmt_type)
@@ -319,6 +398,12 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 				errorConflictingDefElem(defel, pstate);
 			dpassName = defel;
 		}
+		else if (strcmp(defel->defname, "expiresin") == 0)
+		{
+			if (dpassExpiresIn)
+				errorConflictingDefElem(defel, pstate);
+			dpassExpiresIn = defel;
+		}
 
 		else
 			elog(ERROR, "option \"%s\" not recognized",
@@ -507,6 +592,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		Relation	pg_auth_password_rel;
 		TupleDesc	pg_auth_password_dsc;
 		HeapTuple	new_tuple;
+		Datum		passExpiresIn_datum;
 
 		/*
 		 * Don't allow an empty password. Libpq treats an empty password the
@@ -528,19 +614,27 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
 		}
 		else
 		{
+			MemSet(new_password_record, 0, sizeof(new_password_record));
+			MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
+
+			passExpiresIn_datum = expires_in_datum(dpassExpiresIn);
+			if (passExpiresIn_datum != PointerGetDatum(NULL))
+				new_password_record[Anum_pg_auth_password_expiration - 1] = passExpiresIn_datum;
+			else
+				new_password_record_nulls[Anum_pg_auth_password_expiration - 1] = true;
+
 			/* Encrypt the password to the requested format. */
 			validate_and_get_salt(stmt->role, &salt, &logdetail);
 			shadow_pass = encrypt_password(Password_encryption, salt,
 										   password);
 
-			MemSet(new_password_record, 0, sizeof(new_password_record));
-			MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
 			if (passname != NULL)
 				new_password_record[Anum_pg_auth_password_name - 1] =
 								DirectFunctionCall1(namein, CStringGetDatum(passname));
 			else
 				new_password_record[Anum_pg_auth_password_name - 1] =
 								DirectFunctionCall1(namein, CStringGetDatum(default_passname));
+
 			/* open password table and insert it. */
 			pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
 			pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
@@ -647,6 +741,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	char	   *validUntil = NULL;	/* time the login is valid until */
 	Datum		validUntil_datum;	/* same, as timestamptz Datum */
 	bool		validUntil_null;
+	Datum		passExpiresIn_datum; /* Time period until password expires */
 	DefElem    *dpassword = NULL;
 	DefElem    *dissuper = NULL;
 	DefElem    *dinherit = NULL;
@@ -659,6 +754,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	DefElem    *dvalidUntil = NULL;
 	DefElem    *dbypassRLS = NULL;
 	DefElem    *dpassName = NULL;
+	DefElem	   *dpassExpiresIn = NULL;
 	Oid			roleid;
 
 	check_rolespec_name(stmt->role,
@@ -742,6 +838,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 				errorConflictingDefElem(defel, pstate);
 			dpassName = defel;
 		}
+		else if (strcmp(defel->defname, "expiresin") == 0)
+		{
+			if (dpassExpiresIn)
+				errorConflictingDefElem(defel, pstate);
+			dpassExpiresIn = defel;
+		}
 		else
 			elog(ERROR, "option \"%s\" not recognized",
 				 defel->defname);
@@ -762,6 +864,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	if (dpassName)
 		passname = strVal(dpassName->arg);
 
+
 	/*
 	 * Scan the pg_authid relation to be certain the user exists.
 	 */
@@ -848,6 +951,13 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
 	MemSet(new_password_record_repl, false, sizeof(new_password_record_repl));
 
+	passExpiresIn_datum = expires_in_datum(dpassExpiresIn);
+	if (passExpiresIn_datum != PointerGetDatum(NULL)) {
+		new_password_record[Anum_pg_auth_password_expiration - 1] = passExpiresIn_datum;
+		new_password_record_repl[Anum_pg_auth_password_expiration - 1] = true;
+	}
+	else
+		new_password_record_nulls[Anum_pg_auth_password_expiration - 1] = true;
 
 	/*
 	 * issuper/createrole/etc
@@ -957,7 +1067,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
 	ReleaseSysCache(tuple);
 	heap_freetuple(new_tuple);
 
-	if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true)
+	if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true
+		|| new_password_record_repl[Anum_pg_auth_password_expiration - 1] == true)
 	{
 		Relation	pg_auth_password_rel;
 		TupleDesc	pg_auth_password_dsc;
diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index e5ddcda0b4..ca85a10b80 100644
--- a/src/backend/commands/variable.c
+++ b/src/backend/commands/variable.c
@@ -24,6 +24,7 @@
 #include "access/xlog.h"
 #include "catalog/pg_authid.h"
 #include "commands/variable.h"
+#include "commands/user.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
 #include "utils/acl.h"
@@ -933,3 +934,107 @@ show_role(void)
 	/* Otherwise we can just use the GUC string */
 	return role_string ? role_string : "none";
 }
+
+
+/*
+ * check_password_duration: GUC check_hook for password_duration
+ */
+bool
+check_password_duration(char **newval, void **extra, GucSource source)
+{
+	Interval	*new_interval;
+	char	   *endptr;
+
+	const char *valueptr = *newval;
+	char	   *val;
+	Interval   *interval;
+
+	if (newval == NULL || *newval == NULL) {
+		extra = NULL;
+		return true;
+	}
+
+	elog(NOTICE,"Setting password duration to \"%s\"",
+					*newval);
+
+	while (isspace((unsigned char) *valueptr))
+		valueptr++;
+	if (*valueptr != '\'') {
+		val = pstrdup(valueptr);
+	}
+	else
+	{
+		valueptr++;
+		val = pstrdup(valueptr);
+		/* Check and remove trailing quote */
+		endptr = strchr(val, '\'');
+		if (!endptr || endptr[1] != '\0')
+		{
+			pfree(val);
+			return false;
+		}
+		*endptr = '\0';
+	}
+
+	/*
+		* Try to parse it.  XXX an invalid interval format will result in
+		* ereport(ERROR), which is not desirable for GUC.  We did what we
+		* could to guard against this in flatten_set_variable_args, but a
+		* string coming in from postgresql.conf might contain anything.
+		*/
+	interval = DatumGetIntervalP(DirectFunctionCall3(interval_in,
+														CStringGetDatum(val),
+														ObjectIdGetDatum(InvalidOid),
+														Int32GetDatum(-1)));
+
+	pfree(val);
+
+	if (!interval) {
+		return false;
+	}
+
+	new_interval = malloc(sizeof(Interval));
+	memcpy(new_interval, interval, sizeof(Interval));
+	pfree(interval);
+
+	/*
+	 * Pass back data for assign_password_validity to use
+	 */
+	*extra = malloc(sizeof(Interval *));
+	if (!*extra)
+		return false;
+	*((Interval **) *extra) = new_interval;
+
+	return true;
+}
+
+/*
+ * assign_password_validity: GUC assign_hook for timezone
+ */
+void
+assign_password_duration(const char *newval, void *extra)
+{
+	if (extra == NULL)
+		default_password_duration = NULL;
+	else
+		default_password_duration = *((Interval **) extra);
+}
+
+/*
+ * show_password_validity: GUC show_hook for timezone
+ */
+const char *
+show_password_duration(void)
+{
+	const char *intervalout;
+	if (default_password_duration == NULL) {
+		return "";
+	}
+	intervalout = DatumGetCString(DirectFunctionCall1(interval_out,
+										PointerGetDatum(default_password_duration)));
+
+	if (intervalout != NULL)
+		return intervalout;
+
+	return "";
+}
\ No newline at end of file
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 4cc59d39ce..857c58027c 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -860,9 +860,14 @@ CheckPWChallengeAuth(Port *port, const char **logdetail)
 			auth_result = CheckSASLAuth(&pg_be_scram_mech, port, (const char **) passwords, num,
 											logdetail);
 
-		for (i = 0; i < num; i++) 
-			pfree(passwords[i]);
+		for (i = 0; i < num; i++) {
 
+			if (passwords[i] != NULL)
+				pfree(passwords[i]);
+			else
+				ereport(LOG,
+					(errmsg("Password %d was null", i)));
+		}
 		pfree(passwords);
 	}
 
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 09b6bebf40..ae3ca1175f 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -40,10 +40,12 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
 {
 	HeapTuple	roleTup;
 	Datum		datum;
+	TimestampTz current, vuntil = 0;
+
 	bool		isnull;
 	char	   **passwords;
 	CatCList   *passlist;
-	int		    i;
+	int		    i, j = 0, num_valid_passwords = 0;
 
 	/* Get role info from pg_authid */
 	roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
@@ -51,6 +53,7 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
 	{
 		*logdetail = psprintf(_("Role \"%s\" does not exist."),
 							  role);
+		*num = 0;
 		return NULL;			/* no such user */
 	}
 
@@ -58,7 +61,6 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
 	ReleaseSysCache(roleTup);
 	/* Find any existing password that is not the one being updated to get the salt */
 	passlist = SearchSysCacheList1(AUTHPASSWORDNAME, datum);
-	*num = passlist->n_members;
 
 	if (passlist->n_members == 0)
 	{
@@ -69,15 +71,41 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
 		return NULL;			/* user has no password */
 	}
 
-	passwords = palloc0(sizeof(char *) * passlist->n_members); 
+	current = GetCurrentTimestamp();
+
+	for (i = 0; i < passlist->n_members; i++)
+	{
+		HeapTuple	tup = &passlist->members[i]->tuple;
+
+		datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
+							Anum_pg_auth_password_expiration, &isnull);
+
+		if (!isnull)
+			vuntil = DatumGetTimestampTz(datum);
+
+		if (isnull || vuntil > current)
+			num_valid_passwords++;
+	}
+
+	passwords = palloc0(sizeof(char *) * num_valid_passwords);
+	*num = num_valid_passwords;
 
 	for (i = 0; i < passlist->n_members; i++)
 	{
 		HeapTuple	tup = &passlist->members[i]->tuple;
 
 		datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
-							Anum_pg_auth_password_password, &isnull);
-		passwords[i] = TextDatumGetCString(datum);
+							Anum_pg_auth_password_expiration, &isnull);
+
+		if (!isnull)
+			vuntil = DatumGetTimestampTz(datum);
+
+		if (isnull || vuntil > current)
+		{
+			datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
+								Anum_pg_auth_password_password, &isnull);
+			passwords[j++] = pstrdup(TextDatumGetCString(datum));
+		}
 	}
 
 	ReleaseCatCacheList(passlist);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7c1d023b7e..1f00a0aeaf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -790,7 +790,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	DOUBLE_P DROP
 
 	EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ERROR_P ESCAPE
-	EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
+	EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPIRES EXPLAIN EXPRESSION
 	EXTENSION EXTERNAL EXTRACT
 
 	FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR
@@ -1266,6 +1266,12 @@ AlterOptRoleElem:
 					$$ = makeDefElem("passname",
 									 (Node *)makeString($2), @1);
 				}
+			| EXPIRES IN_P Sconst opt_interval
+				{
+					TypeName *t = SystemTypeName("interval");
+					t->typmods = $4;
+					$$ = makeDefElem("expiresin", (Node *)makeStringConstCast($3, @3, t), @1);
+				}
 			| VALID FOR Sconst
 				{
 					$$ = makeDefElem("validFor", (Node *)makeString($3), @1);
@@ -17766,6 +17772,7 @@ unreserved_keyword:
 			| EXCLUDING
 			| EXCLUSIVE
 			| EXECUTE
+			| EXPIRES
 			| EXPLAIN
 			| EXPRESSION
 			| EXTENSION
@@ -18340,6 +18347,7 @@ bare_label_keyword:
 			| EXCLUSIVE
 			| EXECUTE
 			| EXISTS
+			| EXPIRES
 			| EXPLAIN
 			| EXPRESSION
 			| EXTENSION
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a7cc49898b..0d4c2c5d79 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -716,6 +716,7 @@ static char *recovery_target_string;
 static char *recovery_target_xid_string;
 static char *recovery_target_name_string;
 static char *recovery_target_lsn_string;
+static char *password_duration_string;

 /* should be static, but commands/variable.c needs to get at this */
@@ -4721,6 +4722,17 @@ static struct config_string ConfigureNamesString[] =
 		check_backtrace_functions, assign_backtrace_functions, NULL
 	},
 
+	{
+		{"password_valid_duration", PGC_SUSET, CONN_AUTH_AUTH,
+			gettext_noop("Specifies the default validity duration of new passwords."),
+			NULL,
+			GUC_SUPERUSER_ONLY | GUC_NOT_IN_SAMPLE
+		},
+		&password_duration_string,
+		NULL,
+		check_password_duration, assign_password_duration, show_password_duration
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 1c14f60a05..13d3479fac 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -19,6 +19,9 @@
 /* GUC. Is actually of type PasswordType. */
 extern PGDLLIMPORT int Password_encryption;
 
+/* GUC. system-wide password validity duration */
+extern Interval *default_password_duration;
+
 /* Hook to check passwords in CreateRole() and AlterRole() */
 typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null);
 
diff --git a/src/include/commands/variable.h b/src/include/commands/variable.h
index 0e5ddcbcf3..3b941c554c 100644
--- a/src/include/commands/variable.h
+++ b/src/include/commands/variable.h
@@ -34,5 +34,9 @@ extern void assign_session_authorization(const char *newval, void *extra);
 extern bool check_role(char **newval, void **extra, GucSource source);
 extern void assign_role(const char *newval, void *extra);
 extern const char *show_role(void);
+extern bool check_password_duration(char **newval, void **extra, GucSource source);
+extern void assign_password_duration(const char *newval, void *extra);
+extern const char *show_password_duration(void);
+
 
 #endif							/* VARIABLE_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ec79fafabf..cebf01840b 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -163,6 +163,7 @@ PG_KEYWORD("excluding", EXCLUDING, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("exists", EXISTS, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("expires", EXPIRES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("explain", EXPLAIN, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("expression", EXPRESSION, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("extension", EXTENSION, UNRESERVED_KEYWORD, BARE_LABEL)
-- 
2.34.1

---

From: "Brindle, Joshua" <[email-masked]>
Date: Fri, 1 Jul 2022 10:50:56 -0400

> Joshua and I had discussed it at some length as he was working on it.

Adding myself to the CC list here /waves

I gave Gurjeet a bit of a brain dump on what I had planned (and what 
we'd talked about), though he's free to take it in a different direction 
if he wants.

> version and I'll add it to the July commitfest.

Ah, thanks. Hopefully it wasn't too horrible of a rebase.

---

From: Stephen Frost <[email-masked]>
Date: Fri, 1 Jul 2022 11:13:35 -0400

Greetings,

> Adding myself to the CC list here /waves

Hi!

> we'd talked about), though he's free to take it in a different direction

Perhaps though would certainly like this to patch to be useful for the
use-cases that we had discussed, naturally. :)

> Ah, thanks. Hopefully it wasn't too horrible of a rebase.

Wasn’t too bad.. needs more clean-up, there was some white space issues and
some simple re-base stuff, but then the support for “md5” pg_hba option was
broken for users with SCRAM passwords because we weren’t checking if there
was a SCRAM pw stored and upgrading to SCRAM in that case.  That’s the main
case that I fixed.  We will need to document this though, of course.  The
patch I submitted should basically do:

pg_hba md5 + md5-only pws -> md5 auth used
pg_hba md5 + scram-only pws -> scram
pg_hba md5 + md5 and scram pws -> scram
pg_hba scram -> scram

Not sure if we need to try and do something to make it possible to have
pg_hba md5 + mixed pws and have md5 used but it’s tricky as we would have
to know on the server side early on if that’s what we want to do.  We could
add an option to md5 to say “only do md5” maybe but I’m also inclined to
not bother and tell people to just get moved to scram already.

For my 2c, I’d also like to move to having a separate column for the PW
type from the actual secret but that’s largely an independent change.

Thanks!

Stephen

---

From: Gurjeet Singh <[email-masked]>
Date: Mon, 25 Sep 2023 00:31:43 -0700

> Wasn’t too bad.. needs more clean-up, there was some white space issues and some simple re-base stuff, but then the support for “md5” pg_hba option was broken for users with SCRAM passwords because we weren’t checking if there was a SCRAM pw stored and upgrading to SCRAM in that case.  That’s the main case that I fixed.  We will need to document this though, of course.  The patch I submitted should basically do:
>
> Not sure if we need to try and do something to make it possible to have pg_hba md5 + mixed pws and have md5 used but it’s tricky as we would have to know on the server side early on if that’s what we want to do.  We could add an option to md5 to say “only do md5” maybe but I’m also inclined to not bother and tell people to just get moved to scram already.
>
> For my 2c, I’d also like to move to having a separate column for the PW type from the actual secret but that’s largely an independent change.

The docs say this about rolpassword in case it stores SCRAM-SHA-256
encrypted password "If the password is encrypted with SCRAM-SHA-256,
it has the format SCRAM-SHA-256$... This format is the same as that
specified by RFC-5803". So I believe our hands are tied, and we
cannot change that without breaking compliance with RFC 5803.

Please see attached v4 of the patch. The patch takes care of rebase to
the master/17-devel branch, and includes some changes, too. The
rebase/merge conflicts were quite involved, since some affected files
had been removed, or even split into multiple files over the course of
the last year; resolving merge-conflicts was more of a grunt work.

The changes since V3 are (compare [1] vs. [2], Git branches linked below):
- Remove TOAST table and corresponding index from pg_authid.
- Fix memory leak/allocation bug; replace malloc() with guc_alloc().
- Fix assumptions about passed-in double-pointers to GUC handling functions.
- Remove the new function is_role_valid() and its call sites, because
I believe it made backward-incompatible change to authentication
behavior (see more below).
- Improve error handling that was missing at a few places.
- Remove unnecessary checks, like (*var != NULL) checks when we know
all callers pass a NULL by convention.
- Replace MemSet() calls with var={0} styled initialization.
- Minor edits to docs to change them from pg_authid to pg_auth_password.

More details about why I chose to remove is_role_valid() and revert to
the old code:
is_role_valid() was a new function that pulled out a small section of
code from get_role_passwords() . I don't think moving this code block
to a new function gains us anything; in fact, it now forces us to call
the new function in two new locations, which we didn't have to do
before. It has to throw the same error messages as before, to maintain
compatibility with external tools/libraries, hence it duplicates those
messages as well, which is not ideal.

Moreover, before the patch, in case of CheckPasswordAuth(), the error
(if any) would have been thrown _after_ network communication done by
sendAuthRequest() call. But with this patch, the error is thrown
before the network interaction, hence this changes the order of
network interaction and the error message. This may have security
implications, too, but I'm unable to articulate one right now.

If we really want the role-validity check to be a function of its own,
a separate patch can address that; this patch doesn't have to make
that decision.

Open question: If a client is capable of providing just md5 passwords
handshake, and because of pg_hba.conf setting, or because the role has
at least one SCRAM password (essentially the 3rd case you mention
above: pg_hba md5 + md5 and scram pws -> scram), the server will
respond with a SASL/SCRAM authentication response, and that would
break the backwards compatibility and will deny access to the client.
Does this make it necessary to use a newer libpq/client library?

Before the patch, the rolvaliduntil was used to check and complain
that the password has expired, as the docs explicitly state that
rolvaliduntil represents "Password expiry time (only used for password
authentication); null if no expiration" . Keeping that column after
the introduction of per-password expiry times now separates the
role-validity from password validity. During an internal discussion a
curiosity arose whether we can simply remove rolvaliduntil. And I
believe the answer is yes, primarily because of how the docs describe
the column. So my proposal is to remove rolvaliduntil from pg_authid,
and on a case-by-case basis, optionally replace its uses with
max(pg_auth_password.expiration).

Comments?

Next steps:
- Break the patch into smaller patches.
- Address TODO items
- Comment each new function
- Add tests
- Add/update documentation

PS: Since this is a large patch, and because in some portions the code
has been indented by a level or two (e.g. to run a `for` loop over
existing code for single-password), I have found the following Git
command to be helpful in reviewing the changes between master and this
branch,: `git diff -b --color-words -U20 origin/master...HEAD -- `

[1]: v3 patch, applied to a contemporary commit on master branch
https://github.com/gurjeet/postgres/commits/multiple_passwords_v3

[2]: main development branch, patch rebased to current master branch,
followed by many changes
https://github.com/gurjeet/postgres/commits/multiple_passwords

Best regards,
Gurjeet
http://Gurje.et

---

From: Jeff Davis <[email-masked]>
Date: Tue, 26 Sep 2023 16:36:31 -0700

On Mon, 2023-09-25 at 00:31 -0700, Gurjeet Singh wrote:

> the master/17-devel branch, and includes some changes, too.

FWIW I got some failures applying. I didn't investigate much, and
instead I looked at your git branch (7a35619e).

> implications, too, but I'm unable to articulate one right now.

You mean before v3 or before v4? Is this currently a problem in v4?

> Does this make it necessary to use a newer libpq/client library?

Perhaps you can try the MD5 passwords first, and only if they fail,
move on to try scram passwords?

> Comments?

IIUC, for the case of multiple scram passwords, we use the salt to
select the right scram password, and then proceed from there?

I'm not very excited about the idea of naming passwords, or having
passwords with default names. I can't think of anything better right
now, so it might be OK.

> - Add/update documentation

These are needed to provide better review.

Regards,
	Jeff Davis

---

From: Gurjeet Singh <[email-masked]>
Date: Wed, 4 Oct 2023 22:41:15 -0700

I had an idea to simplify this feature/patch and after some validation
in internal discussions, I am posting the new approach here. I'd
appreciate any feedback and comments.

To begin with, the feature we are chasing is to make it convenient for
the users to rollover their passwords. Currently there is no easy way
to rollover passwords without increasing the risk of an application
outage. After a password change, the users/admins have to rush to
change the password in all locations where it is stored. There is a
window of time where if the application password is not changed to the
new one, and the application tries to connect/reconnect for any
reason, the application will fail authentication and lead to an outage.

I personally haven't seen any attempts by any
application/driver/framework to solve this problem in the wild, so
following is just me theorizing how one may solve this problem on the
application side; there may be other ways in which others may solve
this problem. The application may be written in such a way that upon
password authentication failure, it tries again with a second
password. The application's configuration file (or environment
variables) may allow specifying 2 passwords at the same time, and the
application will keep trying these 2 passwords alternatively until it
succeeds or the user restarts it with a new configuration. With such a
logic in place in their application, the users may first change the
configuration of all the instances of the application to hold the new
password along with the old/current working password, and only then
change the password in the database. This way, in the event of an
application instance start/restart either the old password will
succeed, or the new password will.

There may be other ways to solve this problem, but I can't imagine any
of those ways to be convenient and straightforward. At least not as
convenient as it can be if the database itself allowed for storing
both the passwords, and honored both passwords at the same time, while
allowing to associate a separate validity period with each of the
passwords.

The patches posted in this thread so far attempt to add the ability to
allow the user to have an arbitrary number of passwords. I believe
that allowing arbitrary number of passwords is not only unnecessary,
but the need to name passwords, the need to store them in a shared
catalog, etc. may actually create problems in the field. The
users/admins will have to choose names for passwords, which they
didn't have to previously. The need to name them may also lead to
users storing password-hints in the password names (e.g. 'mom''s
birthday', 'ex''s phone number', 'third password'), rendering the
passwords weak.

Moreover, allowing an arbitrarily many number of passwords will
require us to provide additional infrastructure to solve problems like
observability (which passwords are currently in use, and which ones
have been effectively forgotten by applications), or create a nuisance
for admins that can create more problems than it solves.

So I propose that the feature should allow no more than 2 passwords
for a role, each with their own validity periods. This eliminates the
need to name passwords, because at any given time there are no more
than 2 passwords; current one, and old one. This also eliminates the
need for a shared catalog to hold passwords, because with the limit of
2 imposed, we can store the old password and its validity period in
additional columns in the pg_authid table.

The patches so far also add a notion of max validity period of
passwords, which only a superuser can override. I believe this is a
useful feature, but that feature can be dealt with separately,
independent of password rollover feature. So in the newer patches I
will not include the relevant GUC and code.

With the above being said, following is the user interface I can think
of that can allow for various operations that users may need to
perform to rollover their passwords. The 'ADD PASSWORD' and 'ALL
PASSWORD' are additions to the grammar. rololdpassword and
rololdvaliduntil will be new columns in pg_authid that will hold the
old password and its valid-until value.

In essence, we create a stack that can hold 2 passwords. Pushing an
element when it's full will make it forget the bottom element. Popping
the stack makes it forget the top element, and the only remaining
element, if any, becomes the top.

-- Create a user, as usual
CREATE ROLE u1 PASSWORD 'p1' VALID UNTIL '2020/01/01';

-- Add another password that the user can use for authentication. This moves
-- the 'p1' password hash and its valid-until value to rololdpassword and
-- rololdvaliduntil, respectively.
ALTER ROLE u1 ADD PASSWORD 'p2' VALID UNTIL '2021/01/01';

-- Change the password 'p2's (current password's) validity
ALTER ROLE u1 VALID UNTIL '2022/01/01';
-- Note that currently I don't have a proposal for how to change the old
-- password's validity period, without deleting the latest/main password. See
-- PASSWORD NULL command below on how to delete the current password. It's very
-- likely that in a password rollover use case it's unnecessary, even
-- undesirable, to change the old password's validity period.

-- If, for some reason, the user wants to get rid of the latest password added.
-- Remove 'p2' (current password). The old password (p1), will be restored to
-- rolpassword, along with its valid-until value.
ALTER ROLE u1 PASSWORD NULL;
-- This may come as a surprise to some users, because currently they expect the
-- user to completely lose the ability to use passwords for login after this
-- command. To get the old behavior, the user must now use the ALL PASSWORD
-- NULL incantation; see below.
-- Issuing this command one more time will remove even the restored password,
-- hence leaving the user with no passwords.

-- Change the validity of the restored/current password (p1)
ALTER ROLE u1 VALID UNTIL '2022/01/01';

-- Add a new password (p3) without affecting old password's (p1) validity
ALTER ROLE u1 ADD PASSWORD 'p3' VALID UNTIL '2023/01/01';

-- Add a new password 'p4'. This will move 'p3' to rololdpassword, and hence
-- 'p1' will be forgotten completely.
-- After this command, user can use passwords 'p3' (old) and 'p4' (current) to
-- login.
ALTER ROLE u1 ADD PASSWORD 'p4' VALID UNTIL '2024/01/01';

-- Replace 'p4' (current) password with 'p5'. Note that this command is _not_
-- using the ADD keyword, hence 'p4' is _not_ moved to rololdpassword column.
-- After this command, user can use passwords 'p3' (old) and 'p5'
-- (current) to login.
ALTER ROLE u1 PASSWORD 'p5' VALID UNTIL '2025/01/01';

-- Using the old password to login will produce a warning, hopefully nudging
-- the user to start using the new password.
export PGPASSWORD=p3
psql -U u1
...
WARNING: Used old password to login

export PGPASSWORD=p5
psql -U u1
...
=> (no warning)

-- Remove all passwords from the role. Even old password, if any, is removed.
ALTER ROLE u1 ALL PASSWORD NULL;

In normal use, the users can simply keep ADDing new passwords, and the
database will promptly remember only one old password, and keep
forgetting any passwords older than that. But on the off chance
that someone needs to forget the latest password they added, and
restore the old password to be the "current" password, they can use
the PASSWORD NULL incantation. Note that this will result in
rol*old*password being set to NULL, because our password memory stack
cannot hold more than 2 elements.

Since this feature is targeted towards password rollovers, it's a legitimate
question to ask if we should enforce that the new password being added has a
valid-until greater than the valid-until of the existing/old password. I don't
think we should enforce this, at least not in this patch, because the
user/admin may have a use case where they want a short-lived new password that
they intend/want to change very soon; I'm thinking of cases where passwords are
being rolled over while they are also moving from older clients/libraries that
don't yet support scram-sha-256; keep using md5 and add passwords to honor
password rollover policy, but then as soon as all clients have been updated and
have the ability to use scram-sha-256, rollover the password again to utilize
the better mechanism.

I realize that allowing for a maximum of 2 passwords goes against the
zero-one-infinity rule [1], but I think in the case of password
rollovers it's perfectly acceptable to limit the number of active
passwords to just 2. If there are use cases, either related to password
rollovers, or in its vicinity, that can be better addressed by
allowing an arbitrarily many passwords, I would love to learn about
them and change this design to accommodate for those use cases, or
perhaps revert to pursuing the multiple-passwords feature.

[1]: https://en.wikipedia.org/wiki/Zero_one_infinity_rule

Best regards,
Gurjeet
http://Gurje.et

---

From: Nathan Bossart <[email-masked]>
Date: Thu, 5 Oct 2023 14:04:53 -0500

> for admins that can create more problems than it solves.

IMHO neither of these problems seems insurmountable.  Besides advising
against using hints as names, we could also automatically generate safe
names, or even disallow user-provided names entirely.  And adding
observability for passwords seems worthwhile anyway.

> additional columns in the pg_authid table.

Another approach could be to allow an abritrary number of passwords but to
also allow administrators to limit how many passwords can be associated to
each role.  That way, we needn't restrict this feature to 2 passwords for
everyone.  Perhaps 2 should be the default, but in any case, IMO we
shouldn't design to only support 2.

> element, if any, becomes the top.

I think this would be mighty confusing to users since it's not clear that
adding a password will potentially invalidate a current password (which
might be actively in use), but only if there are already 2 in the stack.  I
worry that such a desіgn might be too closely tailored to the
implementation details.  If we proceed with this design, perhaps we should
consider ERROR-ing if a user tries to add a third password.

> -- hence leaving the user with no passwords.

Is it possible to remove the oldest password added without removing the
latest password added?

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

---

From: Gurjeet Singh <[email-masked]>
Date: Thu, 5 Oct 2023 13:55:26 -0700

On Thu, Oct 5, 2023 at 12:04 PM Nathan Bossart <[email-masked]> wrote:
> IMHO neither of these problems seems insurmountable.

Agreed.

> Besides advising
> names, or even disallow user-provided names entirely.

Somehow naming passwords doesn't feel palatable to me.

> And adding

Agreed.

> shouldn't design to only support 2.

I don't see a real use case to support more than 2 passwords. Allowing
an arbitrary number of passwords might look good and clean from
aesthetics and documentation perspective (no artificially enforced
limits, as in the zero-one-infinity rule), but in absence of real use
cases for that many passwords, I'm afraid we might end up with a
feature that creates more and worse problems for the users than it
solves.

> might be actively in use), but only if there are already 2 in the stack.

Fair point. We can aid the user by emitting a NOTICE (or a WARNING)
message whenever an old password is removed from the system because of
addition of a new password.

> I

I did not have a stack in mind when developing the use case and the
grammar, so implementation details did not drive this design. This new
design was more of a response to the manageability nightmare that I
could see the old approach may lead to. When writing the email I
thought mentioning the stack analogy may make it easier to develop a
mental model. I certainly won't suggest using it in the docs for
explanation :-)

> Is it possible to remove the oldest password added without removing the

In the patch I have so far, ALTER ROLE u1 ADD PASSWORD '' (empty
string) will drop the old password (what you asked for), and move the
current password to rololdpassword (which is not exactly what you
asked for :-). Hence the oldest password will be forgotten, and the
current password will continue to work;

Perhaps an explicit syntax like ALTER ROLE u1 DROP OLD PASSWORD can be
used for this.

Best regards,
Gurjeet
http://Gurje.et

---

From: vignesh C <[email-masked]>
Date: Sat, 27 Jan 2024 07:18:59 +0530

On Tue, 10 Oct 2023 at 16:37, Gurjeet Singh <[email-masked]> wrote:
>
> > On Mon, Oct 9, 2023 at 2:31 AM Gurjeet Singh <[email-masked]> wrote:
> > >
> > > Next steps:
> > > - Break the patch into a series of smaller patches.
> > > - Add TAP tests (test the ability to actually login with these passwords)
> > > - Add more regression tests
>
> Please see attached the v4 of the patchset that introduces the notion
> of named passwords slots, namely 'first' and 'second' passwords, and
> allows users to address each of these passwords separately for the
> purposes of adding, dropping, or assigning expiration times.
>
> Apart from the changes described by each patch's commit title, one
> significant change since v3 is that now (included in v4-0002...patch)
> it is not allowed for a role to have a mix of a types of passwords.
> When adding a password, the patch ensures that the password being
> added uses the same hashing algorithm (md5 or scram-sha-256) as the
> existing password, if any.  Having all passwords of the same type
> helps the server pick the corresponding authentication method during
> connection attempt.
>
> The v3 patch also had a few bugs that were exposed by cfbot's
> automatic run. All those bugs have now been fixed, and the latest run
> on the v4 branch [1] on my private Git repo shows a clean run [1].
>
> The list of patches, and their commit titles are as follows:
>
> > v4-0001-...patch Add new columns to pg_authid
> > v4-0002-...patch Update password verification infrastructure to handle two passwords
> > v4-0003-...patch Added SQL support for ALTER ROLE to manage two passwords
> > v4-0004-...patch Updated pg_dumpall to support exporting a role's second password
> > v4-0005-...patch Update system views pg_roles and pg_shadow
> > v4-0006-...patch Updated pg_authid catalog documentation
> > v4-0007-...patch Updated psql's describe-roles meta-command
> > v4-0008-...patch Added documentation for ALTER ROLE command
> > v4-0009-...patch Added TAP tests to prove that a role can use two passwords to login
> > v4-0010-...patch pgindent run
> > v4-0011-...patch Run pgperltidy on files changed by this patchset
>
> Running pgperltidy updated many perl files unrelated to this patch, so
> in the last patch I chose to include only the one perl file that is
> affected by this patchset.

CFBot shows that the patch does not apply anymore as in [1]:
=== Applying patches on top of PostgreSQL commit ID
4d969b2f85e1fd00e860366f101fd3e3160aab41 ===
=== applying patch
./v4-0002-Update-password-verification-infrastructure-to-ha.patch
...
patching file src/backend/libpq/auth.c
Hunk #4 FAILED at 828.
Hunk #5 succeeded at 886 (offset -2 lines).
Hunk #6 succeeded at 907 (offset -2 lines).
1 out of 6 hunks FAILED -- saving rejects to file src/backend/libpq/auth.c.rej

Please post an updated version for the same.

[1] - http://cfbot.cputube.org/patch_46_4432.log

Regards,
Vignesh