On Fri, 09 Jan 2026 at 14:10, Yuefei Shi <[email protected]> wrote: > 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 ...`
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 protected]> 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 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..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 protected]> 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
