public inbox for [email protected]
help / color / mirror / Atom feedFrom: Gilles Darold <[email protected]>
To: Japin Li <[email protected]>
To: songjinzhou <[email protected]>
Cc: Gilles Darold <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Bossart, Nathan <[email protected]>
Cc: Tom Lane <[email protected]>
Subject: Re: Pasword expiration warning
Date: Thu, 8 Jan 2026 07:04:16 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <MEAPR01MB3031D75330A64DD25A5B8A04B685A@MEAPR01MB3031.ausprd01.prod.outlook.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<MEAPR01MB3031B4F594E62343BF1F8EDBB684A@MEAPR01MB3031.ausprd01.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
<MEAPR01MB3031D75330A64DD25A5B8A04B685A@MEAPR01MB3031.ausprd01.prod.outlook.com>
Le 08/01/2026 à 04:37, Japin Li a écrit :
> On Thu, 08 Jan 2026 at 10:57, "songjinzhou" <[email protected]> wrote:
>> Hi, Gilles Darold
>>
>> First of all, thank you for your reply. This is indeed not a simple
>> countdown. I did think it would be abrupt for users to see "0 days". I
>> checked v4, and I think it's fine. (PS: Should we add relevant
>> explanations to the SGML?) Thank you.
>>
> I'd like to hear more opinions on this.
>
Here is a new version of the patch that adds the documentation for the
new GUC, fix the warning message (days(s) instead of days) and handle
the 'Infinity' value for the VALID UNTIL clause.
--
Gilles Darold
http://hexacluster.ai/
Attachments:
[text/x-patch] v5-0001-Add-password_expire_warning-GUC-to-warn-clients.patch (7.6K, ../[email protected]/2-v5-0001-Add-password_expire_warning-GUC-to-warn-clients.patch)
download | inline diff:
From 3361b5260ab4d432223012d14991742bcc653d70 Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email protected]>
Date: Thu, 8 Jan 2026 06:55:44 +0100
Subject: [PATCH v5] Add password_expire_warning GUC to warn clients
Introduce a new server configuration parameter, password_expire_warning,
which controls how many days before a role's password expiration a
warning message is sent to the client upon successful connection.
Author: Gilles Darold <[email protected]>
---
doc/src/sgml/config.sgml | 19 ++++++++++++++++++-
src/backend/libpq/crypt.c | 19 +++++++++++++++++++
src/backend/utils/init/miscinit.c | 1 +
src/backend/utils/init/postinit.c | 7 +++++++
src/backend/utils/misc/guc_parameters.dat | 9 +++++++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/libpq/crypt.h | 3 +++
src/include/libpq/libpq-be.h | 9 +++++++++
8 files changed, 67 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..3384347a822 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1106,8 +1106,25 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-password-expire-warnings" xreflabel="password_expire_warning">
+ <term><varname>password_expire_warning</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>password_expire_warning</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls how many days before a role's password expiration a <literal>WARNING</literal>
+ message is sent to the client upon successful connection. It requires that
+ a <command>VALID UNTIL</command> date is set for the user. A value of <literal>0d</literal>
+ disable this behavior. The default value is <literal>7d</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-password-encryption" xreflabel="password_encryption">
- <term><varname>password_encryption</varname> (<type>enum</type>)
+ <term><varname>password_encryption</varname> (<type>enum</type>
+)
<indexterm>
<primary><varname>password_encryption</varname> configuration parameter</primary>
</indexterm>
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..47a9d425a71 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -20,6 +20,7 @@
#include "common/scram-common.h"
#include "libpq/crypt.h"
#include "libpq/scram.h"
+#include "postmaster/postmaster.h"
#include "utils/builtins.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -27,6 +28,9 @@
/* Enables deprecation warnings for MD5 passwords. */
bool md5_password_warnings = true;
+/* Emit a warning 7 days before password expiration */
+int password_expire_warning = 604800;
+
/*
* Fetch stored password for a user, for authentication.
*
@@ -80,6 +84,21 @@ get_role_password(const char *role, const char **logdetail)
return NULL;
}
+ /*
+ * Password OK, but check if rolvaliduntil is less than GUC
+ * password_expire_warning days to send a warning to the client
+ */
+ if (!isnull && password_expire_warning > 0 && vuntil < PG_INT64_MAX)
+ {
+ float8 result;
+
+ result = ((float8) (vuntil - GetCurrentTimestamp())) / 1000000.0; /* in seconds */
+
+ if ((int) result <= password_expire_warning)
+ MyClientConnectionInfo.warning_message =
+ psprintf("your password will expire in %d day(s)", (int) (result / 86400));
+ }
+
return shadow_pass;
}
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..24737c95c28 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -1089,6 +1089,7 @@ RestoreClientConnectionInfo(char *conninfo)
/* Copy the fields back into place */
MyClientConnectionInfo.authn_id = NULL;
+ MyClientConnectionInfo.warning_message = NULL;
MyClientConnectionInfo.auth_method = serialized.auth_method;
if (serialized.authn_id_len >= 0)
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 52c05a9d1d5..d0d5c87d4ea 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1229,6 +1229,13 @@ InitPostgres(const char *in_dbname, Oid dboid,
if (!bootstrap)
pgstat_bestart_final();
+ /*
+ * Emit a warning message to the client when set, for example
+ * to warn the user that the password will expire.
+ */
+ if (MyClientConnectionInfo.warning_message)
+ ereport(WARNING, (errmsg("%s", MyClientConnectionInfo.warning_message)));
+
/* close the transaction we started above */
if (!bootstrap)
CommitTransactionCommand();
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 7c60b125564..1f9929e7eaf 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2248,6 +2248,15 @@
options => 'password_encryption_options',
},
+{ name => 'password_expire_warning', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
+ short_desc => 'Sets the number of days before password expire to emit a warning at client connection. Default is 7 days, 0 means no warning.',
+ flags => 'GUC_UNIT_S',
+ variable => 'password_expire_warning',
+ boot_val => '604800',
+ min => '0',
+ max => '2592000',
+},
+
{ name => 'plan_cache_mode', type => 'enum', context => 'PGC_USERSET', group => 'QUERY_TUNING_OTHER',
short_desc => 'Controls the planner\'s selection of custom or generic plan.',
long_desc => 'Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better. This can be set to override the default behavior.',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index dc9e2255f8a..ca59b7cc1f6 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -98,6 +98,7 @@
#scram_iterations = 4096
#md5_password_warnings = on # display md5 deprecation warnings?
#oauth_validator_libraries = '' # comma-separated list of trusted validator modules
+#password_expire_warning = '7d' # 0-30d time before password expiration to emit a warning
# GSSAPI using Kerberos
#krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..e92f4f151c9 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -28,6 +28,9 @@
/* Enables deprecation warnings for MD5 passwords. */
extern PGDLLIMPORT bool md5_password_warnings;
+/* number of days before emitting a warning for password expiration */
+extern PGDLLIMPORT int password_expire_warning;
+
/*
* Types of password hashes or secrets.
*
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 921b2daa4ff..4ed61921ccd 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -103,6 +103,15 @@ typedef struct ClientConnectionInfo
* meaning if authn_id is not NULL; otherwise it's undefined.
*/
UserAuth auth_method;
+
+ /*
+ * Message to send to the client in case of connection success.
+ * When not NULL a WARNING message is sent to the client at end
+ * of the connection in src/backend/utils/init/postinit.c at
+ * enf of InitPostgres(). For example, it is use to show the
+ * password expiration warning.
+ */
+ const char *warning_message;
} ClientConnectionInfo;
/*
--
2.43.0
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Pasword expiration warning
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox