sw-postgres-mcp

Safe-write Postgres MCP server

An agent can read and modify a database without being able to cause an unrecoverable accident. The differentiator is the safety layer, not the tool coverage: 8 MCP tools, 3 of them read-only, 4 write tools that only ever *preview* a change, and one execute_plan that commits a previewed change and nothing else.

Architecture

 Claude (agent)

│ MCP stdio (tools/call) ▼ sw-postgres-mcp │ ├─ describe_schema / query / explain_plan ──► readonly pool ──► Postgres "readonly" role (SELECT only) │ ├─ delete_rows / insert_rows / update_rows / run_migration │ │ │ ▼ │ TwoPhaseWrite.preview() │ BEGIN → run the real statement → ROLLBACK │ DML (delete_rows/insert_rows/update_rows): capture exact RETURNING count + sample │ DDL (run_migration): no RETURNING to capture — reports 0 affected rows, a target │ table/schema extracted from the statement text, and always │ goes to "awaiting_approval" regardless of that row count │ └─► plan_token = sha256(statement + params) ("statementFingerprint") │ ├─ affected_rows ≤ approvalRequiredAboveRows → status: "previewed" │ ├─ affected_rows > approvalRequiredAboveRows → status: "awaiting_approval" │ └─ affected_rows > hardMaxRows → refused outright, no token issued │ (run_migration ignores both thresholds — every DDL preview is "awaiting_approval") │ ├─ execute_plan(plan_token, statement, params) ──► writer pool ──► Postgres "writer" role (DML + DDL) │ re-derives the fingerprint from what was passed back and refuses on any mismatch │ (STATEMENT_MISMATCH) or an affected-row-set that changed since preview (ROWSET_CHANGED) │ └─ every preview / approval / execution / rejection / refusal ──► mcp_audit.log (INSERT-only grant; UPDATE/DELETE/TRUNCATE revoked — see "Audit log" below)

an "awaiting_approval" plan surfaces at: localhost approval UI — http://127.0.0.1:4319/?token= (bound to 127.0.0.1, human-only; the token is printed once on stderr at startup and every route requires it as Authorization: Bearer or ?token= — see "Localhost approval UI" below) approve() / reject() are called directly on the shared TwoPhaseWrite instance — never exposed as an MCP tool the agent itself can reach

Two Postgres connection pools, each authenticated as a distinct role (see Threat model below): readonly for describe_schema/query/explain_plan, writer for the four write-preview tools and execute_plan. All 4 write tools — delete_rows, insert_rows, update_rows, run_migration — share one core, TwoPhaseWrite (src/writeCore.ts): every one of them previews inside a transaction that always rolls back, then requires a separate execute_plan call with the exact plan token to actually commit. There is no 5th write tool and no tool that skips the preview step — execute_plan is the only thing in this server that commits anything, and it only ever replays a statement that was already previewed. See Tools below for what each tool takes and returns, and Two-phase writes for the mechanics.

Threat model

