Subject: Re: Pasword expiration warning

From: Yuefei Shi <[email-masked]>
Date: Fri, 9 Jan 2026 14:10:47 +0800

A few review comments for V8.

===
1.

+		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));
+		}

Please consider localization of the warning message.

2. typo fix

a. `Controls how many time ...` should be `Controls how much time ...`.

b. `Sets how many time before password expire to emit ...` should be
`Sets how much time before password expires to emit ...`

---

From: Japin Li <[email-masked]>
Date: Fri, 09 Jan 2026 15:12:26 +0800

On Fri, 09 Jan 2026 at 14:10, Yuefei Shi <[email-masked]> wrote:
> +						psprintf("your password will expire in %d day(s)", (int) (result / 86400));
> +		}
> b. `Sets how many time before password expire to emit ...` should be `Sets how much time before password expires to emit ...`

Nice catch. Updated in v9.  Please take to look.

I've also replaced the magic constant 86400 with the SECS_PER_DAY macro and
enclosed the statement in braces since it now spans multiple lines.

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

>From 7a519631dd6df96ed94b58fd77b13fe256e49e6e Mon Sep 17 00:00:00 2001
From: Japin Li <[email-masked]>
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 <[email-masked]>
---
 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 <[email-masked]>
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: Steven Niu <[email-masked]>
Date: Fri, 9 Jan 2026 07:36:59 +0000

From: Japin Li <[email-masked]>
Sent: Friday, January 09, 2026 15:12
To: Yuefei Shi <[email-masked]>
Cc: Gilles Darold <[email-masked]>; songjinzhou <[email-masked]>; PostgreSQL Hackers <[email-masked]>; Andrew Dunstan <[email-masked]>; Bossart, Nathan <[email-masked]>; Tom Lane <[email-masked]>; liu xiaohui <[email-masked]>
Subject: Re: Pasword expiration warning

________________________________________

Hi, Jiapin, 

I reviewed the v9-0002-Add-TAP-test-for-password_expire_warning.patch 
and here are my comments:

1. I think we should add tow more cases. One case is for the feature is disbaled. And another is for no warning when >1d remaining. 
2. The modification to pg_hba.conf is unnecessary as the default pg_hba.conf generated by initdb already allows local connections with appropriate methods.
    unlink($node->data_dir . '/pg_hba.conf');
    $node->append_conf('pg_hba.conf', "local all all scram-sha-256");
3. Make the expected string to be more exact. 
qr/your password will expire in/);
-->
qr/your password will expire in 1d/);

Thanks,
Steven

---

From: Japin Li <[email-masked]>
Date: Fri, 09 Jan 2026 17:04:31 +0800

Hi, Steven

Thanks for the review.

> 1. I think we should add tow more cases. One case is for the feature is disbaled. And another is for no warning when >1d remaining.

Add in v10.

>     $node->append_conf('pg_hba.conf', "local all all scram-sha-256");

Yes, it allows local connections, but they are always in trust mode, so no
password is required (or used).

> qr/your password will expire in 1d/);

Fixed. PFA.

v10-0001 - No changes.
v10-0002 - Address review comments.

>From e5f0562176b41d77c6b8f2166551d55bac72e8e9 Mon Sep 17 00:00:00 2001
From: Japin Li <[email-masked]>
Date: Fri, 9 Jan 2026 15:03:52 +0800
Subject: [PATCH v10 1/2] Add password_expire_warning GUC to warn clients

 
 
