Odd behaviour of credcheck with password_valid_until and password_reuse_history options.

[t-ushar]

Hi Team

I am seeing some issues when I am forcing set password_valid_until to 60 days, password_reuse_history to 4, and password_reuse_interval to 15 days.

here, I want to test will credcheck performs the required validations or not. hence I executed the below command first.

create role credcheck_test with login password 'password';

and the above command failed due to create user command did not contain the "VALID UNTIL" clause, But the strange thing here is that, for failed command credcheck has created a record into the "pg_password_history" table.

So, when I am re-running the command with the VALID UNTIL clause it fails due to the error "Cannot use this credential following the password reuse policy" which is being checked by "password_reuse_history" validations.

Can you please confirm that is the expected nature of credcheck? Or is it a bug?

Please refer below test to reproduce it.

Validate whether the user exists or not

psql (13.6)
Type "help" for help.

postgres=# \du credcheck_test
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

postgres=# select * from pg_password_history ;
 rolename | password_date | password_hash 
----------+---------------+---------------
(0 rows)

Create a user without the VALID UNTIL option. And expected result to fail the command

postgres=# 
postgres=# create role credcheck_test with login password 'password'
postgres-# ;
ERROR:  require a VALID UNTIL option with a date older than 60 days

Here, it inserted one row into the pg_password_history table which I believe it's an issue/unexpected thing.

postgres=# select * from pg_password_history ;
    rolename    |         password_date         |                          password_hash                           
----------------+-------------------------------+------------------------------------------------------------------
 credcheck_test | 2023-05-12 07:59:38.002537+00 | badc9547f6c4657cd79405e0616b5091680ef723206bbb8a1f7111438a92e38d
(1 row)

Modified create user command to contain the VALID UNTIL options and here, the expectation is to succeed the command. however, it failed

postgres=# create role credcheck_test with login password 'password' VALID UNTIL '2023-09-12 07:55:55.332415+00';
ERROR:  Cannot use this credential following the password reuse policy
postgres=# \du credcheck_test
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

postgres=# 

[darold]

That's correct, commit fc8ae1e fixes this issue.

SET credcheck.password_valid_until to 60;
SET credcheck.password_reuse_interval to 15;
SET credcheck.password_reuse_history to 4;
CREATE role credcheck_test with login password 'password';
ERROR:  require a VALID UNTIL option
-- History must be empty
SELECT count(*), '0' AS "expected" FROM pg_password_history ;
 count | expected
-------+----------
     0 | 0
(1 row)