Date: Tue, 3 Feb 2026 09:28:49 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: Gilles Darold <gilles@darold.net>
Cc: Zsolt Parragi <zsolt.parragi@percona.com>,
	Japin Li <japinli@hotmail.com>,
	Yuefei Shi <shiyuefei1004@gmail.com>,
	songjinzhou <tsinghualucky912@foxmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
	Andrew Dunstan <andrew@dunslane.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	liu xiaohui <liuxh.zj.cn@gmail.com>,
	Steven Niu <niushiji@gmail.com>
Subject: Re: Pasword expiration warning
Message-ID: <aYIUMbyDWobSi94m@nathan>
References: <f7d6e318-415a-4b98-86b0-28ae954707cc@darold.net>
 <CAN4CZFMBj-mZq_o8BtQz_TgqDTALbn31kc-SFtWE4FR1N-4RBw@mail.gmail.com>
 <MEAPR01MB3031CF12043573A245D931AAB69EA@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <CAN4CZFP4AGeO-AynwE3e62XOKqaYnXtcGT1qfj=6nw2t5FOXwA@mail.gmail.com>
 <aXuehKtwtUXWVFYp@nathan>
 <197512e0-8116-4776-a600-822d29ee68a6@darold.net>
 <aYDZA3r3lG2oKc5D@nathan>
 <c933b0f1-ed95-4327-aca6-538acd2c547f@darold.net>
 <aYDlAhyYkjaEoEm7@nathan>
 <aYEJhk83XAqk76dP@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="AoOqqkjfdNotqxuB"
Content-Disposition: inline
In-Reply-To: <aYEJhk83XAqk76dP@nathan>
Archived-At: <https://www.postgresql.org/message-id/aYIUMbyDWobSi94m%40nathan>
Precedence: bulk


--AoOqqkjfdNotqxuB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Feb 02, 2026 at 02:31:02PM -0600, Nathan Bossart wrote:
> Here is an updated patch with the units set to seconds.  There are two main
> things on my mind:
> 
> * The placement of the WARNING.  Right now, I have it placed at the end of
> InitPostgres().  There are various other ways to get a WARNING during this
> function, so I think it's technically okay, but perhaps it makes more sense
> to put it at the end of ClientAuthentication() or something.  But the risk
> there is that something between the call to ClientAuthentication() and the
> end of InitPostgres() could ERROR/FATAL, in which case our new WARNING
> might be giving away more information than necessary.  So, I guess I lean
> towards keeping it where it is now, but I would be interested to hear other
> opinions on the matter.
> 
> * Whether we should emit the warnings for special client backends.
> Specifically, I think the current patch will send warnings to logical
> replication connections, but not physical replication connections.  My
> current feeling is that we should send warnings to any backend that uses a
> password to authenticate, i.e., add a call to EmitConnectionWarnings() at
> the end of the "am_walsender && !am_db_walsender" block.  Thoughts?  Are
> there any other backend types I'm forgetting that would be relevant here?

Hearing nothing, I've updated the patch to send warnings to all backends
that use a password to authenticate.

-- 
nathan

--AoOqqkjfdNotqxuB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v15-0001-Add-password-expiration-warnings.patch

From bc5fdffb74c3d4766c536b5980969cad20eadd8b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 2 Feb 2026 14:26:33 -0600
Subject: [PATCH v15 1/1] Add password expiration warnings.

This commit adds a new parameter called
password_expiration_warning_threshold that controls when the server
begins emitting imminent-password-expiration warnings upon
successful password authentication.  By default, this parameter is
set to 7 days, but this functionality can be disabled by setting it
to 0.  This patch also introduces a new "connection warning"
infrastructure that can be reused elsewhere.  For example, we may
want to emit warnings about the use of MD5 passwords for a couple
of releases before removing MD5 password support.