+/*

-	/*
 
 	return shadow_pass;

 
+	/*

 # GSSAPI using Kerberos

 /*
-- 
2.43.0

>From bbe19d8c6a879c54d00daf6f96d5d6be298dad3c Mon Sep 17 00:00:00 2001
From: Japin Li <[email-masked]>
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: Gilles Darold <[email-masked]>
Date: Fri, 9 Jan 2026 12:27:59 +0100

> v10-0002 - Address review comments.

Here is a v11 version of the patch.

v11-0001 - fix a miss on the typo fixes ( s/expire/expires/ in GUC 
description ) and add your name in the authors list.

v11-0002 - Add a test with Infinity in VALID UNTIL value.

-- 
Gilles Darold
http://hexacluster.ai/
From d80098d595754d58427df5705790db51f794940e Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email-masked]>
Date: Fri, 9 Jan 2026 12:13:59 +0100
Subject: [PATCH v11 1/2] Add password_expire_warning GUC to warn clients

Authors: Gilles Darold <[email-masked]>, Japin Li <[email-masked]>
---

 
+/*

-	/*
 
 	return shadow_pass;

 
+	/*
index 7c60b125564..43b3af0cb5c 100644
 
+  short_desc => 'Sets how much time before password expires to emit a warning at client connection. Default is 7 days, 0 means no warning.',
 
 # GSSAPI using Kerberos

 /*
-- 
2.43.0

From 6774c4bdf83499ddcaabcb784bc29c2f7c4a1e9e Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email-masked]>
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

index 00000000000..02e4bc9e7b9
@@ -0,0 +1,56 @@
+	"CREATE USER test_user3 WITH VALID UNTIL 'Infinity' PASSWORD '12345678'");
+$node->connect_ok('user=test_user3 dbname=postgres',
+	qq(no password expiration warning is emitted for infinity));

---

From: Japin Li <[email-masked]>
Date: Fri, 09 Jan 2026 19:56:13 +0800

> v11-0002 - Add a test with Infinity in VALID UNTIL value.

Thanks for updating the patches.  LGTM.

---

From: Soumya S Murali <[email-masked]>
Date: Wed, 28 Jan 2026 10:53:24 +0530

Hi all,

Thank you for the updated patches.

> v11-0002 - Add a test with Infinity in VALID UNTIL value.

I went through the discussions and I applied the posted patches on the
current master branch and have completed testing.
Firstly, the conceptual approach of adding a server-side
password_expire_warning GUC in patch 0001 looks reasonable for me too
as it allows all clients to benefit from the warning.
Here, the password expiry enforcement is strictly tied to
password-based authentication. With md5 authentication explicitly
configured, expiry enforcement works as expected, login succeeds while
the password is valid and fails with “password has expired” once the
expiry timestamp is reached. This is confirmed via server logs showing
the md5 authentication path being exercised. When non-password
authentication methods (trust/peer) are used, password expiry is
bypassed. While expiry enforcement functions correctly after expiry,
no advance warning is emitted prior to expiry in the baseline
behavior, which matches the motivation for this change. The approach
in patch 0001 of adding a server-side password_expire_warning GUC and
adding the corresponding TAP coverage in patch 0002 seems
directionally correct.

Regards,
Soumya

---

From: "Euler Taveira" <[email-masked]>
Date: Wed, 28 Jan 2026 09:44:55 -0300

You should use ngettext() for plural forms. I don't think you need a string
into ClientConnectionInfo. Instead, you could store only the number of days.

+   /*

... and you construct the message directly in the ereport().

-- 
Euler Taveira
EDB   https://www.enterprisedb.com/

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 28 Jan 2026 19:25:34 +0000

Hello!

A first question: have you looked at the GoAway patch[1]? While that
isn't exactly about the same situation, it was already considered for
password expiration checks in[2], and the same idea could be useful
for this situation too, especially in the context of my last question
in this email.

Shouldn't that use MemoryContextStrtup(TopMemoryContext, ...)?

+ /*
+ * 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;

Handling of this new variable is missing from
EstimateClientConnectionInfoSpace and SerializeClientConnectionInfo,
which the struct explicitly asks for a few lines above this change.
Even if you think that's not necessary for some reason, it should be
explained to avoid confusing readers.

+ * 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)

Could this use TIMESTAMP_NOT_FINITE?

And I think that "days"  should be "seconds".

Maybe call this variable something more descriptive, like
seconds_until_expiration?

+

This is not that useful on the last day - have you considered
displaying hours if the expiration date is within a day, or maybe
HH:MM?

There are a few typos (warnin in the commit message, disable ->
disables in the documentation, enf -> end in a comment)

I would also consider long lived connections. The warning currently
only fires when a user connects, maybe it would be useful to also do
something when the user enters the expiration period during an active
connection?

[1]: https://www.postgresql.org/message-id/DDPQ1RV5FE9U.I2WW34NGRD8Z%40jeltef.nl
[2]: https://www.postgresql.org/message-id/CAER375OvH3_ONmc-SgUFpA6gv_d6eNj2KdZktzo-f_uqNwwWNw%40mail.gmail.com

---

From: Japin Li <[email-masked]>
Date: Thu, 29 Jan 2026 19:44:33 +0800

> for this situation too, especially in the context of my last question

I didn't follow the thread.

If we perform password expiration checks based on an interval, wouldn’t it
also be possible to emit the warning during that check?

> Shouldn't that use MemoryContextStrtup(TopMemoryContext, ...)?

Here the memory context is TopTransactionContext.  I don't think this would
cause any problem.  Could you explain why you prefer TopMemoryContext?

> displaying hours if the expiration date is within a day, or maybe

Yeah, this was already proposed in [0], but there was no consensus.

> disables in the documentation, enf -> end in a comment)

Good catch.

> [2]: https://www.postgresql.org/message-id/CAER375OvH3_ONmc-SgUFpA6gv_d6eNj2KdZktzo-f_uqNwwWNw%40mail.gmail.com

[0] https://www.postgresql.org/message-id/tencent_8C7890411E85257F512EFE4F0DD00EE29806%40qq.com
-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

---

From: Zsolt Parragi <[email-masked]>
Date: Thu, 29 Jan 2026 13:41:05 +0000

> Yeah, this was already proposed in [0], but there was no consensus.

Sorry, I missed that! I should have read all earlier messages in more detail.

> cause any problem. Could you explain why you prefer TopMemoryContext?

I mainly looked at it similarly to the serialization question: how
existing parts of the struct are used? There are two places where
authn_id, the other string field, is set. Both use the same pattern,
and if I read the code correctly one of them is already in
TopMemoryContext, similarly to this call place. (and the other context
is the parallel worker context, which would also be long lived enough
for storing the authn_field, so again, technically the explicit
TopMemoryContext isn't necessary)

It seems to me that both existing places only use this explicit way of
allocating it in TopMemoryContext for clarity.

---

From: Gilles Darold <[email-masked]>
Date: Thu, 29 Jan 2026 16:39:54 +0100

Le 28/01/2026 à 13:44, Euler Taveira a écrit :
> You should use ngettext() for plural forms.

Is it a use we must do now or a wish?

    $ grep -r "ngettext(" src/backend/ | wc -l
    9
    $ grep -r "(s)" src/backend/ | wc -l
    831

If this is not a must do now, I prefer to use the old way because we 
don't have to repeat 2 times the constant.

> I don't think you need a string into ClientConnectionInfo. Instead, you could store only the number of days.
>
> +   /*

I have explained this choice in my first message:

"I have chosen to add a new field, const char *warning_message, to 
struct ClientConnectionInfo so that it can be used to send other 
messages to the client at end of connection ( 
src/backend/utils/init/postinit.c: InitPostgres() ). Not sure sure that 
this is the best way to do that but as it is a message dedicated to the 
connection I've though it could be the right place. If we don't expect 
other warning message sent to the client at connection time, just using 
an integer for the number of days remaining will be enough. We could use 
notice but it is not logged by default and also I think that warning is 
the good level for this message. "

-- 
Gilles Darold
http://hexacluster.ai/

---

From: Gilles Darold <[email-masked]>
Date: Thu, 29 Jan 2026 17:07:04 +0100

> for this situation too, especially in the context of my last question

I don't know about this thread before you mention it. With a quick read 
of the thread it looks that this GoAway protocol addition is use to ask 
to the client to disconnect/reconnect. Here we just want to emit a 
warning at connection to inform the user that his password will expire 
and it don't need re-connection at all.  Anyway I will have a deeper 
look in this thread.

> explained to avoid confusing readers.

This is intentional because this message is only emitted at the main 
connection and don't needed to be in the 
MyClientConnectionInfo serialization. I forgot to add a comment, I will do.

> Could this use TIMESTAMP_NOT_FINITE?

Thanks, it will be fixed too.

> displaying hours if the expiration date is within a day, or maybe

When you see that the password is about to expire in 0 day, do you 
really think that saying it will expire in 12h30m42s will encourage the 
user to change it now? If he don't do that in the previous days he will 
probably not do it in the hour too. Quite useless IMO but if there more 
vote to have HH:MM why not.

-- 
Gilles Darold
http://hexacluster.ai/

---

From: Nathan Bossart <[email-masked]>
Date: Thu, 29 Jan 2026 11:53:08 -0600

Sorry, I haven't been following the discussion, but I took a brief look at
the latest patch in the thread.

I'm not sure we should subject folks to these warnings by default, and I
don't see a reason to restrict the maximum value to 30 days.  IMHO we
should have this disabled by default and the maximum value should be
INT_MAX.

nitpick: I suspect we could simplify this code a bit, but I haven't tried.

Also, IMO we should be more precise about the expiration time.  There is a
reasonable difference between a password expiring in 1 second as opposed to
23 hours, 59 minutes, 59 seconds, but in both cases this message would say
"0 days".  You might be able to borrow from psql/common.c's PrintTiming()
function to add more detail here.

+	/*

Having a variable for warning messages could come in handy later.  For
example, we might add a warning about using MD5 passwords at some point.
In my draft patch for this [0], I put the warning after closing the
transaction, whereas this patch puts it just before.  I'm not sure I had a
principled reason for doing so, but it's an interesting difference between
the two patches.

[0] https://postgr.es/m/attachment/177167/v2-0002-WIP-add-warning-upon-authentication-with-MD5-pass.patch

-- 
nathan

---

From: Zsolt Parragi <[email-masked]>
Date: Thu, 29 Jan 2026 20:44:21 +0000

> vote to have HH:MM why not.

My concern with this is mainly the relation with the 2 threads I
linked. The password expiration patch disconnects users after their
password expires, opposed to the current behavior of letting existing
connections to continue - which I think is a quite useful security
improvement. And with that, the exact expiration time, maybe even
periodic reminders while the connection is active are way more useful.
("Your password is only valid for 2 more hours, please don't forget
this or you will be disconnected" ... "Now you only have 15 minutes,
last chance to fix it")

This is why I think something that could make periodic reminders, not
only one reminder during when the client connects, could be useful.
Even if not GoAway itself, maybe something similar to it? I mainly
linked these because I think the goal/problem is similar, and while
both patches look good separately, the user experience could use some
improvements if both get merged. (Another related discussion in the
password expiration thread is oauth token expiration checks, which
could use similar "Your token expired, you have X more minutes or you
will be disconnected" messages)

---

From: Gilles Darold <[email-masked]>
Date: Thu, 29 Jan 2026 22:48:39 +0100

> should have this disabled by default and the maximum value should be

This was my first though but I agree with Tom comment "Off-by-default is 
pretty much guaranteed to not help most people.". I will use INT_MAX.

> function to add more detail here.

Ok, there's now more vote for being more precise about the expiration 
time, I will add it.

> [0] https://postgr.es/m/attachment/177167/v2-0002-WIP-add-warning-upon-authentication-with-MD5-pass.patch

Understood, I will rewrite the patch to use a int variable.

Thanks.

-- 
Gilles Darold
http://www.darold.net/

---

From: Nathan Bossart <[email-masked]>
Date: Thu, 29 Jan 2026 15:56:25 -0600

> Understood, I will rewrite the patch to use a int variable.

Sorry if my note was not clear, but I didn't mean to suggest rewriting
anything here.  I thought the difference in placement for the warning
between your patch and mine was interesting, but I'm not sure there's
anything wrong that needs to be changed.

-- 
nathan

---

From: Gilles Darold <[email-masked]>
Date: Fri, 30 Jan 2026 12:33:54 +0100

Here a new v12 version of the patch. Changes are the following:

- Add more precision to the time remaining with format '%d day(s) 
%02dh%02dm'.

- Change GUC maximum value limit from 30d to INT_MAX

- Add comment to struct SerializedClientConnectionInfo explaining why 
the warning_message variable is not reported from 
struct ClientConnectionInfo.

- Use TIMESTAMP_INFINITY instead of PG_INT64_MAX to check Infinity value 
for rolvaliduntil.

- Fix 2 typo.

- Update TAP tests to reflect the time format change.

-- 
Gilles Darold
http://hexacluster.ai/
From 9edbc36af1dc90fd4a5d1793fc07ec04360e46bb Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email-masked]>
Date: Fri, 30 Jan 2026 12:21:24 +0100
Subject: [PATCH v12 2/2] Add TAP test for password_expire_warning

---

index 00000000000..a1172a2edff
+		qr/your password will expire in 0 day\(s\) \d{2}h\d{2}m/);

From cde8ce346bb7f6fe8b6131fc6c3ebe4423cd4787 Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email-masked]>
Date: Fri, 30 Jan 2026 11:51:08 +0100
Subject: [PATCH v12 1/2] Add password_expire_warning GUC to warn clients

Authors: Gilles Darold <[email-masked]>, Japin Li <[email-masked]>
---
 doc/src/sgml/config.sgml                      | 17 +++++++
 src/backend/libpq/crypt.c                     | 47 ++++++++++++++++---
 src/backend/utils/init/miscinit.c             |  4 +-
 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, 89 insertions(+), 8 deletions(-)

index 5560b95ee60..2d2e84ad870 100644
 
+        for the role. A value of <literal>0</literal> disables this behavior. The
+        default value is <literal>7d</literal>.
index 52722060451..77d7dda8afc 100644
 
+/*
@@ -70,14 +76,41 @@ get_role_password(const char *role, const char **logdetail)

-	/*
-	 * 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 < TIMESTAMP_INFINITY)
+		{
+			TimestampTz result = (vuntil - now) / USECS_PER_SEC;	/* in seconds */
+
+			if (result <= (TimestampTz) password_expire_warning)
+			{
+				int days, hours, minutes = 0;
+
+				days = (int) ( result / SECS_PER_DAY );
+				hours = (int) ( (result % SECS_PER_DAY) / 3600 );
+				minutes = (int) ( ((result % SECS_PER_DAY) % 3600) / 60 );
+
+				MyClientConnectionInfo.warning_message =
+						psprintf(_("your password will expire in %d day(s) %02dh%02dm"),
+									 days, hours, minutes);
+			}
+		}
 	}
 
 	return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..4cc385f7891 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -1020,7 +1020,8 @@ ClientConnectionInfo MyClientConnectionInfo;
 /*
  * Intermediate representation of ClientConnectionInfo for easier
  * serialization.  Variable-length fields are allocated right after this
- * header.
+ * header. We don't add ClientConnectionInfo's warning_message variable
+ * in serialization, it is not used by the parallel workers.
  */
 typedef struct SerializedClientConnectionInfo
 {
@@ -1089,6 +1090,7 @@ RestoreClientConnectionInfo(char *conninfo)

 
+	/*
index f0260e6e412..48e411ef0cf 100644
@@ -2242,6 +2242,15 @@
 
+  max => 'INT_MAX',
index c4f92fcdac8..f74e6e2ccff 100644
+#password_expire_warning = '7d'         # time before password expiration to emit a warning
 
 # GSSAPI using Kerberos
 
index 921b2daa4ff..826c5682a63 100644
+	 * end of InitPostgres(), currently only used to show the password
 
 /*
-- 
2.43.0

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 2 Feb 2026 11:04:03 -0600

> Here a new v12 version of the patch. Changes are the following:

Thanks.  I spent some time preparing this for commit, and I came up with
the attached.  Notable changes include:

* Renamed the parameter to password_expiration_warning_threshold.  It's a
mouthful, but I thought it was more descriptive.

* Changed the units for the parameter to minutes.  I can't imagine anyone
needs more granularity than hours or days, let alone seconds, so IMO
minutes is a good middle ground.

* I added a new "connection warnings" infrastructure that we can reuse
if/when we want to emit warnings for MD5 passwords.

* Moved the warning messages to Port.  ClientConnectionInfo appears to be
meant only for parallel workers, and I don't think we will ever want to
emit connection warnings there.

* Moved the tests into 001_password.pl.  I'm a bit concerned about these
tests being flaky, but I've tried setting the VALID UNTIL dates to make
spurious failures virtually impossible.

WDYT?

-- 
nathan
>From cb31a261878a7b7e839c9503418831cf23f3be08 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Mon, 2 Feb 2026 10:07:05 -0600
Subject: [PATCH v13 1/1] Add password expiration warnings.

This commit adds a new parameter called
parameter_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 <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: songjinzhou <[email-masked]>
Reviewed-by: liu xiaohui <[email-masked]>
Reviewed-by: Yuefei Shi <[email-masked]>
Reviewed-by: Steven Niu <[email-masked]>
Reviewed-by: Soumya S Murali <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Reviewed-by: Zsolt Parragi <[email-masked]>
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             | 72 +++++++++++++++++++
 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, 204 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5560b95ee60..5b9ab15bc92 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 minutes.  The
+        default is 7 days.  This parameter can only 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..3d86eb9a245 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 = 10080;
+
 /* 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_MINUTE < 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..f9a963c0a47 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 ***/
@@ -1232,6 +1235,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 +1452,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..6add1f37b4e 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_MIN',
+  variable => 'password_expiration_warning_threshold',
+  boot_val => '10080',
+  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)

