← back to Secrets Manager

add rotate-dw-admin-full.sh — complete the forward dw_admin rotation

8b0e8f06b6c96a23b890d812242700d3897e6f06 · 2026-06-03 21:03:03 -0700 · SteveStudio2

Console-ready (Steve-run, supervised) script that finishes what ROTATION-PRESTAGE.md
half-did on 2026-06-03: mint new pw (never echoed) -> ALTER dw_admin on Mac2 + prod
-> fan to Mac2 consumers via secrets cli -> in-place DSN rewrite on Kamatera with
.pre-rot.bak backups -> restart ONLY affected apps in batches <=6 (never restart all)
-> converge the 2 split-brain workers -> verify 0 auth failures. --dry-run validated.

Files touched

• A rotate-dw-admin-full.sh

Diff

commit 8b0e8f06b6c96a23b890d812242700d3897e6f06
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Wed Jun 3 21:03:03 2026 -0700

    add rotate-dw-admin-full.sh — complete the forward dw_admin rotation
    
    Console-ready (Steve-run, supervised) script that finishes what ROTATION-PRESTAGE.md
    half-did on 2026-06-03: mint new pw (never echoed) -> ALTER dw_admin on Mac2 + prod
    -> fan to Mac2 consumers via secrets cli -> in-place DSN rewrite on Kamatera with
    .pre-rot.bak backups -> restart ONLY affected apps in batches <=6 (never restart all)
    -> converge the 2 split-brain workers -> verify 0 auth failures. --dry-run validated.
---
 rotate-dw-admin-full.sh | 181 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 181 insertions(+)

