swf-monitor/scripts/setup-postgres-mcp.sh

0001 #!/usr/bin/env bash
0002 #
0003 # setup-postgres-mcp.sh — opt-in, per-user: give YOUR Claude Code read-only SQL
0004 # access to the swfdb system database through the Postgres MCP server.
0005 #
0006 # The postgres-mcp tool is installed system-wide (/usr/local/bin); this script
0007 # only adds, for the running user, a private 0600 credential and a user-scope
0008 # Claude Code MCP entry. It changes nothing shared, restarts no service, and does
0009 # nothing for anyone who does not run it. The swf-monitor bot never sees it.
0010 #
0011 # See docs/POSTGRES_MCP.md.
0012 #
0013 # Usage:
0014 #   scripts/setup-postgres-mcp.sh            # register for you
0015 #   scripts/setup-postgres-mcp.sh --remove   # undo (leaves the system tool)
0016 #
0017 set -euo pipefail
0018
0019 NAME=postgres-swf
0020 SERVER=/usr/local/bin/postgres-mcp
0021 CONN="postgresql://swf_ro@localhost:5432/swfdb"
0022 SHARED_CRED=/data/swf-shared/swf_ro.pgpass
0023 USER_PGPASS="$HOME/.config/swf/swf_ro.pgpass"
0024
0025 # ---- teardown ---------------------------------------------------------------
0026 if [[ "${1:-}" == "--remove" ]]; then
0027   claude mcp remove -s user "$NAME" 2>/dev/null || true
0028   rm -f "$USER_PGPASS"
0029   echo "Removed: $NAME user-scope MCP entry and $USER_PGPASS (system tool left in place)."
0030   exit 0
0031 fi
0032
0033 # ---- setup ------------------------------------------------------------------
0034 command -v claude >/dev/null 2>&1 || { echo "ERROR: claude CLI not found." >&2; exit 1; }
0035 [[ -x "$SERVER" ]] || {
0036   echo "ERROR: $SERVER not found. An admin installs it system-wide once — see docs/POSTGRES_MCP.md." >&2
0037   exit 1; }
0038 [[ -r "$SHARED_CRED" ]] || {
0039   echo "ERROR: $SHARED_CRED not readable. Ask for swf_ro access." >&2; exit 1; }
0040
0041 # Private 0600 credential — a copy of the shared read-only line.
0042 # libpq refuses a PGPASSFILE that is group/world-readable, so we copy rather than point.
0043 mkdir -p "$(dirname "$USER_PGPASS")"
0044 install -m 600 "$SHARED_CRED" "$USER_PGPASS"
0045
0046 # Register at USER scope: personal, never committed, bot-invisible, cwd-independent.
0047 # --access-mode restricted = read-only plus guards against heavy/unsafe queries.
0048 claude mcp remove -s user "$NAME" 2>/dev/null || true
0049 claude mcp add -s user "$NAME" -e PGPASSFILE="$USER_PGPASS" -- \
0050   "$SERVER" --access-mode restricted "$CONN"
0051
0052 cat <<'EOF'
0053
0054 Registered postgres-swf (read-only, swfdb) at user scope.
0055 NOTE: a mid-session `claude mcp add` is not live until you RESTART Claude Code.
0056 After restart, `/mcp` should list postgres-swf; confirm with `list_schemas` and:
0057   SELECT version();
0058 EOF