---

From: Gilles Darold <[email-masked]>
Date: Mon, 2 Feb 2026 18:43:28 +0100

> spurious failures virtually impossible.

Thanks Nathan, I'm comfortable with all the changes except the first two.

I think configuration name password_expiration_warning_threshold is too 
long, why not only password_warning_threshold?  I think the description 
is here to explain more the configuration directive.

About the unit, I think using minutes is useless. It should be set in 
days to be really useful but like all other time based directives it can 
be set in seconds, minutes or hours too. This give the user the 
granularity he wants. The common use will be to set it using 'Nd' syntax 
but if someone wants to use 'Ns' syntax, it can be done.

-- 
Gilles Darold
http://www.darold.net/

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 2 Feb 2026 11:55:14 -0600

On Mon, Feb 02, 2026 at 06:43:28PM +0100, Gilles Darold wrote:
> long, why not only password_warning_threshold?  I think the description is
> here to explain more the configuration directive.

I agree that it's long, but leaving out "expiration" makes the name rather
ambiguous.  FWIW we do have 3 other GUCs with lengths >= this
(max_parallel_apply_workers_per_subscription,
ssl_passphrase_command_supports_reload, and
autovacuum_vacuum_insert_scale_factor).

> About the unit, I think using minutes is useless. It should be set in days
> to be really useful but like all other time based directives it can be set
> in seconds, minutes or hours too. This give the user the granularity he
> wants. The common use will be to set it using 'Nd' syntax but if someone
> wants to use 'Ns' syntax, it can be done.