diff --git a/rotate-dw-admin-full.sh b/rotate-dw-admin-full.sh
new file mode 100755
index 0000000..6ec6e86
--- /dev/null
+++ b/rotate-dw-admin-full.sh
@@ -0,0 +1,181 @@
+#!/usr/bin/env bash
+# rotate-dw-admin-full.sh — complete the dw_admin rotation (the FORWARD path).
+# Finishes what ROTATION-PRESTAGE.md half-did on 2026-06-03 (prod ALTER ran but the
+# fan + restart never did). Run FROM Mac2, in a console, supervised.
+#
+# WHAT IT DOES
+#   1. Mints a new 32-char password (never echoed, never in argv/history).
+#   2. ALTER ROLE dw_admin on Mac2 local PG AND prod Kamatera (each gated by y/N).
+#   3. Fans the new value to Mac2 consumers via the canonical secrets-manager CLI.
+#   4. On Kamatera: rewrites the dw_admin DSN password in-place wherever it lives
+#      (app .env / ecosystem / pm2 dump), backing each file up to *.pre-rot.bak,
+#      then restarts ONLY the apps actually affected, in batches of <=6
+#      (NEVER `pm2 restart all` — that OOM-kills the daemon: 186 apps).
+#   5. Starts the 2 split-brain workers (am-recrawl, vendor-review-worker-enhanced)
+#      which now carry the new pw, converging the fleet on one password.
+#   6. Verifies: 0 dw_admin auth failures, pm2 health, secrets-cli check.
+#
+# SECRET HYGIENE (per Steve's standing rules)
+#   The new pw exists only in: this process memory, the ALTER SQL piped via stdin,
+#   the secrets registry, and the rewritten consumer envs. It is NEVER printed
+#   (unless --show-once), never an argv, never in shell history.
+#
+# USAGE
+#   ./rotate-dw-admin-full.sh --dry-run     # print the plan, change NOTHING
+#   ./rotate-dw-admin-full.sh               # execute (confirms before each write)
+#   ./rotate-dw-admin-full.sh --show-once   # also print the new pw ONCE at the end
+#
+# ROLLBACK
+#   Every rewritten file has a sibling *.pre-rot.bak on Kamatera. To revert prod
+#   dw_admin to the prior pw, re-run the 2026-06-03 recovery (extract from any
+#   *.pre-rot.bak DSN, ALTER via stdin). Mac2 PG + registry similarly.
+
+set -uo pipefail
+
+DRY=0; SHOW=0
+for a in "$@"; do case "$a" in
+  --dry-run) DRY=1 ;; --show-once) SHOW=1 ;;
+  *) echo "unknown arg: $a" >&2; exit 2 ;;
+esac; done
+
+KAM="root@kamatera"
+SECRETS_CLI="$HOME/Projects/secrets-manager/cli.js"
+REG_KEY="PG_DW_ADMIN_PASSWORD"
+SPLIT_BRAIN=(am-recrawl vendor-review-worker-enhanced)
+
+say(){ printf '\n\033[1m%s\033[0m\n' "$*"; }
+confirm(){ [ "$DRY" = 1 ] && { echo "[dry-run] would prompt: $1"; return 0; }
+           read -r -p "$1 [y/N] " r; [ "$r" = y ] || { echo "aborted."; exit 1; }; }
+
+command -v openssl >/dev/null || { echo "need openssl" >&2; exit 1; }
+command -v node    >/dev/null || { echo "need node"    >&2; exit 1; }
+[ -f "$SECRETS_CLI" ] || { echo "missing $SECRETS_CLI" >&2; exit 1; }
+
+say "dw_admin FULL rotation $([ "$DRY" = 1 ] && echo '(DRY RUN — no changes)')"
+
+# ---- 1. mint -------------------------------------------------------------
+NEW_PG="$(openssl rand -base64 48 | tr -dc 'A-Za-z0-9' | head -c 32)"
+[ "${#NEW_PG}" -ge 20 ] || { echo "pw-gen failed" >&2; exit 1; }
+echo "[1] minted a new 32-char password (hidden)"
+
+# Emit the ALTER statement on stdout, dollar-quoted so any char is safe.
+alter_sql(){ python3 - "$NEW_PG" <<'PY'
+import sys
+pw=sys.argv[1]; tag="$dwrot$"
+assert tag not in pw, "tag collision — regenerate"
+print(f"ALTER ROLE dw_admin PASSWORD {tag}{pw}{tag};")
+PY
+}
+
+# ---- 2. Mac2 local PG ----------------------------------------------------
+say "[2] ALTER ROLE dw_admin on Mac2 local PG"
+if [ "$DRY" = 1 ]; then
+  echo "[dry-run] alter_sql | psql -d postgres -f -   (superuser = \$USER; adjust -U if needed)"
+else
+  confirm "  apply to Mac2 local PG (psql -d postgres as \$USER)?"
+  alter_sql | psql -d postgres -v ON_ERROR_STOP=1 -f - && echo "  Mac2: ALTER ok"
+fi
+
+# ---- 3. prod Kamatera PG -------------------------------------------------
+say "[3] ALTER ROLE dw_admin on prod Kamatera"
+if [ "$DRY" = 1 ]; then
+  echo "[dry-run] alter_sql | ssh $KAM 'sudo -n -u postgres psql -d postgres -f -'"
+else
+  confirm "  apply to PROD Kamatera?"
+  alter_sql | ssh "$KAM" "sudo -n -u postgres psql -d postgres -v ON_ERROR_STOP=1 -f -" && echo "  prod: ALTER ok"
+fi
+
+# ---- 4. fan to Mac2 consumers via secrets-manager ------------------------
+say "[4] route new pw to Mac2 consumers (secrets-manager import-paste, stdin)"
+if [ "$DRY" = 1 ]; then
+  echo "[dry-run] printf '$REG_KEY=<new>' | node $SECRETS_CLI import-paste"
+else
+  printf '%s=%s\n' "$REG_KEY" "$NEW_PG" | node "$SECRETS_CLI" import-paste && echo "  registry + Mac2 .envs updated"
+fi
+
+# ---- 5. fan to Kamatera (in-place DSN rewrite) + targeted batched restart -
+say "[5] fan to prod Kamatera (rewrite dw_admin DSNs in place, restart affected apps <=6/batch)"
+if [ "$DRY" = 1 ]; then
+  echo "[dry-run] push pw via stdin to remote rewriter; restart only apps whose cwd contains a rewritten file"
+else
+  confirm "  rewrite prod app envs + restart affected dw_admin apps (batched)?"
+  printf '%s' "$NEW_PG" | ssh "$KAM" 'bash -s' <<'REMOTE'
+set -uo pipefail
+NEW_PG="$(cat)"            # pw arrives on stdin — never in argv
+export NEW_PG
+# 5a. rewrite every file carrying a dw_admin DSN; record which dirs changed.
+CHANGED_DIRS="$(python3 - <<'PY'
+import os,re,sys
+new=os.environ["NEW_PG"]
+roots=["/root/DW-Agents","/root/public-projects","/root/Projects","/etc/environment","/root/.pm2/dump.pm2"]
+pat=re.compile(r'(postgres(?:ql)?://dw_admin:)[^@]+(@)')
+files=set()
+for r in roots:
+    if os.path.isfile(r): files.add(r)
+    elif os.path.isdir(r):
+        for dp,_,fs in os.walk(r):
+            if "node_modules" in dp or "/.git/" in dp: continue
+            for f in fs:
+                if f.endswith((".env",".local",".js",".cjs",".json")): files.add(os.path.join(dp,f))
+dirs=set()
+for f in files:
+    try: s=open(f,encoding="utf-8",errors="replace").read()
+    except Exception: continue
+    if "dw_admin:" not in s: continue
+    ns=pat.sub(r'\g<1>'+new+r'\g<2>', s)
+    if ns!=s:
+        try:
+            open(f+".pre-rot.bak","w").write(s); open(f,"w").write(ns)
+            dirs.add(os.path.dirname(f))
+        except Exception as e:
+            sys.stderr.write(f"skip {f}: {e}\n")
+sys.stderr.write(f"rewrote files in {len(dirs)} dirs\n")
+print("\n".join(sorted(dirs)))
+PY
+)"
+# 5b. restart ONLY online apps whose cwd is under a changed dir, in batches of 6.
+mapfile -t TARGETS < <(pm2 jlist | python3 -c '
+import sys,json,os
+dirs=[d for d in os.environ.get("CHG","").splitlines() if d]
+for p in json.load(sys.stdin):
+    e=p["pm2_env"]
+    if e.get("status")!="online": continue
+    cwd=e.get("pm_cwd","") or ""
+    if any(cwd==d or cwd.startswith(d+"/") or d.startswith(cwd+"/") for d in dirs):
+        print(p["name"])
+' CHG="$CHANGED_DIRS")
+echo "affected apps: ${#TARGETS[@]}"
+batch=()
+for a in "${TARGETS[@]}"; do
+  batch+=("$a")
+  if [ "${#batch[@]}" -ge 6 ]; then pm2 restart "${batch[@]}" --update-env >/dev/null 2>&1; sleep 4; batch=(); fi
+done
+[ "${#batch[@]}" -gt 0 ] && pm2 restart "${batch[@]}" --update-env >/dev/null 2>&1
+# 5c. converge the split-brain workers (now carry the new pw) + persist.
+pm2 start am-recrawl vendor-review-worker-enhanced >/dev/null 2>&1 || true
+pm2 save >/dev/null 2>&1
+echo "remote fan + targeted restart done"
+REMOTE
+fi
+
+# ---- 6. verify -----------------------------------------------------------
+say "[6] verify"
+if [ "$DRY" = 1 ]; then
+  echo "[dry-run] would: check 0 dw_admin auth failures, pm2 health, secrets-cli check"
+else
+  ssh "$KAM" 'bash -s' <<'REMOTE'
+LOG=/var/log/postgresql/postgresql-14-main.log
+sleep 12
+f=$(awk -v c="$(date -u -d "12 seconds ago" "+%Y-%m-%d %H:%M:%S")" '/authentication failed for user "dw_admin"/ && $1" "$2 > c {n++} END{print n+0}' "$LOG" 2>/dev/null)
+echo "  dw_admin auth failures (last 12s): $f  (want 0)"
+pm2 jlist | python3 -c "import sys,json;from collections import Counter;print('  pm2:',dict(Counter(p['pm2_env'].get('status') for p in json.load(sys.stdin))))"
+sudo -n -u postgres psql -d dw_unified -tAc "SELECT '  dw_unified rows: '||count(*) FROM products;" 2>&1 | head -1
+REMOTE
+  node "$SECRETS_CLI" check 2>&1 | grep -iE "dw_admin|PG_DW|FAIL|VALID" | head -5 || true
+fi
+
+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)"
+[ "$SHOW" = 1 ] && printf '  new dw_admin pw (record in your vault, then clear): %s\n' "$NEW_PG"
+unset NEW_PG

← 4e224ba secrets viewer: add read-only /rotation page (renders ROTATI  ·  back to Secrets Manager  ·  fix secrets fan: top-level routes invisible (22) + dw_admin 41aafea →