swf-monitor/docs/POSTGRES_MCP.md

0001 # Postgres MCP — read-only swfdb access for Claude Code
0002
0003 A Claude Code session occasionally needs to read the `swfdb` system database
0004 directly — to answer a question no purpose-built tool covers. The Postgres MCP
0005 server ([crystaldba/postgres-mcp](https://github.com/crystaldba/postgres-mcp))
0006 provides that as read-only SQL plus schema-inspection tools. It complements the
0007 curated MCP tools (`swf_*`, `pcs_*`, `panda_*`); it does not replace them.
0008
0009 This is for **Claude Code**, not the swf-monitor bot. The bot loads its own fixed
0010 server list; this server is never visible to it.
0011
0012 The server binary is installed system-wide; each user opts in with one script that
0013 touches only their own environment.
0014
0015 ## Opt in
0016
0017 ```bash
0018 swf-monitor/scripts/setup-postgres-mcp.sh        # register for you
0019 # ... restart Claude Code ...
0020 ```
0021
0022 The script copies the read-only credential to a private `~/.config/swf/swf_ro.pgpass`
0023 (mode 0600) and registers the server with Claude Code at **user scope**. The only
0024 prerequisite is the `claude` CLI; the `postgres-mcp` tool is already system-wide.
0025 A mid-session registration is not live until Claude Code restarts; afterwards
0026 `/mcp` lists `postgres-swf`. Confirm with `list_schemas` and `SELECT version();`.
0027
0028 ## What it touches — and what it doesn't
0029
0030 Running the script affects only the running user: one private credential file and
0031 one user-scope Claude Code entry. It is opt-in — a user who does not run it is
0032 wholly unaffected — and reversible:
0033
0034 ```bash
0035 swf-monitor/scripts/setup-postgres-mcp.sh --remove
0036 ```
0037
0038 Nothing shared is reconfigured. No service restarts. swfdb authentication for the
0039 monitor, bots, agents, and deploy is unchanged: the MCP is one additional
0040 read-only client, not a change to how anyone else connects.
0041
0042 ## Under the hood
0043
0044 - **Role `swf_ro`** on swfdb — `SELECT`-only, `default_transaction_read_only = on`,
0045   `statement_timeout = 15s`. Created once as shared infrastructure; serves every
0046   opt-in user. Default privileges are scoped to the migration role so future
0047   tables stay readable. The role is purely additive — it alters no existing grant.
0048 - **Credential** at `/data/swf-shared/swf_ro.pgpass`, in libpq `.pgpass` format.
0049   The setup script copies it to a private 0600 file per user; it is never embedded
0050   in the Claude Code config.
0051 - **`--access-mode restricted`** — read-only, and it blocks heavy or unsafe
0052   queries. Use `unrestricted` only with a real write need; for inspecting the live
0053   swfdb, restricted is correct.
0054
0055 ## Admin: one-time system install
0056
0057 `uv` and the `postgres-mcp` server are installed system-wide so opt-in users need
0058 only the registration step. On `swf-testbed` this was done once:
0059
0060 ```bash
0061 # uv (static binary) into /usr/local/bin
0062 sudo cp ~/.local/bin/uv ~/.local/bin/uvx /usr/local/bin/
0063
0064 # postgres-mcp as a tool whose venv + managed Python live in a world-readable /opt
0065 sudo env UV_TOOL_DIR=/opt/uv/tools UV_TOOL_BIN_DIR=/usr/local/bin \
0066         UV_PYTHON_INSTALL_DIR=/opt/uv/python \
0067         /usr/local/bin/uv tool install postgres-mcp
0068 sudo chmod -R a+rX /opt/uv         # so non-root users can execute it
0069 ```
0070
0071 The `UV_PYTHON_INSTALL_DIR` matters: without it uv places its managed Python under
0072 root's home, where other users cannot execute it.
0073
0074 ## Scope
0075
0076 This points at `swfdb` on `localhost` — the swf-testbed host, where the database is
0077 local. PanDA and Rucio are deliberately out of scope: PanDA is read through its own
0078 curated tools, Rucio through its API, not direct SQL.
0079
0080 `pg_stat_statements` is not installed on swfdb, so the workload/index-advisor tools
0081 (`get_top_queries`, `analyze_workload_indexes`) are inert. Installing it would
0082 require a database restart and is intentionally not done.