Hi, Gilles Darold
On Tue, 06 Jan 2026 at 15:43, Gilles Darold <[email protected]> wrote:
> 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 protected]> 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.
>
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.
+{ 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',
+},
+
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 protected]>
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 protected]>
---
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
