From: Japin Li <japinli@hotmail.com>
To: Gilles Darold <gilles@darold.net>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,  Andrew Dunstan
 <andrew@dunslane.net>,  "Bossart, Nathan" <bossartn@amazon.com>,  Tom Lane
 <tgl@sss.pgh.pa.us>
Subject: Re: Pasword expiration warning
In-Reply-To: <e16e7928-a51e-47d9-94fa-f5b50cab58ce@darold.net> (Gilles
	Darold's message of "Tue, 6 Jan 2026 15:43:20 +0100")
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>
	<e16e7928-a51e-47d9-94fa-f5b50cab58ce@darold.net>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Wed, 07 Jan 2026 13:12:57 +0800
Message-ID:
 <MEAPR01MB3031B4F594E62343BF1F8EDBB684A@MEAPR01MB3031.ausprd01.prod.outlook.com>
Content-Type: multipart/mixed; boundary="=-=-="
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: MEAPR01MB3031:EE_|ME5PR01MB9802:EE_
X-MS-Office365-Filtering-Correlation-Id: c0d0023a-560a-40b7-c99e-08de4dab6e91
X-MS-Exchange-SLBlob-MailProps:
	feAVlmA1hHXkvOKpPMms8sW6kVQq8ZJNmtXMWoAQlpJLfq7ZvOtvh9AomVpHUk4PVfhAoGdm4c9oU/WY+nUWChUnVBv8/fXMczdAHKZScd0ZNaHi3UMsxCO95ao+5X3AGhEMoIMk8/s9xq2pw2yS0f+BTJu/yMMrgAtOmumBXhI0e4K3xYervZqc/q7d+00IyGMB3Vk4fDngwI+JwYGyWsMj4htvdmqdFEnbxtsb6ZWqYPNlXPwYBJERMhYoE44pvbYuN4I+P+eehKoTt8/6nSmduD5/7lUUkRPrPhNJ1DKfMlfI7QdrR9wYi5+aqdkOZEIDrE/BAcUTANonTs8lMXh3xgYUOuzIPOope10C5/CIqfXtAFvmkrn14NtTVt7Jurv9WQYtSyrycgmtbC3BXbWo2l7UnB4wWgKwW/Qn1Glrg9KmCv/6HN7i0hSBvJ3M8SQOOn9Y/SZp8L6oBYanbzUjO3UD8a4Zv0Q1X7XP5uS5WCy4/OFsVjDzQ91NZ2YiLPHitWi/7QejdbdT+OSGZ9qsoBYcZl8t3vvyE4MBF/V8LW+BjDTA6/FUw9GLzn7lLj3BlRe6LTYPY+XCDcSsjCbsTHY8mboiUQjuHEhJ40kMmibVtI8Tj9JkveIC6dJ7+YGWOycelyDcyxZsP8QFe0HdWt6otc80P00gAJo+fNAamj27Q7mJ0Ur5nvre4cbcgUtr/Vip/mEAQXgi6x1ncQ==
X-Microsoft-Antispam:
	BCL:0;ARA:14566002|8060799015|19110799012|461199028|12121999013|23021999003|5072599009|6092099016|15080799012|3412199025|440099028|53005399003|40105399003;
X-Microsoft-Antispam-Message-Info:
	=?utf-8?B?a3BqUEFxTEkvUXNYTjErQ0RDYTBNeUJaWUtJUFJRZnNGdzRhWTUwTTFDR3BB?=
 =?utf-8?B?d09SUHpaaG5QdDdSemE2elo1OHpUTHJ6OCtlZndNb0hZazB2SDJhK0ZWbWEr?=
 =?utf-8?B?cysrRGZDQ2ZkRXlPRFJnUnc4SWlRRFUrQ05hREJJYnNpSDlOTURHLzdocEYy?=
 =?utf-8?B?aUN2VWRJRzREKy9ZaEVaVFNZa3J5L0RRbk5kdXdyOHBPdEdZd1NucHR1akdL?=
 =?utf-8?B?am92azNUL3UvMUkweXoreWZ0YWNra0IvWWJNQmNmQW90VG83L3JjdDdtT3k5?=
 =?utf-8?B?R29qVXB2ZVRieWJsaXJzNWhHRkhTVy9qelRSbkhQUG83T0dzUnc0d2tkcDVE?=
 =?utf-8?B?czZTbXRkTGdGU2RYZDVoaWF5K1V4MnRva3JDenhEeWIyVStGUjJ0Wi9kZlFz?=
 =?utf-8?B?dEp2SWNKUWR3VSswRFh6bzlxK056SXR2UklMQzVaWnVJUnRpUmRzTnRRY09Q?=
 =?utf-8?B?c1VTdklmK3c4QTRnUXE4aUlLS1RVQmJxK2toTVIyem9XNFJEZ2l5eTZPZGJk?=
 =?utf-8?B?Z2xwMFZNWWpkaTlsbVJINm8yUkgza05xbGQ4MThjaU45akd2S1lkQTlqaW1X?=
 =?utf-8?B?cnFBWFNSb0pXbTJiSmhHVG82YXl0Tk9hcnUzZ1A1Vml2NCt0bThpdllvWnhT?=
 =?utf-8?B?MFFnYnA5NGlkU2hnRmFxdDBnYWliWEdFaTN4dzFodWYxWENGVlRFV0JjQkd3?=
 =?utf-8?B?bEpIMlhqVDYrRGQ3V2dUZkpnSTZuVXJsc2xEVnpEN0RLVXl2ODR4Qjc4MEFG?=
 =?utf-8?B?ZHZTQ0N5QjNTWll5UVRMeXgrOVQ4LzlPV2wvZXJnanMwNU84SWNXNHlxdExl?=
 =?utf-8?B?U2liTGViRjhtQTJVckZVWFVFS0Y0dEQ1eS9idGphdnJiTGdJejBvdis2TXNM?=
 =?utf-8?B?RC95ZU1yUExOQktCRXhpTTRZT2lTSXRwZDhTTzFMNUFZZzd3b1FtSCtQNnpG?=
 =?utf-8?B?V1FnVnRDRUk1MkhLMndoZUZybnNoVXN5U0Jmc3NMZUY5bWhhUEt2ZzNaMU1j?=
 =?utf-8?B?SThqRHJPUHZMUkJEdkF6NElTZW9DVTFjWTVhZ0ZubXdiMm5BOGVQWkJWVHJY?=
 =?utf-8?B?Q0prendveHAvSW5aWmpzdk96TmZzdnNTZmNaLzd1ZGRDY3dwdFR3MWt3bk5z?=
 =?utf-8?B?cWJQU1I0UlhIcThtSmlEZXoyb1lnOGVPdFN4NlV4YlR5YTFvQ3lMYkN4UUFn?=
 =?utf-8?B?T3Mwa1JpeTJUTzd5cTNycXJoanJrS3pNaHkveXpiNG5lNEd4STFOYUhiRGlN?=
 =?utf-8?B?a2xINDczZ1dSOStMK1kzb3RSRDYvbi9FNHZKbmFhVlpGc213WGFyME9JcENL?=
 =?utf-8?B?Vk9pRnRQdVd1Z3NFTFRMSHVjOHdnMWpvQlZvOU5WbzAxWDUvZ2t1Rzh5bWNB?=
 =?utf-8?B?bWdBS0thSGNJQ0w1TVl2R2xNUzlsZ2pzQW9wd29PSHg0VjQwV2cvbGxIb2VP?=
 =?utf-8?B?SGtGTi84a3hmTzgvdnlHOHI4V2hDbzFSU3FkSzMreFVEd2gvV0hIRDJpQUlV?=
 =?utf-8?B?VUtEaVpvbmpFNmpFR1AyUnFKaDhmV3ltYXQzSXFLRkREdXA0MzY2YUFoenZO?=
 =?utf-8?B?bXkrL09qZlhyYzg3VDFaQ2cxK2kxQk80emV4c2lzUE1xNnQwWjJhclhRT0o1?=
 =?utf-8?Q?iDEmOx+/AOHHkrPsxqMY5ExO+s0zbgBp50oHrV9YpQ5w=3D?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?utf-8?B?bHJwZHVKaEtKcXBvM0ZjVXZZWHZaUUJHYjVXMDBzcHRQWlYzd2p1NGVhVEht?=
 =?utf-8?B?WFhobkVZdlhnbnZoWStjZlRyZXAzTjB0c1VVNjFCVndxYVc0Nyt6cUhtTVV4?=
 =?utf-8?B?VU40NXhrcmorVmpvSTB2RDBndnpjWHY2LzJlbkFRakQvZVVuK0wyRzNFMnhr?=
 =?utf-8?B?TWtmemRqN3l5K0RuR3IrNHhsMVREamZLQ2RhcjB2QVdTQ3I4U2gxVThtVy9J?=
 =?utf-8?B?cGdGV2xyUTljYjJUdGg2Y3I2L2NVR2xMaGhXNHUvT1BkTUVudHpCQjNGcUha?=
 =?utf-8?B?VGlEQ3NSa3VZNHA3dEhuT3ZpQy9OS3g2S1ZuTXY1Vm92MHBXMExVdGFNb2pK?=
 =?utf-8?B?SUdoNHJQOWJ2K2NtdVIvcVJiVXBNU2hlT0g5WWx2b3JTbXVReGdGZUNnYlBC?=
 =?utf-8?B?TnFhR0g1VEtlVDlQelQ3UHZmRk1Vdk9SLy9NVDFyTlFBMitvb1d2U2dDZHRI?=
 =?utf-8?B?UEVUenVXZEtDR3hFVzJkZkRFMEJDNVBEWWMrYVc2UmZIWlZ1eGV1aEJuajVW?=
 =?utf-8?B?RVYwKzZDVU9ZYm11dURwNjhPNnhaaVo1ak44ZXFKckYzYXFGWjZVZHlYYi9t?=
 =?utf-8?B?MDhJNVljTHZaanUvRkZaK3N0SnhSWmtjREJnT2c3dEZlQTdsVkRWenRxbk5X?=
 =?utf-8?B?ZE1MK2tsaEtBT1JOVjFiVlhTQm4xVTNCenpza1VwbHRBY2hyaGZEcmxRNzMy?=
 =?utf-8?B?M1RHOFBKeU1TcmVjbDhYeDN1Z1NuWHljVTBLWFFUZDgwQVZiRHkrTE1xaVMy?=
 =?utf-8?B?QytWdHc4aWh3U3ZtRmdSa1VFdHRaQW9HdkNCcEt1Qy91SktjY0pTbGYzMUlE?=
 =?utf-8?B?a1FmMTgwbzBsQ1FYSS84cCt0WkhKSVhTenBGdHlLaFU1NGMzQjA1WWVmcUJ5?=
 =?utf-8?B?TSt0aTF6bWVMT0ZkU2E4a2NnL1hHVnJwUzhOOU9WRHRmN29rODJhdHlIczha?=
 =?utf-8?B?elFHM0hOaFJ5UzE2MEp6WDl4OUlZWXFjWEFZc28xeXVzQ1M3aEdraWlEM3ow?=
 =?utf-8?B?cG5pOVZiVGh0MlZwZjZ2b1NTb3MwTzdocSs1WVN4Q0o4Q3AzVnJmQW1ncDdt?=
 =?utf-8?B?aU1jU0UvMGJLUlJnTURJVUJyS203L2pmcTBsdDJZRWtDNzEvQU1TZkJTeG95?=
 =?utf-8?B?UFdUUDcrVDRQR3phTzB4QmY0RUZtVGlON1dyNXVVTU5Na1lESjRoZHI2UHlS?=
 =?utf-8?B?bUNDZXhTWmg3aFhKcDcyL2NDK3JVZjhDYjRoakxHUXUzUjloenlrakg0VmFU?=
 =?utf-8?B?dVBVYWhUM1VFQUVCSVpZZXJYVkU2NXpvWHlGQzlvSjFYQmpHMG1FSG1NOE5F?=
 =?utf-8?B?aGs3UC9ncS85ZFpHMVIzVDBWNUpuMVM4Mm9reHF0YXM4R0k1Q3ZEUnJTKzNj?=
 =?utf-8?B?cStNSFcrK2xPdGF1Tmg0TjBMcEVMNS9aWWRQdWJ3RXB2Zy9JK0VZTER2OVJy?=
 =?utf-8?B?NmFXV2FsZHZETTRJZEZoZXlJRm02S0xYWmY3REh4bG51RHIvK2JBalRRbitW?=
 =?utf-8?B?ZmppbVhBVjc5a25qemdpQWJybHU4QnhPRnM4U2tEY3Y3M01hWDQ1Qi9rZnVM?=
 =?utf-8?B?d1d5R1kwb0RZRENuMi9KQVRkQURXdE1OeEt1ajhNL01FQ0ZKWVY4QXIzVGxq?=
 =?utf-8?B?SGNha0ZwbnlCb0lORzB1OGlQUGZlK1JKOStRcHNTV21rVXNVcTU1YURJMVRI?=
 =?utf-8?B?MC9KeWw1Q3JPYXFiUzV6ZngwR2xCYS9pRTFFWDAwOS9YODgxa0EwK0lsT2JP?=
 =?utf-8?B?L1JxQlB1b0ZiclVRbTQvb1B3ZVhLQmF6eks2TEhSbC9yVGJvTkdjQ0lXNit1?=
 =?utf-8?B?QmU2eUdzMkh1S3ZJRlFXbW1EYjJjdkhMZHB3RUI4amk1eWdvazRMUlNkL1Bj?=
 =?utf-8?Q?Lbdund97WuOfV?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-a559e.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: c0d0023a-560a-40b7-c99e-08de4dab6e91
X-MS-Exchange-CrossTenant-AuthSource: MEAPR01MB3031.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 07 Jan 2026 05:13:03.0424
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg:
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ME5PR01MB9802
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/MEAPR01MB3031B4F594E62343BF1F8EDBB684A%40MEAPR01MB3031.ausprd01.prod.outlook.com>
Precedence: bulk

--=-=-=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


Hi, Gilles Darold

On Tue, 06 Jan 2026 at 15:43, Gilles Darold <gilles@darold.net> wrote:
> Le 21/11/2021 =C3=A0 10:49, Gilles Darold a =C3=A9crit=C2=A0:
>> Le 20/11/2021 =C3=A0 14:48, Andrew Dunstan a =C3=A9crit=C2=A0:
>>> 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 mess=
age.
>
> 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:
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 $ /usr/local/pgsql/bin/psql -h localhost -U t=
est -d postgres
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Password for user test:
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 WARNING:=C2=A0 your password will expire in 4=
 days
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 psql (19devel)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Type "help" for help.
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 postgres=3D>
>
> Output in the log:
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 2026-01-05 23:23:13.763 CET [136001] WARNING:=
=C2=A0 your password
> will expire in 4 days
>
> Using a script:
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 $ perl test_conn.pl
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 WARNING:=C2=A0 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.warni=
ng_message)));
warning: 2 lines add whitespace errors.

