Subject: Re: Pasword expiration warning

From: Gilles Darold <[email-masked]>
Date: Tue, 6 Jan 2026 15:43:20 +0100

Le 21/11/2021 à 10:49, Gilles Darold a écrit :
> Le 20/11/2021 à 14:48, Andrew Dunstan a écrit :
>> On 11/19/21 19:17, Bossart, Nathan wrote:
>>> On 11/19/21, 7:56 AM, "Tom Lane" <[email-masked]> wrote:
>>>> That leads me to wonder about server-side solutions.  It's easy
>>>> enough for the server to see that it's used a password with an
>>>> expiration N days away, but how could that be reported to the
>>>> client?  The only idea that comes to mind that doesn't seem like
>>>> a protocol break is to issue a NOTICE message, which doesn't
>>>> seem like it squares with your desire to only do this interactively.
>>>> (Although I'm not sure I believe that's a great idea.  If your
>>>> application breaks at 2AM because its password expired, you
>>>> won't be any happier than if your interactive sessions start to
>>>> fail.  Maybe a message that would leave a trail in the server log
>>>> would be best after all.)
>>> I bet it's possible to use the ClientAuthentication_hook for this.  In
>>> any case, I agree that it probably belongs server-side so that other
>>> clients can benefit from this.
>>>
>> +1 for a server side solution. The people most likely to benefit from
>> this are the people least likely to be using psql IMNSHO.
>>
>>
>> Ok, I can try to implement something at server side using a NOTICE message.

Hi,

Sorry to resurrect this old thread, but I had completely forgotten about 
it. If there's still interest in this feature, then please find in 
attachment a patch to emit a warning to the client and into the logs 
when the password will expire within 7 days by default. A GUC, 
password_expire_warning, allow to change the number of days before 
sending the message or to disable this feature with setting value 0.

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.

Output at psql connection:

         $ /usr/local/pgsql/bin/psql -h localhost -U test -d postgres
         Password for user test:
         WARNING:  your password will expire in 4 days
         psql (19devel)
         Type "help" for help.

         postgres=>

Output in the log:

         2026-01-05 23:23:13.763 CET [136001] WARNING:  your password 
will expire in 4 days

Using a script:

         $ perl test_conn.pl
         WARNING:  your password will expire in 3 days

The message can be handled by any client application to warn the user if 
required.

Thanks in advance for your feedback and suggestion for a better 
implementation.

Best regards,

-- 
Gilles Darold
http://hexacluster.ai/
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: Japin Li <[email-masked]>
Date: Wed, 07 Jan 2026 13:12:57 +0800

Hi, Gilles Darold

On Tue, 06 Jan 2026 at 15:43, Gilles Darold <[email-masked]> wrote:
> Sorry to resurrect this old thread, but I had completely forgotten
> about it. If there's still interest in this feature, then please find
> in attachment a patch to emit a warning to the client and into the
> logs when the password will expire within 7 days by default. A GUC,
> 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.
>
> The message can be handled by any client application to warn the user
> if required.
>
>

Thanks for updating the patch.

1.
$ git apply ~/password_expire_warning-v1.patch
/home/japin/password_expire_warning-v1.patch:71: indent with spaces.
        if (MyClientConnectionInfo.warning_message)
/home/japin/password_expire_warning-v1.patch:72: indent with spaces.
                ereport(WARNING, (errmsg("%s", MyClientConnectionInfo.warning_message)));
warning: 2 lines add whitespace errors.

2.

The GUC_UNIT_S flag specifies that the unit is seconds, meaning the default
value of 7 corresponds to 7 seconds rather than 7 days. For example:

	# ALTER SYSTEM SET password_expire_warning TO 60;
	ERROR:  60 s is outside the valid range for parameter "password_expire_warning" (0 s .. 30 s)

3.
I tested the patch and only received an empty WARNING message. After some
analysis, I found that the warning_message buffer is likely freed after
transaction commit.

Here's a new patch that resolves the issues mentioned earlier.

Furthermore, if the remaining time until expiration is less than one day,
should we display it in minutes (or hours) instead of 0 days?

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

>From a9f38f5c03cd3ccf492848a47b690b14e882dff6 Mon Sep 17 00:00:00 2001
From: Japin Li <[email-masked]>
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 <[email-masked]>
---
 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(+)

index 4c1052b3d42..315c559e4ac 100644
 
+/* 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;
 	}
 
+	/*

 
 	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();
 
+	/*
index 7c60b125564..1f9929e7eaf 100644
 
+  boot_val => '604800',
+  max => '2592000',

 /*
-- 
2.43.0