PostgreSQL: Store a SCRAM verifier / md5 hash instead of plaintext (no-PBKDF2 auth + SCRAM pass-through)

[rahim-kanji]

Problem

ProxySQL stores PostgreSQL user passwords as plaintext in pgsql_users.password
(include/ProxySQL_Admin_Tables_Definitions.h:290). Total flexibility, but two costs:

1. Security — cleartext passwords sit at rest in the admin SQLite DB and in runtime memory.
2. Performance — a SCRAM-SHA-256 client makes ProxySQL run PBKDF2 (4096 HMAC-SHA256
   iterations) to derive SCRAM keys before it can verify the client, and the same cost is paid
   again per backend connection: libpq re-derives the keys from the plaintext on every new
   server connection. The backend leg has no cache and is the dominant unsolved cost.

PostgreSQL itself never stores the password — it stores a verifier in pg_authid.rolpassword:

SCRAM-SHA-256$<iterations>:<base64 salt>$<base64 StoredKey>:<base64 ServerKey>

Goal: allow storing that verifier (or an md5… hash) in pgsql_users.password, verify
clients with no per-auth PBKDF2, and authenticate to backends by reusing key material instead of
re-deriving it from a plaintext password.

Why a verifier is enough

• A verifier is enough to act as a SCRAM server (verify a client) with no PBKDF2: recover
  ClientKey = ClientProof XOR ClientSignature, check SHA256(ClientKey) == StoredKey.
• A verifier is not enough to act as a SCRAM client (log into a backend): that needs the
  ClientKey, and the verifier only holds StoredKey = SHA256(ClientKey) — one-way by design.
  (That one-wayness is exactly what makes a stored verifier safe.)
• SCRAM pass-through closes the gap: while verifying the client, the server recovers the
  client's ClientKey as a by-product. Reuse it to authenticate to the backend over the backend's
  own nonce — without ever knowing the plaintext.
• md5 is the easy parallel: the stored md5 || md5(password||username) is the exact secret for
  both legs — md5(storedHash||frontend_salt) to verify a client, md5(storedHash||backend_salt)
  to the backend. No harvest, no PBKDF2, no cold-start.

---

Authentication combination matrices

A connection has two independent legs — the client→ProxySQL leg (frontend) and the
ProxySQL→backend leg (backend). A session succeeds only if both legs in its row succeed. The
stored credential type (plaintext / md5 / SCRAM verifier) drives both.

Frontend leg — client → ProxySQL

pgsql-authentication_method is now a global minimum-strength floor: 1 = cleartext, 2 = md5,
3 = scram-sha-256. It is a minimum, never a cap. ProxySQL picks the method to challenge the
client with from (stored type, floor):

Stored secret ↓ \ Floor →	cleartext (1)	md5 (2)	scram-sha-256 (3)
plaintext	✅ challenge cleartext	✅ challenge md5	✅ challenge SCRAM (PBKDF2 1st time / per thread)
md5 hash	✅ challenge md5 ¹	✅ challenge md5	❌ REJECT — md5 can't satisfy SCRAM; no downgrade ²
SCRAM verifier	✅ challenge SCRAM ³	✅ challenge SCRAM ³	✅ challenge SCRAM (no PBKDF2)

¹ An md5 secret is challenged with md5 even under the weaker cleartext floor — md5 ≥ cleartext
in strength, and an md5 hash literally cannot do a cleartext comparison. The floor is the minimum,
so a stronger method is fine.
² The one reject cell: an md5 secret under a SCRAM floor. The floor forbids downgrading to md5,
and you can't derive a SCRAM verifier from an md5 hash. The connection fails generically (see
Anti-enumeration).
³ A SCRAM verifier is always challenged with SCRAM, even under a lower floor — ProxySQL never
downgrades a verifier.

Plain-language read of the rows:

• plaintext → works under all three floors (ProxySQL holds the secret and can produce any method).
• md5 hash → works under cleartext/md5, rejected under scram.
• SCRAM verifier → works under all three floors, always via SCRAM, never PBKDF2.

