[SDDC-Manager] Incorrect password encryption method configured for PostgreSQL

Products

• VMware SDDC Manager

Issue/Introduction

After restarting the SDDC Manager VM, back-end services are stuck in activating state. Postgres instance is running but the services are not able to authenticate with the DB.

journalctl logs show that the db password encryption method is unsupported

MONTH HR:MIN:SEC xxxxx000sd0001.xxx.xxx.xx bash[60396]: ERROR:  password encryption failed: unsupported
MONTH HR:MIN:SEC xxxx000sd0001.xxx.xxx.xx bash[60397]: ERROR:  password encryption failed: unsupported

Environment

VCF 5.2.1 and above if using older STIG hardening rules.

Resolution

1. Validate if this is any issue with incorrect password encryption algorithm. Confirm Postgres version on SDDC Manager is at 15.6
   psql --version
   
   
2. Log into postgres DB and check the password encryption method set
   psql -U postgres -h localhost
show password_encryption;
   
   
3. If the Postgres version is 15.6 and output shows that it is set to md5 as below, then we need to change it to scram-sha-256.
   postgres=# show password_encryption;

password_encryption
---------------------
md5
(1 row)

Resolution:

1. Postgres initializes the password encryption method from the postgresql.auto.conf and postgresql.conf file at service restart. Check the postgresql.auto.conf file under folder /data/pgdata to see if password_encyption is set to md5.
   
   cat /data/pgdata/postgresql.auto.conf
   
   Output:
   
   # Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
password_encryption = 'md5'
log_statement = 'ddl'
   
   
2. If yes, then delete the line ""password_encryption = 'md5'"" and restart the postgresql service.
   vi /data/pgdata/postgresql.auto.conf
   
   
3. Restart postgres using command below:
   systemctl restart postgres