Migrating from MD5 to SCRAM-SHA-256: A Practical Guide for PostgreSQL

Migrating your PostgreSQL password hashing from the legacy MD5 algorithm to SCRAM-SHA-256 is a critical security upgrade. This guide walks through the impact analysis and provides a step-by-step migration plan, including detection scripts, password reset strategies, and rollback considerations.

Why Migrate from MD5 to SCRAM-SHA-256?

MD5 password hashing has known vulnerabilities (fast hash, collision attacks). SCRAM-SHA-256 (Salted Challenge Response Authentication Mechanism with SHA-256) is the modern standard in PostgreSQL 10+. It uses a salted, iterated hash that is resistant to brute-force and rainbow-table attacks. Compliance frameworks (e.g., 等级保护) often require SCRAM-SHA-256.

Understanding the Impact

Key Concepts

• password_encryption (in postgresql.conf): Controls the algorithm used for new passwords set via CREATE USER or ALTER USER. Changing it does not re-hash existing passwords.
• pg_hba.conf authentication method: md5 in PostgreSQL 10+ actually accepts both MD5 and SCRAM-SHA-256 hashes. scram-sha-256 only accepts SCRAM-SHA-256.
• Password storage: Hashes are stored in pg_authid.rolpassword. MD5 hashes start with md5, SCRAM-SHA-256 hashes start with SCRAM-SHA-256$.

Impact Matrix

Step-by-Step Migration Plan

Phase 1: Inventory and Preparation

1. Identify all login roles with MD5 hashes:

SELECT rolname, rolpassword
FROM pg_authid
WHERE rolpassword LIKE 'md5%'
  AND rolcanlogin = true;

2. Check client driver compatibility: Ensure all client libraries (psql, JDBC, Npgsql, etc.) support SCRAM-SHA-256. PostgreSQL 10+ clients are compatible.

3. Backup pg_authid:

COPY pg_authid TO '/tmp/pg_authid_backup.csv' WITH CSV HEADER;

Phase 2: Password Migration (with pg_hba.conf still using md5)

1. Set password_encryption to scram-sha-256 in postgresql.conf:

password_encryption = scram-sha-256

2. Reload configuration:

pg_ctl reload
# or SQL: SELECT pg_reload_conf();

3. Reset passwords for all MD5 users. For system users with known passwords:

ALTER USER postgres PASSWORD 'admin_password';
ALTER USER replicator PASSWORD 'replication_password';

For application users, collect passwords from config files and reset:

# Python example
users = {
    'app_user1': 'pwd_from_config',
    'app_user2': 'pwd_from_config',
}
for user, pwd in users.items():
    execute_sql(f"ALTER USER {user} PASSWORD '{pwd}';")

4. Verify no MD5 hashes remain:

SELECT rolname, rolpassword FROM pg_authid
WHERE rolpassword LIKE 'md5%' AND rolcanlogin = true;
-- Should return no rows

Phase 3: Switch Authentication Method

1. Edit pg_hba.conf: Change md5 to scram-sha-256 for all relevant lines.

2. Reload configuration:

pg_ctl reload

3. Test login:

psql -U postgres -d postgres

Phase 4: Validation

• Verify all application connections work.
• For replication users, update primary_conninfo if necessary.
• Confirm no leftover MD5 hashes:

SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%' AND rolcanlogin = true;

Common Pitfalls

• Forgetting replication users: Replication roles (e.g., replicator) also need password resets; otherwise replication breaks.
• Skipping client compatibility: Older drivers (pre-PG10) may not support SCRAM-SHA-256. Upgrade them first.
• Assuming password_encryption retroactively changes existing hashes: It does not. You must explicitly reset passwords.
• No rollback plan: MD5 hashes cannot be converted back to MD5 from SCRAM-SHA-256. Keep a backup of pg_authid.
• Using scram-sha-256 in pg_hba.conf before all passwords are migrated: Users with MD5 hashes will be locked out immediately.

Rollback Strategy

If you need to revert:

1. Restore pg_authid from backup.
2. Set password_encryption = md5 and pg_hba.conf back to md5.
3. Reload configuration.
4. Reset passwords again (since SCRAM-SHA-256 hashes are not backward-compatible).

FAQ

What happens if I change password_encryption but not pg_hba.conf?

New passwords will be hashed with SCRAM-SHA-256, but existing MD5 passwords remain usable because md5 in pg_hba.conf accepts both. This is a safe intermediate step.

Can I convert MD5 hashes to SCRAM-SHA-256 without knowing the plaintext password?

No. MD5 hashing is one-way. You must know the original password to re-hash it with SCRAM-SHA-256.

How do I handle application users whose passwords I don't know?

Options:

• Force password change on first login after migration.
• Temporarily keep pg_hba.conf as md5 for those users (not compliant).
• Collect passwords from application configuration files before migration (recommended).

Does SCRAM-SHA-256 affect performance?

SCRAM-SHA-256 is more CPU-intensive than MD5 for authentication, but the overhead is negligible for typical login rates. The security benefits far outweigh the cost.

What about PostgreSQL versions before 10?

SCRAM-SHA-256 is supported from PostgreSQL 10. For older versions, upgrade PostgreSQL first.

Try it in our Bcrypt hash generator to understand modern hashing principles.