Author: Gilles Darold <gilles@darold.net>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: songjinzhou <tsinghualucky912@foxmail.com>
Reviewed-by: liu xiaohui <liuxh.zj.cn@gmail.com>
Reviewed-by: Yuefei Shi <shiyuefei1004@gmail.com>
Reviewed-by: Steven Niu <niushiji@gmail.com>
Reviewed-by: Soumya S Murali <soumyamurali.work@gmail.com>
Reviewed-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Zsolt Parragi <zsolt.parragi@percona.com>
Discussion: https://postgr.es/m/129bcfbf-47a6-e58a-190a-62fc21a17d03%40migops.com
---
 doc/src/sgml/config.sgml                      | 22 ++++++
 src/backend/libpq/crypt.c                     | 57 ++++++++++++--
 src/backend/utils/init/postinit.c             | 75 +++++++++++++++++++
 src/backend/utils/misc/guc_parameters.dat     | 10 +++
 src/backend/utils/misc/postgresql.conf.sample |  3 +-
 src/include/libpq/crypt.h                     |  3 +
 src/include/libpq/libpq-be.h                  |  9 +++
 src/test/authentication/t/001_password.pl     | 34 +++++++++
 8 files changed, 207 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5560b95ee60..4219f14dd06 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1157,6 +1157,28 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-password-expiration-warning-threshold" xreflabel="password_expiration_warning_threshold">
+      <term><varname>password_expiration_warning_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>password_expiration_warning_threshold</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        When this parameter is greater than zero, the server will emit a
+        <literal>WARNING</literal> upon successful password authentication if
+        less than this amount of time remains until the authenticated role's
+        password expires.  Note that a role's password only expires if a date
+        was specified in a <literal>VALID UNTIL</literal> clause for
+        <command>CREATE ROLE</command> or <command>ALTER ROLE</command>.  If
+        this value is specified without units, it is taken as seconds.  The
+        default is 7 days.  This parameter can only be set in the
+        <filename>postgresql.conf</filename> file or on the server command
+        line.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-md5-password-warnings" xreflabel="md5_password_warnings">
       <term><varname>md5_password_warnings</varname> (<type>boolean</type>)
       <indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 52722060451..fe9059d090c 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -19,11 +19,16 @@
 #include "common/md5.h"
 #include "common/scram-common.h"
 #include "libpq/crypt.h"
+#include "libpq/libpq-be.h"
 #include "libpq/scram.h"
 #include "utils/builtins.h"
+#include "utils/memutils.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
 
+/* Time before password expiration warnings. */
+int			password_expiration_warning_threshold = 604800;
+
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
@@ -71,13 +76,55 @@ get_role_password(const char *role, const char **logdetail)
 	ReleaseSysCache(roleTup);
 
 	/*
-	 * Password OK, but check to be sure we are not past rolvaliduntil
+	 * Password OK, but check to be sure we are not past rolvaliduntil or
+	 * password_expiration_warning_threshold.
 	 */
