public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 04/11] Introduce RemoveWaitEvent().
27+ messages / 11 participants
[nested] [flat]
* [PATCH 04/11] Introduce RemoveWaitEvent().
@ 2020-02-24 06:05 Thomas Munro <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Thomas Munro @ 2020-02-24 06:05 UTC (permalink / raw)
This will allow WaitEventSet objects to be used in more
long lived scenarios, where sockets are added and removed.
Author: Thomas Munro
Discussion: https://postgr.es/m/CA%2BhUKGJAC4Oqao%3DqforhNey20J8CiG2R%3DoBPqvfR0vOJrFysGw%40mail.gmail.com
---
src/backend/storage/ipc/latch.c | 131 ++++++++++++++++++++++++++++----
src/include/storage/latch.h | 3 +
2 files changed, 120 insertions(+), 14 deletions(-)
diff --git a/src/backend/storage/ipc/latch.c b/src/backend/storage/ipc/latch.c
index 30e461e965..025545fc89 100644
--- a/src/backend/storage/ipc/latch.c
+++ b/src/backend/storage/ipc/latch.c
@@ -84,6 +84,7 @@ struct WaitEventSet
{
int nevents; /* number of registered events */
int nevents_space; /* maximum number of events in this set */
+ int free_list; /* position of first free event */
/*
* Array, of nevents_space length, storing the definition of events this
@@ -119,6 +120,8 @@ struct WaitEventSet
#elif defined(WAIT_USE_POLL)
/* poll expects events to be waited on every poll() call, prepare once */
struct pollfd *pollfds;
+ /* track the populated range of pollfds */
+ int npollfds;
#elif defined(WAIT_USE_WIN32)
/*
@@ -127,6 +130,8 @@ struct WaitEventSet
* event->pos + 1).
*/
HANDLE *handles;
+ /* track the populated range of handles */
+ int nhandles;
#endif
};
@@ -642,13 +647,16 @@ CreateWaitEventSet(MemoryContext context, int nevents)
#elif defined(WAIT_USE_POLL)
set->pollfds = (struct pollfd *) data;
data += MAXALIGN(sizeof(struct pollfd) * nevents);
+ set->npollfds = 0;
#elif defined(WAIT_USE_WIN32)
set->handles = (HANDLE) data;
data += MAXALIGN(sizeof(HANDLE) * nevents);
+ set->nhandles = 0;
#endif
set->latch = NULL;
set->nevents_space = nevents;
+ set->nevents = 0;
set->exit_on_postmaster_death = false;
#if defined(WAIT_USE_EPOLL)
@@ -714,11 +722,25 @@ CreateWaitEventSet(MemoryContext context, int nevents)
* Note: pgwin32_signal_event should be first to ensure that it will be
* reported when multiple events are set. We want to guarantee that
* pending signals are serviced.
+ *
+ * We set unused handles to INVALID_HANDLE_VALUE, because
+ * WaitForMultipleObjects() considers that to mean "this process" which is
+ * not signaled until process, so it's a way of leaving a hole in the
+ * middle of the wait set if you remove something (just like -1 in the poll
+ * implementation). An alternative would be to fill in holes and create a
+ * non 1-to-1 mapping between 'events' and 'handles'.
*/
set->handles[0] = pgwin32_signal_event;
- StaticAssertStmt(WSA_INVALID_EVENT == NULL, "");
+ for (int i = 0; i < nevents; ++i)
+ set->handles[i + 1] = INVALID_HANDLE_VALUE;
#endif
+ /* Set up the free list. */
+ for (int i = 0; i < nevents; ++i)
+ set->events[i].next_free = i + 1;
+ set->events[nevents - 1].next_free = -1;
+ set->free_list = 0;
+
return set;
}
@@ -727,7 +749,6 @@ CreateWaitEventSet(MemoryContext context, int nevents)
*
* Note: preferably, this shouldn't have to free any resources that could be
* inherited across an exec(). If it did, we'd likely leak those resources in
- * many scenarios. For the epoll case, we ensure that by setting FD_CLOEXEC
* when the FD is created. For the Windows case, we assume that the handles
* involved are non-inheritable.
*/
@@ -748,9 +769,12 @@ FreeWaitEventSet(WaitEventSet *set)
WaitEvent *cur_event;
for (cur_event = set->events;
- cur_event < (set->events + set->nevents);
+ cur_event < (set->events + set->nhandles);
cur_event++)
{
+ if (set->handles[cur_event->pos + 1] == INVALID_HANDLE_VALUE)
+ continue;
+
if (cur_event->events & WL_LATCH_SET)
{
/* uses the latch's HANDLE */
@@ -805,9 +829,6 @@ AddWaitEventToSet(WaitEventSet *set, uint32 events, pgsocket fd, Latch *latch,
{
WaitEvent *event;
- /* not enough space */
- Assert(set->nevents < set->nevents_space);
-
if (events == WL_EXIT_ON_PM_DEATH)
{
events = WL_POSTMASTER_DEATH;
@@ -833,8 +854,12 @@ AddWaitEventToSet(WaitEventSet *set, uint32 events, pgsocket fd, Latch *latch,
if (fd == PGINVALID_SOCKET && (events & WL_SOCKET_MASK))
elog(ERROR, "cannot wait on socket event without a socket");
- event = &set->events[set->nevents];
- event->pos = set->nevents++;
+ /* Do we have any free slots? */
+ if (set->free_list == -1)
+ elog(ERROR, "WaitEventSet is full");
+
+ event = &set->events[set->free_list];
+ event->pos = set->free_list;
event->fd = fd;
event->events = events;
event->user_data = user_data;
@@ -868,6 +893,11 @@ AddWaitEventToSet(WaitEventSet *set, uint32 events, pgsocket fd, Latch *latch,
WaitEventAdjustWin32(set, event);
#endif
+ /* Remove it from the free list. */
+ set->free_list = event->next_free;
+ event->next_free = -1;
+ set->nevents++;
+
return event->pos;
}
@@ -885,7 +915,7 @@ ModifyWaitEvent(WaitEventSet *set, int pos, uint32 events, Latch *latch)
int old_events;
#endif
- Assert(pos < set->nevents);
+ Assert(pos < set->nevents_space);
event = &set->events[pos];
#if defined(WAIT_USE_KQUEUE)
@@ -933,6 +963,63 @@ ModifyWaitEvent(WaitEventSet *set, int pos, uint32 events, Latch *latch)
#endif
}
+/*
+ * If the descriptor has already been closed, the kernel should already have
+ * removed it from the wait set (except in WAIT_USE_POLL). Pass in true for
+ * fd_closed in that case, so we don't try to remove it ourselves.
+ */
+void
+RemoveWaitEvent(WaitEventSet *set, int pos, bool fd_closed)
+{
+ WaitEvent *event;
+
+ Assert(pos >= 0);
+ Assert(pos < set->nevents_space);
+ event = &set->events[pos];
+
+ /* For now only sockets can be removed */
+ if ((event->events & WL_SOCKET_MASK) == 0)
+ elog(ERROR, "event type cannot be removed");
+
+#if defined(WAIT_USE_EPOLL)
+ if (!fd_closed)
+ WaitEventAdjustEpoll(set, event, EPOLL_CTL_DEL);
+#elif defined(WAIT_USE_KQUEUE)
+ if (!fd_closed)
+ {
+ int old_events = event->events;
+
+ event->events = 0;
+ WaitEventAdjustKqueue(set, event, old_events);
+ }
+#elif defined(WAIT_USE_POLL)
+ /* no kernel state to remove, just blank out the fd */
+ set->pollfds[event->pos].fd = -1;
+ /* see if we can shrink the range of active fds */
+ while (set->npollfds > 0 &&
+ set->pollfds[set->npollfds - 1].fd == -1)
+ set->npollfds -= 1;
+#elif defined(WAIT_USE_WIN32)
+ if (!fd_closed)
+ WSAEventSelect(event->fd, NULL, 0);
+ if (set->handles[event->pos + 1] != INVALID_HANDLE_VALUE)
+ {
+ WSACloseEvent(set->handles[event->pos + 1]);
+ set->handles[event->pos + 1] = INVALID_HANDLE_VALUE;
+ }
+ /* see if we can shrink the range of active handles */
+ while (set->nhandles > 0 &&
+ set->handles[set->nhandles] == INVALID_HANDLE_VALUE)
+ set->nhandles -= 1;
+#endif
+
+ /* This position is now free. */
+ memset(event, 0, sizeof(*event));
+ event->next_free = set->free_list;
+ set->free_list = pos;
+ set->nevents--;
+}
+
#if defined(WAIT_USE_EPOLL)
/*
* action can be one of EPOLL_CTL_ADD | EPOLL_CTL_MOD | EPOLL_CTL_DEL
@@ -994,6 +1081,9 @@ WaitEventAdjustPoll(WaitEventSet *set, WaitEvent *event)
pollfd->revents = 0;
pollfd->fd = event->fd;
+ /* track the known range of populated slots */
+ set->npollfds = Max(event->pos + 1, set->nevents);
+
/* prepare pollfd entry once */
if (event->events == WL_LATCH_SET)
{
@@ -1072,7 +1162,9 @@ WaitEventAdjustKqueue(WaitEventSet *set, WaitEvent *event, int old_events)
Assert(event->events != WL_LATCH_SET || set->latch != NULL);
Assert(event->events == WL_LATCH_SET ||
event->events == WL_POSTMASTER_DEATH ||
- (event->events & (WL_SOCKET_READABLE | WL_SOCKET_WRITEABLE)));
+ (event->events & (WL_SOCKET_READABLE | WL_SOCKET_WRITEABLE)) ||
+ (event->events == 0 &&
+ (old_events & (WL_SOCKET_READABLE | WL_SOCKET_WRITEABLE))));
if (event->events == WL_POSTMASTER_DEATH)
{
@@ -1149,6 +1241,9 @@ WaitEventAdjustWin32(WaitEventSet *set, WaitEvent *event)
{
HANDLE *handle = &set->handles[event->pos + 1];
+ /* track the known range of populated slots */
+ set->nhandles = Max(event->pos + 1, set->nhandles);
+
if (event->events == WL_LATCH_SET)
{
Assert(set->latch != NULL);
@@ -1169,12 +1264,15 @@ WaitEventAdjustWin32(WaitEventSet *set, WaitEvent *event)
if (event->events & WL_SOCKET_CONNECTED)
flags |= FD_CONNECT;
- if (*handle == WSA_INVALID_EVENT)
+ if (*handle == INVALID_HANDLE_VALUE)
{
*handle = WSACreateEvent();
if (*handle == WSA_INVALID_EVENT)
+ {
+ *handle = INVALID_HANDLE_VALUE;
elog(ERROR, "failed to create event for socket: error code %u",
WSAGetLastError());
+ }
}
if (WSAEventSelect(event->fd, *handle, flags) != 0)
elog(ERROR, "failed to set up event for socket: error code %u",
@@ -1304,6 +1402,11 @@ WaitEventSetWait(WaitEventSet *set, long timeout,
return returned_events;
}
+int
+WaitEventSetSize(WaitEventSet *set)
+{
+ return set->nevents;
+}
#if defined(WAIT_USE_EPOLL)
@@ -1589,7 +1692,7 @@ WaitEventSetWaitBlock(WaitEventSet *set, int cur_timeout,
struct pollfd *cur_pollfd;
/* Sleep */
- rc = poll(set->pollfds, set->nevents, (int) cur_timeout);
+ rc = poll(set->pollfds, set->npollfds, (int) cur_timeout);
/* Check return code */
if (rc < 0)
@@ -1761,9 +1864,9 @@ WaitEventSetWaitBlock(WaitEventSet *set, int cur_timeout,
/*
* Sleep.
*
- * Need to wait for ->nevents + 1, because signal handle is in [0].
+ * Need to wait for ->nhandles + 1, because signal handle is in [0].
*/
- rc = WaitForMultipleObjects(set->nevents + 1, set->handles, FALSE,
+ rc = WaitForMultipleObjects(set->nhandles + 1, set->handles, FALSE,
cur_timeout);
/* Check return code */
diff --git a/src/include/storage/latch.h b/src/include/storage/latch.h
index ec1865a8fd..210f37659e 100644
--- a/src/include/storage/latch.h
+++ b/src/include/storage/latch.h
@@ -144,6 +144,7 @@ typedef struct WaitEvent
uint32 events; /* triggered events */
pgsocket fd; /* socket fd associated with event */
void *user_data; /* pointer provided in AddWaitEventToSet */
+ int next_free; /* free list for internal use */
#ifdef WIN32
bool reset; /* Is reset of the event required? */
#endif
@@ -168,6 +169,8 @@ extern void FreeWaitEventSet(WaitEventSet *set);
extern int AddWaitEventToSet(WaitEventSet *set, uint32 events, pgsocket fd,
Latch *latch, void *user_data);
extern void ModifyWaitEvent(WaitEventSet *set, int pos, uint32 events, Latch *latch);
+extern void RemoveWaitEvent(WaitEventSet *set, int pos, bool fd_closed);
+extern int WaitEventSetSize(WaitEventSet *set);
extern int WaitEventSetWait(WaitEventSet *set, long timeout,
WaitEvent *occurred_events, int nevents,
--
2.18.2
----Next_Part(Fri_Mar_13_16_21_13_2020_620)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="0005-Fix-interface-of-PQregisterEventProc.patch"
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
@ 2026-01-09 06:10 Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Yuefei Shi @ 2026-01-09 06:10 UTC (permalink / raw)
To: Japin Li <[email protected]>; +Cc: Gilles Darold <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>
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 ...`
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
@ 2026-01-09 07:12 ` Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Japin Li @ 2026-01-09 07:12 UTC (permalink / raw)
To: Yuefei Shi <[email protected]>; +Cc: Gilles Darold <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>
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.
Attachments:
[text/x-diff] v9-0001-Add-password_expire_warning-GUC-to-warn-clients.patch (7.8K, ../../MEAPR01MB30319B858B3028CA38BEDC5EB682A@MEAPR01MB3031.ausprd01.prod.outlook.com/2-v9-0001-Add-password_expire_warning-GUC-to-warn-clients.patch)
download | inline diff:
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
[text/x-diff] v9-0002-Add-TAP-test-for-password_expire_warning.patch (1.6K, ../../MEAPR01MB30319B858B3028CA38BEDC5EB682A@MEAPR01MB3031.ausprd01.prod.outlook.com/3-v9-0002-Add-TAP-test-for-password_expire_warning.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
@ 2026-01-09 07:36 ` Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Steven Niu @ 2026-01-09 07:36 UTC (permalink / raw)
To: Japin Li <[email protected]>; Yuefei Shi <[email protected]>; +Cc: Gilles Darold <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>
From: Japin Li <[email protected]>
Sent: Friday, January 09, 2026 15:12
To: Yuefei Shi <[email protected]>
Cc: Gilles Darold <[email protected]>; songjinzhou <[email protected]>; PostgreSQL Hackers <[email protected]>; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>
Subject: Re: Pasword expiration warning
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.
________________________________________
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.
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");
3. Make the expected string to be more exact.
qr/your password will expire in/);
-->
qr/your password will expire in 1d/);
Thanks,
Steven
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
@ 2026-01-09 09:04 ` Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Japin Li @ 2026-01-09 09:04 UTC (permalink / raw)
To: Steven Niu <[email protected]>; +Cc: Yuefei Shi <[email protected]>; Gilles Darold <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>
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.
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
Attachments:
[text/x-diff] v10-0001-Add-password_expire_warning-GUC-to-warn-clients.patch (7.8K, ../../MEAPR01MB30319C7BB75B88028E1DB7E1B682A@MEAPR01MB3031.ausprd01.prod.outlook.com/2-v10-0001-Add-password_expire_warning-GUC-to-warn-clients.patch)
download | inline diff:
From e5f0562176b41d77c6b8f2166551d55bac72e8e9 Mon Sep 17 00:00:00 2001
From: Japin Li <[email protected]>
Date: Fri, 9 Jan 2026 15:03:52 +0800
Subject: [PATCH v10 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
[text/x-diff] v10-0002-Add-TAP-test-for-password_expire_warning.patch (2.2K, ../../MEAPR01MB30319C7BB75B88028E1DB7E1B682A@MEAPR01MB3031.ausprd01.prod.outlook.com/3-v10-0002-Add-TAP-test-for-password_expire_warning.patch)
download | inline diff:
From bbe19d8c6a879c54d00daf6f96d5d6be298dad3c Mon Sep 17 00:00:00 2001
From: Japin Li <[email protected]>
Date: Fri, 9 Jan 2026 15:04:45 +0800
Subject: [PATCH v10 2/2] Add TAP test for password_expire_warning
---
.../authentication/t/008_password_expire.pl | 50 +++++++++++++++++++
1 file changed, 50 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..fdeae1b84d1
--- /dev/null
+++ b/src/test/authentication/t/008_password_expire.pl
@@ -0,0 +1,50 @@
+# 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'");
+
+# 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->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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
@ 2026-01-09 11:27 ` Gilles Darold <[email protected]>
2026-01-09 11:56 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-28 05:23 ` Re: Pasword expiration warning Soumya S Murali <[email protected]>
2026-01-28 12:44 ` Re: Pasword expiration warning Euler Taveira <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
0 siblings, 4 replies; 27+ messages in thread
From: Gilles Darold @ 2026-01-09 11:27 UTC (permalink / raw)
To: Japin Li <[email protected]>; +Cc: Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
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/
Attachments:
[text/x-patch] v11-0001-Add-password_expire_warning-GUC-to-warn-clie.patch (7.8K, ../../[email protected]/2-v11-0001-Add-password_expire_warning-GUC-to-warn-clie.patch)
download | inline diff:
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
[text/x-patch] v11-0002-Add-TAP-test-for-password_expire_warnin.patch (2.4K, ../../[email protected]/3-v11-0002-Add-TAP-test-for-password_expire_warnin.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-01-09 11:56 ` Japin Li <[email protected]>
3 siblings, 0 replies; 27+ messages in thread
From: Japin Li @ 2026-01-09 11:56 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
> 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.
>
>
Thanks for updating the patches. LGTM.
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-01-28 05:23 ` Soumya S Murali <[email protected]>
3 siblings, 0 replies; 27+ messages in thread
From: Soumya S Murali @ 2026-01-28 05:23 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Hi all,
Thank you for the updated patches.
On Tue, Jan 27, 2026 at 12:21 PM Gilles Darold <[email protected]> wrote:
>
> 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.
I went through the discussions and I applied the posted patches on the
current master branch and have completed testing.
Firstly, the conceptual approach of adding a server-side
password_expire_warning GUC in patch 0001 looks reasonable for me too
as it allows all clients to benefit from the warning.
Here, the password expiry enforcement is strictly tied to
password-based authentication. With md5 authentication explicitly
configured, expiry enforcement works as expected, login succeeds while
the password is valid and fails with “password has expired” once the
expiry timestamp is reached. This is confirmed via server logs showing
the md5 authentication path being exercised. When non-password
authentication methods (trust/peer) are used, password expiry is
bypassed. While expiry enforcement functions correctly after expiry,
no advance warning is emitted prior to expiry in the baseline
behavior, which matches the motivation for this change. The approach
in patch 0001 of adding a server-side password_expire_warning GUC and
adding the corresponding TAP coverage in patch 0002 seems
directionally correct.
Regards,
Soumya
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-01-28 12:44 ` Euler Taveira <[email protected]>
2026-01-29 15:39 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
3 siblings, 1 reply; 27+ messages in thread
From: Euler Taveira @ 2026-01-28 12:44 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; japin <[email protected]>; +Cc: Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On Fri, Jan 9, 2026, at 8:27 AM, Gilles Darold wrote:
>
> Here is a v11 version of the patch.
>
+ if (result <= (TimestampTz) password_expire_warning)
+ {
+ MyClientConnectionInfo.warning_message =
+ psprintf(_("your password will expire in %d day(s)"),
+ (int) (result / SECS_PER_DAY));
+ }
You should use ngettext() for plural forms. I don't think you need a string
into ClientConnectionInfo. Instead, you could store only the number of days.
+ /*
+ * 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)));
+
... and you construct the message directly in the ereport().
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 12:44 ` Re: Pasword expiration warning Euler Taveira <[email protected]>
@ 2026-01-29 15:39 ` Gilles Darold <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Gilles Darold @ 2026-01-29 15:39 UTC (permalink / raw)
To: [email protected]
Le 28/01/2026 à 13:44, Euler Taveira a écrit :
> On Fri, Jan 9, 2026, at 8:27 AM, Gilles Darold wrote:
>> Here is a v11 version of the patch.
>>
> + if (result <= (TimestampTz) password_expire_warning)
> + {
> + MyClientConnectionInfo.warning_message =
> + psprintf(_("your password will expire in %d day(s)"),
> + (int) (result / SECS_PER_DAY));
> + }
>
> You should use ngettext() for plural forms.
Is it a use we must do now or a wish?
$ grep -r "ngettext(" src/backend/ | wc -l
9
$ grep -r "(s)" src/backend/ | wc -l
831
If this is not a must do now, I prefer to use the old way because we
don't have to repeat 2 times the constant.
> I don't think you need a string into ClientConnectionInfo. Instead, you could store only the number of days.
>
> + /*
> + * 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)));
> +
>
> ... and you construct the message directly in the ereport().
I have explained this choice in my first message:
"I have chosen to add a new field, const char *warning_message, to
struct ClientConnectionInfo so that it can be used to send other
messages to the client at end of connection (
src/backend/utils/init/postinit.c: InitPostgres() ). Not sure sure that
this is the best way to do that but as it is a message dedicated to the
connection I've though it could be the right place. If we don't expect
other warning message sent to the client at connection time, just using
an integer for the number of days remaining will be enough. We could use
notice but it is not logged by default and also I think that warning is
the good level for this message. "
--
Gilles Darold
http://hexacluster.ai/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-01-28 19:25 ` Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 16:07 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
3 siblings, 2 replies; 27+ messages in thread
From: Zsolt Parragi @ 2026-01-28 19:25 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Hello!
A first question: have you looked at the GoAway patch[1]? While that
isn't exactly about the same situation, it was already considered for
password expiration checks in[2], and the same idea could be useful
for this situation too, especially in the context of my last question
in this email.
+ MyClientConnectionInfo.warning_message =
+ psprintf(_("your password will expire in %d day(s)"),
+ (int) (result / SECS_PER_DAY));
Shouldn't that use MemoryContextStrtup(TopMemoryContext, ...)?
+ /*
+ * 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;
Handling of this new variable is missing from
EstimateClientConnectionInfoSpace and SerializeClientConnectionInfo,
which the struct explicitly asks for a few lines above this change.
Even if you think that's not necessary for some reason, it should be
explained to avoid confusing readers.
+ * 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 && vuntil < PG_INT64_MAX)
Could this use TIMESTAMP_NOT_FINITE?
And I think that "days" should be "seconds".
+ TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
Maybe call this variable something more descriptive, like
seconds_until_expiration?
+
+ if (result <= (TimestampTz) password_expire_warning)
+ {
+ MyClientConnectionInfo.warning_message =
+ psprintf(_("your password will expire in %d day(s)"),
+ (int) (result / SECS_PER_DAY));
+ }
This is not that useful on the last day - have you considered
displaying hours if the expiration date is within a day, or maybe
HH:MM?
There are a few typos (warnin in the commit message, disable ->
disables in the documentation, enf -> end in a comment)
I would also consider long lived connections. The warning currently
only fires when a user connects, maybe it would be useful to also do
something when the user enters the expiration period during an active
connection?
[1]: https://www.postgresql.org/message-id/DDPQ1RV5FE9U.I2WW34NGRD8Z%40jeltef.nl
[2]: https://www.postgresql.org/message-id/CAER375OvH3_ONmc-SgUFpA6gv_d6eNj2KdZktzo-f_uqNwwWNw%40mail.gma...
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
@ 2026-01-29 11:44 ` Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Japin Li @ 2026-01-29 11:44 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Gilles Darold <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On Wed, 28 Jan 2026 at 19:25, Zsolt Parragi <[email protected]> wrote:
> Hello!
>
> A first question: have you looked at the GoAway patch[1]? While that
> isn't exactly about the same situation, it was already considered for
> password expiration checks in[2], and the same idea could be useful
> for this situation too, especially in the context of my last question
> in this email.
I didn't follow the thread.
If we perform password expiration checks based on an interval, wouldn’t it
also be possible to emit the warning during that check?
>
> + MyClientConnectionInfo.warning_message =
> + psprintf(_("your password will expire in %d day(s)"),
> + (int) (result / SECS_PER_DAY));
>
> Shouldn't that use MemoryContextStrtup(TopMemoryContext, ...)?
Here the memory context is TopTransactionContext. I don't think this would
cause any problem. Could you explain why you prefer TopMemoryContext?
>
> + /*
> + * 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;
>
> Handling of this new variable is missing from
> EstimateClientConnectionInfoSpace and SerializeClientConnectionInfo,
> which the struct explicitly asks for a few lines above this change.
> Even if you think that's not necessary for some reason, it should be
> explained to avoid confusing readers.
>
> + * 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 && vuntil < PG_INT64_MAX)
>
> Could this use TIMESTAMP_NOT_FINITE?
>
> And I think that "days" should be "seconds".
>
> + TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
>
> Maybe call this variable something more descriptive, like
> seconds_until_expiration?
>
> +
> + if (result <= (TimestampTz) password_expire_warning)
> + {
> + MyClientConnectionInfo.warning_message =
> + psprintf(_("your password will expire in %d day(s)"),
> + (int) (result / SECS_PER_DAY));
> + }
>
> This is not that useful on the last day - have you considered
> displaying hours if the expiration date is within a day, or maybe
> HH:MM?
>
Yeah, this was already proposed in [0], but there was no consensus.
> There are a few typos (warnin in the commit message, disable ->
> disables in the documentation, enf -> end in a comment)
>
Good catch.
> I would also consider long lived connections. The warning currently
> only fires when a user connects, maybe it would be useful to also do
> something when the user enters the expiration period during an active
> connection?
>
> [1]: https://www.postgresql.org/message-id/DDPQ1RV5FE9U.I2WW34NGRD8Z%40jeltef.nl
> [2]: https://www.postgresql.org/message-id/CAER375OvH3_ONmc-SgUFpA6gv_d6eNj2KdZktzo-f_uqNwwWNw%40mail.gma...
[0] https://www.postgresql.org/message-id/tencent_8C7890411E85257F512EFE4F0DD00EE29806%40qq.com
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
@ 2026-01-29 13:41 ` Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Zsolt Parragi @ 2026-01-29 13:41 UTC (permalink / raw)
To: Japin Li <[email protected]>; +Cc: Gilles Darold <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
> Yeah, this was already proposed in [0], but there was no consensus.
Sorry, I missed that! I should have read all earlier messages in more detail.
> Here the memory context is TopTransactionContext. I don't think this would
> cause any problem. Could you explain why you prefer TopMemoryContext?
I mainly looked at it similarly to the serialization question: how
existing parts of the struct are used? There are two places where
authn_id, the other string field, is set. Both use the same pattern,
and if I read the code correctly one of them is already in
TopMemoryContext, similarly to this call place. (and the other context
is the parallel worker context, which would also be long lived enough
for storing the authn_field, so again, technically the explicit
TopMemoryContext isn't necessary)
It seems to me that both existing places only use this explicit way of
allocating it in TopMemoryContext for clarity.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
@ 2026-01-29 17:53 ` Nathan Bossart <[email protected]>
2026-01-29 21:48 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Nathan Bossart @ 2026-01-29 17:53 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Japin Li <[email protected]>; Gilles Darold <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Sorry, I haven't been following the discussion, but I took a brief look at
the latest patch in the thread.
+ 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>.
I'm not sure we should subject folks to these warnings by default, and I
don't see a reason to restrict the maximum value to 30 days. IMHO we
should have this disabled by default and the maximum value should be
INT_MAX.
+ 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));
+ }
+ }
nitpick: I suspect we could simplify this code a bit, but I haven't tried.
Also, IMO we should be more precise about the expiration time. There is a
reasonable difference between a password expiring in 1 second as opposed to
23 hours, 59 minutes, 59 seconds, but in both cases this message would say
"0 days". You might be able to borrow from psql/common.c's PrintTiming()
function to add more detail here.
+ /*
+ * 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)));
Having a variable for warning messages could come in handy later. For
example, we might add a warning about using MD5 passwords at some point.
In my draft patch for this [0], I put the warning after closing the
transaction, whereas this patch puts it just before. I'm not sure I had a
principled reason for doing so, but it's an interesting difference between
the two patches.
[0] https://postgr.es/m/attachment/177167/v2-0002-WIP-add-warning-upon-authentication-with-MD5-pass.patc...
--
nathan
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-01-29 21:48 ` Gilles Darold <[email protected]>
2026-01-29 21:56 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Gilles Darold @ 2026-01-29 21:48 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Zsolt Parragi <[email protected]>; +Cc: Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Le 29/01/2026 à 18:53, Nathan Bossart a écrit :
> Sorry, I haven't been following the discussion, but I took a brief look at
> the latest patch in the thread.
>
> + 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>.
>
> I'm not sure we should subject folks to these warnings by default, and I
> don't see a reason to restrict the maximum value to 30 days. IMHO we
> should have this disabled by default and the maximum value should be
> INT_MAX.
This was my first though but I agree with Tom comment "Off-by-default is
pretty much guaranteed to not help most people.". I will use INT_MAX.
> + 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));
> + }
> + }
>
> nitpick: I suspect we could simplify this code a bit, but I haven't tried.
>
> Also, IMO we should be more precise about the expiration time. There is a
> reasonable difference between a password expiring in 1 second as opposed to
> 23 hours, 59 minutes, 59 seconds, but in both cases this message would say
> "0 days". You might be able to borrow from psql/common.c's PrintTiming()
> function to add more detail here.
Ok, there's now more vote for being more precise about the expiration
time, I will add it.
> + /*
> + * 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)));
>
> Having a variable for warning messages could come in handy later. For
> example, we might add a warning about using MD5 passwords at some point.
> In my draft patch for this [0], I put the warning after closing the
> transaction, whereas this patch puts it just before. I'm not sure I had a
> principled reason for doing so, but it's an interesting difference between
> the two patches.
>
> [0] https://postgr.es/m/attachment/177167/v2-0002-WIP-add-warning-upon-authentication-with-MD5-pass.patc...
Understood, I will rewrite the patch to use a int variable.
Thanks.
--
Gilles Darold
http://www.darold.net/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-29 21:48 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-01-29 21:56 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Nathan Bossart @ 2026-01-29 21:56 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On Thu, Jan 29, 2026 at 10:48:39PM +0100, Gilles Darold wrote:
> Le 29/01/2026 à 18:53, Nathan Bossart a écrit :
>> Having a variable for warning messages could come in handy later. For
>> example, we might add a warning about using MD5 passwords at some point.
>> In my draft patch for this [0], I put the warning after closing the
>> transaction, whereas this patch puts it just before. I'm not sure I had a
>> principled reason for doing so, but it's an interesting difference between
>> the two patches.
>
> Understood, I will rewrite the patch to use a int variable.
Sorry if my note was not clear, but I didn't mean to suggest rewriting
anything here. I thought the difference in placement for the warning
between your patch and mine was interesting, but I'm not sure there's
anything wrong that needs to be changed.
--
nathan
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-01-30 11:33 ` Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Gilles Darold @ 2026-01-30 11:33 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Zsolt Parragi <[email protected]>; +Cc: Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Here a new v12 version of the patch. Changes are the following:
- Add more precision to the time remaining with format '%d day(s)
%02dh%02dm'.
- Change GUC maximum value limit from 30d to INT_MAX
- Add comment to struct SerializedClientConnectionInfo explaining why
the warning_message variable is not reported from
struct ClientConnectionInfo.
- Use TIMESTAMP_INFINITY instead of PG_INT64_MAX to check Infinity value
for rolvaliduntil.
- Fix 2 typo.
- Update TAP tests to reflect the time format change.
--
Gilles Darold
http://hexacluster.ai/
Attachments:
[text/x-patch] v12-0002-Add-TAP-test-for-password_expire_warning.patch (2.4K, ../../[email protected]/2-v12-0002-Add-TAP-test-for-password_expire_warning.patch)
download | inline diff:
From 9edbc36af1dc90fd4a5d1793fc07ec04360e46bb Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email protected]>
Date: Fri, 30 Jan 2026 12:21:24 +0100
Subject: [PATCH v12 2/2] Add TAP test for password_expire_warning
---
.../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..a1172a2edff
--- /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\) \d{2}h\d{2}m/);
+
+$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
[text/x-patch] v12-0001-Add-password_expire_warning-GUC-to-warn-clients.patch (8.4K, ../../[email protected]/3-v12-0001-Add-password_expire_warning-GUC-to-warn-clients.patch)
download | inline diff:
From cde8ce346bb7f6fe8b6131fc6c3ebe4423cd4787 Mon Sep 17 00:00:00 2001
From: Gilles Darold <[email protected]>
Date: Fri, 30 Jan 2026 11:51:08 +0100
Subject: [PATCH v12 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 | 47 ++++++++++++++++---
src/backend/utils/init/miscinit.c | 4 +-
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, 89 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5560b95ee60..2d2e84ad870 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>0</literal> disables this behavior. The
+ default value is <literal>7d</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 52722060451..77d7dda8afc 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,41 @@ 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 < TIMESTAMP_INFINITY)
+ {
+ TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
+
+ if (result <= (TimestampTz) password_expire_warning)
+ {
+ int days, hours, minutes = 0;
+
+ days = (int) ( result / SECS_PER_DAY );
+ hours = (int) ( (result % SECS_PER_DAY) / 3600 );
+ minutes = (int) ( ((result % SECS_PER_DAY) % 3600) / 60 );
+
+ MyClientConnectionInfo.warning_message =
+ psprintf(_("your password will expire in %d day(s) %02dh%02dm"),
+ days, hours, minutes);
+ }
+ }
}
return shadow_pass;
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index 563f20374ff..4cc385f7891 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -1020,7 +1020,8 @@ ClientConnectionInfo MyClientConnectionInfo;
/*
* Intermediate representation of ClientConnectionInfo for easier
* serialization. Variable-length fields are allocated right after this
- * header.
+ * header. We don't add ClientConnectionInfo's warning_message variable
+ * in serialization, it is not used by the parallel workers.
*/
typedef struct SerializedClientConnectionInfo
{
@@ -1089,6 +1090,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 f0260e6e412..48e411ef0cf 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2242,6 +2242,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 => '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..f74e6e2ccff 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' # 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..826c5682a63 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
+ * end of InitPostgres(), currently only used to show the password
+ * expiration warning.
+ */
+ const char *warning_message;
} ClientConnectionInfo;
/*
--
2.43.0
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-02-02 17:04 ` Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Nathan Bossart @ 2026-02-02 17:04 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[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)
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-02-02 17:43 ` Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Gilles Darold @ 2026-02-02 17:43 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Le 02/02/2026 à 18:04, Nathan Bossart a écrit :
> 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?
Thanks Nathan, I'm comfortable with all the changes except the first two.
I think configuration name password_expiration_warning_threshold is too
long, why not only password_warning_threshold? I think the description
is here to explain more the configuration directive.
About the unit, I think using minutes is useless. It should be set in
days to be really useful but like all other time based directives it can
be set in seconds, minutes or hours too. This give the user the
granularity he wants. The common use will be to set it using 'Nd' syntax
but if someone wants to use 'Ns' syntax, it can be done.
--
Gilles Darold
http://www.darold.net/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-02-02 17:55 ` Nathan Bossart <[email protected]>
2026-02-02 20:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-02-02 20:31 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Nathan Bossart @ 2026-02-02 17:55 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On Mon, Feb 02, 2026 at 06:43:28PM +0100, Gilles Darold wrote:
> I think configuration name password_expiration_warning_threshold is too
> long, why not only password_warning_threshold? I think the description is
> here to explain more the configuration directive.
I agree that it's long, but leaving out "expiration" makes the name rather
ambiguous. FWIW we do have 3 other GUCs with lengths >= this
(max_parallel_apply_workers_per_subscription,
ssl_passphrase_command_supports_reload, and
autovacuum_vacuum_insert_scale_factor).
> About the unit, I think using minutes is useless. It should be set in days
> to be really useful but like all other time based directives it can be set
> in seconds, minutes or hours too. This give the user the granularity he
> wants. The common use will be to set it using 'Nd' syntax but if someone
> wants to use 'Ns' syntax, it can be done.
Not all time-based GUCs use seconds. log_rotation_age and
wal_summary_keep_time use minutes, and many others using milliseconds. But
I'm fine with changing it back to seconds. That would still allow setting
the threshold up to ~68 years, which ought to be enough for anyone.
--
nathan
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-02-02 20:25 ` Zsolt Parragi <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Zsolt Parragi @ 2026-02-02 20:25 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Gilles Darold <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
Hello!
In the commit message:
> This commit adds a new parameter called
> parameter_expiration_warning_threshold that controls when the
That should be password expiration warning
It is also clearer to me with expiration in it, without that I would
think about a complexity warning first.
And in the documentation:
+ default is 7 days. This parameter can only set in the
That should be "can only be set"
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-02-02 20:31 ` Nathan Bossart <[email protected]>
2026-02-03 15:28 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Nathan Bossart @ 2026-02-02 20:31 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
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)
Attachments:
[text/plain] v14-0001-Add-password-expiration-warnings.patch (13.6K, ../../aYEJhk83XAqk76dP@nathan/2-v14-0001-Add-password-expiration-warnings.patch)
download | inline diff:
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)
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 20:31 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-02-03 15:28 ` Nathan Bossart <[email protected]>
2026-02-03 17:08 ` Re: Pasword expiration warning Greg Sabino Mullane <[email protected]>
2026-02-04 14:44 ` Re: Pasword expiration warning Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Nathan Bossart @ 2026-02-03 15:28 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On Mon, Feb 02, 2026 at 02:31:02PM -0600, Nathan Bossart wrote:
> 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?
Hearing nothing, I've updated the patch to send warnings to all backends
that use a password to authenticate.
--
nathan
From bc5fdffb74c3d4766c536b5980969cad20eadd8b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 2 Feb 2026 14:26:33 -0600
Subject: [PATCH v15 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 | 75 +++++++++++++++++++
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, 207 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..213064bab77 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 ***/
@@ -987,6 +990,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
/* close the transaction we started above */
CommitTransactionCommand();
+ /* send any WARNINGs we've accumulated during initialization */
+ EmitConnectionWarnings();
+
return;
}
@@ -1232,6 +1238,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 +1455,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)
Attachments:
[text/plain] v15-0001-Add-password-expiration-warnings.patch (13.9K, ../../aYIUMbyDWobSi94m@nathan/2-v15-0001-Add-password-expiration-warnings.patch)
download | inline diff:
From bc5fdffb74c3d4766c536b5980969cad20eadd8b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Mon, 2 Feb 2026 14:26:33 -0600
Subject: [PATCH v15 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 | 75 +++++++++++++++++++
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, 207 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..213064bab77 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 ***/
@@ -987,6 +990,9 @@ InitPostgres(const char *in_dbname, Oid dboid,
/* close the transaction we started above */
CommitTransactionCommand();
+ /* send any WARNINGs we've accumulated during initialization */
+ EmitConnectionWarnings();
+
return;
}
@@ -1232,6 +1238,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 +1455,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)
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 20:31 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-03 15:28 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-02-03 17:08 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Greg Sabino Mullane @ 2026-02-03 17:08 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Gilles Darold <[email protected]>; Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On Tue, Feb 3, 2026 at 10:29 AM Nathan Bossart <[email protected]>
wrote:
> Hearing nothing, I've updated the patch to send warnings to all backends
> that use a password to authenticate.
>
+1, that makes sense.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 20:31 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-03 15:28 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
@ 2026-02-04 14:44 ` Peter Eisentraut <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Peter Eisentraut @ 2026-02-04 14:44 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Gilles Darold <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Japin Li <[email protected]>; Yuefei Shi <[email protected]>; songjinzhou <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; liu xiaohui <[email protected]>; Steven Niu <[email protected]>
On 03.02.26 16:28, Nathan Bossart wrote:
> + 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);
You cannot use INT64_FORMAT inside translatable messages. But you can
use PRId64.
Using the type TimestampTz for what are essentially interval/duration
quantities is a bit weird and confusing. So maybe another placeholder
would be more appropriate.
That said, I find writing plurals with "(s)" kind of lame. It's not a
good look.
It's a bit difficult to do this correctly when you have three separate
values in one string. I would consider for example just showing the
number of days if the value is larger than one day, number hours if it's
larger than one hour, else minutes. I don't think you need
minute-precision when the expiration time is several days out.
Alternatively, just print the actual expiration timestamp.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
@ 2026-01-29 16:07 ` Gilles Darold <[email protected]>
2026-01-29 20:44 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Gilles Darold @ 2026-01-29 16:07 UTC (permalink / raw)
To: [email protected]
Le 28/01/2026 à 20:25, Zsolt Parragi a écrit :
> Hello!
>
> A first question: have you looked at the GoAway patch[1]? While that
> isn't exactly about the same situation, it was already considered for
> password expiration checks in[2], and the same idea could be useful
> for this situation too, especially in the context of my last question
> in this email.
I don't know about this thread before you mention it. With a quick read
of the thread it looks that this GoAway protocol addition is use to ask
to the client to disconnect/reconnect. Here we just want to emit a
warning at connection to inform the user that his password will expire
and it don't need re-connection at all. Anyway I will have a deeper
look in this thread.
> + /*
> + * 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;
>
> Handling of this new variable is missing from
> EstimateClientConnectionInfoSpace and SerializeClientConnectionInfo,
> which the struct explicitly asks for a few lines above this change.
> Even if you think that's not necessary for some reason, it should be
> explained to avoid confusing readers.
This is intentional because this message is only emitted at the main
connection and don't needed to be in the
MyClientConnectionInfo serialization. I forgot to add a comment, I will do.
> + * 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 && vuntil < PG_INT64_MAX)
>
> Could this use TIMESTAMP_NOT_FINITE?
Thanks, it will be fixed too.
> And I think that "days" should be "seconds".
>
> + TimestampTz result = (vuntil - now) / USECS_PER_SEC; /* in seconds */
>
> Maybe call this variable something more descriptive, like
> seconds_until_expiration?
>
> +
> + if (result <= (TimestampTz) password_expire_warning)
> + {
> + MyClientConnectionInfo.warning_message =
> + psprintf(_("your password will expire in %d day(s)"),
> + (int) (result / SECS_PER_DAY));
> + }
>
> This is not that useful on the last day - have you considered
> displaying hours if the expiration date is within a day, or maybe
> HH:MM?
When you see that the password is about to expire in 0 day, do you
really think that saying it will expire in 12h30m42s will encourage the
user to change it now? If he don't do that in the previous days he will
probably not do it in the hour too. Quite useless IMO but if there more
vote to have HH:MM why not.
--
Gilles Darold
http://hexacluster.ai/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Pasword expiration warning
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 16:07 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2026-01-29 20:44 ` Zsolt Parragi <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Zsolt Parragi @ 2026-01-29 20:44 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: [email protected]
> When you see that the password is about to expire in 0 day, do you
> really think that saying it will expire in 12h30m42s will encourage the
> user to change it now? If he don't do that in the previous days he will
> probably not do it in the hour too. Quite useless IMO but if there more
> vote to have HH:MM why not.
My concern with this is mainly the relation with the 2 threads I
linked. The password expiration patch disconnects users after their
password expires, opposed to the current behavior of letting existing
connections to continue - which I think is a quite useful security
improvement. And with that, the exact expiration time, maybe even
periodic reminders while the connection is active are way more useful.
("Your password is only valid for 2 more hours, please don't forget
this or you will be disconnected" ... "Now you only have 15 minutes,
last chance to fix it")
This is why I think something that could make periodic reminders, not
only one reminder during when the client connects, could be useful.
Even if not GoAway itself, maybe something similar to it? I mainly
linked these because I think the goal/problem is similar, and while
both patches look good separately, the user experience could use some
improvements if both get merged. (Another related discussion in the
password expiration thread is oauth token expiration checks, which
could use similar "Your token expired, you have X more minutes or you
will be disconnected" messages)
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2026-02-04 14:44 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-24 06:05 [PATCH 04/11] Introduce RemoveWaitEvent(). Thomas Munro <[email protected]>
2026-01-09 06:10 Re: Pasword expiration warning Yuefei Shi <[email protected]>
2026-01-09 07:12 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 07:36 ` Re: Pasword expiration warning Steven Niu <[email protected]>
2026-01-09 09:04 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-09 11:27 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-09 11:56 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-28 05:23 ` Re: Pasword expiration warning Soumya S Murali <[email protected]>
2026-01-28 12:44 ` Re: Pasword expiration warning Euler Taveira <[email protected]>
2026-01-29 15:39 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-28 19:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 11:44 ` Re: Pasword expiration warning Japin Li <[email protected]>
2026-01-29 13:41 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-01-29 17:53 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-29 21:48 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-29 21:56 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-01-30 11:33 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:04 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 17:43 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-02-02 17:55 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-02 20:25 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
2026-02-02 20:31 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-03 15:28 ` Re: Pasword expiration warning Nathan Bossart <[email protected]>
2026-02-03 17:08 ` Re: Pasword expiration warning Greg Sabino Mullane <[email protected]>
2026-02-04 14:44 ` Re: Pasword expiration warning Peter Eisentraut <[email protected]>
2026-01-29 16:07 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2026-01-29 20:44 ` Re: Pasword expiration warning Zsolt Parragi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox