Setting password_hash_algorithm to scram-sha-256 Causes Catalog Error

Products

VMware Tanzu Data Intelligence

Issue/Introduction

Summary

When the Greenplum Database parameter password_hash_algorithm is set to scram-sha-256, and a user’s password is subsequently changed, gpcheckcat may report an inconsistent_pg_authid issue.

This occurs because the rolpassword values across segments differ after the password update.

Example

# gpconfig -c password_hash_algorithm -v 'scram-sha-256'
# gpstop -u

# psql
# alter role gpadmin with password 'abc123';
# select gp_segment_id, rolname, rolpassword 
  from gp_dist_random('pg_authid') where rolname = 'gpadmin';

Example output:

 gp_segment_id | rolname | rolpassword
---------------+----------+---------------------------------------------------------------------------------------------------------------------------------------
             0 | gpadmin | SCRAM-SHA-256$4096:r+OihpZy8wOvnFve66TPDg==$njnxZK+h2WW2sB3pmrjCtbZgZkPo2/2GImNjM9Flz5g=:Yx8bdP2EUUbJYjAAMi5BVqNBHhD/DxqJ7lyTY+Fsc+Y=
             1 | gpadmin | SCRAM-SHA-256$4096:jRbBjTvfh4iN11lKaqLGsg==$tYLaIue8YyAxdSWi5qr/g1LbocYJ+/ph0KUcEc9jItU=:Yoo6NcQdPkUBNAB/081RvctFo22jOJgMyjm0/mzcacU=
(2 rows)

When running gpcheckcat, you may see output similar to:

# gpcheckcat -A -p 5432
......
Object oid: 10
Table name: pg_authid    Name of test which found this issue: inconsistent_pg_authid
         rolpassword is 'SCRAM-SHA-256$4096:CETQX6GfFG8jvFFbPBtVoA==$dvu/Y8Bjq48LaU8I0SWRJBO3suqweY+YwQ2HiOYVXs8=:XYVBEdc1DYDeptSW7AoAYpr4QojQzEgDg81k3hcbOaE=' on content -1 (gpdb12:5432)
         rolpassword is 'SCRAM-SHA-256$4096:r+OihpZy8wOvnFve66TPDg==$njnxZK+h2WW2sB3pmrjCtbZgZkPo2/2GImNjM9Flz5g=:Yx8bdP2EUUbJYjAAMi5BVqNBHhD/DxqJ7lyTY+Fsc+Y=' on content 0 (gpdb12:20000)
         rolpassword is 'SCRAM-SHA-256$4096:jRbBjTvfh4iN11lKaqLGsg==$tYLaIue8YyAxdSWi5qr/g1LbocYJ+/ph0KUcEc9jItU=:Yoo6NcQdPkUBNAB/081RvctFo22jOJgMyjm0/mzcacU=' on content 1 (gpdb12:20001)

Cause

Root Cause

SCRAM (Salted Challenge Response Authentication Mechanism) uses a random salt when hashing passwords.
Even when using the same plain-text password, each encryption process generates a different hash value.
During a password change, each Greenplum segment encrypts the password independently, leading to different encrypted values across segments.
As a result, gpcheckcat detects these differences and flags them as catalog inconsistencies.

Resolution

This behavior is expected and does not affect database functionality.
You can safely ignore the reported inconsistent_pg_authid error.
Starting from Greenplum 6.31.0, the gpcheckcat utility has been enhanced to automatically handle this scenario and will no longer report this inconsistency.

Recommendation

To permanently avoid this false-positive catalog warning and benefit from other improvements, it is recommended to upgrade to Greenplum 6.31.0 or later.