PostgreSQL v19: Password expiration warnings.

Through this new patch to PostgreSQL, when connecting to the PostgreSQL database, if the user account has a password expiration date set - using the VALID UNTIL clause, by default, the user will be warned 7 days before the password expires. You can control the delay before this warning message appears using a new GUC password_expiration_warning_threshold. A value of 0 disable this feature.

After successful connection the user will received a warning like:

    WARNING:  The password for role "user1" will expire in 4 days.

When using psql, the message will be displayed on stdout, it can be handled in any application by getting the warning message at successfull logon.

Overall this patch introduces a new "connection warning" infrastructure that can be reused elsewhere in PostgreSQL code to emit warnings to the users when they logs.

Before this patch, the only way to do that was to add an ON LOGIN event trigger to check the expiration date. The main problem of this solution is that the trigger must be created in each databases. The credcheck extension uses this workaround, I will modify it soon to disable this feature with PostgreSQL greater or equal to version 19.

Thanks to all reviewers and Nathan Bossart for improving this patch:

Author: Gilles Darold <gilles@hexacluster.ai>
Committer: Nathan Bossart <nathan@postgresql.org>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: songjinzhou <tsinghualucky912@foxmail.com>
Reviewed-by: liu xiaohui <liuxh.zj.cn@gmail.com>
Reviewed-by: Yuefei Shi <shiyuefei1004@gmail.com>
Reviewed-by: Steven Niu <niushiji@gmail.com>
Reviewed-by: Soumya S Murali <soumyamurali.work@gmail.com>
Reviewed-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Zsolt Parragi <zsolt.parragi@percona.com>

🔹 HexaRocket Recommendation

To ensure a seamless and end-to-end database migrations, we recommend using HexaRocket, which enables you to automate and manage your migration process with precision. HexaRocket supports Schema conversion with near to 100% accuracy with validation, data migration and cdc for live replication with validation of data at every stage. HexaRocket enables you to perform online migration and rollback capability using its live replication functionality. Supported databases are Oracle, MSSQL (SQL Server), MySQL, MariaDB, Sybase and PostgreSQL

Subscribe to our Newsletters and Stay tuned for more interesting topics.

If you need expert support migrating legacy or complex Oracle, SQL Server, Sybase, MySQL, MariaDB databases to PostgreSQL or distributed databases, we’re here to help. HexaCluster provides end-to-end migration and modernization services, including application migration and modernization, database migration, and PostgreSQL consulting such as performance tuning, health audits, managed DBA services, and 24/7/265 support. To start a conversation or explore how we can support your migration journey, please contact us at connect@hexacluster.ai