Pasword expiration warning

diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..e93a9059f58 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 = 7;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -80,6 +84,22 @@ 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;
+
+		result = ((float8) (vuntil - GetCurrentTimestamp())) / 1000000.0; /* in seconds */
+		result /= 86400; /* in days */
+
+		if ((int) result <= password_expire_warning)
+			MyClientConnectionInfo.warning_message =
+					psprintf("your password will expire in %d days", (int) result);
+	}
+
 	return shadow_pass;
 }
 
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..356c8ffe215 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1232,6 +1232,13 @@ InitPostgres(const char *in_dbname, Oid dboid,
 	/* close the transaction we started above */
 	if (!bootstrap)
 		CommitTransactionCommand();
+
+	/*
+	 * 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)));
 }
 
 /*
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 7c60b125564..b7c42da28da 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 => '7',
+  min => '0',
+  max => '30',
+},
+
 { 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 f01886e1098..e92f4f151c9 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 921b2daa4ff..4ed61921ccd 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;
 
 /*

From a9f38f5c03cd3ccf492848a47b690b14e882dff6 Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Wed, 7 Jan 2026 12:58:41 +0800
Subject: [PATCH v2] 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                 | 20 ++++++++++++++++++++
 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, 49 insertions(+)

diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..315c559e4ac 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,22 @@ 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;
+
+		result = ((float8) (vuntil - GetCurrentTimestamp())) / 1000000.0; /* in seconds */
+		result /= 86400; /* in days */
+
+		if ((int) result <= password_expire_warning)
+			MyClientConnectionInfo.warning_message =
+					psprintf("your password will expire in %d days", (int) result);
+	}
+
 	return shadow_pass;
 }
 
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..d0d5c87d4ea 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 7c60b125564..1f9929e7eaf 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 f01886e1098..e92f4f151c9 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 921b2daa4ff..4ed61921ccd 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

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

From 8eed415101585e98b1b2cdbb3714a707302f8d7c Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Wed, 7 Jan 2026 15:36:46 +0100
Subject: [PATCH v4] 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                     | 19 +++++++++++++++++++
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 +++++++
 src/backend/utils/misc/guc_parameters.dat     |  9 +++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 +++
 src/include/libpq/libpq-be.h                  |  9 +++++++++
 7 files changed, 49 insertions(+)

diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..1f8bce00b16 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,21 @@ 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;
+
+		result = ((float8) (vuntil - GetCurrentTimestamp())) / 1000000.0; /* in seconds */
+
+		if ((int) result <= password_expire_warning)
+			MyClientConnectionInfo.warning_message =
+					psprintf("your password will expire in %d days", (int) (result / 86400));
+	}
+
 	return shadow_pass;
 }
 
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..d0d5c87d4ea 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 7c60b125564..1f9929e7eaf 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..e92f4f151c9 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 921b2daa4ff..4ed61921ccd 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

From 3361b5260ab4d432223012d14991742bcc653d70 Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Thu, 8 Jan 2026 06:55:44 +0100
Subject: [PATCH v5] 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>
---
 doc/src/sgml/config.sgml                      | 19 ++++++++++++++++++-
 src/backend/libpq/crypt.c                     | 19 +++++++++++++++++++
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 +++++++
 src/backend/utils/misc/guc_parameters.dat     |  9 +++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 +++
 src/include/libpq/libpq-be.h                  |  9 +++++++++
 8 files changed, 67 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..3384347a822 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,8 +1106,25 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warnings" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how many days before a role's password expiration a <literal>WARNING</literal>
+        message is sent to the client  upon successful connection. It requires that
+        a <command>VALID UNTIL</command> date is set for the user. A value of <literal>0d</literal>
+        disable this behavior. The default value is <literal>7d</literal>. 
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
-      <term><varname>password_encryption</varname> (<type>enum</type>)
+      <term><varname>password_encryption</varname> (<type>enum</type>
+)
       <indexterm>
        <primary><varname>password_encryption</varname> configuration parameter</primary>
       </indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..47a9d425a71 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,21 @@ 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 && vuntil < PG_INT64_MAX)
