From 908591a520c72288f8abb594c62da2cc95a7443f Mon Sep 17 00:00:00 2001 From: songjinzhou Date: Wed, 7 Jan 2026 00:14:43 -0800 Subject: [PATCH] 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 --- src/backend/libpq/crypt.c | 60 +++++++++++++++++++++++ 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, 89 insertions(+) diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c index 4c1052b3d4..1022f6cc8c 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,62 @@ 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; + int64 remaining_seconds; + + remaining_seconds = (vuntil - GetCurrentTimestamp()) / 1000000; /* in seconds */ + result = (float8)remaining_seconds / 86400; /* in days */ + + if ((int) result <= password_expire_warning) + { + if (remaining_seconds >= 86400) /* 1 day or more */ + { + int days = (int)result; + int hours = (int)((remaining_seconds % 86400) / 3600); + if (hours > 0) + MyClientConnectionInfo.warning_message = + psprintf("your password will expire in %d day%s and %d hour%s", + days, (days != 1) ? "s" : "", + hours, (hours != 1) ? "s" : ""); + else + MyClientConnectionInfo.warning_message = + psprintf("your password will expire in %d day%s", + days, (days != 1) ? "s" : ""); + } + else if (remaining_seconds >= 3600) /* 1 hour or more but less than 1 day */ + { + int hours = (int)(remaining_seconds / 3600); + int minutes = (int)((remaining_seconds % 3600) / 60); + if (minutes > 0) + MyClientConnectionInfo.warning_message = + psprintf("your password will expire in %d hour%s and %d minute%s", + hours, (hours != 1) ? "s" : "", + minutes, (minutes != 1) ? "s" : ""); + else + MyClientConnectionInfo.warning_message = + psprintf("your password will expire in %d hour%s", + hours, (hours != 1) ? "s" : ""); + } + else /* less than 1 hour */ + { + int minutes = (int)(remaining_seconds / 60); + int seconds = (int)(remaining_seconds % 60); + if (minutes == 0 && seconds > 0) + minutes = 1; /* show at least 1 minute if there's any time left */ + + MyClientConnectionInfo.warning_message = + psprintf("your password will expire in %d minute%s", + minutes, (minutes != 1) ? "s" : ""); + } + } + } + return shadow_pass; } diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c index 563f20374f..24737c95c2 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 52c05a9d1d..d0d5c87d4e 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 7c60b12556..1f9929e7ea 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 f01886e109..e92f4f151c 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 921b2daa4f..4ed61921cc 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