Re: Pasword expiration warning

Hi, Gilles Darold

On Tue, 06 Jan 2026 at 15:43, Gilles Darold <gilles@darold.net> wrote:
> Le 21/11/2021 à 10:49, Gilles Darold a écrit :
>> Le 20/11/2021 à 14:48, Andrew Dunstan a écrit :
>>> On 11/19/21 19:17, Bossart, Nathan wrote:
>>>> On 11/19/21, 7:56 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>>>>> That leads me to wonder about server-side solutions.  It's easy
>>>>> enough for the server to see that it's used a password with an
>>>>> expiration N days away, but how could that be reported to the
>>>>> client?  The only idea that comes to mind that doesn't seem like
>>>>> a protocol break is to issue a NOTICE message, which doesn't
>>>>> seem like it squares with your desire to only do this interactively.
>>>>> (Although I'm not sure I believe that's a great idea.  If your
>>>>> application breaks at 2AM because its password expired, you
>>>>> won't be any happier than if your interactive sessions start to
>>>>> fail.  Maybe a message that would leave a trail in the server log
>>>>> would be best after all.)
>>>> I bet it's possible to use the ClientAuthentication_hook for this.  In
>>>> any case, I agree that it probably belongs server-side so that other
>>>> clients can benefit from this.
>>>>
>>> +1 for a server side solution. The people most likely to benefit from
>>> this are the people least likely to be using psql IMNSHO.
>>>
>>>
>>> Ok, I can try to implement something at server side using a NOTICE message.
>
> Hi,
>
> Sorry to resurrect this old thread, but I had completely forgotten
> about it. If there's still interest in this feature, then please find
> in attachment a patch to emit a warning to the client and into the
> logs when the password will expire within 7 days by default. A GUC,
> password_expire_warning, allow to change the number of days before
> sending the message or to disable this feature with setting value 0.
>
> 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.
>
> Output at psql connection:
>
>         $ /usr/local/pgsql/bin/psql -h localhost -U test -d postgres
>         Password for user test:
>         WARNING:  your password will expire in 4 days
>         psql (19devel)
>         Type "help" for help.
>
>         postgres=>
>
> Output in the log:
>
>         2026-01-05 23:23:13.763 CET [136001] WARNING:  your password
> will expire in 4 days
>
> Using a script:
>
>         $ perl test_conn.pl
>         WARNING:  your password will expire in 3 days
>
> The message can be handled by any client application to warn the user
> if required.
>
>
> Thanks in advance for your feedback and suggestion for a better
> implementation.
>

Thanks for updating the patch.

1.
$ git apply ~/password_expire_warning-v1.patch
/home/japin/password_expire_warning-v1.patch:71: indent with spaces.
        if (MyClientConnectionInfo.warning_message)
/home/japin/password_expire_warning-v1.patch:72: indent with spaces.
                ereport(WARNING, (errmsg("%s", MyClientConnectionInfo.warning_message)));
warning: 2 lines add whitespace errors.

2.
+{ name => 'password_expire_warning', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
+  short_desc => 'Sets the number of days before password expire to emit a warning at client connection. Default is 7
days,0 means no warning.', 
+  flags => 'GUC_UNIT_S',
+  variable => 'password_expire_warning',
+  boot_val => '7',
+  min => '0',
+  max => '30',
+},
+

The GUC_UNIT_S flag specifies that the unit is seconds, meaning the default
value of 7 corresponds to 7 seconds rather than 7 days. For example:

    # ALTER SYSTEM SET password_expire_warning TO 60;
    ERROR:  60 s is outside the valid range for parameter "password_expire_warning" (0 s .. 30 s)

3.
I tested the patch and only received an empty WARNING message. After some
analysis, I found that the warning_message buffer is likely freed after
transaction commit.

Here's a new patch that resolves the issues mentioned earlier.

Furthermore, if the remaining time until expiration is less than one day,
should we display it in minutes (or hours) instead of 0 days?

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.