-	if (!isnull && vuntil < GetCurrentTimestamp())
+	if (!isnull)
 	{
-		*logdetail = psprintf(_("User \"%s\" has an expired password."),
-							  role);
-		return NULL;
+		TimestampTz expire_time = vuntil - GetCurrentTimestamp();
+
+		/*
+		 * If we're past rolvaliduntil, the connection attempt should fail, so
+		 * update logdetail and return NULL.
+		 */
+		if (expire_time < 0)
+		{
+			*logdetail = psprintf(_("User \"%s\" has an expired password."),
+								  role);
+			return NULL;
+		}
+
+		/*
+		 * If we're past the warning threshold, the connection attempt should
+		 * succeed, but we still want to emit a warning.  To do so, we queue
+		 * the warning message using StoreConnectionWarning() so that it will
+		 * be emitted at the end of InitPostgres(), and we return normally.
+		 */
+		if (expire_time / USECS_PER_SEC < password_expiration_warning_threshold)
+		{
+			MemoryContext oldcontext;
+			TimestampTz days;
+			TimestampTz hours;
+			TimestampTz minutes;
+			char	   *warning;
+			char	   *detail;
+
+			oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+
+			days = expire_time / USECS_PER_DAY;
+			hours = (expire_time % USECS_PER_DAY) / USECS_PER_HOUR;
+			minutes = (expire_time % USECS_PER_HOUR) / USECS_PER_MINUTE;
+
+			warning = pstrdup(_("role password will expire soon"));
+			detail = psprintf(_("The password for role \"%s\" will expire in "
+								INT64_FORMAT " day(s), " INT64_FORMAT
+								" hour(s), " INT64_FORMAT " minute(s)."),
+							  role, days, hours, minutes);
+
+			StoreConnectionWarning(warning, detail);
+
+			MemoryContextSwitchTo(oldcontext);
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 3f401faf3de..213064bab77 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -70,6 +70,8 @@
 #include "utils/syscache.h"
 #include "utils/timeout.h"
 
+static bool ConnectionWarningsEmitted = false;
+
 static HeapTuple GetDatabaseTuple(const char *dbname);
 static HeapTuple GetDatabaseTupleByOid(Oid dboid);
 static void PerformAuthentication(Port *port);
@@ -85,6 +87,7 @@ static void ClientCheckTimeoutHandler(void);
 static bool ThereIsAtLeastOneRole(void);
 static void process_startup_options(Port *port, bool am_superuser);
 static void process_settings(Oid databaseid, Oid roleid);
+static void EmitConnectionWarnings(void);
 
 
 /*** InitPostgres support ***/
@@ -987,6 +990,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
 		/* close the transaction we started above */
 		CommitTransactionCommand();
 
+		/* send any WARNINGs we've accumulated during initialization */
+		EmitConnectionWarnings();
+
 		return;
 	}
 
@@ -1232,6 +1238,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
 	/* close the transaction we started above */
 	if (!bootstrap)
 		CommitTransactionCommand();
+
+	/* send any WARNINGs we've accumulated during initialization */
+	EmitConnectionWarnings();
 }
 
 /*
@@ -1446,3 +1455,69 @@ ThereIsAtLeastOneRole(void)
 
 	return result;
 }
+
+/*
+ * Stores a warning message to be sent at the end of InitPostgres().  "detail"
+ * can be NULL, but "msg" cannot.
+ *
+ * NB: Caller should ensure the strings are allocated in a long-lived context
+ * like TopMemoryContext.
+ */
+void
+StoreConnectionWarning(char *msg, char *detail)
+{
+	MemoryContext oldcontext;
+
+	Assert(msg);
+
+	if (ConnectionWarningsEmitted)
+		elog(ERROR, "StoreConnectionWarning() called after EmitConnectionWarnings()");
+
+	oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+
+	MyProcPort->warning_msgs = lappend(MyProcPort->warning_msgs, msg);
+	MyProcPort->warning_details = lappend(MyProcPort->warning_details, detail);
+
+	MemoryContextSwitchTo(oldcontext);
+}
+
+/*
+ * Sends the warning messages saved for the end of InitPostgres() and frees the
+ * strings and lists.
+ *
+ * NB: This can only be called once per backend.
+ */
+static void
+EmitConnectionWarnings(void)
+{
+	ListCell   *lc_msg;
+	ListCell   *lc_detail;
+
+	if (ConnectionWarningsEmitted)
+		elog(ERROR, "EmitConnectionWarnings() called more than once");
+	else
+		ConnectionWarningsEmitted = true;
+
+	if (MyProcPort == NULL)
+		return;
+
+	forboth(lc_msg, MyProcPort->warning_msgs,
+			lc_detail, MyProcPort->warning_details)
+	{
+		char	   *msg = (char *) lfirst(lc_msg);
+		char	   *detail = (char *) lfirst(lc_detail);
+
+		ereport(WARNING,
+				(errmsg("%s", msg),
+				 detail ? errdetail("%s", detail) : 0));
+
+		pfree(msg);
+		if (detail)
+			pfree(detail);
+	}
+
+	list_free(MyProcPort->warning_msgs);
+	list_free(MyProcPort->warning_details);
+	MyProcPort->warning_msgs = NIL;
+	MyProcPort->warning_details = NIL;
+}
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index f0260e6e412..d00cd2437d9 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2242,6 +2242,16 @@
   options => 'password_encryption_options',
 },
 
