0003-Per-password-expiration.patch

From 83cad16a61589a434e8d97a08e6d302048627775 Mon Sep 17 00:00:00 2001
From: Joshua Brindle <joshua.brindle@crunchydata.com>
Date: Wed, 2 Mar 2022 06:07:40 -0800
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

Signed-off-by: Joshua Brindle <joshua.brindle@crunchydata.com>
---
 src/backend/commands/user.c            | 121 ++++++++++++++++++++++++-
 src/backend/commands/variable.c        | 105 +++++++++++++++++++++
 src/backend/libpq/auth.c               |   9 +-
 src/backend/libpq/crypt.c              |  37 +++++++-
 src/backend/parser/gram.y              |  10 +-
 src/backend/utils/misc/guc.c           |  13 +++
 src/include/catalog/pg_auth_password.h |   1 +
 src/include/commands/user.h            |   3 +
 src/include/commands/variable.h        |   4 +
 src/include/parser/kwlist.h            |   1 +
 10 files changed, 291 insertions(+), 13 deletions(-)

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 69daabd7928..bbb33aaa7ef 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -48,8 +48,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__";
@@ -167,6 +168,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
  */
@@ -211,7 +290,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)
@@ -320,6 +399,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",
@@ -508,6 +593,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
@@ -529,18 +615,26 @@ 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. */
 			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));
 			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 e5ddcda0b4a..ca85a10b80b 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 5569599ab31..641cc92549b 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -855,9 +855,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 46b458f7d8a..877bb7a4f31 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));
@@ -58,7 +60,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)
 	{
@@ -68,15 +69,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 2a288ff13f9..8e0887618d0 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -682,7 +682,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	DOUBLE_P DROP
 
 	EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT
-	EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
+	EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPIRES EXPLAIN EXPRESSION
 	EXTENSION EXTERNAL EXTRACT
 
 	FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR
@@ -1137,6 +1137,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);
@@ -15766,6 +15772,7 @@ unreserved_keyword:
 			| EXCLUDING
 			| EXCLUSIVE
 			| EXECUTE
+			| EXPIRES
 			| EXPLAIN
 			| EXPRESSION
 			| EXTENSION
@@ -16311,6 +16318,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 1e3650184b1..a1295a3070f 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -682,6 +682,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 */
@@ -4662,6 +4663,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
@@ -5039,6 +5051,7 @@ static struct config_enum ConfigureNamesEnum[] =
 		NULL, NULL, NULL
 	},
 
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/pg_auth_password.h
index 4181caad30b..1cb0a211ee7 100644
--- a/src/include/catalog/pg_auth_password.h
+++ b/src/include/catalog/pg_auth_password.h
@@ -31,6 +31,7 @@ CATALOG(pg_auth_password,4551,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_RO
 
 #ifdef CATALOG_VARLEN                /* variable-length fields start here */
     text            password BKI_FORCE_NOT_NULL;        /* password */
+    timestamptz     expiration BKI_FORCE_NULL;	        /* password expiration time, if any */
 #endif
 } FormData_pg_auth_password;
 
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 7223b1fdae4..1203358933a 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -19,6 +19,9 @@
 /* GUC. Is actually of type PasswordType. */
 extern 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 0e5ddcbcf37..3b941c554cb 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 e7239b34885..0e1c8098363 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -159,6 +159,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.31.1