Backend leg — ProxySQL → PostgreSQL

The backend method is dictated by the backend's pg_hba.conf. Whether ProxySQL can satisfy it
depends only on the stored type (the floor is a frontend concept):

Stored secret ↓ \ Backend requires →	password (cleartext)	md5	scram-sha-256
plaintext	✅ send the plaintext	✅ compute md5 from plaintext	✅ derive SCRAM (PBKDF2 per conn)
md5 hash	❌ no plaintext to send	✅ inject stored md5_secret	❌ can't derive SCRAM from md5
SCRAM verifier	❌ no plaintext to send	❌ can't derive md5 from a verifier	✅ pass-through harvested ClientKey (no PBKDF2)

Plain-language read of the rows:

• plaintext → works against any backend method (cleartext / md5 / scram).
• md5 hash → works only against an md5 backend.
• SCRAM verifier → works only against a scram-sha-256 backend.

End-to-end summary (both legs together)

Stored secret	Frontend (client)	Backend (server)	Net usable when…	Cost
plaintext	any floor	any backend method	always — most flexible	PBKDF2 on the SCRAM legs
md5 hash	floor ≤ md5 (scram floor ⇒ reject)	md5 backend only	both legs are md5	cheap, no PBKDF2
SCRAM verifier	any floor, always SCRAM	scram backend only	backend speaks scram-sha-256	cheapest — no PBKDF2 either leg

So a verifier-stored user requires a scram-sha-256 backend; an md5-stored user requires an
md5 backend; only a plaintext user works against password/cert/gss/ldap backends or
a mixed fleet. Store as plaintext when legacy md5/cleartext clients or non-SCRAM backends must be
supported.

Anti-enumeration

The three failure cases — unknown user, secret too weak for the floor (the md5-under-scram
reject), and wrong password — must be indistinguishable to the client: same handshake shape,
one generic error, the real reason logged server-side only. Distinct errors would form a
user-existence + secret-type oracle.

---

What to implement

1. Storage + type detection. Overload pgsql_users.password — the admin pastes the backend's
actual secret (copied from pg_authid.rolpassword). Auto-detect plaintext / md5 / SCRAM by prefix
via get_password_type(). Reject a malformed verifier at LOAD.

2. Frontend method selection — the minimum-strength floor reconcile above
(pgsql_reconcile_auth_method): SCRAM verifier always SCRAM; md5 rejected under a SCRAM floor;
plaintext follows the floor. Move the credential lookup ahead of the challenge.

3. Backend authentication — follow the stored type per the backend matrix above: plaintext via
libpq unchanged; md5 by injecting the stored hash (md5_secret); SCRAM verifier by pass-through
(inject harvested ClientKey + verifier ServerKey).

4. Anti-enumeration — uniform failure: always challenge first, run the same handshake shape to
completion against a deterministic mock secret for unknown/too-weak users, then one generic error.
The SCRAM floor needs a stable per-username mock salt (build_mock_scram_secret) so the fake
isn't distinguishable; advertise a consistent iteration count (4096).