+	{
+		float8          result;
+
+		result = ((float8) (vuntil - GetCurrentTimestamp())) / 1000000.0; /* in seconds */
+
+		if ((int) result <= password_expire_warning)
+			MyClientConnectionInfo.warning_message =
+					psprintf("your password will expire in %d day(s)", (int) (result / 86400));
+	}
+
 	return shadow_pass;
 }
 
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..d0d5c87d4ea 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 7c60b125564..1f9929e7eaf 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..e92f4f151c9 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 921b2daa4ff..4ed61921ccd 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

From 9716ba56b0866e87dee6ab30462df634b4afcd33 Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Thu, 8 Jan 2026 06:55:44 +0100
Subject: [PATCH v6] 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>
---
 doc/src/sgml/config.sgml                      | 16 +++++++++
 src/backend/libpq/crypt.c                     | 36 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 +++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 +++++
 8 files changed, 75 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 601aa3afb8e..9bc1723a2b2 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1104,6 +1104,22 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warnings" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how many days before a role's password expiration a <literal>WARNING</literal>
+        message is sent to the client  upon successful connection. It requires that
+        a <command>VALID UNTIL</command> date is set for the user. A value of <literal>0d</literal>
+        disable this behavior. The default value is <literal>7d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..35a876eea5d 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.
  *
@@ -70,14 +74,32 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz	now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+				MyClientConnectionInfo.warning_message =
+						psprintf("your password will expire in %d day(s)", (int) (result / 86400));
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..d0d5c87d4ea 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 7c60b125564..1f9929e7eaf 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..e92f4f151c9 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 921b2daa4ff..4ed61921ccd 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

From 81de8df39982df5724ce51ea88bc29a28e0005f1 Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Thu, 8 Jan 2026 09:30:45 +0100
Subject: [PATCH v7] 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>
---
 doc/src/sgml/config.sgml                      | 16 ++++++++
 src/backend/libpq/crypt.c                     | 39 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 +++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 +++++
 8 files changed, 78 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..483aa8f8026 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,6 +1106,22 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warnings" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how many time before a role's password expiration a <literal>WARNING</literal>
+        message is sent to the client upon successful connection. It requires that
+        a <command>VALID UNTIL</command> date is set for the user. A value of <literal>0d</literal>
+        disable this behavior. The default value is <literal>7d</literal> and the max value <literal>30d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..147c8834380 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,12 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/*
+ * Threshold (in seconds) before password expiration to emit a warning
+ * at login (0 = disabled; default 7 days)
+ */
+int		password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -70,14 +77,32 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz	now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+				MyClientConnectionInfo.warning_message =
+						psprintf("your password will expire in %d day(s)", (int) (result / 86400));
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..d0d5c87d4ea 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 7c60b125564..15e0d10e162 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 how many time 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..420f8053255 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 seconds 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 921b2daa4ff..4dac9f98089 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 after a
+	 * successful connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(), currently only used to show the password
+	 * expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0

From 9b4fe1f5fa7d55799f95be356625fee0400cb77e Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Thu, 8 Jan 2026 13:23:10 +0100
Subject: [PATCH v8] 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>
---
 doc/src/sgml/config.sgml                      | 16 ++++++++
 src/backend/libpq/crypt.c                     | 39 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 +++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 +++++
 8 files changed, 78 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..9d1361dfad3 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,6 +1106,22 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warning" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how many time before a role's password expiration a <literal>WARNING</literal>
+        message is sent to the client upon successful connection. It requires that
+        a <command>VALID UNTIL</command> date is set for the user. A value of <literal>0d</literal>
+        disable this behavior. The default value is <literal>7d</literal> and the max value <literal>30d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..147c8834380 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,12 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/*
+ * Threshold (in seconds) before password expiration to emit a warning
+ * at login (0 = disabled; default 7 days)
+ */
+int		password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -70,14 +77,32 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz	now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+				MyClientConnectionInfo.warning_message =
+						psprintf("your password will expire in %d day(s)", (int) (result / 86400));
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 52c05a9d1d5..d0d5c87d4ea 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 7c60b125564..15e0d10e162 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 how many time 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..420f8053255 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 seconds 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 921b2daa4ff..4dac9f98089 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 after a
+	 * successful connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(), currently only used to show the password
+	 * expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0