+{ name => 'password_expiration_warning_threshold', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
+  short_desc => 'Time before password expiration warnings.',
+  long_desc => '0 means not to emit these warnings.',
+  flags => 'GUC_UNIT_S',
+  variable => 'password_expiration_warning_threshold',
+  boot_val => '604800',
+  min => '0',
+  max => 'INT_MAX',
+},
+
 { 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 c4f92fcdac8..6575a37405c 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -96,7 +96,8 @@
 #authentication_timeout = 1min          # 1s-600s
 #password_encryption = scram-sha-256    # scram-sha-256 or (deprecated) md5
 #scram_iterations = 4096
-#md5_password_warnings = on             # display md5 deprecation warnings?
+#password_expiration_warning_threshold = 7d  # time before expiration warnings
+#md5_password_warnings = on                  # display md5 deprecation warnings?
 #oauth_validator_libraries = '' # comma-separated list of trusted validator modules
 
 # GSSAPI using Kerberos
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..081817972d5 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -25,6 +25,9 @@
  */
 #define MAX_ENCRYPTED_PASSWORD_LEN (512)
 
+/* Time before password expiration warnings. */
+extern PGDLLIMPORT int password_expiration_warning_threshold;
+
 /* Enables deprecation warnings for MD5 passwords. */
 extern PGDLLIMPORT bool md5_password_warnings;
 
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 921b2daa4ff..fa382261fb1 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -238,6 +238,13 @@ typedef struct Port
 	char	   *raw_buf;
 	ssize_t		raw_buf_consumed,
 				raw_buf_remaining;
+
+	/*
+	 * Content of warning messages to send to the client upon successful
+	 * authentication.
+	 */
+	List	   *warning_msgs;
+	List	   *warning_details;
 } Port;
 
 /*
@@ -367,4 +374,6 @@ extern int	pq_setkeepalivesinterval(int interval, Port *port);
 extern int	pq_setkeepalivescount(int count, Port *port);
 extern int	pq_settcpusertimeout(int timeout, Port *port);
 
+extern void StoreConnectionWarning(char *msg, char *detail);
+
 #endif							/* LIBPQ_BE_H */
diff --git a/src/test/authentication/t/001_password.pl b/src/test/authentication/t/001_password.pl
index f4d65ba7bae..0ec9aa9f4e8 100644
--- a/src/test/authentication/t/001_password.pl
+++ b/src/test/authentication/t/001_password.pl
@@ -68,8 +68,24 @@ $node->init;
 $node->append_conf('postgresql.conf', "log_connections = on\n");
 # Needed to allow connect_fails to inspect postmaster log:
 $node->append_conf('postgresql.conf', "log_min_messages = debug2");
+$node->append_conf('postgresql.conf', "password_expiration_warning_threshold = '1100d'");
 $node->start;
 
+# Set up roles for password_expiration_warning_threshold test
+my $current_year = 1900 + ${ [ localtime(time) ] }[5];
+my $expire_year = $current_year - 1;
+$node->safe_psql(
+	'postgres',
+	"CREATE ROLE expired LOGIN VALID UNTIL '$expire_year-01-01' PASSWORD 'pass'");
+$expire_year = $current_year + 2;
+$node->safe_psql(
+	'postgres',
+	"CREATE ROLE expiration_warnings LOGIN VALID UNTIL '$expire_year-01-01' PASSWORD 'pass'");
+$expire_year = $current_year + 5;
+$node->safe_psql(
+	'postgres',
+	"CREATE ROLE no_warnings LOGIN VALID UNTIL '$expire_year-01-01' PASSWORD 'pass'");
+
 # Test behavior of log_connections GUC
 #
 # There wasn't another test file where these tests obviously fit, and we don't
@@ -531,6 +547,24 @@ $node->connect_fails(
 	  qr/authentication method requirement "!password,!md5,!scram-sha-256" failed: server requested SCRAM-SHA-256 authentication/
 );
 
+# Test password_expiration_warning_threshold
+$node->connect_fails(
+	"user=expired dbname=postgres",
+	"connection fails due to expired password",
+	expected_stderr =>
+	  qr/password authentication failed for user "expired"/
+);
+$node->connect_ok(
+	"user=expiration_warnings dbname=postgres",
+	"connection succeeds with password expiration warning",
+	expected_stderr =>
+	  qr/role password will expire soon/
+);
+$node->connect_ok(
+	"user=no_warnings dbname=postgres",
+	"connection succeeds with no password expiration warning"
+);
+
 # Test SYSTEM_USER <> NULL with parallel workers.
 $node->safe_psql(
 	'postgres',
-- 
2.50.1 (Apple Git-155)


--AoOqqkjfdNotqxuB--