Here is an updated patch with the units set to seconds. There are two main things on my mind:
* The placement of the WARNING. Right now, I have it placed at the end of InitPostgres(). There are various other ways to get a WARNING during this function, so I think it's technically okay, but perhaps it makes more sense to put it at the end of ClientAuthentication() or something. But the risk there is that something between the call to ClientAuthentication() and the end of InitPostgres() could ERROR/FATAL, in which case our new WARNING might be giving away more information than necessary. So, I guess I lean towards keeping it where it is now, but I would be interested to hear other opinions on the matter. * Whether we should emit the warnings for special client backends. Specifically, I think the current patch will send warnings to logical replication connections, but not physical replication connections. My current feeling is that we should send warnings to any backend that uses a password to authenticate, i.e., add a call to EmitConnectionWarnings() at the end of the "am_walsender && !am_db_walsender" block. Thoughts? Are there any other backend types I'm forgetting that would be relevant here? -- nathan
>From cdbf9236523e2f8c886d5c766ec7fb661246e070 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 2 Feb 2026 14:26:33 -0600 Subject: [PATCH v14 1/1] Add password expiration warnings. This commit adds a new parameter called password_expiration_warning_threshold that controls when the server begins emitting imminent-password-expiration warnings upon successful password authentication. By default, this parameter is set to 7 days, but this functionality can be disabled by setting it to 0. This patch also introduces a new "connection warning" infrastructure that can be reused elsewhere. For example, we may want to emit warnings about the use of MD5 passwords for a couple of releases before removing MD5 password support. Author: Gilles Darold <[email protected]> Reviewed-by: Japin Li <[email protected]> Reviewed-by: songjinzhou <[email protected]> Reviewed-by: liu xiaohui <[email protected]> Reviewed-by: Yuefei Shi <[email protected]> Reviewed-by: Steven Niu <[email protected]> Reviewed-by: Soumya S Murali <[email protected]> Reviewed-by: Euler Taveira <[email protected]> Reviewed-by: Zsolt Parragi <[email protected]> Discussion: https://postgr.es/m/129bcfbf-47a6-e58a-190a-62fc21a17d03%40migops.com --- doc/src/sgml/config.sgml | 22 ++++++ src/backend/libpq/crypt.c | 57 +++++++++++++-- src/backend/utils/init/postinit.c | 72 +++++++++++++++++++ src/backend/utils/misc/guc_parameters.dat | 10 +++ src/backend/utils/misc/postgresql.conf.sample | 3 +- src/include/libpq/crypt.h | 3 + src/include/libpq/libpq-be.h | 9 +++ src/test/authentication/t/001_password.pl | 34 +++++++++ 8 files changed, 204 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5560b95ee60..4219f14dd06 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1157,6 +1157,28 @@ include_dir 'conf.d' </listitem> </varlistentry> + <varlistentry id="guc-password-expiration-warning-threshold" xreflabel="password_expiration_warning_threshold"> + <term><varname>password_expiration_warning_threshold</varname> (<type>integer</type>) + <indexterm> + <primary><varname>password_expiration_warning_threshold</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + When this parameter is greater than zero, the server will emit a + <literal>WARNING</literal> upon successful password authentication if + less than this amount of time remains until the authenticated role's + password expires. Note that a role's password only expires if a date + was specified in a <literal>VALID UNTIL</literal> clause for + <command>CREATE ROLE</command> or <command>ALTER ROLE</command>. If + this value is specified without units, it is taken as seconds. The + default is 7 days. This parameter can only be set in the + <filename>postgresql.conf</filename> file or on the server command + line. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-md5-password-warnings" xreflabel="md5_password_warnings"> <term><varname>md5_password_warnings</varname> (<type>boolean</type>) <indexterm> diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c index 52722060451..fe9059d090c 100644 --- a/src/backend/libpq/crypt.c +++ b/src/backend/libpq/crypt.c @@ -19,11 +19,16 @@ #include "common/md5.h" #include "common/scram-common.h" #include "libpq/crypt.h" +#include "libpq/libpq-be.h" #include "libpq/scram.h" #include "utils/builtins.h" +#include "utils/memutils.h" #include "utils/syscache.h" #include "utils/timestamp.h" +/* Time before password expiration warnings. */ +int password_expiration_warning_threshold = 604800; + /* Enables deprecation warnings for MD5 passwords. */ bool md5_password_warnings = true; @@ -71,13 +76,55 @@ get_role_password(const char *role, const char **logdetail) ReleaseSysCache(roleTup); /* - * Password OK, but check to be sure we are not past rolvaliduntil + * Password OK, but check to be sure we are not past rolvaliduntil or + * password_expiration_warning_threshold. */ - if (!isnull && vuntil < GetCurrentTimestamp()) + if (!isnull) { - *logdetail = psprintf(_("User \"%s\" has an expired password."), - role); - return NULL; + TimestampTz expire_time = vuntil - GetCurrentTimestamp(); + + /* + * If we're past rolvaliduntil, the connection attempt should fail, so + * update logdetail and return NULL. + */ + if (expire_time < 0) + { + *logdetail = psprintf(_("User \"%s\" has an expired password."), + role); + return NULL; + } + + /* + * If we're past the warning threshold, the connection attempt should + * succeed, but we still want to emit a warning. To do so, we queue + * the warning message using StoreConnectionWarning() so that it will + * be emitted at the end of InitPostgres(), and we return normally. + */ + if (expire_time / USECS_PER_SEC < password_expiration_warning_threshold) + { + MemoryContext oldcontext; + TimestampTz days; + TimestampTz hours; + TimestampTz minutes; + char *warning; + char *detail; + + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + + days = expire_time / USECS_PER_DAY; + hours = (expire_time % USECS_PER_DAY) / USECS_PER_HOUR; + minutes = (expire_time % USECS_PER_HOUR) / USECS_PER_MINUTE; + + warning = pstrdup(_("role password will expire soon")); + detail = psprintf(_("The password for role \"%s\" will expire in " + INT64_FORMAT " day(s), " INT64_FORMAT + " hour(s), " INT64_FORMAT " minute(s)."), + role, days, hours, minutes); + + StoreConnectionWarning(warning, detail); + + MemoryContextSwitchTo(oldcontext); + } } return shadow_pass; diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 3f401faf3de..f9a963c0a47 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -70,6 +70,8 @@ #include "utils/syscache.h" #include "utils/timeout.h" +static bool ConnectionWarningsEmitted = false; + static HeapTuple GetDatabaseTuple(const char *dbname); static HeapTuple GetDatabaseTupleByOid(Oid dboid); static void PerformAuthentication(Port *port); @@ -85,6 +87,7 @@ static void ClientCheckTimeoutHandler(void); static bool ThereIsAtLeastOneRole(void); static void process_startup_options(Port *port, bool am_superuser); static void process_settings(Oid databaseid, Oid roleid); +static void EmitConnectionWarnings(void); /*** InitPostgres support ***/ @@ -1232,6 +1235,9 @@ InitPostgres(const char *in_dbname, Oid dboid, /* close the transaction we started above */ if (!bootstrap) CommitTransactionCommand(); + + /* send any WARNINGs we've accumulated during initialization */ + EmitConnectionWarnings(); } /* @@ -1446,3 +1452,69 @@ ThereIsAtLeastOneRole(void) return result; } + +/* + * Stores a warning message to be sent at the end of InitPostgres(). "detail" + * can be NULL, but "msg" cannot. + * + * NB: Caller should ensure the strings are allocated in a long-lived context + * like TopMemoryContext. + */ +void +StoreConnectionWarning(char *msg, char *detail) +{ + MemoryContext oldcontext; + + Assert(msg); + + if (ConnectionWarningsEmitted) + elog(ERROR, "StoreConnectionWarning() called after EmitConnectionWarnings()"); + + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + + MyProcPort->warning_msgs = lappend(MyProcPort->warning_msgs, msg); + MyProcPort->warning_details = lappend(MyProcPort->warning_details, detail); + + MemoryContextSwitchTo(oldcontext); +} + +/* + * Sends the warning messages saved for the end of InitPostgres() and frees the + * strings and lists. + * + * NB: This can only be called once per backend. + */ +static void +EmitConnectionWarnings(void) +{ + ListCell *lc_msg; + ListCell *lc_detail; + + if (ConnectionWarningsEmitted) + elog(ERROR, "EmitConnectionWarnings() called more than once"); + else + ConnectionWarningsEmitted = true; + + if (MyProcPort == NULL) + return; + + forboth(lc_msg, MyProcPort->warning_msgs, + lc_detail, MyProcPort->warning_details) + { + char *msg = (char *) lfirst(lc_msg); + char *detail = (char *) lfirst(lc_detail); + + ereport(WARNING, + (errmsg("%s", msg), + detail ? errdetail("%s", detail) : 0)); + + pfree(msg); + if (detail) + pfree(detail); + } + + list_free(MyProcPort->warning_msgs); + list_free(MyProcPort->warning_details); + MyProcPort->warning_msgs = NIL; + MyProcPort->warning_details = NIL; +} diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..d00cd2437d9 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2242,6 +2242,16 @@ options => 'password_encryption_options', }, +{ name => 'password_expiration_warning_threshold', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH', + short_desc => 'Time before password expiration warnings.', + long_desc => '0 means not to emit these warnings.', + flags => 'GUC_UNIT_S', + variable => 'password_expiration_warning_threshold', + boot_val => '604800', + min => '0', + max => 'INT_MAX', +}, + { 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 c4f92fcdac8..6575a37405c 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -96,7 +96,8 @@ #authentication_timeout = 1min # 1s-600s #password_encryption = scram-sha-256 # scram-sha-256 or (deprecated) md5 #scram_iterations = 4096 -#md5_password_warnings = on # display md5 deprecation warnings? +#password_expiration_warning_threshold = 7d # time before expiration warnings +#md5_password_warnings = on # display md5 deprecation warnings? #oauth_validator_libraries = '' # comma-separated list of trusted validator modules # GSSAPI using Kerberos diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h index f01886e1098..081817972d5 100644 --- a/src/include/libpq/crypt.h +++ b/src/include/libpq/crypt.h @@ -25,6 +25,9 @@ */ #define MAX_ENCRYPTED_PASSWORD_LEN (512) +/* Time before password expiration warnings. */ +extern PGDLLIMPORT int password_expiration_warning_threshold; + /* Enables deprecation warnings for MD5 passwords. */ extern PGDLLIMPORT bool md5_password_warnings; diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h index 921b2daa4ff..fa382261fb1 100644 --- a/src/include/libpq/libpq-be.h +++ b/src/include/libpq/libpq-be.h @@ -238,6 +238,13 @@ typedef struct Port char *raw_buf; ssize_t raw_buf_consumed, raw_buf_remaining; + + /* + * Content of warning messages to send to the client upon successful + * authentication. + */ + List *warning_msgs; + List *warning_details; } Port; /* @@ -367,4 +374,6 @@ extern int pq_setkeepalivesinterval(int interval, Port *port); extern int pq_setkeepalivescount(int count, Port *port); extern int pq_settcpusertimeout(int timeout, Port *port); +extern void StoreConnectionWarning(char *msg, char *detail); + #endif /* LIBPQ_BE_H */ diff --git a/src/test/authentication/t/001_password.pl b/src/test/authentication/t/001_password.pl index f4d65ba7bae..0ec9aa9f4e8 100644 --- a/src/test/authentication/t/001_password.pl +++ b/src/test/authentication/t/001_password.pl @@ -68,8 +68,24 @@ $node->init; $node->append_conf('postgresql.conf', "log_connections = on\n"); # Needed to allow connect_fails to inspect postmaster log: $node->append_conf('postgresql.conf', "log_min_messages = debug2"); +$node->append_conf('postgresql.conf', "password_expiration_warning_threshold = '1100d'"); $node->start; +# Set up roles for password_expiration_warning_threshold test +my $current_year = 1900 + ${ [ localtime(time) ] }[5]; +my $expire_year = $current_year - 1; +$node->safe_psql( + 'postgres', + "CREATE ROLE expired LOGIN VALID UNTIL '$expire_year-01-01' PASSWORD 'pass'"); +$expire_year = $current_year + 2; +$node->safe_psql( + 'postgres', + "CREATE ROLE expiration_warnings LOGIN VALID UNTIL '$expire_year-01-01' PASSWORD 'pass'"); +$expire_year = $current_year + 5; +$node->safe_psql( + 'postgres', + "CREATE ROLE no_warnings LOGIN VALID UNTIL '$expire_year-01-01' PASSWORD 'pass'"); + # Test behavior of log_connections GUC # # There wasn't another test file where these tests obviously fit, and we don't @@ -531,6 +547,24 @@ $node->connect_fails( qr/authentication method requirement "!password,!md5,!scram-sha-256" failed: server requested SCRAM-SHA-256 authentication/ ); +# Test password_expiration_warning_threshold +$node->connect_fails( + "user=expired dbname=postgres", + "connection fails due to expired password", + expected_stderr => + qr/password authentication failed for user "expired"/ +); +$node->connect_ok( + "user=expiration_warnings dbname=postgres", + "connection succeeds with password expiration warning", + expected_stderr => + qr/role password will expire soon/ +); +$node->connect_ok( + "user=no_warnings dbname=postgres", + "connection succeeds with no password expiration warning" +); + # Test SYSTEM_USER <> NULL with parallel workers. $node->safe_psql( 'postgres', -- 2.50.1 (Apple Git-155)
