DBAtlas · Database operations atlas

Migrate from md5 passwords to scram-sha-256

PostgreSQLSecurity

Command

-- 1) Store new passwords as SCRAM
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- 2) Find roles still carrying md5 password hashes (requires superuser)
SELECT rolname,
       CASE
         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
         WHEN rolpassword LIKE 'md5%' THEN 'md5'
         WHEN rolpassword IS NULL THEN 'no password'
         ELSE 'other'
       END AS password_type,
       rolvaliduntil
FROM pg_authid
WHERE rolcanlogin
ORDER BY password_type, rolname;

-- 3) Reset the password of each md5 role (preferably in psql: \password legacy_app)
ALTER ROLE legacy_app PASSWORD 'New#Password2';

# 4) Once all roles use SCRAM, change md5 -> scram-sha-256 in pg_hba.conf and reload

What it does

Makes new passwords stored as SCRAM-SHA-256, finds login roles that still carry md5 hashes, and gives the steps to switch pg_hba.conf to scram-sha-256 once passwords are renewed.

When to use it

When a security audit flags md5, after upgrading to PG14+, or when preparing for versions where md5 is deprecated.

Caution

SCRAM cannot be derived from existing md5 hashes; every password must be set again in clear text (requires coordination with application teams). The 'md5' method in pg_hba.conf automatically performs SCRAM authentication for roles whose password is SCRAM, so keeping md5 during the migration is a safe intermediate step, but once the method is 'scram-sha-256' roles with md5 passwords can no longer connect. Old client drivers (libpq < 10, old JDBC/.NET drivers) may not support SCRAM; upgrade drivers first. ALTER ROLE ... PASSWORD may write the password to the log via log_statement; psql \password hashes it on the client before sending.

Open in DBAtlas →

Related