A database password reuse policy is a set of rules that govern the use of passwords within a database system. The policy is designed to ensure that users create strong and unique passwords, and that they do not reuse the same password.

By implementing a database password reuse policy, organizations can reduce the risk of security breaches due to weak or compromised passwords.

When thinking about password security with PostgreSQL you must consider the different elements of a database password reuse policy and whether PostgreSQL supports it:

• Password Complexity: The policy may require that passwords be of a certain length, contain both upper and lowercase letters, numbers, and special characters. ❌.
• Password Expiration: The policy may require that passwords expire after a certain amount of time and be changed regularly to ensure that old passwords are not used. ✅.
• Password History: The policy may prevent users from reusing a certain number of their previous passwords, to ensure that users are not recycling old passwords. ❌.
• Password Lockout: The policy may automatically lock out users who enter their password incorrectly a certain number of times to prevent brute force attacks. ❌.
• Authenication Failure Delay: Add a brief pause before reporting authentication failure, to make brute-force attacks on database passwords more difficult. Not in core but available through the auth_delay extension ✅.
• Two-Factor Authentication: The policy may require that users employ two-factor authentication in order to access the database system. ❌.

PostgreSQL does not have all these password policies natively except a part of the password expiration through the VALID UNTIL clause. But even for this policy, nothing can force the administrator to use a VALID UNTIL clause at user creation.

With PostgreSQL, password reuse policies used to be delegated to an external authentication mechanism like GSSPI if required.

But what if you can’t or don’t want to use such external service? This is where the credcheck extension comes to help.

Password Complexity

This password policies is covered by the credcheck extension and already explained in this blog it will not be explained here again.

Password Expiration

This password policy is covered partially by PostgreSQL through the use of the VALID UNTIL clause in the CREATE or ALTER ROLE/USER DDL statement but also extended by the credcheck extension. When credcheck.password_valid_until has a value upper than 0 it will force the administrator to create or alter the role using a VALID UNTIL clause with a date in the future greater than the number of days specified in the custom variable. For example:

SET credcheck.password_valid_until TO 4;

CREATE USER test1 PASSWORD 'DummY';
ERROR:  require a VALID UNTIL option with a date older than 4 days

ALTER USER test1 PASSWORD 'DummY2' VALID UNTIL '2022-01-01 00:00:00';
ERROR:  the VALID UNTIL option must have a date older than 4 days

Password Reuse Policies

To ensure that users are not recycling old passwords it requires that the history of passwords is preserved. Until version 1.0 of the credcheck extension there was no solution outside an external authentication mechanism. By request of users I decided to implement this feature and it is now available with the following policies.

Password History

This policy prohibits reusing any of the past passwords. The history size is controlled by the custom setting credcheck.password_reuse_history which will preserve the defined number of passwords in the history for a user.

-- Only keep 2 passwords in the history
SET credcheck.password_reuse_history = 2;

-- Password is registered in the history at creation time
CREATE USER credtest WITH PASSWORD 'H8Hdre=S2';

-- The new password is also registered in the history
ALTER USER credtest PASSWORD 'J8YuRe=6O';

-- Looking at the password history file using the pg_password_history view
SELECT rolename, password_hash FROM pg_password_history WHERE rolename = 'credtest' ORDER BY password_date;
 rolename |                         password_hash
----------+------------------------------------------------------------------
 credtest | 7488570b80076cf9da26644d5eeb316c4768ff5bee7bf319344e7bb328032098
 credtest | e61e58c22aa6bf31a92b385932f7d0e4dbaba24fa3fdb2982510d6c72a961335
(2 rows)

-- Trying to reuse a credential that is still in the history will throw an error
ALTER USER credtest PASSWORD 'J8YuRe=6O';
ERROR:  Cannot use this credential following the password reuse policy

Password Reuse Interval

The passwords are kept in the history for a minimum of days defined in setting credcheck.password_reuse_interval, whatever is the value of setting credcheck.password_reuse_history. The user must wait until the interval of days has elapsed before permitting reuse of the password.

CREATE USER credtest WITH PASSWORD 'AJ8YuRe=6O0';

SET credcheck.password_reuse_interval = 365;

-- Add a new password in the history and set its age to 100 days
ALTER USER credtest PASSWORD 'J8YuRe=6O';

SELECT pg_password_history_timestamp('credtest', now()::timestamp - '100 days'::interval);
 pg_password_history_timestamp
