Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1ve8QI-009HEu-2l for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Jan 2026 09:04:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1ve8QH-006Up8-2A for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Jan 2026 09:04:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1ve8QH-006Up0-0g for pgsql-hackers@lists.postgresql.org; Fri, 09 Jan 2026 09:04:46 +0000 Received: from mail-australiaeastazolkn19010018.outbound.protection.outlook.com ([52.103.72.18] helo=SY8PR01CU002.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1ve8QF-005RRd-13 for pgsql-hackers@postgresql.org; Fri, 09 Jan 2026 09:04:45 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=aVFroOSsFDv70Vfx8qKzfvreQ3cp8EW12ztA0eXnl+3/oiakbgYsvOq4/MIrzcUssJrqXi0xL8N4HRAzpcxhcGNxUAOqL200Xu7UkIoQOuq4nCTUtD79aFzZp0Ej7yHMRO12UCk3q1rn8SNVqCb+Qsye+Ac3mtl5E8J3vqMLjf+Rzj+ghsM04IeTf+Q+DeN318EmRhY+XqPVlvWRue8m+N5R4LHvdDXpXpf6R3zZeW7m269609sNsRZFAaQs0YSWX8cC/bmLam/9pFacv12jduzvkCQeTzBemuKGxfgrNKnQeRJKRF+COgCXpgjYR1gOJz0HOGsNWFrcHAB1SbMlKQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=l/rfSCV/7mhu29FIJm/rM9SYzwv34eLX1F2aWg92p74=; b=UbxrdSBm1qR6sT2SiMIS+tbDl/oKheTqHNd0v7KHQGZ+EMU6AZILQgy1Ir4hxuN4t2qgmomolqAQhZ5YgnqQMUbUksYU1rdO9cCqAXFJujkz4z2j1THULq0MFzHkDG70keYQ+z32698ACKTGFB05c4hg2ODkhFVIP42U3s9+ib7WAN1P593iNrqFU0P/yc6tGQxwySKXnTYi1mf1IrIAhSHfetvAYQG9BwJvMvR/ELYcwUF9X4QO8wKj6RezpnXFIBrnct7ewS3ynH5K3/XuMfiWtt4uPEiQqbNXoYyvg9F5p/MneIg9b814SjSrQ5iOXvJZIB2+fh1tw2PTLT/c4A== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=l/rfSCV/7mhu29FIJm/rM9SYzwv34eLX1F2aWg92p74=; b=p0puvvdEFswpY8kGWmFqiwE5dT0YvQ5DC16qHkfR4OM885wATI6cTY7h6MdwbZqBAsGRV6jU3KnZ4WLR68/bwvJ2DIAdmPWp/6EqA2rYXjJ0ZB8dTRlIeCqhEj5W6p64Z+5auvNIc/+l3uA9NflS6x8OR3UG7+yeOvsJMuFz8tKzcr3mBL0ae83QyevG2fudorNl2hU6UiMgDdDBrw7cF4Y76k4rYER2Br1c48ZJtc+jpOPNQLvHyuRH0Fi2z9zy8uzJ44gtxEPNMHNCL0xBkrDlSTnMxrBdczZVmkqJEMU/29m1MTO1Gt2wDbaAmVyDJuXNvicLqUFU+jlofk8V/g== Received: from MEAPR01MB3031.ausprd01.prod.outlook.com (2603:10c6:201:e::21) by SY9PR01MB9781.ausprd01.prod.outlook.com (2603:10c6:10:308::19) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9499.4; Fri, 9 Jan 2026 09:04:37 +0000 Received: from MEAPR01MB3031.ausprd01.prod.outlook.com ([fe80::df58:5838:425d:8ab6]) by MEAPR01MB3031.ausprd01.prod.outlook.com ([fe80::df58:5838:425d:8ab6%6]) with mapi id 15.20.9478.005; Fri, 9 Jan 2026 09:04:37 +0000 From: Japin Li To: Steven Niu Cc: Yuefei Shi , Gilles Darold , songjinzhou , PostgreSQL Hackers , Andrew Dunstan , "Bossart, Nathan" , Tom Lane , liu xiaohui Subject: Re: Pasword expiration warning In-Reply-To: (Steven Niu's message of "Fri, 9 Jan 2026 07:36:59 +0000") References: <129bcfbf-47a6-e58a-190a-62fc21a17d03@migops.com> <3046422.1637337334@sss.pgh.pa.us> <3ea14054-af0c-3f21-0ced-04c896438bdc@dunslane.net> <08e09a01-5762-3b2e-1fa7-513478ab7394@migops.com> <786b70c5-ee75-4450-84e7-936a20de63b9@gmail.com> <7aa9c67e-c423-43c4-89e8-4fbe8f5019d2@darold.net> <3002ddbd-64b5-499e-b15c-e1c1720cc356@darold.net> <94634e38-7184-42a1-b9e1-7611076e1c08@darold.net> User-Agent: mu4e 1.12.12; emacs 29.3 Date: Fri, 09 Jan 2026 17:04:31 +0800 Message-ID: Content-Type: multipart/mixed; boundary="=-=-=" X-ClientProxiedBy: TY4P301CA0088.JPNP301.PROD.OUTLOOK.COM (2603:1096:405:37a::9) To MEAPR01MB3031.ausprd01.prod.outlook.com (2603:10c6:201:e::21) X-Microsoft-Original-Message-ID: <87v7hbgns0.fsf@hotmail.com> MIME-Version: 1.0 X-MS-Exchange-MessageSentRepresentingType: 1 X-MS-PublicTrafficType: Email X-MS-TrafficTypeDiagnostic: MEAPR01MB3031:EE_|SY9PR01MB9781:EE_ X-MS-Office365-Filtering-Correlation-Id: 90a2d783-9c28-4233-6daf-08de4f5e1c6e X-MS-Exchange-SLBlob-MailProps: feAVlmA1hHXkvOKpPMms8sW6kVQq8ZJNPGo+JsOuJNqyOkTmPHd6tUfVI/4kT31W9IlkkIxMZ2YinfYY/uIUd6DrhTcu6K1iLGms2Jjp3JyDrWhTRmz/zKtKEMvn4sx0vvBgdUBg91WM9iKJ9xCfQFkIEy+oybULqq23Q6PsnruX9nXQTbo/wIQB1RkdyPurmtD0XNTYTEhzReK2gU9Cge2Nty0bo5qWR0ghG1zHXf0GAOviItUxa+32lHec4H29JyVp4SHuyWlBdGBHbaLxanplxK9OvWRShTSzI/rZKU8HiOTGm96xRvxehy/VsujLXq8zAWwZL+1egMYvlPBwnnjbMhb5jNc1tsODQO5c0KqSzKOU+pbIaX58hb/Jblw4Hxq7wevx/3Vc5D2kDLGPvAECmmB45kEqH87XCqyfKQcwPMPOW2ArtowIrzrpefw7WX9WIJPCqoe6tU3lCzD5nRD7NImGZLtfBL74+qOnwyY62TtO4PYCYvnCV/4RKPf2k4EjlQ91zMRprLjHMZTSrGu6qrCcWKxYbmgqhU5GBK9JsXPkaKlGFUFImkjzrWnrpd4VNHZXbq6z5m8WhRh6vNz8IqmDhWKJNWLRyxj4Vrb3kjNDBwlmerdR4k9dvDPjuIY2SZ6S9/MrlVsbG3Rav5r4U7hvy1lSE/GMDOuEfyICO2M1qZlpW8vifqycERkcygBDOKK2FxQgRF3UiQ6Ovw== X-Microsoft-Antispam: BCL:0;ARA:14566002|5072599009|8060799015|19110799012|23021999003|15080799012|41001999006|6092099016|461199028|440099028|3412199025|40105399003|53005399003; X-Microsoft-Antispam-Message-Info: =?us-ascii?Q?0t0crQ0IEuCY+p63vkDZ5M0eYXG6dTBcNcPGRxxqCXuhZ/ioZqqi3nqndPOr?= =?us-ascii?Q?WrPkpJcns8u/om4WhcMUS2aFmxiFr8mGjLgSsZjKppCZL3vuOzbT4811HGKr?= =?us-ascii?Q?Z5CqnHGQQwxhYSwNhfGvk29nWZYQMfRNariwct5a7oZku/2BubgO6EbWYTCX?= =?us-ascii?Q?8D/Dl5c9wcXyV5IPMb/ucXL6bPI+7lGBA5ewYQlCOG+PN++HrJD6AFuyKZai?= =?us-ascii?Q?yzG83bHSat3V++BiQDnAoUoB4l3vsu8bjCmcRk13j3ZYk+N2r9dd60fYCGV0?= =?us-ascii?Q?ZpuK1DpEOjhbH53a5NbEg+O9hJozljSKFdfizbAx/MVtPpTWePffbVLkqvDH?= =?us-ascii?Q?8Z6FIgVjIrP6bMpxnrtubs2AeZ1Mim1zBKKKVmy+gE2qQqIUYSPDaESTIHVF?= =?us-ascii?Q?c8Ac1pfR1oZRuVTWeQs2b7v0FkeHl3IkPbEAzuI3zHgWeDYPr/N+VZQL0+Hl?= =?us-ascii?Q?DmfxfmgEJgq0//UPGEmjrjEFFzyUYwVAzVGgq3Q9eRV0GuJJ5Q26zvP6gsZH?= =?us-ascii?Q?aFpZ7HGt6KFNf0+aqZ9JpZsnqPuBxXuwJvmRR4E0iX86UklsE3NHlSBuKjpl?= =?us-ascii?Q?vb9CjokQlavkuygF/Gq+cxJlo5s6Pb0bvLWOl7cz4xgn2NfIFbk3SbSkGUIT?= =?us-ascii?Q?Ai/qap8d21c4AD8YQ3QoNgxbT7CTtQsBfMBGi/PSpiG5rC9Ftlw1W6/zGOHd?= =?us-ascii?Q?SQKrvj7SqBkIp2I+vsLc01OqkWodgnmmuNJWQiT7kAHI0skdyGR2XkDBAP8O?= =?us-ascii?Q?IGsr8dIAicbLN4+52BanXbWwZF9PnnpjTVqAgEjf3bnadUys78I0vngtRTJi?= =?us-ascii?Q?C/W1Wb+3I7MMSICGi3rldCdMifrfZrXabxnV4C8ChG+Mfm0jZfA6ExbLz8ak?= =?us-ascii?Q?GmfFQlNxd8/sz1n3FBOgtIbRxBuYYhoP8jcBRZSWFOQYZqp3UVZZI7fvRaaR?= =?us-ascii?Q?oJfU2QWA/S2cXaWj0WuYOL+gWj1RQW6j9m2R0MwLenxPLgNx/T0Il8eexKHO?= =?us-ascii?Q?jm0nzDHmqcCB/FEPt0ZF675Xm90ADUonRPwl0jbeNF6ZcYuvZYTTVNPDm1MR?= =?us-ascii?Q?8IoASvzv34Sr13qVMSwO16TfOmW19U46yCBqMgyAKDcJS5n0tAvvUIKurf+K?= =?us-ascii?Q?A2ETn+WNhHOBSpdCjpXFcYW83fiKazyiBvi8+ChFcph4Iwm/6mLESi/B+NfY?= =?us-ascii?Q?2kPHYsIVmdNTSJ9u21Ql+nVfAK7/OzUcOIYIsrrjoOCMeipEO6Jm2Gefju8?= =?us-ascii?Q?=3D?= X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1 X-MS-Exchange-AntiSpam-MessageData-0: =?us-ascii?Q?VKc3TsK6xBWeg/BY+KEHbhoHT1Q39WbOHNpLoPKt7OfGhFPpabyyd48gR+n0?= =?us-ascii?Q?Po9Ww4lIb0aEcarCCZGD+TZXMH18rZ5lVtXf/0dEdFpTAMYz+Tabj3+P4Xag?= =?us-ascii?Q?ofXExxB8a/YD14ESsixf1aLBwFpsfSh1qvyYe9bQNfWX7Cd96JIs/VBaz3tI?= =?us-ascii?Q?mk7BMOAbCwv+nc49yc67LUVakBkHRquW4iikq50mXhv54bMLt+/Vf+rBM7MN?= =?us-ascii?Q?YtvaBKQJdgGk9hDTes0YKfnhSvlahUKw3xiq6sZrkD6KBB1x3NATL0DEzVTu?= =?us-ascii?Q?8ZVyooiphR9yqo+Vrg5OSP6KMe8B+XqnucY8XqpEFN6j4BDT/HzrKj36RJV/?= =?us-ascii?Q?JAFFH9lvEl/m56dpbI9xnlzUCGBZ0LS9kzdHdqU7ZMLHg9wS6iGhXo/8JMRr?= =?us-ascii?Q?iHxZBZ3QiN93rquqvQ9xhs9paZNaxgcWT10lb+ObyuswvY/CKRGW9AQ+gVjE?= =?us-ascii?Q?ORPK34EUSSZo6fUVTVhNWYLg8A9RDV89d/djHD5Ka6QNXQ3tiqltW7QM3+ps?= =?us-ascii?Q?EJjyPtPBRGnaweI4/DDTdkqbmGs4NQZzMIVRkPCjFbUGhdRYn2ZqNTAYogR4?= =?us-ascii?Q?oVzLCdeizl19kro+JaEXJAVn3rbY1q2R14izWe+3XdCO7SaOL4OA+o0JCe1x?= =?us-ascii?Q?+ZgmtrIJv27e9wN7VAeAcy3T2dFeQ4uW0wrSNW4jn7lbtzLrD7t+lf2UhVFk?= =?us-ascii?Q?1kfcr4rgckywgyd1CN2RZFoAi2uMLHKBRzBZEaml54xagN0DFPKOnLY1mgHt?= =?us-ascii?Q?vuL+8D7YuMvKcI5hoi86WR1O0jP7oclVzY9Lwca/nM9qBR9JPxPW1qhynNP5?= =?us-ascii?Q?1gqJ06b9zsNYbrGdrN6Ta01W+o/iis7pjM5UVhWCZbu4M7EPlaQmvcBLyoZY?= =?us-ascii?Q?1LGULS3tiVPwEPL2RMyHcToZfs/1llqiz6cZYtVBAbheIFkFSrBySfnn6sHE?= =?us-ascii?Q?ObDz4xMWqD3dwPukr2F7bWz6Lh3Q9HIH4INkW1XvtfKB7qPp1k63qiKi6tV2?= =?us-ascii?Q?yC1ithmreC0tuc0sbyluo9aTenBMZloeFpyBT0jwVwmy0cfQWRuHi67xJxoq?= =?us-ascii?Q?28gQ6qK6V7JMf0XtxFnj0KX6LzY3yMBJ5/tPTEC2A4KthmCKla8JEGpdnb/b?= =?us-ascii?Q?uklQhRNijb6oQvFkHTuoLSUOZ/0OJxndkHWOqX6l+QrmlxmytderxJVQJE2j?= =?us-ascii?Q?7tnvORTaDkdLOYzZp7eELTUYh5QQHzIvp2G/rTGe/L98No6K5XRRFcXIx+rz?= =?us-ascii?Q?IWC5eV96/H9e9cQu4nwlsrIrcuOXqnlP03pChpYSasbdtMwcW9aXHoLmCNEQ?= =?us-ascii?Q?1HQeRmsQfveYPi3yVoDI34wa6IpGMJP9l824uJWKkMsCEEBOH2BCz7lQWHQa?= =?us-ascii?Q?N6mtKurLIHvpTF4LVnw0Eg25GzqJ?= X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-a559e.templateTenant X-MS-Exchange-CrossTenant-Network-Message-Id: 90a2d783-9c28-4233-6daf-08de4f5e1c6e X-MS-Exchange-CrossTenant-AuthSource: MEAPR01MB3031.ausprd01.prod.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-OriginalArrivalTime: 09 Jan 2026 09:04:36.8389 (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: SY9PR01MB9781 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-=-= Content-Type: text/plain Hi, Steven Thanks for the review. On Fri, 09 Jan 2026 at 07:36, Steven Niu 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. --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v10-0001-Add-password_expire_warning-GUC-to-warn-clients.patch From e5f0562176b41d77c6b8f2166551d55bac72e8e9 Mon Sep 17 00:00:00 2001 From: Japin Li 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 --- 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' + + password_expire_warning (integer) + + password_expire_warning configuration parameter + + + + + Controls how much time (in seconds) before a role's password expiration + a WARNING message is sent to the client upon successful + connection. It requires that a VALID UNTIL date is set + for the role. A value of 0d disable this behavior. The + default value is 7d and the maximum value 30d. + + + + password_encryption (enum) 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 --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v10-0002-Add-TAP-test-for-password_expire_warning.patch From bbe19d8c6a879c54d00daf6f96d5d6be298dad3c Mon Sep 17 00:00:00 2001 From: Japin Li 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 --=-=-=--