From 1bd224fffecc8d7752dd41897126d391ccfdf138 Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Thu, 8 Jan 2026 13:23:10 +0100
Subject: [PATCH v8 1/2] 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>
---
 doc/src/sgml/config.sgml                      | 16 ++++++++
 src/backend/libpq/crypt.c                     | 38 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 +++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 +++++
 8 files changed, 77 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..9d1361dfad3 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,6 +1106,22 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warning" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how many time before a role's password expiration a <literal>WARNING</literal>
+        message is sent to the client upon successful connection. It requires that
+        a <command>VALID UNTIL</command> date is set for the user. A value of <literal>0d</literal>
+        disable this behavior. The default value is <literal>7d</literal> and the max value <literal>30d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..8b6b28fbec3 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -27,6 +27,12 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/*
+ * Threshold (in seconds) before password expiration to emit a warning
+ * at login (0 = disabled; default 7 days)
+ */
+int		password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -70,14 +76,32 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz	now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+				MyClientConnectionInfo.warning_message =
+						psprintf("your password will expire in %d day(s)", (int) (result / 86400));
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 3f401faf3de..3441c75e54a 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 7c60b125564..15e0d10e162 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 how many time 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..420f8053255 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 seconds 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 921b2daa4ff..4dac9f98089 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 after a
+	 * successful connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(), currently only used to show the password
+	 * expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0

From 6e589c4098abe2b198f8db08e2053d6f708e8ecb Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Fri, 9 Jan 2026 10:21:19 +0800
Subject: [PATCH v8 2/2] Add TAP test for password_expire_warning

---
 .../authentication/t/008_password_expire.pl   | 36 +++++++++++++++++++
 1 file changed, 36 insertions(+)
 create mode 100644 src/test/authentication/t/008_password_expire.pl

diff --git a/src/test/authentication/t/008_password_expire.pl b/src/test/authentication/t/008_password_expire.pl
new file mode 100644
index 00000000000..83c69a35d61
--- /dev/null
+++ b/src/test/authentication/t/008_password_expire.pl
@@ -0,0 +1,36 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test for authentication password expiration warning message.
+
+use strict;
+use warnings FATAL => 'all';
+use Time::Piece;
+use Time::Seconds;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $dt = localtime;   # Current datetime
+$dt += ONE_DAY;       # Add 1 day
+
+my $valid_until = $dt->strftime("%Y-%m-%d %H:%M:%S");
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->append_conf('postgresql.conf', "password_expire_warning = '1d'");
+$node->start;
+
+$node->safe_psql('postgres',
+	"CREATE USER test_user WITH VALID UNTIL '$valid_until' PASSWORD '12345678'");
+
+unlink($node->data_dir . '/pg_hba.conf');
+$node->append_conf('pg_hba.conf', "local all all scram-sha-256");
+$node->reload;
+
+$ENV{"PGPASSWORD"} = '12345678';
+$node->connect_ok('user=test_user dbname=postgres',
+	qq(test password_expire_warning),
+	expected_stderr =>
+		qr/your password will expire in/);
+
+done_testing();
-- 
2.43.0

From 7a519631dd6df96ed94b58fd77b13fe256e49e6e Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Thu, 8 Jan 2026 13:23:10 +0100
Subject: [PATCH v9 1/2] 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>
---
 doc/src/sgml/config.sgml                      | 17 ++++++++
 src/backend/libpq/crypt.c                     | 41 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 ++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 ++++
 8 files changed, 81 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..6760aa3b641 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,6 +1106,23 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warning" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how much time (in seconds) before a role's password expiration
