Password history is erased after database upgrade

[nikhil-postgres]

After we upgraded the database from 13 to 15, the password history is not retained.

We tried copying the password history file from old version to new version and placed it in $datadir/global. This way we are able to get the data back but it does not honour the password_reuse policy.

Example:

Same password cannot be reused for atleast 5 times

postgres=# show credcheck.password_reuse_history;
 credcheck.password_reuse_history 
 5

After upgrade, we placed the password history file in $datadir/global for the Pg15 and got below output:

postgres=# select * from pg_password_history ;
  rolename  |         password_date         |                          password_hash                           
------------+-------------------------------+------------------------------------------------------------------
 testuser | 2024-02-21 04:54:39.207879+00 | 2ccxxxxxxxxx
(1 row)

If we reset the password first time, it works and does not honour the password reuse policy

postgres=# alter user testuser password '<oldpassword>';
ALTER ROLE

If we reset the password second time, it does not work and honours the password reuse policy

postgres=# alter user testuser password '<oldpassword>';
ERROR: Cannot use this credential following the password reuse policy

Note: Before upgrade, we had created the credcheck extension in postgres 15.

[darold]

Does it was the same credcheck version?

[darold]

I have tested a copy from PG15 to PG16 with the same credcheck version and it works:

gilles=# create user testuser password 'hello';
CREATE ROLE
gilles=# ALTER user testuser password 'hello1';
ALTER ROLE
gilles=# ALTER user testuser password 'hello';
ERROR:  Cannot use this credential following the password reuse policy
gilles=# select * from pg_password_history ;
 rolename |         password_date         |                          password_hash                           
----------+-------------------------------+------------------------------------------------------------------
 testuser | 2024-03-04 09:17:04.993053+01 | 881e2066855e5334562e157bbd4f48c0175282d5271c76160707ea716a859fb9
 testuser | 2024-03-04 09:16:43.798775+01 | af36a512839bba846f0e16dcc4e37fe25dbe60ba497a3d4ef56d17fcf638ae11

sudo systemctl stop postgresql@15-main
sudo su - postgres
cp 15/main/global/pg_password_history 16/main/global/pg_password_history
sudo systemctl start postgresql@16-main

Then on PG16:

gilles=# create user testuser password 'hello';
ERROR:  Cannot use this credential following the password reuse policy
gilles=# select * from pg_password_history ;
 rolename |         password_date         |                          password_hash                           
----------+-------------------------------+------------------------------------------------------------------
 testuser | 2024-03-04 11:17:04.993053+03 | 881e2066855e5334562e157bbd4f48c0175282d5271c76160707ea716a859fb9
 testuser | 2024-03-04 11:16:43.798775+03 | af36a512839bba846f0e16dcc4e37fe25dbe60ba497a3d4ef56d17fcf638ae11

[nikhil-postgres]

We are using the same version of credcheck on rhel8.

postgres=# \dx credcheck List of installed extensions Name | Version | Schema | Description
-----------+---------+--------+------------------------------------------------------ credcheck | 2.3.0 | public | credcheck - postgresql plain text credential checker (1 row)

I tried again but we are still getting the issue. Steps are as below

Create users in Pg13

postgres=# create user testuser password 'abcdxxxx123';
CREATE ROLE
postgres=# ALTER user testuser password '1abcdxxxx123';
ALTER ROLE
postgres=# ALTER user testuser password 'abcdxxxx123';
ERROR:  Cannot use this credential following the password reuse policy
postgres=# select * from pg_password_history ;
 rolename |         password_date         |                          password_hash                           
----------+-------------------------------+------------------------------------------------------------------
 testuser | 2024-03-05 06:35:37.096158+00 | 569a6337e97bf47b5c7b533b1f91103cf4312954758c75489bbbcadbf8f3b035
 testuser | 2024-03-05 06:37:31.474423+00 | 29433de08c1d84e161fc415167f3cb7e1a949b72839f8526ad94a2cc9e6780e8
(2 rows)

Initialize Pg15 with credcheck extension already enabled and enable parameter 'credcheck.encrypted_password_allowed' to allow restore for non-plain text password during pg_upgrade.

Copy pg_password_history file and start Pg15

psql (15.5)
Type "help" for help.

postgres=# table pg_password_history ;
 rolename |         password_date         |                          password_hash                           
----------+-------------------------------+------------------------------------------------------------------
 testuser | 2024-03-05 06:35:37.096158+00 | 569a6337e97bf47b5c7b533b1f91103cf4312954758c75489bbbcadbf8f3b035
 testuser | 2024-03-05 06:37:31.474423+00 | 29433de08c1d84e161fc415167f3cb7e1a949b72839f8526ad94a2cc9e6780e8
(2 rows)

postgres=# ALTER user testuser password 'abcdxxxx123';
ALTER ROLE
postgres=# ALTER user testuser password 'abcdxxxx123';
ERROR:  Cannot use this credential following the password reuse policy
postgres=# table pg_password_history ;
 rolename |         password_date         |                          password_hash                           
----------+-------------------------------+------------------------------------------------------------------
 testuser | 2024-03-05 06:35:37.096158+00 | 569a6337e97bf47b5c7b533b1f91103cf4312954758c75489bbbcadbf8f3b035
 testuser | 2024-03-05 06:37:31.474423+00 | 29433de08c1d84e161fc415167f3cb7e1a949b72839f8526ad94a2cc9e6780e8
 testuser | 2024-03-05 06:44:37.000573+00 | a71720bde688aa81e55baab4ee9702c603c9797572f5bbf4491d63d8a35af009
(3 rows)
postgres=# ```

[nikhil-postgres]

Hi @darold , Do you know why we have this behaviour?

[darold]

@nikhil-postgres I have done the same and I have no issues. Please upgrade to version 2.6 or current development code, your current version is 2.3.0, some bug have been fixed since this release.