Add idempotent fleet-wide fix-all-stale dw_admin password repair script (dry-run default, tests candidate pw before any write/restart)

ffc4b860098d365b3d87509ae1893a3101f2b794 · 2026-06-15 07:57:56 -0700 · Steve Abrams
Files touched

A scripts/fix-all-stale-dw-admin.sh
Diff

commit ffc4b860098d365b3d87509ae1893a3101f2b794
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jun 15 07:57:56 2026 -0700

    Add idempotent fleet-wide fix-all-stale dw_admin password repair script (dry-run default, tests candidate pw before any write/restart)
---
 scripts/fix-all-stale-dw-admin.sh | 152 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 152 insertions(+)

diff --git a/scripts/fix-all-stale-dw-admin.sh b/scripts/fix-all-stale-dw-admin.sh
new file mode 100755
index 0000000..c946315
--- /dev/null
+++ b/scripts/fix-all-stale-dw-admin.sh
@@ -0,0 +1,152 @@
+#!/usr/bin/env bash
+# fix-all-stale-dw-admin.sh — idempotent fleet-wide repair of the stale dw_admin
+# Postgres password across pm2 services on Kamatera.
+#
+# Root problem (2026-06): the dw_admin password was rotated, but many service
+# .env files still carry the OLD password, so every DB query fails with
+# `password authentication failed for user "dw_admin"`. (First seen breaking
+# the Pairs-Well coordinates widget; same rot logged in memory
+# dw-unified-backup-broken.md — nightly pg_dump 0-byte since 2026-06-13.)
+#
+# This script: for every pm2 process whose .env DATABASE_URL uses dw_admin,
+#   1. test the CURRENT url with `SELECT 1` — if it AUTHENTICATES, skip (idempotent);
+#   2. else build a candidate url with the CANONICAL password from secrets-manager
+#      and test THAT with `SELECT 1`;
+#   3. ONLY if the candidate authenticates: back up .env, swap *only* the password
+#      segment, `pm2 restart NAME --update-env`, then re-verify (psql + healthz).
+#   4. if neither the current nor the candidate authenticates, report STILL-FAILING
+#      and touch nothing (it's not a stale-password problem — host down, db gone,
+#      or canon itself wrong).
+#
+# DRY-RUN by default. Pass --apply to actually write + restart.
+# The password is NEVER echoed; it only ever lives in process env vars.
+#
+# Usage (run ON Kamatera):
+#   bash fix-all-stale-dw-admin.sh            # dry-run: show what's stale + the plan
+#   bash fix-all-stale-dw-admin.sh --apply    # repair every stale service, one at a time
+
+set -u
+
+APPLY=0
+[ "${1:-}" = "--apply" ] && APPLY=1
+
+SECRETS_ENV="${SECRETS_ENV:-/root/Projects/secrets-manager/.env}"
+# Fall back to the Mac2 path if the Kamatera one isn't present.
+[ -f "$SECRETS_ENV" ] || SECRETS_ENV="$HOME/Projects/secrets-manager/.env"
+
+red()  { printf '\033[31m%s\033[0m\n' "$*"; }
+grn()  { printf '\033[32m%s\033[0m\n' "$*"; }
+ylw()  { printf '\033[33m%s\033[0m\n' "$*"; }
+mask() { sed -E 's#(://[^:]+:)[^@]+(@)#\1***\2#g'; }
+
+command -v jq   >/dev/null 2>&1 || { red "jq not found"; exit 1; }
+command -v psql >/dev/null 2>&1 || { red "psql not found"; exit 1; }
+command -v pm2  >/dev/null 2>&1 || { red "pm2 not found"; exit 1; }
+
+CANON="$(grep -m1 '^DW_ADMIN_PG_PASSWORD=' "$SECRETS_ENV" 2>/dev/null | cut -d= -f2-)"
+if [ -z "${CANON:-}" ]; then
+  red "FATAL: DW_ADMIN_PG_PASSWORD not found in $SECRETS_ENV — cannot proceed."
+  exit 1
+fi
+export CANON
+
+if [ "$APPLY" = "1" ]; then ylw "=== MODE: APPLY (will edit .env + pm2 restart stale services) ==="
+else                        grn  "=== MODE: DRY-RUN (no writes) — pass --apply to repair ==="; fi
+echo "canonical pw source: $SECRETS_ENV  (loaded, value hidden)"
+echo
+
+# Build "<name>\t<cwd>" for every pm2 process (one per line).
+mapfile -t ROWS < <(pm2 jlist 2>/dev/null | jq -r '.[] | [.name, .pm2_env.pm_cwd] | @tsv')
+
+OK=0; FIXED=0; WOULDFIX=0; SKIP=0; FAILED=0; SEEN_ENV=""
+
+# Swap ONLY the password between "://dw_admin:" and the next "@". Password is
+# treated as a literal string (no regex interpretation) by reading it from env.
+build_url() {  # $1 = old url ; uses $CANON
+  OLDURL="$1" python3 - <<'PY'
+import os, re, sys
+url = os.environ["OLDURL"]; pw = os.environ["CANON"]
+m = re.match(r'^(.*?://dw_admin:)([^@]*)(@.*)$', url, re.S)
+if not m:
+    sys.exit(3)
+sys.stdout.write(m.group(1) + pw + m.group(3))
+PY
+}
+
+write_env() {  # $1 = envfile ; $2 = new url
+  F="$1" NEWURL="$2" python3 - <<'PY'
+import os
+f = os.environ["F"]; nl = os.environ["NEWURL"]
+out = []
+for ln in open(f).read().splitlines():
+    out.append("DATABASE_URL=" + nl if ln.startswith("DATABASE_URL=") else ln)
+open(f, "w").write("\n".join(out) + "\n")
+PY
+}
+
+healthz_of() {  # $1 = cwd -> echo HEALTH_URL from .deploy.conf if present
+  [ -f "$1/.deploy.conf" ] && grep -m1 '^HEALTH_URL=' "$1/.deploy.conf" | cut -d= -f2-
+}
+
+for row in "${ROWS[@]}"; do
+  NAME="${row%%$'\t'*}"
+  CWD="${row#*$'\t'}"
+  ENVF="$CWD/.env"
+  [ -f "$ENVF" ] || continue
+
+  URL="$(grep -m1 '^DATABASE_URL=' "$ENVF" 2>/dev/null | cut -d= -f2-)"
+  [ -n "$URL" ] || continue
+  case "$URL" in *dw_admin:*) ;; *) continue ;; esac   # only password'd dw_admin urls
+
+  # de-dupe: several pm2 apps may share one .env
+  case "$SEEN_ENV" in *"|$ENVF|"*) continue ;; esac
+  SEEN_ENV="$SEEN_ENV|$ENVF|"
+
+  # 1) current password good?  -> idempotent skip
+  if psql "$URL" -tAc 'SELECT 1' >/dev/null 2>&1; then
+    grn "OK     $NAME  ($ENVF)"; OK=$((OK+1)); continue
+  fi
+
+  # 2) build + test the canonical-password candidate
+  NEWURL="$(build_url "$URL")" || { red "SKIP   $NAME  — unparseable DATABASE_URL shape"; SKIP=$((SKIP+1)); continue; }
+  if [ "$NEWURL" = "$URL" ]; then
+    red "FAIL   $NAME  — already on canonical pw but auth still fails (host/db issue, not stale pw)"; FAILED=$((FAILED+1)); continue
+  fi
+  if ! psql "$NEWURL" -tAc 'SELECT 1' >/dev/null 2>&1; then
+    red "FAIL   $NAME  — canonical pw ALSO fails (host down / db gone / canon wrong) — leaving untouched"; FAILED=$((FAILED+1)); continue
+  fi
+
+  # 3) candidate authenticates — stale password confirmed
+  if [ "$APPLY" = "0" ]; then
+    ylw "STALE  $NAME  — WOULD fix ($ENVF) + pm2 restart"; WOULDFIX=$((WOULDFIX+1)); continue
+  fi
+
+  BAK="$ENVF.bak.$(date +%Y%m%d-%H%M%S)"
+  cp "$ENVF" "$BAK"
+  write_env "$ENVF" "$NEWURL"
+  # verify the write took and now authenticates before bouncing the process
+  CHK="$(grep -m1 '^DATABASE_URL=' "$ENVF" | cut -d= -f2-)"
+  if ! psql "$CHK" -tAc 'SELECT 1' >/dev/null 2>&1; then
+    red "ERROR  $NAME  — post-write psql failed; rolling back"; cp "$BAK" "$ENVF"; FAILED=$((FAILED+1)); continue
+  fi
+  pm2 restart "$NAME" --update-env >/dev/null 2>&1
+  sleep 2
+  HU="$(healthz_of "$CWD")"
+  if [ -n "$HU" ]; then
+    CODE="$(curl -s -o /dev/null -m 15 -w '%{http_code}' "$HU")"
+    if [ "$CODE" = "200" ]; then grn "FIXED  $NAME  — restarted, healthz 200 ($BAK)"; FIXED=$((FIXED+1))
+    else ylw "FIXED? $NAME  — restarted + db OK, but healthz=$CODE ($HU) — check app log"; FIXED=$((FIXED+1)); fi
+  else
+    grn "FIXED  $NAME  — restarted, db auth OK (no HEALTH_URL to probe) ($BAK)"; FIXED=$((FIXED+1))
+  fi
+done
+
+echo
+echo "──────── summary ────────"
+echo "already OK : $OK"
+[ "$APPLY" = "0" ] && echo "stale      : $WOULDFIX  (run with --apply to fix)" || echo "fixed      : $FIXED"
+echo "failed     : $FAILED  (host/db/canon issue — NOT touched)"
+echo "skipped    : $SKIP  (unparseable url shape)"
+[ "$FAILED" -gt 0 ] && ylw "Note: 'failed' services have a non-password problem — investigate before any further action."
+[ "$APPLY" = "0" ] && [ "$WOULDFIX" -gt 0 ] && echo "Next: re-run with  --apply  to repair the $WOULDFIX stale service(s), one at a time."
+exit 0
← 6d446b9 Add per-site favicon (kills /favicon.ico 404) · back to Dw Pairs Well · Add /api/similar (visually-similar designs) + similar-design 5e32b37 →