-------------------------------
                            1
(1 row)

SELECT rolename, password_hash FROM pg_password_history WHERE rolename = 'credtest' ORDER BY password_date ;
 rolename |                         password_hash
----------+------------------------------------------------------------------
 credtest | e61e58c22aa6bf31a92b385932f7d0e4dbaba24fa3fdb2982510d6c72a961335
(1 row)

-- fail, the password is in the history for less than 1 year
ALTER USER credtest PASSWORD 'J8YuRe=6O';
ERROR:  Cannot use this credential following the password reuse policy

-- Change the timestamp of the password to more than a year
SELECT pg_password_history_timestamp('credtest', now()::timestamp - '380 days'::interval);
 pg_password_history_timestamp
-------------------------------
                            2
(1 row)

-- Success, the password age present in the history has expired
ALTER USER credtest PASSWORD 'J8YuRe=6O';

It is also possible to combine both types of reuse restrictions.

Password Lockout

PostgreSQL doesn’t have any mechanism to limit the number of authentication failure attempts before the user is banned. With the credcheck extension, after an amount of authentication failure defined by configuration directive credcheck.max_auth_failure the user can be banned and never connect anymore even if he gives the right password later.

The credcheck extension adds the "Authentication failure ban" feature in release 2.0. To use this feature, the credcheck extension MUST be added to the shared_preload_libraries configuration setting and PostgreSQL restarted.

All users authentication failures are registered in shared memory with the timestamps of when the user has been banned. The authentication failure history is saved into memory only, that means that the history is lost at each PostgreSQL restart.

The authentication failure cache size is set to 1024 records by default and can be adjusted using the credcheck.auth_failure_cache_size configuration directive. Change of this GUC requires a PostgreSQL restart.

Two settings allow to control the behavior of this feature:

• credcheck.max_auth_failure: number of authentication failures allowed for a user before being banned.
• credcheck.reset_superuser: force superuser to not be banned or reset a banned superuser when set to true.

The default value for the first setting is 0 which means that the authentication failure ban feature is disabled. The default value for the second setting is false which means that postgres superuser can be banned.

In case the postgres superuser was banned, he can not logged anymore. If there is no other superuser account that can be used to reset the record of the banned superuser, set the credcheck.reset_superuser configuration directive to true into postgresql.conf file and send the SIGHUP signal (kill -1) to the PostgreSQL process pid so that it will reread the configuration. Next time the superuser will try to connect, its authentication failure cache entry will be removed.

You can also exclude the superusers or any other users from being restricted by all the password policies. This can be done using the credcheck.whitelist setting. Just set it to a comma separated list of usernames that must be whitelisted.

Free Oracle to PostgreSQL Migration Assessment Interested in migrating from Oracle or SQL Server to PostgreSQL? Sign up for our Free Migration Assessment! - No cost evaluation - Smooth transition to PostgreSQL. Please enable JavaScript in your browser to complete this form. Name * Email * Type of Database Migration * Oracle to PostgreSQL SQL Server to PostgreSQL Others Submit

Authentication Failure Delay

This feature consist on adding a brief pause before reporting authentication failure. This delay makes brute-force attacks on database passwords more difficult.

You can enable this feature in PostgreSQL by using the auth_delay extension which is dedicated to that.

If you use the credcheck extension, you don’t need to use the auth_delay extension. Just set credcheck.auth_delay_ms to a value greater than 0.

Two-factor Authentication

A Two-factor authentication (2FA) validates database users login by two separate identifiers. The authentication to access the database is not completed until both identification methods are done by the user and are correct.

This method is usually used at a higher level, at network or application access for example. There is no native implementation of this authentication method in PostgreSQL but if an external method like GSSPI provide the 2FA mechanism then it can be applied to PostgreSQL connection. You must just be sure that the PostgreSQL parameter authentication_timeout gives enough time for the two method to be completed by the user.

This article was written at the time of credcheck version 2.4.

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

 Contact Us Today!

Please enable JavaScript in your browser to complete this form.

Database and Application Migrations

• Oracle to PostgreSQL
• SQL Server to PostgreSQL
• MySQL/MariaDB to PostgreSQL
• Application Migration & Modernization

PostgreSQL Services

• Architectural Health Audit
• Managed DBA Services
• Performance Tuning & Development
• 24/7 PostgreSQL Support

Name *

Company Email *

Submit