5. Channel binding — keep SCRAM-SHA-256-PLUS unselectable on the frontend (a TLS-terminating
proxy can't honestly relay it) and reject clients that require it. The backend leg keeps libpq's
own channel_binding and must still work — derive StoredKey = SHA256(injected ClientKey).

6. Non-regression. All-plaintext deployments behave exactly as today; verifier/md5 storage is
opt-in.

Phasing

Phase 1 — frontend (no libpq changes): type detection + load validation; the floor reconcile;
credential lookup before the challenge; no-PBKDF2 SCRAM verify from a stored verifier + an md5-hash
verify path; the uniform generic failure; keep PLUS unselectable.

⚠️ Not independently deployable for verifier/md5 users — without Phase 2 the backend leg still
hands libpq the verifier string as a password → PBKDF2 → wrong keys → backend auth fails. Ship
Phase 1 + 2 together.

Phase 2 — backend pass-through (bundled-libpq patch): harvest ClientKey on the client's SCRAM
login, store it session-local on the backend userinfo; add conninfo params scram_client_key /
mandatory scram_server_key / md5_secret + an auth-path patch (skip saslprep/PBKDF2,
StoredKey = SHA256(ClientKey), verify the backend ServerSignature against the injected ServerKey,
fail closed if absent); inject per stored type in PgSQL_Connection::connect_start; guard so
adhoc/plaintext-derived keys are never reused; clear error when a backend demands an unsupported
method.

Key code touch-points: lib/PgSQL_Protocol.cpp (challenge selection + harvest),
lib/PgSQL_Connection.cpp (connect_start, userinfo),
deps/postgresql/.../interfaces/libpq/fe-auth-scram.c + fe-auth.c (libpq patch),
deps/libscram/src/scram.c (server-side verify).

Preconditions & constraints

• SCRAM pass-through: the stored verifier must be byte-identical to the backend's
  rolpassword — same salt and iterations, not merely the same password (ClientKey depends on
  SaltedPassword, which depends on the salt). Satisfied by pasting the backend's actual verifier
  (SELECT rolpassword FROM pg_authid WHERE rolname='…', superuser).
• md5: the stored hash binds the username (md5(password||username)) → requires the same
  username and password on both legs.
• Uniform rolpassword across a hostgroup: a frontend user may route to many backends; pass-through
  / md5-reuse require all reachable backends to share the same verifier (incl. salt). Automatic
  for physical/streaming replicas (they share pg_authid byte-for-byte); a per-backend salt mismatch
  fails only that backend's connection. See Limitation below for divergent verifiers.
• A backend using password (cleartext), cert, gss, ldap, etc. requires a plaintext-stored
  user (see the backend matrix). Because a SCRAM-stored user always authenticates via SCRAM, a
  legacy md5/cleartext-only client cannot connect as that user — store it as plaintext if such
  clients must be supported.

Limitation: backends with divergent verifiers (logical replication / independent clusters)

The uniform-rolpassword requirement is automatic for physical/streaming replicas — they share
pg_authid byte-for-byte, salt included. It does not hold when one hostgroup spans independent
clusters or logically-replicated members: logical replication does not replicate
roles/pg_authid, so each cluster's CREATE/ALTER ROLE … PASSWORD 'plaintext' generates a fresh
random salt → different verifiers for the same password. With one static stored verifier per user,
SCRAM pass-through (and md5 reuse) then matches only the backend whose rolpassword is identical;
the others reject that connection.

How to handle it (in order of preference):

1. Pin one identical verifier on every backend. Don't let each cluster pick its own salt — set
   the verifier explicitly, with the same string everywhere: ALTER ROLE x PASSWORD 'SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>' (PostgreSQL stores a pre-formed verifier
   verbatim; likewise a pre-computed md5… string). All backends then share one rolpassword.
2. Separate hostgroups for divergent backends — one stored verifier per hostgroup, each matching
   that hostgroup's backends; route the user per hostgroup.
3. auth_query-style per-backend fetch (deferred — see Non-goals). Fetch each backend's own
   verifier at connect time and cache it per backend. This is the general solution; the initial
   design uses static admin-provided verifiers and leaves the fetch as future work.

A salt mismatch on one backend fails only that backend's connection (with a clear error), never the
whole hostgroup — so options 1–2 can be applied incrementally.

[coderabbitai]

🔗 Related PRs

#5416 - Handle late COPY protocol messages after session exits fast_forward mode - P0 [merged]
#5583 - Per-server SSL parameters for PostgreSQL backends [merged]
#5641 - fix(mysqlx): resolve backend target from identity.default_route [merged]
#5654 - fix(pgsql): surface mid-transaction backend errors instead of silently replaying [merged]
#5809 - Integration batch 2026-05-23: bundle of 6 fixes [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!