The risk here is not SQL injection. delete_rows, insert_rows, and update_rows take structured arguments — table, where + parameterized params, a set object — and every value in those structured inputs goes through $n placeholders, never string concatenation (see update_rows's note on this in Tools). run_migration is different: DDL can't be parameterized the way DML values can, so it sends its raw agent-supplied statement directly to Postgres, the same way query/explain_plan already handle raw SQL — its safety comes not from parameterization but from always requiring human approval regardless of row count (see Tools), never from an $n-placeholder guarantee it doesn't have. The agent is the *author* of the SQL it sends, and it's a trusted-but-fallible author: it isn't trying to escape a quote, but it can absolutely produce a syntactically perfect, well-formed statement whose *scope* is the problem — DELETE FROM users WHERE active = false when 40,000 rows happen to match, or an UPDATE that silently drops its WHERE clause because the agent forgot one. That is the failure mode this project is built to survive, and three mechanisms carry the weight:

1. Preview-and-rollback, not `EXPLAIN`. EXPLAIN only ever gives the Postgres planner's *estimate* of how many rows a statement will touch, derived from table statistics that can be stale (especially right after a bulk load, before ANALYZE has run) or simply wrong for a correlated predicate the planner can't model well. An approval gate built on an estimate is a gate an agent (or ordinary data skew) can defeat by accident, not just by malice — a statement whose *true* affected-row count is 40,000 could still sail under a threshold if the planner guessed 80. So every write tool here instead runs the real statement inside BEGIN … ROLLBACK: the row count in the preview is the exact count a real execution just produced, not a projection. EXPLAIN still has a job — the standalone explain_plan tool offers it as a cheap, side-effect-free pre-check an agent can call before ever attempting a two-phase write — but it is never what the approval thresholds compare against.

2. Role separation, not parsing. readonly and writer are two distinct Postgres roles with distinct grants (docker/init/01-roles.sql): readonly has SELECT only (and CREATE explicitly revoked on its schema); writer has SELECT, INSERT, UPDATE, DELETE, gated further by this project's own write allowlist. A mutating statement submitted through the readonly pool is refused by Postgres itself with permission denied — verified against a live database in tests/roles.test.ts, not just asserted in code. The alternative — parsing or regex-matching SQL text to decide "is this a write?" — was deliberately not made the safety boundary: a parser can always be fooled by a form it wasn't written to catch (a CTE-wrapped WITH x AS (DELETE FROM ... RETURNING *) SELECT * FROM x, a mutating function call, a quoting edge case), and getting that wrong is a security hole, not a cosmetic bug. query/explain_plan do still reject non-SELECT statements and enforce the read allowlist by extracting table references from the statement text (src/tools/sqlGuard.ts) — but that is explicitly a second, defense-in-depth layer on top of the role grant, not the property itself. See DECISIONS.md for the full reasoning, including why a gap in that text-based allowlist parsing (which needed several hardening passes for quoted/Unicode-escaped identifiers) is a bounded allowlist-bypass risk rather than a "read tool executed a write" risk.

3. Plan tokens bind to a statement-hash fingerprint. A plan token by itself — a random, opaque ID — would only prove "some preview happened at some point." It says nothing about *which* statement was previewed, which means a token alone can't stop a bait-and-switch: swap in a wider WHERE clause, a different table, extra rows, and hand the same-looking token to execute_plan. So every token is bound to statementFingerprint(statement, params) — a SHA-256 hash of the trimmed statement text plus the JSON-serialized parameter list — computed at preview time and re-derived from whatever execute_plan is actually called with; any mismatch is refused as STATEMENT_MISMATCH before anything runs. This is what makes a human's approval in the localhost approval UI mean something: they're approving the *exact* statement and params they were shown, not a token that could later be replayed against different SQL. (A second, independent check — the rows-affected digest — separately catches the case where the *same* statement now matches a different row set because of concurrent activity; see Two-phase writes below.)

See Limitations for what this model deliberately does not cover, and DECISIONS.md for the full write-up of each of these three decisions plus the approval-mechanism spike (#1).

Quick start

docker compose up -d

npm install npm test npm run build

Point Claude Desktop at the server (see config.example.json and Claude Desktop section below). node dist/index.js also starts a localhost approval UI at http://127.0.0.1:4319/ alongside it — open the full URL (with ?token=...) the server prints once on stderr, since every route requires the per-session bearer token.

Configuration

Copy config.example.json to config.json (or set SW_POSTGRES_CONFIG to a custom path):

{

"database": { "readonlyConnectionString": "postgres://readonly:readonly_password@localhost:5432/mcp_test", "writerConnectionString": "postgres://writer:writer_password@localhost:5432/mcp_test" }, "allowlist": { "read": { "schemas": ["public"], "tables": [] }, "write": { "schemas": [], "tables": [] } } }

• allowlist.read — schemas/tables the agent may see via describe_schema/query. If empty, all tables are readable. If tables is non-empty, only those fully-qualified tables are listed.
• allowlist.write — schemas/tables the agent may mutate. Defaults to deny: if both schemas and tables are empty, nothing is writable. Add entries explicitly.
• write.planTtlMs — how long a plan_token stays valid (default 60000). Overridable with SW_PLAN_TTL_MS.
• write.statementTimeoutMs — per-connection statement_timeout for write executions (default 10000). Overridable with SW_STATEMENT_TIMEOUT_MS.
• write.approvalRequiredAboveRows — a preview whose exact rollback-preview affected-row count is at or below this returns a token execute_plan will honour immediately, same as today. Above it, the preview instead returns status: "awaiting_approval" and the token is refused by execute_plan until a human approves it out-of-band (see Approval threshold and hard row cap below — approval is deliberately not an agent-facing MCP tool). Default 100. Overridable with SW_APPROVAL_REQUIRED_ABOVE_ROWS.
• write.hardMaxRows — a second, higher, separate threshold. A preview whose exact affected-row count exceeds this is refused outright: no token is issued at all, and there is no approval path — the response is a flat structured error (HARD_MAX_ROWS_EXCEEDED), not something to escalate past. Default 10000. Overridable with SW_HARD_MAX_ROWS. Must be >= write.approvalRequiredAboveRows; loadConfig throws otherwise.
• approvalServer.enabled — whether the localhost approval UI starts alongside the MCP server (default true). Overridable with SW_APPROVAL_SERVER_ENABLED ("true"/"false"). With it disabled, no plan can ever be approved, so any preview that would require approval (above write.approvalRequiredAboveRows, or any run_migration) is refused outright with a structured APPROVAL_UNAVAILABLE error — no plan_token is issued — rather than issuing a plan that would only block execute_plan until expiry.
• approvalServer.port — port the localhost approval UI listens on, bound to 127.0.0.1 only (default 4319). Overridable with SW_APPROVAL_SERVER_PORT.
• approvalServer.requireAuth — whether the localhost approval UI requires its per-session bearer token on every route, including the read-only GET ones (default true). Loopback binding plus the Host/Origin/Sec-Fetch-Site provenance checks stop a hostile bro