password_expire.patch

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 7a59e65f2d3..91a982e8ba2 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -34,6 +34,7 @@
 #include "access/parallel.h"
 #include "access/printtup.h"
 #include "access/xact.h"
+#include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
 #include "commands/async.h"
 #include "commands/event_trigger.h"
@@ -76,10 +77,12 @@
 #include "tcop/utility.h"
 #include "utils/guc_hooks.h"
 #include "utils/injection_point.h"
+#include "utils/inval.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
 #include "utils/snapmgr.h"
+#include "utils/syscache.h"
 #include "utils/timeout.h"
 #include "utils/timestamp.h"
 #include "utils/varlena.h"
@@ -115,6 +118,13 @@ int			client_connection_check_interval = 0;
 /* flags for non-system relation kinds to restrict use */
 int			restrict_nonsystem_relation_kind;
 
+/*
+ * Flag set by syscache listener to indicate if the user's password validity
+ * (rolvaliduntil) needs to be checked for expiration before the next
+ * command execution.
+ */
+static bool	AuthCheckNeeded = false;
+
 /* ----------------
  *		private typedefs etc
  * ----------------
@@ -196,6 +206,7 @@ static void drop_unnamed_stmt(void);
 static void log_disconnections(int code, Datum arg);
 static void enable_statement_timeout(void);
 static void disable_statement_timeout(void);
+static void CheckPasswordExpiration(void);
 
 
 /* ----------------------------------------------------------------
@@ -1074,6 +1085,13 @@ exec_simple_query(const char *query_string)
 	 */
 	start_xact_command();
 
+	/*
+	 * Check for password expiration if a relevant pg_authid change has occurred.
+	 * AuthCheckNeeded is set by the syscache listener (AuthCacheInvalidated).
+	 */
+	if (AuthCheckNeeded)
+		CheckPasswordExpiration();
+
 	/*
 	 * Zap any pre-existing unnamed statement.  (While not strictly necessary,
 	 * it seems best to define simple-Query mode as if it used the unnamed
@@ -4213,6 +4231,81 @@ PostgresSingleUserMain(int argc, char *argv[],
 	PostgresMain(dbname, username);
 }
 
+/*
+ * CheckPasswordExpiration
+ *
+ * Checks the current user's pg_authid.rolvaliduntil timestamp against the
+ * current transaction start time. If the role's valid-until time is in the
+ * past, the function terminates the backend process with a FATAL error,
+ * enforcing password expiration policy.
+ * This function is typically called only when the AuthCheckNeeded flag is set
+ * by the AuthCacheInvalidated syscache callback.
+ */
+static void
+CheckPasswordExpiration(void)
+{
+	HeapTuple	tuple;
+
+	/*
+	 * Look up the current user's entry in pg_authid. We must do this, even
+	 * if only AuthCheckNeeded is set, because GetUserId() might return a
+	 * different user ID than the one that triggered the invalidation (though
+	 * that's unlikely for AUTHOID).
+	 */
+
+	tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(GetUserId()));
+
+	if (HeapTupleIsValid(tuple))
+	{
+		Datum 		rolvaliduntil_datum;
+		bool		validUntil_null;
+
+		/* Get the expiration time column */
+		rolvaliduntil_datum = SysCacheGetAttr(AUTHNAME, tuple,
+											  Anum_pg_authid_rolvaliduntil,
+											  &validUntil_null);
+
+		if (!validUntil_null)
+		{
+			TimestampTz expiration_time = DatumGetTimestampTz(rolvaliduntil_datum);
+			TimestampTz now = GetCurrentTransactionStartTimestamp();
+
+			if (expiration_time < now)
+			{
+				ReleaseSysCache(tuple);
+				ereport(FATAL,
+						(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+						 errmsg("Connection expired due to internal password policy enforcement"),
+						 errdetail("User's password expired at %s.",
+								   timestamptz_to_str(expiration_time)),
+						 errhint("Reconnect with a renewed password or obtain new authorization.")));
+			}
+		}
+
+		ReleaseSysCache(tuple);
+	}
+	/* Reset the flag after performing the check */
+	AuthCheckNeeded = false;
+}
+
+/*
+ * AuthCacheInvalidated
+ * Syscache callback function registered for the AUTHOID cache (pg_authid).
+ *
+ * This function is executed whenever a tuple in pg_authid is updated, inserted,
+ * or deleted. Its primary purpose is to catch changes to the currently
+ * connected user's 'rolvaliduntil' field.
+ *
+ * It sets the static flag AuthCheckNeeded to true, signaling to the main
+ * execution loop (exec_simple_query) that the user's password expiration
+ * status must be checked before the next command runs.
+ */
+static void
+AuthCacheInvalidated(Datum arg, int cacheid, uint32 hashvalue)
+{
+	/* This callback is executed when an entry in pg_authid changes */
+	AuthCheckNeeded = true;
+}
 
 /* ----------------------------------------------------------------
  * PostgresMain
@@ -4554,6 +4647,16 @@ PostgresMain(const char *dbname, const char *username)
 	if (!ignore_till_sync)
 		send_ready_for_query = true;	/* initially, or after error */
 
+
+	/*
+	 * Register a SysCache listener for pg_authid changes (specifically for
+	 * rolvaliduntil). This provides an event-driven mechanism to enforce
+	 * password/authorization expiration immediately upon change, rather than
+	 * relying on polling. The callback sets a flag (AuthCheckNeeded) which
+	 * is checked before executing each simple query.
+	 */
+	CacheRegisterSyscacheCallback(AUTHOID, AuthCacheInvalidated, (Datum) 0);
+
 	/*
 	 * Non-error queries loop here.
 	 */