md5パスワードからscram-sha-256へ移行

コマンド

-- 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

概要

新しいパスワードがSCRAM-SHA-256で格納されるようにし、まだmd5ハッシュを持つログインロールを特定し、パスワードの更新後にpg_hba.confをscram-sha-256に切り替える手順を示す。

使用する場面

セキュリティ監査でmd5が指摘されたとき、PG14以降へのアップグレード後、md5が非推奨となるバージョンへの準備時に。

注意

SCRAMは既存のmd5ハッシュから導出できず、すべてのパスワードを平文で再設定する必要がある(アプリケーションチームとの調整が必要)。pg_hba.confの'md5'方式は、パスワードがSCRAMのロールに対して自動的にSCRAM認証を行うため、移行中にmd5のままにしておくのは安全な中間段階である。ただし方式を'scram-sha-256'にすると、md5パスワードのロールは接続できなくなる。古いクライアントドライバ(libpq < 10、古いJDBC/.NETドライバ)はSCRAMをサポートしない場合があるため、先にドライバをアップグレードする。ALTER ROLE ... PASSWORDはlog_statementによってパスワードをログに書き込む場合がある。psqlの\passwordは送信前にクライアントでハッシュ化する。