2.
+{ name =3D> 'password_expire_warning', type =3D> 'int', context =3D> 'PGC_=
SIGHUP', group =3D> 'CONN_AUTH_AUTH',
+  short_desc =3D> 'Sets the number of days before password expire to emit =
a warning at client connection. Default is 7 days, 0 means no warning.',
+  flags =3D> 'GUC_UNIT_S',
+  variable =3D> 'password_expire_warning',
+  boot_val =3D> '7',
+  min =3D> '0',
+  max =3D> '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_war=
ning" (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?

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


--=-=-=
Content-Type: text/x-diff
Content-Disposition: attachment;
 filename=v2-0001-Add-password_expire_warning-GUC-to-warn-clients.patch

From a9f38f5c03cd3ccf492848a47b690b14e882dff6 Mon Sep 17 00:00:00 2001
From: Japin Li <japinli@hotmail.com>
Date: Wed, 7 Jan 2026 12:58:41 +0800
Subject: [PATCH v2] 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 <gilles@darold.net>
---
 src/backend/libpq/crypt.c                 | 20 ++++++++++++++++++++
 src/backend/utils/init/miscinit.c         |  1 +
 src/backend/utils/init/postinit.c         |  7 +++++++
 src/backend/utils/misc/guc_parameters.dat |  9 +++++++++
 src/include/libpq/crypt.h                 |  3 +++
 src/include/libpq/libpq-be.h              |  9 +++++++++
 6 files changed, 49 insertions(+)

diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 4c1052b3d42..315c559e4ac 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -20,6 +20,7 @@
 #include "common/scram-common.h"
 #include "libpq/crypt.h"
 #include "libpq/scram.h"
+#include "postmaster/postmaster.h"
 #include "utils/builtins.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
@@ -27,6 +28,9 @@
 /* Enables deprecation warnings for MD5 passwords. */
 bool		md5_password_warnings = true;
 
+/* Emit a warning 7 days before password expiration */
+int		password_expire_warning = 604800;
+
 /*
  * Fetch stored password for a user, for authentication.
  *
@@ -80,6 +84,22 @@ get_role_password(const char *role, const char **logdetail)
 		return NULL;
 	}
 
+	/*
+	 * 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)
+	{
+		float8          result;
+
+		result = ((float8) (vuntil - GetCurrentTimestamp())) / 1000000.0; /* in seconds */
+		result /= 86400; /* in days */
+
+		if ((int) result <= password_expire_warning)
+			MyClientConnectionInfo.warning_message =
+					psprintf("your password will expire in %d days", (int) result);
+	}
+
 	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 52c05a9d1d5..d0d5c87d4ea 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..1f9929e7eaf 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 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 => '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/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f01886e1098..e92f4f151c9 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 days 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..4ed61921ccd 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 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;
 } ClientConnectionInfo;
 
 /*
-- 
2.43.0


--=-=-=--