Content-Type: multipart/mixed; boundary="------------tWjwPacNZqF70IFyWNrMcZeb"
Message-ID: <e16e7928-a51e-47d9-94fa-f5b50cab58ce@darold.net>
Date: Tue, 6 Jan 2026 15:43:20 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Pasword expiration warning
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Andrew Dunstan <andrew@dunslane.net>,
 "Bossart, Nathan" <bossartn@amazon.com>, Tom Lane <tgl@sss.pgh.pa.us>
References: <129bcfbf-47a6-e58a-190a-62fc21a17d03@migops.com>
 <3046422.1637337334@sss.pgh.pa.us>
 <a776f2d0-7ef8-f734-48b7-2aa383f41b2b@migops.com>
 <C7B3C461-2A44-4A73-B71F-933AD2DC3C6A@amazon.com>
 <3ea14054-af0c-3f21-0ced-04c896438bdc@dunslane.net>
 <08e09a01-5762-3b2e-1fa7-513478ab7394@migops.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: <08e09a01-5762-3b2e-1fa7-513478ab7394@migops.com>
Archived-At: <https://www.postgresql.org/message-id/e16e7928-a51e-47d9-94fa-f5b50cab58ce%40darold.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------tWjwPacNZqF70IFyWNrMcZeb
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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.


Best regards,

-- 
Gilles Darold
http://hexacluster.ai/

--------------tWjwPacNZqF70IFyWNrMcZeb
Content-Type: text/x-patch; charset=UTF-8;
 name="password_expire_warning-v1.patch"
