`scripts/rotate-pg-grant.sh`

164 lines
#!/usr/bin/env bash
# rotate-pg-grant.sh — Lane B PG rotation for Grant only.
#
# Creates (or updates) a scoped PG user `grant_app_user` with permissions only
# on the `grant_app` database, swaps Grant's .env.local DATABASE_URL to use it,
# and registers the password in ~/Projects/secrets-manager/.env via the
# canonical secrets-manager CLI. Leaves shared dw_admin untouched.
#
# Usage:
#   ./rotate-pg-grant.sh
#   (the script will prompt for the new password via `read -s`, no echo;
#    nothing lands in shell history or argv)
#
# Per Steve's standing rules:
#   - Never $-interpolate secrets into bash command lines (we use stdin pipes
#     into psql + python heredocs)
#   - Use /secrets skill for routing (we call cli.js add at the end)
#   - Local-only — Kamatera mirror not touched

set -euo pipefail

GRANT_DIR="${GRANT_DIR:-$HOME/Projects/Grant}"
SECRETS_CLI="$HOME/Projects/secrets-manager/cli.js"

if [[ ! -f "$GRANT_DIR/.env.local" ]]; then
  echo "ERROR: $GRANT_DIR/.env.local does not exist — aborting." >&2
  exit 1
fi

echo
echo "Lane B: scoped Grant PG user rotation"
echo "  - creates/updates grant_app_user (login, scoped to grant_app DB)"
echo "  - updates $GRANT_DIR/.env.local DATABASE_URL"
echo "  - registers in secrets-manager"
echo "  - leaves shared dw_admin untouched"
echo

# Read password silently — no echo to terminal, no entry in shell history.
# Suggested: 32+ chars, alphanumeric + symbols, generate with `openssl rand -base64 32`
read -s -p "New password for grant_app_user (input hidden): " NEW_PG
echo
read -s -p "Confirm: " CONFIRM
echo
if [[ "$NEW_PG" != "$CONFIRM" ]]; then
  echo "Passwords do not match — aborting." >&2
  unset NEW_PG CONFIRM
  exit 1
fi
unset CONFIRM

if [[ ${#NEW_PG} -lt 16 ]]; then
  echo "Password must be at least 16 chars — aborting." >&2
  unset NEW_PG
  exit 1
fi

# Step 1 — apply role change via psql, password sent on stdin (never in argv).
# psql reads SQL from stdin via `-f -`; the SQL itself contains the password
# but the SQL never lands in any shell argv.
echo "Applying PG role change..."
NEW_PG="$NEW_PG" python3 - <<'PYEOF'
import os, subprocess, sys, secrets
pw = os.environ['NEW_PG']
# audit P2-7: wrap the password in a RANDOM dollar-quote tag instead of a
# single-quoted f-string literal. A password containing a single quote,
# backslash, or "$$" could otherwise break out of the literal and inject SQL.
# The tag is random and asserted absent from the password, so the literal
# boundary is unforgeable — and the password still never touches argv (stdin).
_tag = 'pw_' + secrets.token_hex(8)
if _tag in pw:
    print('dollar-quote tag collision (1-in-2^64) — re-run', file=sys.stderr)
    sys.exit(1)
_lit = f"${_tag}${pw}${_tag}$"
sql = f"""
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grant_app_user') THEN
    CREATE ROLE grant_app_user WITH LOGIN PASSWORD {_lit};
  ELSE
    ALTER ROLE grant_app_user WITH LOGIN PASSWORD {_lit};
  END IF;
END
$$;
GRANT CONNECT ON DATABASE grant_app TO grant_app_user;
\\c grant_app
GRANT USAGE ON SCHEMA public TO grant_app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO grant_app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO grant_app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO grant_app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO grant_app_user;
"""
r = subprocess.run(
    ['psql', '-d', 'postgres', '-v', 'ON_ERROR_STOP=1', '-X'],
    input=sql, text=True, capture_output=True,
)
if r.returncode != 0:
    print('psql failed:', r.stderr, file=sys.stderr)
    sys.exit(1)
print(r.stdout.strip() or 'role + grants OK')
PYEOF

# Step 2 — verify the new user can actually connect
echo "Verifying connection..."
NEW_PG="$NEW_PG" python3 - <<'PYEOF'
import os, subprocess, sys
pw = os.environ['NEW_PG']
url = f"postgresql://grant_app_user:{pw}@127.0.0.1:5432/grant_app"
r = subprocess.run(
    ['psql', url, '-c', 'SELECT current_user, current_database()'],
    capture_output=True, text=True,
)
if r.returncode != 0:
    print('verify failed:', r.stderr, file=sys.stderr)
    sys.exit(1)
print(r.stdout.strip())
PYEOF

# Step 3 — update Grant's .env.local DATABASE_URL
echo "Updating $GRANT_DIR/.env.local..."
NEW_PG="$NEW_PG" GRANT_DIR="$GRANT_DIR" python3 - <<'PYEOF'
import os
pw = os.environ['NEW_PG']
env = os.path.join(os.environ['GRANT_DIR'], '.env.local')
new_url = f"postgresql://grant_app_user:{pw}@127.0.0.1:5432/grant_app"

with open(env) as f:
    lines = f.readlines()

found = False
for i, line in enumerate(lines):
    if line.startswith('DATABASE_URL='):
        lines[i] = f'DATABASE_URL={new_url}\n'
        found = True
        break
if not found:
    lines.append(f'\nDATABASE_URL={new_url}\n')

# atomic write
tmp = env + '.tmp'
with open(tmp, 'w') as f:
    f.writelines(lines)
os.chmod(tmp, 0o600)
os.replace(tmp, env)
print(f'wrote DATABASE_URL → {env}')
PYEOF

# Step 4 — register in secrets-manager (canonical routing destination)
if [[ -f "$SECRETS_CLI" ]]; then
  echo "Registering in secrets-manager..."
  printf '%s' "GRANT_APP_DB_PASSWORD=$NEW_PG" | node "$SECRETS_CLI" import-paste 2>&1 | tail -5 || \
    echo "  (secrets-manager add failed — manual entry in master .env may be needed)"
else
  echo "  (secrets-manager CLI not found at $SECRETS_CLI — skipping registry)"
fi

# Final cleanup
unset NEW_PG

echo
echo "✅ Done. Grant now connects as grant_app_user (scoped to grant_app DB)."
echo "   Shared dw_admin password is unchanged."
echo "   Old DW2024SecurePass is still on disk in Time Machine snapshots —"
echo "   schedule a global dw_admin rotation in a maintenance window when ready."