fix(security): ship SCRAM-SHA-256 verifier instead of cleartext password

Update 2026-09-29 (Part of postgres-ai/internal#354):

• Review finding fixed (vacuous iteration test). creation and reset honor the server SCRAM iteration count returned early when the server had no scram_iterations. In CI that was always the case, because cli:node:tests only had bookworm's PostgreSQL 15. The test now starts its own server from a PostgreSQL ≥ 16 binary directory. With PGAI_TEST_REQUIRE_PG16=1, which CI now sets, it fails when no such directory exists. The same holds for the whole integration file when the suite would otherwise be skipped.
• Red/green, checked in a node:20-bookworm container that runs this job's apt-get lines. Red, with only PostgreSQL 15: PGAI_TEST_REQUIRE_PG16=1 but no PostgreSQL >= 16 initdb/postgres found (1 fail). Green: CI also installs postgresql-17 from PGDG, and all 9 integration tests pass. The other integration tests still use the first binaries found, which is PostgreSQL 15.
• Merge order with !433: the two MRs used to conflict in cli/test/init.test.ts. This MR moved its two print-sql redaction assertions after the grant connect check (the assertions are unchanged), so the MRs now merge cleanly in either order. A local merge of both heads passes init.test.ts and init.integration.test.ts. Suggested order: !433 first, then !336.
• Known trade-off: CI now trusts the PGDG apt repository, bootstrapped by Debian's postgresql-common script. A pinned CI image would avoid that.

Summary

prepare-db emitted create user … with password '<cleartext>' and alter user … with password '<cleartext>'. That puts the monitoring user's cleartext password into:

• the generated SQL snippet (which ends up in runbooks, tickets, Slack pastes), and
• on execution, pg_stat_activity, the server log, and pg_stat_statements.

Because the monitoring role holds pg_monitor (→ pg_read_all_stats), it could read its own password back out of pg_stat_statements query text.

Fix

Compute a SCRAM-SHA-256 verifier client-side and ship password 'SCRAM-SHA-256$<iters>:<salt>$<storedkey>:<serverkey>'. Postgres recognizes the prefix and stores the verifier verbatim, so the cleartext never reaches the server. The cleartext stays client-side for --print-password / collector config. The generated password is base64url (ASCII), so SASLprep is the identity transform (non-ASCII passwords would need SASLprep — noted in scram.ts).

Changes

• cli/lib/scram.ts (new) — scramSha256Verifier(): PBKDF2-HMAC-SHA256 + HMAC/SHA per RFC 5802/7677, matching PostgreSQL's stored-verifier format.
• cli/lib/init.ts — build the verifier and use it in the CREATE/ALTER ROLE password clause; reject null bytes in the password up front.
• cli/test/scram.test.ts (new) — verifier shape, decoded key lengths (salt 16B, keys 32B), determinism, and StoredKey = H(HMAC(SaltedPassword,'Client Key')) consistency.
• cli/test/init.test.ts — assertions flip from cleartext-inlining to "verifier present, cleartext absent".
• cli/test/init.integration.test.ts — end-to-end proof: the provisioned user authenticates over TCP scram-sha-256 with the cleartext password, and pg_authid.rolpassword stores a SCRAM verifier, not the cleartext.

Validation

• bun test test/scram.test.ts → green (100% coverage of scram.ts).
• Updated buildInitPlan unit tests → green.
• The 21 runCli subprocess tests fail only in my local checkout because a generated build artifact (lib/checkup-dictionary-embedded.ts) is absent — pre-existing/environmental, unrelated to this change. The integration login test is the authoritative SCRAM correctness gate and runs in CI.

Related

Part of the provisioning security review. Sibling fix: platform-all generators (separate MR). Distinct from the explain_generic RCE removal (!335 (merged) / platform-all!542).

Closes #253