Not all time-based GUCs use seconds.  log_rotation_age and
wal_summary_keep_time use minutes, and many others using milliseconds.  But
I'm fine with changing it back to seconds.  That would still allow setting
the threshold up to ~68 years, which ought to be enough for anyone.

-- 
nathan

---

From: Zsolt Parragi <[email-masked]>
Date: Mon, 2 Feb 2026 20:25:42 +0000

Hello!

> parameter_expiration_warning_threshold that controls when the

That should be password expiration warning

It is also clearer to me with expiration in it, without that I would
think about a complexity warning first.

And in the documentation:

That should be "can only be set"

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 2 Feb 2026 14:31:02 -0600

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?

-- 
nathan
>From cdbf9236523e2f8c886d5c766ec7fb661246e070 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Mon, 2 Feb 2026 14:26:33 -0600
Subject: [PATCH v14 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.

index 5560b95ee60..4219f14dd06 100644
 
+        this value is specified without units, it is taken as seconds.  The
+        default is 7 days.  This parameter can only be set in the
index 52722060451..fe9059d090c 100644
 
+/* Time before password expiration warnings. */
+int			password_expiration_warning_threshold = 604800;
+
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;

 	/*
+		if (expire_time / USECS_PER_SEC < password_expiration_warning_threshold)
 
 	return shadow_pass;

 
 /*
 
 	return result;
 }
+
+/*
index f0260e6e412..d00cd2437d9 100644

 # GSSAPI using Kerberos

 
 /*

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 3 Feb 2026 09:28:49 -0600

> 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
>From bc5fdffb74c3d4766c536b5980969cad20eadd8b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Mon, 2 Feb 2026 14:26:33 -0600
Subject: [PATCH v15 1/1] Add password expiration warnings.

 src/backend/libpq/crypt.c                     | 57 ++++++++++++--
 src/backend/utils/init/postinit.c             | 75 +++++++++++++++++++
 8 files changed, 207 insertions(+), 6 deletions(-)

 

 	/*
 
 	return shadow_pass;
index 3f401faf3de..213064bab77 100644

 /*** InitPostgres support ***/
@@ -987,6 +990,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
 		/* close the transaction we started above */
 		CommitTransactionCommand();
 
 		return;
 	}
 
@@ -1232,6 +1238,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
 
 /*
@@ -1446,3 +1455,69 @@ ThereIsAtLeastOneRole(void)
 
 	return result;
 }
+
+/*

 # GSSAPI using Kerberos

 
 /*

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 3 Feb 2026 12:08:08 -0500

> that use a password to authenticate.

+1, that makes sense.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 4 Feb 2026 15:44:05 +0100

> +							  role, days, hours, minutes);

You cannot use INT64_FORMAT inside translatable messages.  But you can 
use PRId64.

Using the type TimestampTz for what are essentially interval/duration 
quantities is a bit weird and confusing.  So maybe another placeholder 
would be more appropriate.

That said, I find writing plurals with "(s)" kind of lame.  It's not a 
good look.

It's a bit difficult to do this correctly when you have three separate 
values in one string.  I would consider for example just showing the 
number of days if the value is larger than one day, number hours if it's 
larger than one hour, else minutes.  I don't think you need 
minute-precision when the expiration time is several days out. 
Alternatively, just print the actual expiration timestamp.