v3-0001-Add-password_expire_warning-GUC-to-warn-clients.patch

From 908591a520c72288f8abb594c62da2cc95a7443f Mon Sep 17 00:00:00 2001
From: songjinzhou <tsinghualucky912@foxmail.com>
Date: Wed, 7 Jan 2026 00:14:43 -0800
Subject: [PATCH] Add password_expire_warning GUC to warn clients

Introduce a new server configuration parameter, password_expire_warning,
which controls how many days before a role's password expiration a
warning message is sent to the client upon successful connection.

Author: Gilles Darold <gilles@darold.net>
---
 src/backend/libpq/crypt.c                 | 60 +++++++++++++++++++++++
 src/backend/utils/init/miscinit.c         |  1 +
 src/backend/utils/init/postinit.c         |  7 +++
 src/backend/utils/misc/guc_parameters.dat |  9 ++++
 src/include/libpq/crypt.h                 |  3 ++
 src/include/libpq/libpq-be.h              |  9 ++++
 6 files changed, 89 insertions(+)

diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d4..1022f6cc8c 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -20,6 +20,7 @@
 #include "common/scram-common.h"
 #include "libpq/crypt.h"
 #include "libpq/scram.h"
+#include "postmaster/postmaster.h"
 #include "utils/builtins.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
@@ -27,6 +28,9 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/* Emit a warning 7 days before password expiration */
+int		password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -80,6 +84,62 @@ get_role_password(const char *role, const char **logdetail)
 		return NULL;
 	}
 
+	/*
+	 * Password OK, but check if rolvaliduntil is less than GUC
+	 * password_expire_warning days to send a warning to the client
+	 */
+	if (!isnull && password_expire_warning > 0)
+	{
+		float8          result;
+		int64           remaining_seconds;
+
+		remaining_seconds = (vuntil - GetCurrentTimestamp()) / 1000000; /* in seconds */
+		result = (float8)remaining_seconds / 86400; /* in days */
+
+		if ((int) result <= password_expire_warning)
+		{
+			if (remaining_seconds >= 86400) /* 1 day or more */
+			{
+				int days = (int)result;
+				int hours = (int)((remaining_seconds % 86400) / 3600);
+				if (hours > 0)
+					MyClientConnectionInfo.warning_message =
+							psprintf("your password will expire in %d day%s and %d hour%s",
+									days, (days != 1) ? "s" : "",
+									hours, (hours != 1) ? "s" : "");
+				else
+					MyClientConnectionInfo.warning_message =
+							psprintf("your password will expire in %d day%s",
+									days, (days != 1) ? "s" : "");
+			}
+			else if (remaining_seconds >= 3600) /* 1 hour or more but less than 1 day */
+			{
+				int hours = (int)(remaining_seconds / 3600);
+				int minutes = (int)((remaining_seconds % 3600) / 60);
+				if (minutes > 0)
+					MyClientConnectionInfo.warning_message =
+							psprintf("your password will expire in %d hour%s and %d minute%s",
+									hours, (hours != 1) ? "s" : "",
+									minutes, (minutes != 1) ? "s" : "");
+				else
+					MyClientConnectionInfo.warning_message =
+							psprintf("your password will expire in %d hour%s",
+									hours, (hours != 1) ? "s" : "");
+			}
+			else /* less than 1 hour */
+			{
+				int minutes = (int)(remaining_seconds / 60);
+				int seconds = (int)(remaining_seconds % 60);
+				if (minutes == 0 && seconds > 0)
+					minutes = 1; /* show at least 1 minute if there's any time left */
+
+				MyClientConnectionInfo.warning_message =
+						psprintf("your password will expire in %d minute%s",
+								minutes, (minutes != 1) ? "s" : "");
+			}
+		}
+	}
+
 	return shadow_pass;
 }
 
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374f..24737c95c2 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -1089,6 +1089,7 @@ RestoreClientConnectionInfo(char *conninfo)
 
 	/* Copy the fields back into place */
 	MyClientConnectionInfo.authn_id = NULL;
+	MyClientConnectionInfo.warning_message = NULL;
 	MyClientConnectionInfo.auth_method = serialized.auth_method;
 
 	if (serialized.authn_id_len >= 0)
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 52c05a9d1d..d0d5c87d4e 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1229,6 +1229,13 @@ InitPostgres(const char *in_dbname, Oid dboid,
 	if (!bootstrap)
 		pgstat_bestart_final();
 
+	/*
+	 * Emit a warning message to the client when set, for example
+	 * to warn the user that the password will expire.
+	 */
+	if (MyClientConnectionInfo.warning_message)
+		ereport(WARNING, (errmsg("%s", MyClientConnectionInfo.warning_message)));
+
 	/* close the transaction we started above */
 	if (!bootstrap)
 		CommitTransactionCommand();
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 7c60b12556..1f9929e7ea 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2248,6 +2248,15 @@
   options => 'password_encryption_options',
 },
 
+{ name => 'password_expire_warning', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
+  short_desc => 'Sets the number of days before password expire to emit a warning at client connection. Default is 7 days, 0 means no warning.',
+  flags => 'GUC_UNIT_S',
+  variable => 'password_expire_warning',
+  boot_val => '604800',
+  min => '0',
+  max => '2592000',
+},
+
 { name => 'plan_cache_mode', type => 'enum', context => 'PGC_USERSET', group => 'QUERY_TUNING_OTHER',
   short_desc => 'Controls the planner\'s selection of custom or generic plan.',
   long_desc => 'Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better.  This can be set to override the default behavior.',
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e109..e92f4f151c 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -28,6 +28,9 @@
 /* Enables deprecation warnings for MD5 passwords. */
 extern PGDLLIMPORT bool md5_password_warnings;
 
+/* number of days before emitting a warning for password expiration */
+extern PGDLLIMPORT int password_expire_warning;
+
 /*
  * Types of password hashes or secrets.
  *
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 921b2daa4f..4ed61921cc 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -103,6 +103,15 @@ typedef struct ClientConnectionInfo
 	 * meaning if authn_id is not NULL; otherwise it's undefined.
 	 */
 	UserAuth	auth_method;
+
+	/*
+	 * Message to send to the client in case of connection success.
+	 * When not NULL a WARNING message is sent to the client at end
+	 * of the connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(). For example, it is use to show the
+	 * password expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0