Le 09/01/2026 à 10:04, Japin Li a écrit :
Hi, Steven
Thanks for the review.
On Fri, 09 Jan 2026 at 07:36, Steven Niu <[email protected]> wrote:
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.
Add in v10.
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");
Yes, it allows local connections, but they are always in trust mode, so no
password is required (or used).
3. Make the expected string to be more exact.
qr/your password will expire in/);
-->
qr/your password will expire in 1d/);
Fixed. PFA.
v10-0001 - No changes.
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 protected]>
Date: Fri, 9 Jan 2026 12:13:59 +0100
Subject: [PATCH v11 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.
Authors: Gilles Darold <[email protected]>, Japin Li <[email protected]>
---
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..43b3af0cb5c 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 expires 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 6774c4bdf83499ddcaabcb784bc29c2f7c4a1e9e Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email protected]>
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
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..02e4bc9e7b9
--- /dev/null
+++ b/src/test/authentication/t/008_password_expire.pl
@@ -0,0 +1,56 @@
+# 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'");
+
+$node->safe_psql('postgres',
+ "CREATE USER test_user3 WITH VALID UNTIL 'Infinity' 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->connect_ok('user=test_user3 dbname=postgres',
+ qq(no password expiration warning is emitted for infinity));
+
+$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
