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/  (bound to 127.0.0.1, human-only)
   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

... View full README on GitHub