Content-Disposition: attachment; filename="password_expire_warning-v1.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2xpYnBxL2NyeXB0LmMgYi9zcmMvYmFja2VuZC9s
aWJwcS9jcnlwdC5jCmluZGV4IDRjMTA1MmIzZDQyLi5lOTNhOTA1OWY1OCAxMDA2NDQKLS0t
IGEvc3JjL2JhY2tlbmQvbGlicHEvY3J5cHQuYworKysgYi9zcmMvYmFja2VuZC9saWJwcS9j
cnlwdC5jCkBAIC0yMCw2ICsyMCw3IEBACiAjaW5jbHVkZSAiY29tbW9uL3NjcmFtLWNvbW1v
bi5oIgogI2luY2x1ZGUgImxpYnBxL2NyeXB0LmgiCiAjaW5jbHVkZSAibGlicHEvc2NyYW0u
aCIKKyNpbmNsdWRlICJwb3N0bWFzdGVyL3Bvc3RtYXN0ZXIuaCIKICNpbmNsdWRlICJ1dGls
cy9idWlsdGlucy5oIgogI2luY2x1ZGUgInV0aWxzL3N5c2NhY2hlLmgiCiAjaW5jbHVkZSAi
dXRpbHMvdGltZXN0YW1wLmgiCkBAIC0yNyw2ICsyOCw5IEBACiAvKiBFbmFibGVzIGRlcHJl
Y2F0aW9uIHdhcm5pbmdzIGZvciBNRDUgcGFzc3dvcmRzLiAqLwogYm9vbAkJbWQ1X3Bhc3N3
b3JkX3dhcm5pbmdzID0gdHJ1ZTsKIAorLyogRW1pdCBhIHdhcm5pbmcgNyBkYXlzIGJlZm9y
ZSBwYXNzd29yZCBleHBpcmF0aW9uICovCitpbnQJCXBhc3N3b3JkX2V4cGlyZV93YXJuaW5n
ID0gNzsKKwogLyoKICAqIEZldGNoIHN0b3JlZCBwYXNzd29yZCBmb3IgYSB1c2VyLCBmb3Ig
YXV0aGVudGljYXRpb24uCiAgKgpAQCAtODAsNiArODQsMjIgQEAgZ2V0X3JvbGVfcGFzc3dv
cmQoY29uc3QgY2hhciAqcm9sZSwgY29uc3QgY2hhciAqKmxvZ2RldGFpbCkKIAkJcmV0dXJu
IE5VTEw7CiAJfQogCisJLyoKKwkgKiBQYXNzd29yZCBPSywgYnV0IGNoZWNrIGlmIHJvbHZh
bGlkdW50aWwgaXMgbGVzcyB0aGFuIEdVQworCSAqIHBhc3N3b3JkX2V4cGlyZV93YXJuaW5n
IGRheXMgdG8gc2VuZCBhIHdhcm5pbmcgdG8gdGhlIGNsaWVudAorCSAqLworCWlmICghaXNu
dWxsICYmIHBhc3N3b3JkX2V4cGlyZV93YXJuaW5nID4gMCkKKwl7CisJCWZsb2F0OCAgICAg
ICAgICByZXN1bHQ7CisKKwkJcmVzdWx0ID0gKChmbG9hdDgpICh2dW50aWwgLSBHZXRDdXJy
ZW50VGltZXN0YW1wKCkpKSAvIDEwMDAwMDAuMDsgLyogaW4gc2Vjb25kcyAqLworCQlyZXN1
bHQgLz0gODY0MDA7IC8qIGluIGRheXMgKi8KKworCQlpZiAoKGludCkgcmVzdWx0IDw9IHBh
c3N3b3JkX2V4cGlyZV93YXJuaW5nKQorCQkJTXlDbGllbnRDb25uZWN0aW9uSW5mby53YXJu
aW5nX21lc3NhZ2UgPQorCQkJCQlwc3ByaW50ZigieW91ciBwYXNzd29yZCB3aWxsIGV4cGly
ZSBpbiAlZCBkYXlzIiwgKGludCkgcmVzdWx0KTsKKwl9CisKIAlyZXR1cm4gc2hhZG93X3Bh
c3M7CiB9CiAKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2luaXQvbWlzY2luaXQu
YyBiL3NyYy9iYWNrZW5kL3V0aWxzL2luaXQvbWlzY2luaXQuYwppbmRleCA1NjNmMjAzNzRm
Zi4uMjQ3MzdjOTVjMjggMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2luaXQvbWlz
Y2luaXQuYworKysgYi9zcmMvYmFja2VuZC91dGlscy9pbml0L21pc2Npbml0LmMKQEAgLTEw
ODksNiArMTA4OSw3IEBAIFJlc3RvcmVDbGllbnRDb25uZWN0aW9uSW5mbyhjaGFyICpjb25u
aW5mbykKIAogCS8qIENvcHkgdGhlIGZpZWxkcyBiYWNrIGludG8gcGxhY2UgKi8KIAlNeUNs
aWVudENvbm5lY3Rpb25JbmZvLmF1dGhuX2lkID0gTlVMTDsKKwlNeUNsaWVudENvbm5lY3Rp
b25JbmZvLndhcm5pbmdfbWVzc2FnZSA9IE5VTEw7CiAJTXlDbGllbnRDb25uZWN0aW9uSW5m
by5hdXRoX21ldGhvZCA9IHNlcmlhbGl6ZWQuYXV0aF9tZXRob2Q7CiAKIAlpZiAoc2VyaWFs
aXplZC5hdXRobl9pZF9sZW4gPj0gMCkKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxz
L2luaXQvcG9zdGluaXQuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2luaXQvcG9zdGluaXQuYwpp
bmRleCA1MmMwNWE5ZDFkNS4uMzU2YzhmZmUyMTUgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5k
L3V0aWxzL2luaXQvcG9zdGluaXQuYworKysgYi9zcmMvYmFja2VuZC91dGlscy9pbml0L3Bv
c3Rpbml0LmMKQEAgLTEyMzIsNiArMTIzMiwxMyBAQCBJbml0UG9zdGdyZXMoY29uc3QgY2hh
ciAqaW5fZGJuYW1lLCBPaWQgZGJvaWQsCiAJLyogY2xvc2UgdGhlIHRyYW5zYWN0aW9uIHdl
IHN0YXJ0ZWQgYWJvdmUgKi8KIAlpZiAoIWJvb3RzdHJhcCkKIAkJQ29tbWl0VHJhbnNhY3Rp
b25Db21tYW5kKCk7CisKKwkvKgorCSAqIEVtaXQgYSB3YXJuaW5nIG1lc3NhZ2UgdG8gdGhl
IGNsaWVudCB3aGVuIHNldCwgZm9yIGV4YW1wbGUKKwkgKiB0byB3YXJuIHRoZSB1c2VyIHRo
YXQgdGhlIHBhc3N3b3JkIHdpbGwgZXhwaXJlLgorCSAqLworICAgICAgICBpZiAoTXlDbGll
bnRDb25uZWN0aW9uSW5mby53YXJuaW5nX21lc3NhZ2UpCisgICAgICAgICAgICAgICAgZXJl
cG9ydChXQVJOSU5HLCAoZXJybXNnKCIlcyIsIE15Q2xpZW50Q29ubmVjdGlvbkluZm8ud2Fy
bmluZ19tZXNzYWdlKSkpOwogfQogCiAvKgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRp
bHMvbWlzYy9ndWNfcGFyYW1ldGVycy5kYXQgYi9zcmMvYmFja2VuZC91dGlscy9taXNjL2d1
Y19wYXJhbWV0ZXJzLmRhdAppbmRleCA3YzYwYjEyNTU2NC4uYjdjNDJkYTI4ZGEgMTAwNjQ0
Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvZ3VjX3BhcmFtZXRlcnMuZGF0CisrKyBi
L3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvZ3VjX3BhcmFtZXRlcnMuZGF0CkBAIC0yMjQ4LDYg
KzIyNDgsMTUgQEAKICAgb3B0aW9ucyA9PiAncGFzc3dvcmRfZW5jcnlwdGlvbl9vcHRpb25z
JywKIH0sCiAKK3sgbmFtZSA9PiAncGFzc3dvcmRfZXhwaXJlX3dhcm5pbmcnLCB0eXBlID0+
ICdpbnQnLCBjb250ZXh0ID0+ICdQR0NfU0lHSFVQJywgZ3JvdXAgPT4gJ0NPTk5fQVVUSF9B
VVRIJywKKyAgc2hvcnRfZGVzYyA9PiAnU2V0cyB0aGUgbnVtYmVyIG9mIGRheXMgYmVmb3Jl
IHBhc3N3b3JkIGV4cGlyZSB0byBlbWl0IGEgd2FybmluZyBhdCBjbGllbnQgY29ubmVjdGlv
bi4gRGVmYXVsdCBpcyA3IGRheXMsIDAgbWVhbnMgbm8gd2FybmluZy4nLAorICBmbGFncyA9
PiAnR1VDX1VOSVRfUycsCisgIHZhcmlhYmxlID0+ICdwYXNzd29yZF9leHBpcmVfd2Fybmlu
ZycsCisgIGJvb3RfdmFsID0+ICc3JywKKyAgbWluID0+ICcwJywKKyAgbWF4ID0+ICczMCcs
Cit9LAorCiB7IG5hbWUgPT4gJ3BsYW5fY2FjaGVfbW9kZScsIHR5cGUgPT4gJ2VudW0nLCBj
b250ZXh0ID0+ICdQR0NfVVNFUlNFVCcsIGdyb3VwID0+ICdRVUVSWV9UVU5JTkdfT1RIRVIn
LAogICBzaG9ydF9kZXNjID0+ICdDb250cm9scyB0aGUgcGxhbm5lclwncyBzZWxlY3Rpb24g
b2YgY3VzdG9tIG9yIGdlbmVyaWMgcGxhbi4nLAogICBsb25nX2Rlc2MgPT4gJ1ByZXBhcmVk
IHN0YXRlbWVudHMgY2FuIGhhdmUgY3VzdG9tIGFuZCBnZW5lcmljIHBsYW5zLCBhbmQgdGhl
IHBsYW5uZXIgd2lsbCBhdHRlbXB0IHRvIGNob29zZSB3aGljaCBpcyBiZXR0ZXIuICBUaGlz
IGNhbiBiZSBzZXQgdG8gb3ZlcnJpZGUgdGhlIGRlZmF1bHQgYmVoYXZpb3IuJywKZGlmZiAt
LWdpdCBhL3NyYy9pbmNsdWRlL2xpYnBxL2NyeXB0LmggYi9zcmMvaW5jbHVkZS9saWJwcS9j
cnlwdC5oCmluZGV4IGYwMTg4NmUxMDk4Li5lOTJmNGYxNTFjOSAxMDA2NDQKLS0tIGEvc3Jj
L2luY2x1ZGUvbGlicHEvY3J5cHQuaAorKysgYi9zcmMvaW5jbHVkZS9saWJwcS9jcnlwdC5o
CkBAIC0yOCw2ICsyOCw5IEBACiAvKiBFbmFibGVzIGRlcHJlY2F0aW9uIHdhcm5pbmdzIGZv
ciBNRDUgcGFzc3dvcmRzLiAqLwogZXh0ZXJuIFBHRExMSU1QT1JUIGJvb2wgbWQ1X3Bhc3N3
b3JkX3dhcm5pbmdzOwogCisvKiBudW1iZXIgb2YgZGF5cyBiZWZvcmUgZW1pdHRpbmcgYSB3
YXJuaW5nIGZvciBwYXNzd29yZCBleHBpcmF0aW9uICovCitleHRlcm4gUEdETExJTVBPUlQg
aW50IHBhc3N3b3JkX2V4cGlyZV93YXJuaW5nOworCiAvKgogICogVHlwZXMgb2YgcGFzc3dv
cmQgaGFzaGVzIG9yIHNlY3JldHMuCiAgKgpkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvbGli
cHEvbGlicHEtYmUuaCBiL3NyYy9pbmNsdWRlL2xpYnBxL2xpYnBxLWJlLmgKaW5kZXggOTIx
YjJkYWE0ZmYuLjRlZDYxOTIxY2NkIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9saWJwcS9s
aWJwcS1iZS5oCisrKyBiL3NyYy9pbmNsdWRlL2xpYnBxL2xpYnBxLWJlLmgKQEAgLTEwMyw2
ICsxMDMsMTUgQEAgdHlwZWRlZiBzdHJ1Y3QgQ2xpZW50Q29ubmVjdGlvbkluZm8KIAkgKiBt
ZWFuaW5nIGlmIGF1dGhuX2lkIGlzIG5vdCBOVUxMOyBvdGhlcndpc2UgaXQncyB1bmRlZmlu
ZWQuCiAJICovCiAJVXNlckF1dGgJYXV0aF9tZXRob2Q7CisKKwkvKgorCSAqIE1lc3NhZ2Ug
dG8gc2VuZCB0byB0aGUgY2xpZW50IGluIGNhc2Ugb2YgY29ubmVjdGlvbiBzdWNjZXNzLgor
CSAqIFdoZW4gbm90IE5VTEwgYSBXQVJOSU5HIG1lc3NhZ2UgaXMgc2VudCB0byB0aGUgY2xp
ZW50IGF0IGVuZAorCSAqIG9mIHRoZSBjb25uZWN0aW9uIGluIHNyYy9iYWNrZW5kL3V0aWxz
L2luaXQvcG9zdGluaXQuYyBhdAorCSAqIGVuZiBvZiBJbml0UG9zdGdyZXMoKS4gRm9yIGV4
YW1wbGUsIGl0IGlzIHVzZSB0byBzaG93IHRoZQorCSAqIHBhc3N3b3JkIGV4cGlyYXRpb24g
d2FybmluZy4KKwkgKi8KKwljb25zdCBjaGFyICp3YXJuaW5nX21lc3NhZ2U7CiB9IENsaWVu
dENvbm5lY3Rpb25JbmZvOwogCiAvKgo=

--------------tWjwPacNZqF70IFyWNrMcZeb--