Guidelines for implementing the password hash algorithm change in phased manner

Products

• VMware Tanzu Data Intelligence

Issue/Introduction

To strengthen password policies for Greenplum Database, you may want to change the password encryption method from the default md5 to sha-256 or scram-sha-256, and enforce the users to reset password to connect through the new password algorithm. To ensure the change is implemented in phased manner and keep some users still follow the original md5 encryption, you can follow this article, which introduces the steps:

• How to change password algorithm from default md5 to sha-256 or scram-sha-256.

• How to enforce password change in a phased manner. For example, enforce 1000 users to reset password but in a phased manner like100 users in a batch or one department at the same time. 

• How to keep some users still follow the md5 password algorithm. 

Environment

GPDB 6.x

Resolution

1. Change password encryption algorithm from md5 to scram-sha-256. 

a. Enable advanced_password_check module and define the new password rule. (GPDB 6 User Guide can be referred for this part.)

b. Set the SCRAM-SHA-256 password hash algorithm.

• • Login with gpadmin account.
  • Execute gpconfig with the password_hash_algorithm set to SCRAM-SHA-256:

$ gpconfig -c password_hash_algorithm -v 'SCRAM-SHA-256' 

c. Reload the system to make the change effect. 

$ gpstop -u

d. Verify all the settings:

$ gpconfig -s shared_preload_libraries


Values on all segments are consistent
GUC          : shared_preload_libraries
Master  value: metrics_collector,advanced_password_check
Segment value: metrics_collector,advanced_password_check

$ gpconfig -s password_encryption

Values on all segments are consistent

GUC          : password_encryption

Master  value: on

Segment value: on

$ gpconfig -s password_hash_algorithm

Values on all segments are consistent
GUC          : password_hash_algorithm
Master  value: SCRAM-SHA-256
Segment value: SCRAM-SHA-256

 

2. Enforce users to reset password with the new enabled algorithm in a phased manner.

a. Divide the users to different groups and arrange the password resetting schedule. For example, Sales department are the first batch. 

b. Create groups and assign the users to the groups according to the schedule. 

c. To ensure all the users, including who haven't changed password, can login GPDB without issue, you have to add some entries in pg_hba.conf on the master. 

local    all     +it_department            md5 

local    all     +mkt_department            md5

local   all     +hr_department        md5

#(Add entries for all the other batches)     ---This is to ensure the users who are the next batches that haven't reset password can login GPDB using the original md5

local   all     +sales_department     md5  ---This is to ensure the users who are the first batch that haven't reset password can login GPDB using the original md5

local    all     +sales_department    scram-sha-256  ---This is to ensure the users who have reset password with the new scram-sha-256 can login GPDB

d. Reload the system to make the change effect. 

$ gpstop -u

e. Notify the users in Sales department to reset password. Since now scram-sha-256 has been enabled on the whole GPDB level, once users reset password, the new ones will be changed to scram-sha-256 at once. As pg_hba.conf file has defined the new password algorithm for Sales departments, the users who have reset password are able to login. At the same time, anyone in Sales departments who haven't reset password are also able to login as pg_hba.conf keep the md5 verification.

Notice: At this stage, if the users from other departments/batches reset password, the new password will follow the scram-sha-256 algorithm. As pg_hba.conf has not defined scram authentication for those users, they will lose access to GPDB. So make sure they do not reset password until it's their turn. 

f. Check the users who from Sales department haven't reset password and notify to reset password. Give them a grace period to reset password, like 7 days. 

SELECT
    u.rolname AS username,
    g.rolname AS groupname,
    u.rolpassword
FROM
    pg_authid u
JOIN
    pg_auth_members m ON u.oid = m.member
JOIN
    pg_authid g ON m.roleid = g.oid
WHERE
    g.rolname = 'sales_department'
    AND u.rolpassword LIKE 'md5%';
 username | groupname | rolpassword
----------+-----------+-------------
(0 rows)

g. After the grace period, remove or comment the md5 entry in pg_hba.conf file for the first batch of users. and reload the configuration by 'gpstop -u' to make it effect. At this stage, whoever hasn't reset password will lose access to GPDB until the super user reset password for them. 

local    all     +it_department            md5 

local    all     +mkt_department            md5

local    all     +hr_department    md5

#local   all     +sales_department     md5    ---This is to forbid whoever from Sales department failed to reset password to login GPDB.
local    all     +sales_department    scram-sha-256  

h. Repeat the steps from b to g to other departments in a proper phase manner according to the schedule you arranged in step a. 

i. After all the groups finish the above steps, check the users who haven't reset password.

SELECT
    g.rolname AS groupname,
    u.rolname AS username,
    u.rolpassword
FROM
    pg_authid u
JOIN
    pg_auth_members m ON u.oid = m.member
JOIN
    pg_authid g ON m.roleid = g.oid
WHERE
    u.rolpassword LIKE 'md5%'
ORDER BY
    g.rolname, u.rolname;


     groupname      | username |   rolpassword  ---in this case, the user 'abcde' in group 'group_mix_password' has not yet reset password.
--------------------+----------+-------------------------------------
 group_mix_password | abcde | md552ee2d32ea143f994c953b2668de0f57     
(1 row)

j. Edit pg_hba.conf file on master, set all the users verification as scram-sha-256 and remove or comment the md5 entries. Reload the configuration by 'gpstop -u'. Again, whoever failed to reset password will lose access to GPDB. 

local    all     all            scram-sha-256

 

3. Make some legacy users still follow the old md5 password rules. 

a. Create a group, for example named 'legacy_group' and assign those legacy users to this group. 

b. Add an entry for legacy_users using md5 at the same time, keep the entry of all users using scram-sha-256. After this, reload the configuration by 'gpstop -u'.

# Require SCRAM for most users
local    all     all          scram-sha-256

# Allow MD5 for legacy users
local    all     legacy_group     md5

The only thing need to pay attention is, once those legacy_users reset password, the new ones will follow the current set algorithm, scram-sha-256, which will lead to access failure. It's a global algorithm to set password encryption so it cannot configure per-user algorithm. 

The only way to manually control per-user algorithm is, by toggling the cluster GUC before each password set, and then switch it back as follows. 

gpconfig -c password_encryption -v 'md5'
gpstop -u  
psql -c "ALTER ROLE legacy_user PASSWORD 'legacy_user';"

gpconfig -c password_encryption -v 'scram-sha-256'
gpstop -u