← back to Secrets Manager
rotate-dw-admin-full.sh: client-side SCRAM-SHA-256 so plaintext never reaches Postgres (TK-11480)
eb703f4aea4d3d7ac5942621d337918f42cde1e0 · 2026-09-26 09:04:05 -0700 · Steve Abrams
ALTER now carries a pre-hashed verifier (pw to python on stdin, never argv), so
log_statement / pg_stat_statements can never capture the plaintext. Harness T6
proves it on a real throwaway PG14 cluster (TCP-only; new pw authenticates, wrong
pw rejected) and goes RED on a corrupted verifier. T6 fails NOT-MEASURED if the
cluster does not start (fixed a false-green negative control). 22/22 green.
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01J3gBZfBgR2c7TZugF5Ybz4
Files touched
M rotate-dw-admin-full.shM test/rotate-dw-admin-full.test.sh
Diff
commit eb703f4aea4d3d7ac5942621d337918f42cde1e0
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat Sep 26 09:04:05 2026 -0700
rotate-dw-admin-full.sh: client-side SCRAM-SHA-256 so plaintext never reaches Postgres (TK-11480)
ALTER now carries a pre-hashed verifier (pw to python on stdin, never argv), so
log_statement / pg_stat_statements can never capture the plaintext. Harness T6
proves it on a real throwaway PG14 cluster (TCP-only; new pw authenticates, wrong
pw rejected) and goes RED on a corrupted verifier. T6 fails NOT-MEASURED if the
cluster does not start (fixed a false-green negative control). 22/22 green.
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01J3gBZfBgR2c7TZugF5Ybz4
---
rotate-dw-admin-full.sh | 25 ++++++++++++++++++-------
test/rotate-dw-admin-full.test.sh | 26 ++++++++++++++++++++++++--
2 files changed, 42 insertions(+), 9 deletions(-)
diff --git a/rotate-dw-admin-full.sh b/rotate-dw-admin-full.sh
index 4613f66..376a48f 100755
--- a/rotate-dw-admin-full.sh
+++ b/rotate-dw-admin-full.sh
@@ -21,7 +21,7 @@
# Synthetic test: test/rotate-dw-admin-full.test.sh (no real host/DB/secret).
#
# SECRET HYGIENE (per Steve's standing rules)
-# The new pw exists only in: this process memory, the ALTER SQL piped via stdin,
+# The new pw exists only in: this process memory, the SCRAM hasher's stdin,
# the secrets registry, and the rewritten consumer envs. It is NEVER printed
# (unless --show-once), never an argv, never in shell history.
#
@@ -78,11 +78,22 @@ NEW_PG="$(openssl rand -base64 48 | tr -dc 'A-Za-z0-9' | head -c 32)"
[[ "$NEW_PG" =~ ^[A-Za-z0-9]{32}$ ]] || { echo "pw-gen failed" >&2; exit 1; }
echo "[1] minted a new 32-char password (hidden)"
-# Emit the ALTER statement on stdout. printf is a bash BUILTIN, so the password
-# never becomes an argv of any exec'd process (the old python3 helper put it in
-# argv, visible to `ps`). NEW_PG is validated [A-Za-z0-9]{32}, so plain quoting
-# is injection-safe.
-alter_sql(){ printf "ALTER ROLE dw_admin PASSWORD '%s';\n" "$NEW_PG"; }
+# Hash CLIENT-SIDE into a SCRAM-SHA-256 verifier (the exact format PG stores in
+# pg_authid). Postgres accepts a pre-hashed verifier as-is, so the ALTER text that
+# reaches the server — and any log_statement/pg_stat_statements capture — never
+# contains the plaintext. The pw goes to python on STDIN (never argv).
+PG_VERIFIER="$(printf '%s' "$NEW_PG" | python3 -c '
+import sys,os,hmac,hashlib,base64
+pw=sys.stdin.read().encode(); salt=os.urandom(16); it=4096
+sp=hashlib.pbkdf2_hmac("sha256",pw,salt,it)
+ck=hmac.new(sp,b"Client Key",hashlib.sha256).digest()
+sk=hmac.new(sp,b"Server Key",hashlib.sha256).digest()
+b=lambda x: base64.b64encode(x).decode()
+print(f"SCRAM-SHA-256${it}:{b(salt)}${b(hashlib.sha256(ck).digest())}:{b(sk)}")
+')"
+[[ "$PG_VERIFIER" =~ ^SCRAM-SHA-256\$4096:[A-Za-z0-9+/=]+\$[A-Za-z0-9+/=]+:[A-Za-z0-9+/=]+$ ]] \
+ || { echo "SCRAM verifier generation failed" >&2; exit 1; }
+alter_sql(){ printf "ALTER ROLE dw_admin PASSWORD '%s';\n" "$PG_VERIFIER"; }
# ---- 2. Mac2 local PG ----------------------------------------------------
say "[2] ALTER ROLE dw_admin on Mac2 local PG"
@@ -240,4 +251,4 @@ say "DONE. After verifying 0 failures + apps online:"
echo " • flip CNCP prod flag (server.js:1718 status:'compromised' -> 'ok')"
echo " • the box is now on a FRESH password (compromised-era pw retired)"
if [ "$SHOW" = 1 ]; then printf ' new dw_admin pw (record in your vault, then clear): %s\n' "$NEW_PG"; fi
-unset NEW_PG
+unset NEW_PG PG_VERIFIER
diff --git a/test/rotate-dw-admin-full.test.sh b/test/rotate-dw-admin-full.test.sh
index f7a3927..7f55f80 100755
--- a/test/rotate-dw-admin-full.test.sh
+++ b/test/rotate-dw-admin-full.test.sh
@@ -107,8 +107,11 @@ mk_sandbox
rc=$(run_script $'y\ny\ny\n')
grep -q "$SYNTH" "$SB/log/argv.log" && bad "T1 password appeared in a process argv (defect 1)" \
|| ok "T1 password never in any process argv"
-grep -q "ALTER ROLE dw_admin" "$SB/log/psql.stdin" 2>/dev/null && [ "$(grep -c "$SYNTH" "$SB/log/psql.stdin")" -ge 2 ] \
- && ok "T1 ALTER reached Mac2 + prod psql via stdin" || bad "T1 ALTER did not reach both psql stdins"
+[ "$(grep -c "ALTER ROLE dw_admin PASSWORD 'SCRAM-SHA-256" "$SB/log/psql.stdin" 2>/dev/null)" -ge 2 ] \
+ && ok "T1 SCRAM ALTER reached Mac2 + prod psql via stdin" || bad "T1 ALTER did not reach both psql stdins"
+grep -q "$SYNTH" "$SB/log/psql.stdin" 2>/dev/null && bad "T1 PLAINTEXT pw reached psql (would land in PG logs)" \
+ || ok "T1 no plaintext pw in any SQL sent to Postgres"
+ALTER_STMT="$(grep -m1 "ALTER ROLE dw_admin" "$SB/log/psql.stdin")"
grep -q "PG_DW_ADMIN_PASSWORD=$SYNTH" "$SB/log/cli.stdin" 2>/dev/null && ok "T1 registry fan got the new pw on stdin" \
|| bad "T1 registry fan missing"
grep -q "dw_admin:$SYNTH@" "$SB/root/public-projects/appA/.env" && grep -q "dw_admin:$SYNTH@" "$SB/root/public-projects/appB/config/db.js" \
@@ -147,6 +150,25 @@ grep -qE '^(psql|ssh|pm2) ' "$SB/log/argv.log" && bad "T4 dry-run invoked psql/s
grep -q "$SYNTH" "$SB/log/out" && bad "T4 dry-run printed the password" || ok "T4 dry-run never printed the password"
rm -rf "$SB"
+# ---- T6: the emitted SCRAM ALTER really authenticates on a REAL throwaway PG --
+if command -v initdb >/dev/null && command -v pg_ctl >/dev/null && [ -n "${ALTER_STMT:-}" ]; then
+ PGT="$(mktemp -d "${TMPDIR:-/tmp}/dwrot-pg.XXXXXX")"; PORT=$((50000 + RANDOM % 5000))
+ if initdb -D "$PGT/data" -U tester --auth=trust >/dev/null 2>&1; then
+ # TCP only: macOS caps unix-socket paths at ~103 chars and TMPDIR paths run long.
+ printf 'host all tester 127.0.0.1/32 trust\nhost all all 127.0.0.1/32 scram-sha-256\n' > "$PGT/data/pg_hba.conf"
+ if pg_ctl -D "$PGT/data" -o "-p $PORT -c unix_socket_directories='' -c listen_addresses=127.0.0.1" -l "$PGT/log" -w start >/dev/null 2>&1 \
+ && psql -X -q -h 127.0.0.1 -p "$PORT" -U tester -d postgres -v ON_ERROR_STOP=1 -c "CREATE ROLE dw_admin LOGIN" >/dev/null 2>&1 \
+ && printf '%s\n' "$ALTER_STMT" | psql -X -q -h 127.0.0.1 -p "$PORT" -U tester -d postgres -v ON_ERROR_STOP=1 -f - >/dev/null 2>&1; then
+ PGPASSWORD="$SYNTH" psql -X -h 127.0.0.1 -p "$PORT" -U dw_admin -d postgres -tAc 'select 1' 2>/dev/null | grep -q 1 \
+ && ok "T6 real PG: new pw authenticates via the client-side SCRAM verifier" || bad "T6 real PG: new pw REJECTED"
+ PGPASSWORD="WRONGpw" psql -X -h 127.0.0.1 -p "$PORT" -U dw_admin -d postgres -tAc 'select 1' >/dev/null 2>&1 \
+ && bad "T6 real PG: WRONG pw accepted (auth not enforced — test invalid)" || ok "T6 real PG: wrong pw rejected (negative control)"
+ else bad "T6 NOT MEASURED: throwaway PG did not start / role or ALTER failed ($(tail -1 "$PGT/log" 2>/dev/null))"; fi
+ pg_ctl -D "$PGT/data" -m immediate stop >/dev/null 2>&1
+ else bad "T6 initdb failed (NOT MEASURED)"; fi
+ rm -rf "$PGT"
+else bad "T6 NOT MEASURED (no initdb or no captured ALTER)"; fi
+
# ---- T5: pm2 jlist fails mid-fan -> must exit non-zero, never a false-green DONE
mk_sandbox
rc=$(FAKE_PM2_JLIST_FAIL=1 run_script $'y\ny\ny\n')
← 5673bb9 rotate-dw-admin-full.sh: repair 4 reproduced defects + synth
·
back to Secrets Manager
·
(newest)