public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: Gilles Darold <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: Japin Li <[email protected]>
Cc: Yuefei Shi <[email protected]>
Cc: songjinzhou <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: liu xiaohui <[email protected]>
Cc: Steven Niu <[email protected]>
Subject: Re: Pasword expiration warning
Date: Mon, 2 Feb 2026 11:04:03 -0600
Message-ID: <aYDZA3r3lG2oKc5D@nathan> (raw)
In-Reply-To: <[email protected]>
References: <CAD43U4WHJm=UkxbfhxQRxQRxtdtu7AGj77uO2fF78THQWCaYTg@mail.gmail.com>
<MEAPR01MB30319B858B3028CA38BEDC5EB682A@MEAPR01MB3031.ausprd01.prod.outlook.com>
<MN2PR15MB30212CEFE2F8B93C398FBF60A782A@MN2PR15MB3021.namprd15.prod.outlook.com>
<MEAPR01MB30319C7BB75B88028E1DB7E1B682A@MEAPR01MB3031.ausprd01.prod.outlook.com>
<[email protected]>
<CAN4CZFMBj-mZq_o8BtQz_TgqDTALbn31kc-SFtWE4FR1N-4RBw@mail.gmail.com>
<MEAPR01MB3031CF12043573A245D931AAB69EA@MEAPR01MB3031.ausprd01.prod.outlook.com>
<CAN4CZFP4AGeO-AynwE3e62XOKqaYnXtcGT1qfj=6nw2t5FOXwA@mail.gmail.com>
<aXuehKtwtUXWVFYp@nathan>
<[email protected]>
On Fri, Jan 30, 2026 at 12:33:54PM +0100, Gilles Darold wrote:
> Here a new v12 version of the patch. Changes are the following:
Thanks. I spent some time preparing this for commit, and I came up with
the attached. Notable changes include:
* Renamed the parameter to password_expiration_warning_threshold. It's a
mouthful, but I thought it was more descriptive.
* Changed the units for the parameter to minutes. I can't imagine anyone
needs more granularity than hours or days, let alone seconds, so IMO
minutes is a good middle ground.
* I added a new "connection warnings" infrastructure that we can reuse
if/when we want to emit warnings for MD5 passwords.
* Moved the warning messages to Port. ClientConnectionInfo appears to be
meant only for parallel workers, and I don't think we will ever want to
emit connection warnings there.
* Moved the tests into 001_password.pl. I'm a bit concerned about these
tests being flaky, but I've tried setting the VALID UNTIL dates to make
spurious failures virtually impossible.
WDYT?
--
nathan
From cb31a261878a7b7e839c9503418831cf23f3be08 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 2 Feb 2026 10:07:05 -0600
Subject: [PATCH v13 1/1] Add password expiration warnings.
This commit adds a new parameter called
parameter_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..5b9ab15bc92 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 minutes. The
+ default is 7 days. This parameter can only 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..3d86eb9a245 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 = 10080;
+
/* 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_MINUTE < 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..6add1f37b4e 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_MIN',
+ variable => 'password_expiration_warning_threshold',
+ boot_val => '10080',
+ 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)
Attachments:
[text/plain] v13-0001-Add-password-expiration-warnings.patch (13.6K, ../aYDZA3r3lG2oKc5D@nathan/2-v13-0001-Add-password-expiration-warnings.patch)
download | inline diff:
From cb31a261878a7b7e839c9503418831cf23f3be08 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 2 Feb 2026 10:07:05 -0600
Subject: [PATCH v13 1/1] Add password expiration warnings.
This commit adds a new parameter called
parameter_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..5b9ab15bc92 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 minutes. The
+ default is 7 days. This parameter can only 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..3d86eb9a245 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 = 10080;
+
/* 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_MINUTE < 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..6add1f37b4e 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_MIN',
+ variable => 'password_expiration_warning_threshold',
+ boot_val => '10080',
+ 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)
view thread (27+ 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], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Pasword expiration warning
In-Reply-To: <aYDZA3r3lG2oKc5D@nathan>
* 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