Message-ID: <a59f9307-369d-4e4d-b798-512b054ba968@darold.net>
Date: Thu, 29 Jan 2026 17:07:04 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Pasword expiration warning
To: pgsql-hackers@lists.postgresql.org
References: <129bcfbf-47a6-e58a-190a-62fc21a17d03@migops.com>
 <MEAPR01MB3031D75330A64DD25A5B8A04B685A@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <7aa9c67e-c423-43c4-89e8-4fbe8f5019d2@darold.net>
 <MEAPR01MB30311B76AFD06D3CAEAEF9C7B685A@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <3002ddbd-64b5-499e-b15c-e1c1720cc356@darold.net>
 <MEAPR01MB303185AAA6F5BF90759D5E86B685A@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <94634e38-7184-42a1-b9e1-7611076e1c08@darold.net>
 <MEAPR01MB303142AEB3F47886C171194EB682A@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <CAD43U4WHJm=UkxbfhxQRxQRxtdtu7AGj77uO2fF78THQWCaYTg@mail.gmail.com>
 <MEAPR01MB30319B858B3028CA38BEDC5EB682A@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <MN2PR15MB30212CEFE2F8B93C398FBF60A782A@MN2PR15MB3021.namprd15.prod.outlook.com>
 <MEAPR01MB30319C7BB75B88028E1DB7E1B682A@MEAPR01MB3031.ausprd01.prod.outlook.com>
 <f7d6e318-415a-4b98-86b0-28ae954707cc@darold.net>
 <CAN4CZFMBj-mZq_o8BtQz_TgqDTALbn31kc-SFtWE4FR1N-4RBw@mail.gmail.com>
Content-Language: en-US
From: Gilles Darold <gilles@darold.net>
Autocrypt: addr=gilles@darold.net; keydata=
 xsBNBEyscTYBCADOHjxbECHbFaVEA8fKAh8gtdRMKCXPhYwgrhIq+435c824l0NkxxT6xAq/
 U2embiCGSBy13yVT016bzcxOnOqxgw4dJgXPE1P6RYLgjd17c5Xt7uzkbn4ChO1LOVtx+7y/
 uf8GV1fiPTtnilzGQvWdXMDDTNaFbyfIsebE6rB2p47Ns0m+cj46hS22pmEWELM4RilmQ2MX
 X7vNZPxQ/C6SKQDgeGZSHrvw9OLzFty8yY7HnExfUmCzfN4asAH3q/lCu1YQgud84bCC6hXz
 Cbw/DvR7pW8QK/ej/UkNzeGUWsWX6GYDF26P3wO9Zqk5T8JixrveiCaWA/e4DR5yr2bTABEB
 AAHNIUdpbGxlcyBEYXJvbGQgPGdpbGxlc0BkYXJvbGQubmV0PsLAjgQTAQoAOBYhBEnFnv37
 SNFcBZKyn3AqzJ89qW5hBQJfxQsiAhsjBQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJEHAq
 zJ89qW5hjXsIAI53R1IdrgOia4DCtL1hK3+nbqPYYAhHAbQa54V/oHFEuXubH5F+mEBRT12M
 XNibbPvOCVUHTa46CuYLyBY6GZZ83fYpPZ3IQIavCDiLmpANR7rOoVmGERKs8VLVnQuJ1a+q
 BObwo1WKOFiqR1UEeT3hfytsmJVan8l2GzNFA+OOwIGEPdLLEpuSO9K3uyEa5FMNxXVWDROR
 MIHWvcS/RLlJby9GgLUIbLCXYXNrxncQajPs4P/TLS7fsW36laBPHIBoGbIBkgGIlpXhuTj2
 PY95YOfyFelXW3AL6oXOncmV9SkfaE2iIpc33FIrN3pEbIC4XEqMANKjidFXcdM3+JfOwE0E
 TKxxNgEIALVvukwz2p2Vw37pnMk2CGWCgoj76Lnts/3sG42Jw6Ewjvjg7u9SGg2fJJ4dzywc
 oBW5ufJlZalghO3RpbZqDwT2432CUdGG500GLLzYrHIhGBw8h9SkoJqnfel3MOjGy/HZMymK
 mCy/uqx3ti3Tp0l0Is38C7GK/lG3NKnYUwQuFaM2pDkIoIx3hyogGXEMwV+IHtYpvFroCBVi
 CgkYubsOEfqJti40BMXJfKv74ecDk43I7x9V6Pzt0j0rfmj4vFbSKHF6ptJNeZBrXrNSeTmf
 AJXaPD/olDLEStWu6cr5XyMmYxYE9D2eMOQHhFb7UOZdh++HhWcPxMMyKNNB2YsAEQEAAcLA
 XwQYAQIACQUCTKxxNgIbDAAKCRBwKsyfPaluYZETB/91iP031oHNFMU62RC8x+bERW5x6Dpu
 7Kk9rOSKf7ChcSWElBQ2A7OnNAACgPNbJgYyuEquYHTdXLP43phNDJ93EhL+af5z2BLx1gkd
 V+1rotlzbzIaUv9uofRWU6UJ/emgL3kn1TRmf3PKWWgJkHRlePqKDeNVc53uApbXHoNrS4ul
 uiwD88mx3+xgZqteCQO2DhEpAckzki82S4n4Ww6fRFB3KIGyafO7fzHFK8cFuT4za4YUFQN9
 Ix6WTABleoRLz3Cn+Jde049Zhg1222l0N07KWRS3ZZxAhwr3rv+pg3cJdbNf/rDszym92rig
 79SbGEEuL3nFCUXmA/QRIE+v
In-Reply-To: <CAN4CZFMBj-mZq_o8BtQz_TgqDTALbn31kc-SFtWE4FR1N-4RBw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a59f9307-369d-4e4d-b798-512b054ba968%40darold.net>
Precedence: bulk

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/