+        a <literal>WARNING</literal> message is sent to the client upon successful
+        connection. It requires that a <command>VALID UNTIL</command> date is set
+        for the role. A value of <literal>0d</literal> disable this behavior. The
+        default value is <literal>7d</literal> and the maximum value <literal>30d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..5c00c7775ce 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -27,6 +27,12 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/*
+ * Threshold (in seconds) before password expiration to emit a warning
+ * at login (0 = disabled; default 7 days)
+ */
+int			password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -70,14 +76,35 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC;	/* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+			{
+				MyClientConnectionInfo.warning_message =
+					psprintf(_("your password will expire in %d day(s)"),
+							 (int) (result / SECS_PER_DAY));
+			}
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 3f401faf3de..3441c75e54a 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 7c60b125564..e4f107cc43b 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 how much time 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..420f8053255 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 seconds 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 921b2daa4ff..4dac9f98089 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 after a
+	 * successful connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(), currently only used to show the password
+	 * expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0

From a3c4981f8c43a356b67eaa15c231fdbdc811af8e Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Fri, 9 Jan 2026 10:21:19 +0800
Subject: [PATCH v9 2/2] Add TAP test for password_expire_warning

---
 .../authentication/t/008_password_expire.pl   | 36 +++++++++++++++++++
 1 file changed, 36 insertions(+)
 create mode 100644 src/test/authentication/t/008_password_expire.pl

diff --git a/src/test/authentication/t/008_password_expire.pl b/src/test/authentication/t/008_password_expire.pl
new file mode 100644
index 00000000000..83c69a35d61
--- /dev/null
+++ b/src/test/authentication/t/008_password_expire.pl
@@ -0,0 +1,36 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test for authentication password expiration warning message.
+
+use strict;
+use warnings FATAL => 'all';
+use Time::Piece;
+use Time::Seconds;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $dt = localtime;   # Current datetime
+$dt += ONE_DAY;       # Add 1 day
+
+my $valid_until = $dt->strftime("%Y-%m-%d %H:%M:%S");
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->append_conf('postgresql.conf', "password_expire_warning = '1d'");
+$node->start;
+
+$node->safe_psql('postgres',
+	"CREATE USER test_user WITH VALID UNTIL '$valid_until' PASSWORD '12345678'");
+
+unlink($node->data_dir . '/pg_hba.conf');
+$node->append_conf('pg_hba.conf', "local all all scram-sha-256");
+$node->reload;
+
+$ENV{"PGPASSWORD"} = '12345678';
+$node->connect_ok('user=test_user dbname=postgres',
+	qq(test password_expire_warning),
+	expected_stderr =>
+		qr/your password will expire in/);
+
+done_testing();
-- 
2.43.0

From e5f0562176b41d77c6b8f2166551d55bac72e8e9 Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Fri, 9 Jan 2026 15:03:52 +0800
Subject: [PATCH v10 1/2] 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>
---
 doc/src/sgml/config.sgml                      | 17 ++++++++
 src/backend/libpq/crypt.c                     | 41 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 ++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 ++++
 8 files changed, 81 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..6760aa3b641 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,6 +1106,23 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warning" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how much time (in seconds) before a role's password expiration
+        a <literal>WARNING</literal> message is sent to the client upon successful
+        connection. It requires that a <command>VALID UNTIL</command> date is set
+        for the role. A value of <literal>0d</literal> disable this behavior. The
+        default value is <literal>7d</literal> and the maximum value <literal>30d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..5c00c7775ce 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -27,6 +27,12 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/*
+ * Threshold (in seconds) before password expiration to emit a warning
+ * at login (0 = disabled; default 7 days)
+ */
+int			password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -70,14 +76,35 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC;	/* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+			{
+				MyClientConnectionInfo.warning_message =
+					psprintf(_("your password will expire in %d day(s)"),
+							 (int) (result / SECS_PER_DAY));
+			}
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 3f401faf3de..3441c75e54a 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 7c60b125564..e4f107cc43b 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 how much time 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..420f8053255 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 seconds 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 921b2daa4ff..4dac9f98089 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 after a
+	 * successful connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(), currently only used to show the password
+	 * expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0

From bbe19d8c6a879c54d00daf6f96d5d6be298dad3c Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Fri, 9 Jan 2026 15:04:45 +0800
Subject: [PATCH v10 2/2] Add TAP test for password_expire_warning

---
 .../authentication/t/008_password_expire.pl   | 50 +++++++++++++++++++
 1 file changed, 50 insertions(+)
 create mode 100644 src/test/authentication/t/008_password_expire.pl

diff --git a/src/test/authentication/t/008_password_expire.pl b/src/test/authentication/t/008_password_expire.pl
new file mode 100644
index 00000000000..fdeae1b84d1
--- /dev/null
+++ b/src/test/authentication/t/008_password_expire.pl
@@ -0,0 +1,50 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test for authentication password expiration warning message.
+
+use strict;
+use warnings FATAL => 'all';
+use Time::Piece;
+use Time::Seconds;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->append_conf('postgresql.conf', "password_expire_warning = '1d'");
+$node->start;
+
+my $dt = localtime;   # Current datetime
+$dt += ONE_DAY;       # Add 1 day
+my $valid_until = $dt->strftime("%Y-%m-%d %H:%M:%S");
+$node->safe_psql('postgres',
+	"CREATE USER test_user1 WITH VALID UNTIL '$valid_until' PASSWORD '12345678'");
+
+$dt += ONE_DAY;
+$valid_until = $dt->strftime("%Y-%m-%d %H:%M:%S");
+$node->safe_psql('postgres',
+	"CREATE USER test_user2 WITH VALID UNTIL '$valid_until' PASSWORD '12345678'");
+
+# Ensure subsequent connections authenticate with the password.
+unlink($node->data_dir . '/pg_hba.conf');
+$node->append_conf('pg_hba.conf', "local all all scram-sha-256");
+$node->reload;
+
+$ENV{"PGPASSWORD"} = '12345678';
+
+$node->connect_ok('user=test_user1 dbname=postgres',
+	qq(emit password expiration warning),
+	expected_stderr =>
+		qr/your password will expire in 0 day\(s\)/);
+
+$node->connect_ok('user=test_user2 dbname=postgres',
+	qq(no password expiration warning is emitted));
+
+$node->append_conf('postgresql.conf', "password_expire_warning = '0'");
+$node->reload;
+
+$node->connect_ok('user=test_user1 dbname=postgres',
+	qq(disable password expire warning));
+
+done_testing();
-- 
2.43.0

From d80098d595754d58427df5705790db51f794940e Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Fri, 9 Jan 2026 12:13:59 +0100
Subject: [PATCH v11 1/2] 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.

Authors: Gilles Darold <gilles@darold.net>, Japin Li <japinli@hotmail.com>
---
 doc/src/sgml/config.sgml                      | 17 ++++++++
 src/backend/libpq/crypt.c                     | 41 +++++++++++++++----
 src/backend/utils/init/miscinit.c             |  1 +
 src/backend/utils/init/postinit.c             |  7 ++++
 src/backend/utils/misc/guc_parameters.dat     |  9 ++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/libpq/crypt.h                     |  3 ++
 src/include/libpq/libpq-be.h                  |  9 ++++
 8 files changed, 81 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..6760aa3b641 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,6 +1106,23 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expire-warning" xreflabel="password_expire_warning">
+      <term><varname>password_expire_warning</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls how much time (in seconds) before a role's password expiration
+        a <literal>WARNING</literal> message is sent to the client upon successful
+        connection. It requires that a <command>VALID UNTIL</command> date is set
+        for the role. A value of <literal>0d</literal> disable this behavior. The
+        default value is <literal>7d</literal> and the maximum value <literal>30d</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-password-encryption" xreflabel="password_encryption">
       <term><varname>password_encryption</varname> (<type>enum</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..5c00c7775ce 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -27,6 +27,12 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/*
+ * Threshold (in seconds) before password expiration to emit a warning
+ * at login (0 = disabled; default 7 days)
+ */
+int			password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -70,14 +76,35 @@ get_role_password(const char *role, const char **logdetail)
 
 	ReleaseSysCache(roleTup);
 
