← back to Secrets Manager
DW-ADMIN-ROTATION-RUNBOOK-2026-06-19.sh
284 lines
#!/usr/bin/env bash
# =============================================================================
# DW-ADMIN ROTATION RUNBOOK — 2026-06-19
# =============================================================================
#
# HARD RULE: STEVE RUNS THIS. THE AGENT (VP Security) EXECUTED NOTHING.
# ----------------------------------------------------------------------
# This file was AUTHORED by the security officer from read-only verification.
# Zero ALTER ROLE, zero ssh-mutate, zero fan, zero pm2, zero flag-clear ran.
# Every block below is a paste you (Steve) execute by hand, step by step,
# reading the VERIFY GATE after each step and STOPPING if it does not pass.
#
# DO NOT pipe this whole file into a shell. It is STEP-GATED on purpose.
# Source/paste ONE step, read its verify output, then proceed to the next.
#
# >>> RUN THIS IN A NON-LOGGING SHELL <<<
# The new password must never hit shell history. Start a clean shell:
# set +o history # zsh/bash: stop appending to history NOW
# …or run the whole session in a subshell and clear history after:
# fc -p; <do work>; fc -P # zsh
# At the very end: unset NEWPW (Step 4 verify reminds you).
#
# -----------------------------------------------------------------------------
# WHY THIS RUNBOOK EXISTS (ground truth from read-only verification)
# -----------------------------------------------------------------------------
# * dw_admin rotation is NOT verifiably complete.
# * The canonical fan key PG_DW_ADMIN_PASSWORD does NOT exist in the secrets
# registry / .env / routes.json — so cli.js has NEVER fanned dw_admin.
# (The DSN_REWRITE map in cli.js DOES already know the key:
# PG_DW_ADMIN_PASSWORD -> { dsn:'DATABASE_URL', user:'dw_admin' }
# but with no routes.json entry there are no destinations to write to.)
# * Old credential 'DW2024SecurePass' is STILL LIVE in 4 local .env.local
# DATABASE_URL DSNs (all 127.0.0.1 / LOCAL Mac2 Postgres):
# ~/Projects/Norma/.env.local (db: sdcc)
# ~/Projects/StudentLoanTracker/.env.local (db: dw_unified)
# ~/Projects/ClawCoder/.env.local (db: clawcoder)
# ~/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/.env.local (db: dw_unified)
# …plus ~45 hardcoded source refs (the secret-strip backlog — DO-LAST).
# * Prod (Kamatera) compromised flag = a HARDCODED literal at
# ~/cncp-starter/server.js:3936 out.prod = { status: 'compromised' }
# (DO-LAST — clear only after 2–4 verify clean.)
# * Prod app DSN fan-out is SSH-manual (ROTATION-PRESTAGE.md step 3b).
# * ROLLBACK SOURCE = the OLD password lives on prod ONLY in
# /root/.pm2/dump.pm2 (NOT in any .env, NOT in the secrets registry)
#
# -----------------------------------------------------------------------------
# ROLLBACK SUMMARY (if anything FATALs after a new-pw ALTER)
# -----------------------------------------------------------------------------
# Revert the role to the OLD pw that prod apps still expect. The OLD pw is
# recoverable from /root/.pm2/dump.pm2 on Kamatera (the DATABASE_URL inside a
# pm2 env block). Re-ALTER dw_admin back to that OLD pw via the same
# stdin-piped pattern (NEVER -c), then pm2 restart. Detailed one-liner is in
# Step 4 (4R). Local Mac2 rollback = re-ALTER local role to OLD pw the same
# way. Because every step is verify-gated, you roll back the SINGLE step that
# failed, not the whole sequence.
#
# -----------------------------------------------------------------------------
# PRE-FLIGHT CHECKLIST (Step 1 — read-only, safe to run as-is)
# -----------------------------------------------------------------------------
# Run Step 1 first. All three must be GREEN before you mint anything.
# =============================================================================
# ─────────────────────────────────────────────────────────────────────────────
# STEP 1 — PRE-FLIGHT (read-only; nothing mutates)
# ─────────────────────────────────────────────────────────────────────────────
# 1a. Kamatera reachable over the canonical ssh alias?
# 1b. dump.pm2 (the OLD-pw rollback source) present + non-empty on prod?
# 1c. A CURRENT dw_unified pg_dump exists as a safety net (NOT 0-byte/stale)?
#
# PASTE:
:
ssh my-server 'echo "ssh OK as $(whoami)@$(hostname)"' \
&& ssh my-server 'ls -l /root/.pm2/dump.pm2 && test -s /root/.pm2/dump.pm2 && echo "dump.pm2 PRESENT+nonempty"' \
&& ssh my-server 'ls -lt /root/backups/db/*dw_unified*.{dump,sql,sql.gz} 2>/dev/null | head -3; echo "(confirm newest is today-ish and NOT 0 bytes)"'
# NOTE (verified 2026-06-19): dumps live in /root/backups/db/ (the daily 03:00
# nightly writes dw_unified_YYYY-MM-DD_0300.dump there; newest verified ~1.49GB).
#
# VERIFY GATE 1: All three lines printed, dump.pm2 is non-empty, and a fresh
# dw_unified dump exists. If ANY is missing — STOP. Do not mint a new pw with
# no rollback source and no safety-net dump. (If the dump is stale, take one
# first: ssh my-server "sudo -u postgres pg_dump -Fc dw_unified > /root/backups/db/dw_unified-$(date +%F).dump" )
# ═════════════════════════════════════════════════════════════════════════════
# ─────────────────────────────────────────────────────────────────────────────
# STEP 2 — MINT NEWPW + ALTER ROLE on BOTH prod AND local Mac2
# ─────────────────────────────────────────────────────────────────────────────
# Secret hygiene baked in:
# * NEWPW minted into a shell var at runtime — never typed, never a literal.
# * Only last4 ever printed: ${NEWPW: -4}
# * Every ALTER is PIPED OVER STDIN (printf | ssh "sudo -u postgres psql"),
# NOT psql -c "…" — so the password never appears in ps/argv on Mac2 OR
# on the remote (an -c arg shows in the remote process table).
# * Connect to db 'postgres' for the prod ALTER (always present; role-level op).
# * VERIFY by opening a FRESH connection with the NEW pw. NEVER probe the old
# pw — a failed auth with the old credential trips security-monitor.sh.
#
# PASTE (2a — mint; prints last4 only):
:
NEWPW="$(openssl rand -base64 30 | tr -d '/+=' | cut -c1-28)"
echo "NEWPW minted, last4=${NEWPW: -4} (full value is in the shell var ONLY)"
#
# PASTE (2b — ALTER on PROD via ssh, stdin-piped, no -c, connect db=postgres):
:
printf "ALTER ROLE dw_admin WITH PASSWORD '%s';\n" "$NEWPW" \
| ssh my-server "sudo -u postgres psql -d postgres -v ON_ERROR_STOP=1 -q -f -" \
&& echo "PROD ALTER applied (last4=${NEWPW: -4})"
#
# PASTE (2c — ALTER on LOCAL Mac2, stdin-piped, no -c):
:
printf "ALTER ROLE dw_admin WITH PASSWORD '%s';\n" "$NEWPW" \
| psql -d postgres -v ON_ERROR_STOP=1 -q -f - \
&& echo "LOCAL Mac2 ALTER applied (last4=${NEWPW: -4})"
#
# VERIFY GATE 2 — prove the NEW pw works with a fresh connection on EACH.
# (Uses PGPASSWORD only for these probe sub-shells; never echoes the value.)
# PASTE (2v):
:
echo "--- prod new-pw probe ---"
ssh my-server "PGPASSWORD='$NEWPW' psql -h 127.0.0.1 -U dw_admin -d dw_unified -tAc 'select current_user, 1'" \
&& echo "PROD new-pw: OK"
echo "--- local new-pw probe ---"
PGPASSWORD="$NEWPW" psql -h 127.0.0.1 -U dw_admin -d dw_unified -tAc "select current_user, 1" \
&& echo "LOCAL new-pw: OK"
#
# VERIFY GATE 2: BOTH probes return dw_admin|1 and print "OK". If either
# fails — STOP. Do NOT proceed to fan-out; the DB and the DSNs would diverge.
# (Roll back the side that failed via Step 4's 4R pattern with the OLD pw.)
# ═════════════════════════════════════════════════════════════════════════════
# ─────────────────────────────────────────────────────────────────────────────
# STEP 3 — REGISTER fan key PG_DW_ADMIN_PASSWORD + fan to LOCAL .env.local DSNs
# ─────────────────────────────────────────────────────────────────────────────
# This is the repair that has NEVER run for dw_admin. cli.js already has the
# DSN_REWRITE entry; it just has NO routes.json destinations, so first you
# MUST add the routes.json entry, THEN register the key so the fan can write.
#
# 3-PRE — ADD THE MISSING routes.json ENTRY (it does not exist today).
# Open ~/Projects/secrets-manager/routes.json and add this under "services":
# ───────────────────────────────────────────────────────────────────────────
# "PG_DW_ADMIN_PASSWORD": {
# "label": "Postgres dw_admin role password (local Mac2 + prod via 3b)",
# "destinations": [
# { "type": "env_file", "path": "~/Projects/Norma/.env.local" },
# { "type": "env_file", "path": "~/Projects/StudentLoanTracker/.env.local" },
# { "type": "env_file", "path": "~/Projects/ClawCoder/.env.local" },
# { "type": "env_file", "path": "~/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/.env.local" }
# ]
# }
# ───────────────────────────────────────────────────────────────────────────
# NOTE: no per-dest "dsn" is needed — cli.js's DSN_REWRITE map already maps
# PG_DW_ADMIN_PASSWORD -> { dsn:'DATABASE_URL', user:'dw_admin' }, so each
# env_file dest gets its DATABASE_URL password surgically rewritten (the rest
# of the DSN — host/port/db/user — is preserved). All 4 are LOCAL 127.0.0.1.
# The Desktop copy (~/Desktop/site-factory.env) is NOT a destination — it was
# RETIRED 2026-06-11 (DTD verdict A); cli.js no longer fans to it. Do not add
# it back. The master ~/Projects/secrets-manager/.env IS written by the fan.
#
# PASTE (3a — sanity that the JSON still parses after your edit):
:
node -e 'JSON.parse(require("fs").readFileSync(process.env.HOME+"/Projects/secrets-manager/routes.json","utf8")); console.log("routes.json parses OK")'
#
# PASTE (3b — register + fan the key, value over STDIN, never in argv):
:
printf "PG_DW_ADMIN_PASSWORD=%s\n" "$NEWPW" \
| node ~/Projects/secrets-manager/cli.js import-paste
# (import-paste reads KEY=VALUE from stdin → writes master .env, then fans to
# the 4 env_file destinations, surgically rewriting each DATABASE_URL pw.
# If a labeled key is required and import-paste balks, the equivalent is:
# node ~/Projects/secrets-manager/cli.js add PG_DW_ADMIN_PASSWORD — and
# paste the value when prompted, so it still never lands in argv/history.)
#
# VERIFY GATE 3 — the 4 DSNs now carry the NEW last4 and ZERO old pw remain.
# PASTE (3v):
:
echo "--- new last4 should appear in all 4 DATABASE_URL DSNs ---"
for f in ~/Projects/Norma/.env.local \
~/Projects/StudentLoanTracker/.env.local \
~/Projects/ClawCoder/.env.local \
"$HOME/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/.env.local"; do
grep -q "DATABASE_URL" "$f" \
&& printf "%s -> dsn-pw-last4=%s\n" "$f" \
"$(grep -m1 DATABASE_URL "$f" | sed -E 's#.*://[^:]+:([^@]+)@.*#\1#' | tail -c 5)"
done
echo "--- there must be NO remaining DW2024SecurePass in the 4 files ---"
grep -l "DW2024SecurePass" \
~/Projects/Norma/.env.local \
~/Projects/StudentLoanTracker/.env.local \
~/Projects/ClawCoder/.env.local \
"$HOME/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/.env.local" \
2>/dev/null && echo "!! OLD PW STILL PRESENT — STOP" || echo "clean: no DW2024SecurePass in the 4 .env.local"
#
# VERIFY GATE 3: all 4 DSNs show the NEW last4 (matches ${NEWPW: -4}) AND the
# "clean: no DW2024SecurePass" line printed. If any file still has the old pw,
# or a last4 mismatches — STOP and re-check the routes.json entry/destinations.
# ═════════════════════════════════════════════════════════════════════════════
# ─────────────────────────────────────────────────────────────────────────────
# STEP 4 — PROD-SIDE MANUAL FAN (ROTATION-PRESTAGE.md step 3b) + pm2 restart
# ─────────────────────────────────────────────────────────────────────────────
# The local fan (Step 3) does NOT touch prod app DSNs. Prod apps read their
# DATABASE_URL from pm2 env / their own .env on Kamatera. Rewrite those DSNs
# to the NEW pw, then pm2 restart with --update-env so the running procs pick
# it up. Keep the OLD-pw rollback one-liner (4R) on hand BEFORE you restart.
#
# PASTE (4a — surface what prod DSNs/apps exist; READ-ONLY, decide scope):
:
ssh my-server 'pm2 jlist | node -e "let d=JSON.parse(require(\"fs\").readFileSync(0));for(const p of d){const u=(p.pm2_env&&p.pm2_env.DATABASE_URL)||\"\";if(u.includes(\"dw_admin\"))console.log(p.name, u.replace(/:[^:@]+@/,\":***@\"))}"'
# (This lists each pm2 app whose DATABASE_URL uses dw_admin, password masked.
# Use the output to confirm exactly which apps need the new pw. Follow the
# per-app DSN-rewrite procedure in ROTATION-PRESTAGE.md step 3b — rewrite the
# prod-side DATABASE_URL for each, value piped/edited on the box, never in a
# ps-visible -c/argv. This runbook intentionally does NOT auto-generate the
# per-app sed; do it from the masked list above so you touch only real apps.)
#
# PASTE (4b — after DSNs rewritten on prod, restart with fresh env):
:
ssh my-server 'pm2 restart all --update-env && pm2 save'
#
# VERIFY GATE 4 — fleet is online and NOT auth-failing on the new pw.
# PASTE (4v):
:
echo "--- pm2 status (expect all online, 0 fresh restarts climbing) ---"
ssh my-server 'pm2 list'
echo "--- look for FATAL auth / password errors in recent logs ---"
ssh my-server 'pm2 logs --nostream --lines 40 2>/dev/null | grep -Ei "password authentication failed|FATAL.*dw_admin|role .dw_admin." || echo "no auth failures in tail"'
#
# VERIFY GATE 4: pm2 list shows all apps 'online' (no crash-loop / climbing
# restart counter) AND the log grep prints "no auth failures in tail". If apps
# are FATAL-auth crash-looping — ROLL BACK immediately with 4R below.
#
# ── 4R — ROLLBACK ONE-LINER (revert role to the OLD pw apps still expect) ──
# The OLD pw lives in /root/.pm2/dump.pm2. Recover it, then re-ALTER the role
# back to it (stdin-piped, never -c), then restart. PASTE only on a FATAL:
# OLDPW=$(ssh my-server "grep -o 'dw_admin:[^@]*@' /root/.pm2/dump.pm2 | head -1 | sed -E 's/dw_admin:(.*)@/\1/'")
# printf "ALTER ROLE dw_admin WITH PASSWORD '%s';\n" "$OLDPW" \
# | ssh my-server "sudo -u postgres psql -d postgres -v ON_ERROR_STOP=1 -q -f -"
# ssh my-server 'pm2 restart all --update-env'
# # then revert local Mac2 the same way and unwind Step 3 (re-fan OLD pw).
#
# ── CLEANUP (run once Step 4 verifies clean) ──
# PASTE:
:
unset NEWPW
echo "NEWPW unset. If you used 'set +o history', you may re-enable history now."
# Also: delete THIS runbook when done (see footer) — it is secret-adjacent.
# ═════════════════════════════════════════════════════════════════════════════
# ─────────────────────────────────────────────────────────────────────────────
# STEP 5 — DO-LAST (stub): secret-strip the ~45 hardcoded refs
# ─────────────────────────────────────────────────────────────────────────────
# ONLY after Steps 2–4 verify clean. Strip the ~45 source/doc refs that still
# contain DW2024SecurePass (env-first, no rotation, no deploy).
# → use the `secret-strip` skill (Skill: secret-strip) on ~/Projects
# Find current refs first: grep -rl "DW2024SecurePass" ~/Projects
# (Not expanded here — this is hygiene, not part of the live rotation.)
# ─────────────────────────────────────────────────────────────────────────────
# ─────────────────────────────────────────────────────────────────────────────
# STEP 6 — DO-LAST (stub): clear the compromised flag + remaining open creds
# ─────────────────────────────────────────────────────────────────────────────
# ONLY after 2–5 verify clean. The Kamatera "compromised" status is a
# HARDCODED literal — edit it from compromised to clean once rotation is done:
# → ~/cncp-starter/server.js : 3936 out.prod = { status: 'compromised' }
# Then address any other still-open creds surfaced during the strip.
# (Not expanded — gate behind a fully-verified rotation.)
# ─────────────────────────────────────────────────────────────────────────────
# =============================================================================
# WHEN DONE: rm THIS FILE — do NOT git-commit it.
# ----------------------------------------------------------------------------
# This .sh is secret-adjacent (it scaffolds a live-credential rotation). The
# repo's .gitignore already excludes .env* but does NOT exclude *.sh, so this
# file is git-trackable — never `git add` it. After a clean rotation:
# rm ~/Projects/secrets-manager/DW-ADMIN-ROTATION-RUNBOOK-2026-06-19.sh
# (or move it out of the repo entirely). Prefer rm over commit.
# =============================================================================