`rotate-dw-admin-full.sh`

182 lines
#!/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