-	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
-	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz now = GetCurrentTimestamp();
+
+		/*
+		 * Password OK, but check to be sure we are not past rolvaliduntil
+		 */
+		if (vuntil < now)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * Password OK, but check if rolvaliduntil is less than GUC
+		 * password_expire_warning days to send a warning to the client
+		 */
+		if (password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC;	/* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+			{
+				MyClientConnectionInfo.warning_message =
+					psprintf(_("your password will expire in %d day(s)"),
+							 (int) (result / SECS_PER_DAY));
+			}
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 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 3f401faf3de..3441c75e54a 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 7c60b125564..43b3af0cb5c 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 how much time before password expires 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
 #scram_iterations = 4096
 #md5_password_warnings = on             # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d'         # 0-30d time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 #krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..420f8053255 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 seconds 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 921b2daa4ff..4dac9f98089 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 after a
+	 * successful connection in src/backend/utils/init/postinit.c at
+	 * enf of InitPostgres(), currently only used to show the password
+	 * expiration warning.
+	 */
+	const char *warning_message;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0

From 6774c4bdf83499ddcaabcb784bc29c2f7c4a1e9e Mon Sep 17 00:00:00 2001
From: Gilles Darold <gilles@darold.net>
Date: Fri, 9 Jan 2026 12:18:31 +0100
Subject: [PATCH v11 2/2 2/2] Add TAP test for password_expire_warnin

---
 .../authentication/t/008_password_expire.pl   | 56 +++++++++++++++++++
 1 file changed, 56 insertions(+)
 create mode 100644 src/test/authentication/t/008_password_expire.pl

diff --git a/src/test/authentication/t/008_password_expire.pl b/src/test/authentication/t/008_password_expire.pl
new file mode 100644
index 00000000000..02e4bc9e7b9
--- /dev/null
+++ b/src/test/authentication/t/008_password_expire.pl
@@ -0,0 +1,56 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test for authentication password expiration warning message.
+
+use strict;
+use warnings FATAL => 'all';
+use Time::Piece;
+use Time::Seconds;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->append_conf('postgresql.conf', "password_expire_warning = '1d'");
+$node->start;
+
+my $dt = localtime;   # Current datetime
+$dt += ONE_DAY;       # Add 1 day
+my $valid_until = $dt->strftime("%Y-%m-%d %H:%M:%S");
+$node->safe_psql('postgres',
+	"CREATE USER test_user1 WITH VALID UNTIL '$valid_until' PASSWORD '12345678'");
+
+$dt += ONE_DAY;
+$valid_until = $dt->strftime("%Y-%m-%d %H:%M:%S");
+$node->safe_psql('postgres',
+	"CREATE USER test_user2 WITH VALID UNTIL '$valid_until' PASSWORD '12345678'");
+
+$node->safe_psql('postgres',
+	"CREATE USER test_user3 WITH VALID UNTIL 'Infinity' PASSWORD '12345678'");
+
+# Ensure subsequent connections authenticate with the password.
+unlink($node->data_dir . '/pg_hba.conf');
+$node->append_conf('pg_hba.conf', "local all all scram-sha-256");
+$node->reload;
+
+$ENV{"PGPASSWORD"} = '12345678';
+
+$node->connect_ok('user=test_user1 dbname=postgres',
+	qq(emit password expiration warning),
+	expected_stderr =>
+		qr/your password will expire in 0 day\(s\)/);
+
+$node->connect_ok('user=test_user2 dbname=postgres',
+	qq(no password expiration warning is emitted));
+
+$node->connect_ok('user=test_user3 dbname=postgres',
+	qq(no password expiration warning is emitted for infinity));
+
+$node->append_conf('postgresql.conf', "password_expire_warning = '0'");
+$node->reload;
+
+$node->connect_ok('user=test_user1 dbname=postgres',
+	qq(disable password expire warning));
+
+done_testing();
-- 
2.43.0