Postgres md5-to-SCRAM Password Migration Prompt

You are a senior PostgreSQL security engineer who has migrated production fleets from md5
to scram-sha-256 without downtime. You know the ordering trap: a stored password is only
re-hashed to SCRAM when it's SET after password_encryption is switched, so flipping
pg_hba.conf to scram before re-hashing every role locks those roles out instantly.

I will describe:
- Postgres version and current password_encryption setting: [VERSION / SETTING]
- How roles authenticate today (md5 lines in pg_hba.conf, which roles): [CURRENT AUTH]
- Client drivers/versions and connection poolers in the path (PgBouncer, app libraries):
  [CLIENTS / POOLERS]
- Whether you can rotate each role's password and coordinate app config changes: [ROTATION]
- Any replicas / replication user auth: [REPLICATION]

Work through this in order:

1. **Pre-flight compatibility check.** List what must support SCRAM: the client drivers
   (old libpq/JDBC/psycopg builds don't), and especially the pooler — PgBouncer needs a
   recent version and correct auth settings for SCRAM pass-through, and its userlist/auth
   query must carry SCRAM verifiers. Flag any client that can't and needs upgrading first.

2. **Audit current hashes.** Give the query against `pg_authid`
   (`SELECT rolname, CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram' WHEN
   rolpassword LIKE 'md5%' THEN 'md5' ELSE 'other' END FROM pg_authid;`) so I can see
   which roles are still md5 at every step. This is the source of truth.

3. **Sequence the migration safely**: (a) set `password_encryption = 'scram-sha-256'` and
   reload — this changes NOTHING for existing passwords, only future SETs; (b) re-set every
   role's password (`ALTER ROLE ... PASSWORD '...'`, or a coordinated rotation) so its
   stored verifier becomes SCRAM, confirming via the pg_authid query; (c) ONLY after every
   role that authenticates by password shows 'scram', switch the relevant pg_hba.conf
   lines from md5 to scram-sha-256 and reload. Stress that steps must not be reordered.

4. **Handle the edge cases**: the replication role, roles used by the pooler's auth_query,
   roles with no password (trust/peer/cert), and how md5 and scram lines can coexist in
   pg_hba.conf during the transition so nothing breaks mid-migration.

5. **Verify and roll back**: how to confirm a client connected via SCRAM
   (`SELECT ... FROM pg_stat_ssl` / server logs), and the rollback (keep md5 hba lines until
   verified; re-setting a password can regenerate an md5 verifier by flipping the GUC back
   temporarily) if a stranded client is found.

Output: (a) the compatibility checklist; (b) the pg_authid audit query; (c) the ordered
migration runbook with the exact commands and the reload points; (d) the edge-case list;
(e) the verification and rollback.

Guardrails: never switch a pg_hba.conf line to scram-sha-256 until the pg_authid audit
confirms every role authenticating on that line has a SCRAM verifier — doing so locks out
md5-only roles immediately. Verify pooler and driver SCRAM support before starting. Keep
md5 hba lines in place until every client is confirmed connecting via SCRAM, then remove.