Migrate PostgreSQL password encryption from MD5 to SCRAM-SHA-256

Knowledge Base Supported
, , , ,
1

Migrate PostgreSQL password encryption from MD5 to SCRAM-SHA-256

This article describes how to migrate PostgreSQL 15 on UCS 5.2 from md5 password encryption to scram-sha-256.

This article applies to UCS 5.2 only.

PostgreSQL 15 on UCS 5.0-10 only supports md5 in the UCS configuration described here.

New UCS 5.2 installations use scram-sha-256 by default. Systems upgraded from UCS 5.0 may still explicitly use md5.

The migration consists of three steps:

  1. Configure PostgreSQL to store newly set passwords as scram-sha-256.
  2. Re-set existing passwords that are still stored as MD5 hashes.
  3. Change PostgreSQL client authentication to scram-sha-256.

The order is important.

Before starting, make sure that all PostgreSQL clients connecting to the server support SCRAM-SHA-256. Older PostgreSQL client libraries may not support SCRAM authentication.

Check whether the system is affected

A normal ucr set changes the configuration of the local UCS system only. The setting is not automatically propagated to other UCS systems.

If PostgreSQL is installed on several UCS systems, check and migrate each affected system separately.

Check whether PostgreSQL 15 is installed and which PostgreSQL clusters are present:

dpkg -l postgresql-15 2>/dev/null | grep '^ii'
pg_lsclusters

Then check the currently active password encryption method:

sudo -u postgres psql -c "SHOW password_encryption;"

The expected result on a system that still needs migration is:

 password_encryption
---------------------
 md5

If more than one PostgreSQL cluster exists, use the port displayed by pg_lsclusters to query the PostgreSQL 15 cluster explicitly:

sudo -u postgres psql -p <PORT> -c "SHOW password_encryption;"

Use the same -p <PORT> option for the following psql commands if necessary.

1. Configure SCRAM-SHA-256 for newly set passwords

Set the PostgreSQL 15 password encryption method:

ucr set postgres15/password-encryption=scram-sha-256
systemctl restart postgresql.service

Verify the active setting:

sudo -u postgres psql -c "SHOW password_encryption;"

It should now return:

scram-sha-256

This setting only controls how passwords are stored when they are set or changed. Existing MD5 password hashes are not converted automatically.

2. Re-set passwords still stored as MD5

Check which login roles still have an MD5 password:

sudo -u postgres psql -c "
SELECT rolname,
       CASE
         WHEN rolpassword IS NULL THEN 'no password'
         WHEN rolpassword LIKE 'md5%' THEN 'md5'
         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
         ELSE 'unknown'
       END AS password_type
FROM pg_authid
WHERE rolcanlogin
ORDER BY rolname;"

Only roles whose password is still shown as md5 need to be migrated.

For each affected role, open psql:

sudo -u postgres psql

and set its password again:

\password <role_name>

Passwords used by UCS services

PostgreSQL roles such as selfservice, keycloak, pkgdbu or importhttpapi can be service accounts. Their passwords are also stored in the configuration of the corresponding service.

Do not simply assign a new random password unless you also update the service configuration. Otherwise the service may no longer be able to connect to PostgreSQL.

Instead, re-enter the password already stored in the corresponding password file. PostgreSQL then stores the same password as a SCRAM-SHA-256 verifier, while the application continues to use the unchanged password.

Common service accounts are:

PostgreSQL role Password file
admindiary /etc/admin-diary.secret
horde /etc/horde.secret
keycloak /etc/postgresql-keycloak.secret
selfservice /etc/self-service-db.secret
importhttpapi /etc/ucsschool-import/postgres.secret
pkgdbu /etc/postgresql/pkgdb.secret

For selfservice, a different password file may be configured. Check it with:

ucr get umc/self-service/postgresql/password-file

The roles available on a system depend on the installed applications and components.

PostgreSQL superuser postgres

The PostgreSQL role postgres does not have a password by default on UCS and uses local peer authentication.

Do not create a password for postgres solely for this migration. If the query above reports no password, no action is required for this role.

After re-setting the affected passwords, run the query again and verify that the required login roles no longer use MD5 hashes.

3. Change client authentication to SCRAM-SHA-256

After all passwords required for password authentication have been migrated, change the authentication method:

ucr set postgres15/pg_hba/password-encryption=scram-sha-256
systemctl restart postgresql.service

The two UCR variables have different purposes:

  • postgres15/password-encryption controls how PostgreSQL stores a password when it is set or changed.
  • postgres15/pg_hba/password-encryption controls which password authentication method PostgreSQL requests from clients through pg_hba.conf.

This is why the authentication method should only be changed after the required passwords have been re-set.

Verify the migration

Verify the configured password encryption:

sudo -u postgres psql -c "SHOW password_encryption;"

It should return:

scram-sha-256

Also verify that no login roles which require password authentication remain with an MD5 password:

sudo -u postgres psql -Atc "
SELECT rolname
FROM pg_authid
WHERE rolcanlogin
  AND rolpassword LIKE 'md5%';"

If the command produces no output, no login role with an MD5 password hash remains.

Finally, verify that the applications using PostgreSQL can still connect to their databases normally.