Sw Postgres

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=<per-session-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 <token>`
    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 until expiry.

The config is read once at server startup — a running server never re-reads config.json. After editing it, restart the MCP server (quit and reopen Claude Desktop / your MCP client), or the old config keeps being enforced. On startup the server logs to stderr which config file it loaded and the effective write allowlist; if no config file is found it warns loudly, because a missing file means an empty write allowlist (default deny — every write refused).

Two connection pools are created with distinct Postgres roles (readonly vs writer). Read-only is enforced by the database grants, not by parsing SQL — a bug in our code cannot turn a read tool into a write tool.

Claude Desktop

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "sw-postgres-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sw-postgres-mcp/dist/index.js"],
      "env": {
        "SW_POSTGRES_CONFIG": "/absolute/path/to/sw-postgres-mcp/config.json",
        "DATABASE_URL_READONLY": "postgres://readonly:readonly_password@localhost:5432/mcp_test",
        "DATABASE_URL_WRITER": "postgres://writer:writer_password@localhost:5432/mcp_test"
      }
    }
  }
}

SW_POSTGRES_CONFIG is set explicitly because MCP clients do not spawn the server with the project directory as the working directory, so the default ./config.json lookup would silently miss it.

Restart Claude Desktop. Ask "what's in this database?" — describe_schema returns tables, columns with types, foreign keys, and row-count estimates for exactly the allowlisted schemas/tables.

Docker

docker compose up starts a disposable Postgres (postgres:16-alpine) with both roles provisioned via docker/init/01-roles.sql, the audit schema created via docker/init/02-audit-log.sql, and its status enum extended for the approval workflow via docker/init/03-approval-workflow.sql. No manual setup required for tests or local dev.

Demo database

docker compose up -d --wait
npm run seed:demo

Seeds a synthetic e-commerce dataset (customers, products, orders, order_items, ~208k rows total) into an empty database, so there's realistic data to point describe_schema / query / the write tools at without a real production dataset lying around. The schema (docker/init/03-demo-schema.sql) is applied automatically for the disposable Docker Postgres; npm run seed:demo applies it itself for a plain local Postgres, so no manual migration step is required either way. Generation is deterministic — a seeded PRNG (mulberry32, not Math.random()), so the same --seed always produces the exact same rows:

npm run seed:demo -- --seed=7
npm run seed:demo -- --connection="postgres://user:pass@host:5432/db"

Connection resolution follows the same precedence as everywhere else in this project: --connection flag > DATABASE_URL_WRITER > POSTGRES_WRITER_URL > DATABASE_URL > the docker-compose writer default. Each run truncates and regenerates the four demo tables, so it's safe to re-run against a non-empty database.

Row-count shape (default seed):

Tests

docker compose up -d --wait
npm test

Integration tests verify against a live Postgres: role separation, readonly cannot write, describe_schema fields, allowlist filtering, and the demo-database seeder's row-count shape (tests/seedDemo.test.ts actually runs npm run seed:demo and queries the results back). tests/approvalUi.test.ts starts the localhost approval HTTP server for real (on an OS-assigned port) and drives it with plain fetch() carrying the per-session bearer token — no browser automation — covering the pending-plan listing, the approve/reject HTTP endpoints end-to-end (including unlocking and permanently killing execute_plan), the loopback-only bind, the bearer-token gate (401s, the ?token= fallback, requireAuth: false opt-out, 403-before-401 ordering), audit rows, expired-plan filtering, and that the surface works with no MCP client connected at all. tests/insertRows.test.ts and tests/updateRows.test.ts cover the same preview→token→execute discipline, the approval threshold/hard cap, the write allowlist, audit logging, SQL-injection-shaped inputs, and (for inserts) the sequence-gap behavior, for the two newer write tools; tests/writeStatements.test.ts unit-tests the no-WHERE guard the two DELETE/UPDATE tools share. tests/runMigration.test.ts drives run_migration through the same real localhost approval server (never TwoPhaseWrite called directly) to prove the row-count threshold is never consulted, a rejection reaches execute_plan as PLAN_REJECTED, multi-statement input and out-of-allowlist targets are refused before touching the database, and — against the live Postgres — that CREATE TABLE, ALTER TABLE, DROP TABLE, and CREATE INDEX all preview-then-roll-back and execute-then-commit correctly; it also unit-tests src/tools/ddlTarget.ts's statement-target extraction directly.

Safety case

Every test file above proves its own ticket's guard works for the one tool that ticket introduced it on. tests/safetyCase.test.ts instead runs a fixed matrix of safety properties — threshold trip, hard-cap refusal, expired/reused/mutated/rejected tokens, allowlist enforcement, the readonly role, audit-trail completeness, audit immutability, and preview-leaves-no-trace — against every write tool each property applies to, via shared it.each-driven helpers rather than per-tool copies, specifically to catch a guard that was wired into one tool but silently missed on another. One block runs the threshold/hard-cap rows against a dataset generated by the same deterministic generator npm run seed:demo uses (issue #10), so the guards are demonstrated at realistic seeded-data volumes, not just small synthetic counts.

Tools

• describe_schema — tables, columns with types, foreign keys, row-count estimates (respects read allowlist).
• query — run a read-only SELECT and return { columns, rows, row_count }. Runs on the readonly role, so a mutating statement is refused by the database regardless of what the SQL says. Enforces a single statement per call and the read allowlist. Optional limit and params.
• explain_plan — run EXPLAIN (FORMAT JSON) for a candidate read statement and return the planner's estimated cost and rows without executing it. A cheap pre-check before running something potentially expensive.
• delete_rows — two-phase delete. Runs the statement inside a transaction, returns the exact affected row count plus a sample of affected rows, then rolls back. The response includes a plan_token, the exact statement, and params — and a status of previewed or awaiting_approval (see Approval threshold and hard row cap below). Refuses a statement with no WHERE clause unless confirm_full_table: true is passed.
• insert_rows — two-phase insert. Takes table, columns (an array of column names), and rows (an array of value-arrays, one per row, positional against columns), plus reason. Runs the INSERT ... VALUES (...), (...) RETURNING * inside a transaction, returns the exact row count and a sample of the rows it would insert, then rolls back. See Sequence values and rolled-back inserts below for a side effect worth knowing about. Example:

  {
    "table": "customers",
    "columns": ["email", "active"],
    "rows": [
      ["[email protected]", true],
      ["[email protected]", false]
    ],
    "reason": "seeding two test accounts"
  }
  

• update_rows — two-phase update. Takes table, set (a { "column": value, ... } object of what to change), where + params (parameterized WHERE conditions, same convention as delete_rows), confirm_full_table, and reason. Runs the UPDATE ... SET ... WHERE ... RETURNING * inside a transaction, returns the exact affected row count and a post-update sample, then rolls back. Refuses a statement with no clause unless is passed — the exact same guard uses (), not a reimplementation. Example: Column names in are always quoted identifiers and values are always parameters — never string-concatenated into the statement — so a crafted column name or value cannot inject SQL; a malformed column name simply fails as an unknown column.

There is deliberately no approve_plan or reject_plan (or any other approval) tool in this list. Approving or rejecting an awaiting_approval plan is not exposed to the agent — see Localhost approval UI below for why and how it's meant to be used instead.

Every tool takes a reason string (recorded in the audit log — see below) and returns errors as structured { code, message, hint } — never a raw Postgres exception or a multi-statement batch.

Two-phase writes

delete_rows, insert_rows, update_rows, and run_migration all go through the exact same core (TwoPhaseWrite in src/writeCore.ts) — the agent must commit to a preview before it can execute:

1. The tool runs the statement in a transaction, captures the exact affected/inserted row count and a sample of affected rows via RETURNING (for run_migration, see below — DDL has no RETURNING to capture), then rolls back. Nothing has changed in the database.
2. execute_plan replays the identical statement and commits — but only if the token is valid, unexpired, unused, and bound to the exact statement + params from the preview. For delete_rows/update_rows, it also refuses to commit if the matched row set changed since the preview (ROWSET_CHANGED). If the plan is still awaiting approval, execute_plan blocks on the token until a human approves it out-of-band (then it executes), rejects it (PLAN_REJECTED), or it expires (EXPIRED_TOKEN) — see below.

A DELETE/UPDATE without a WHERE clause is refused unless confirm_full_table: true is passed — one guard, shared by both tools (src/tools/writeStatements.ts), not two copies that could drift. INSERT has no WHERE clause, so this guard doesn't apply to insert_rows. Every write runs through the writer pool; the readonly pool is never used for a mutation.

The ROWSET_CHANGED check is deliberately skipped for insert_rows: that check exists to catch "the rows a WHERE clause matches changed between preview and execute," which has no equivalent for INSERT — there's no pre-existing row set to match. insert_rows still gets everything else the core provides (the exact statementFingerprint binding, single-use/expiring tokens, the approval threshold and hard cap, and full audit logging); see DECISIONS.md for why comparing RETURNING digests across an INSERT's preview and execute would otherwise refuse the write on every single call against a table with a server-generated column.

run_migration's DDL statements skip the same check for the same underlying reason (no pre-existing matched row set), but for a stronger reason still: DDL doesn't support a RETURNING clause at all, so there's no digest — or affected-row count, or sample rows — to compute in the first place. run_migration runs the raw statement inside the preview's BEGIN/ROLLBACK (Postgres DDL is transactional, so a CREATE TABLE/ALTER TABLE preview rolls back exactly like a DELETE/UPDATE preview does) and reports affected_rows: 0, sample_rows: [] — not a stand-in for a real count, just the accurate answer for a statement with no rows to return — plus a target field naming the schema-qualified table/index it extracted from the statement, so a human approving it has something concrete to judge instead. See Migrations always require approval below and DECISIONS.md.

Sequence values and rolled-back inserts

Because insert_rows's preview is a real INSERT ... RETURNING * that then rolls back, any serial/identity/other sequence-backed default on the target table's columns still advances — Postgres sequences are not transactional, so a rollback does not return a consumed sequence value. This means:

• Previewing an insert (even one you never execute) permanently uses up one or more values from that column's sequence.
• The id (or similar) shown in the preview's sample_rows is illustrative, not a promise — the row actually committed by execute_plan will very likely get a different sequence-generated value, since the preview's own rollback already consumed the one shown.
• This shows up as gaps in serial columns over time (e.g. ids 1, 2, 5, 6 instead of 1, 2, 3, 4) purely from previews, whether or not they were ever executed. This is harmless — Postgres sequences have never guaranteed gap-free values, even without this tool — but worth knowing about before treating a serial column as a dense counter.

Approval threshold and hard row cap

The preview's exact rollback-preview affected-row count (never an EXPLAIN estimate — write.approvalRequiredAboveRows and write.hardMaxRows are only ever compared against the real, rolled-back count) decides what the same tool call does next:

The approval mechanism (#6) a plan token issued by a preview carries an internal requiresApproval / approved / rejected flag alongside the existing fingerprint/expiry/single-use state in the in-memory plan store (safe-write-mcp-core's PlanStore, held by TwoPhaseWrite in src/writeCore.ts). Approving flips approved to true and writes an approved audit row; rejecting sets rejected permanently and writes a rejected audit row (TwoPhaseWrite.approvePlan()/rejectPlan() are the programmatic entry points). Neither is exposed as an MCP tool — the agent-facing tool surface has no approve_plan/reject_plan (or any other approval) entry, because the same agent that requested a gated write must not be able to approve its own plan; see DECISIONS.md. There is deliberately no separate approvals database table — the plan token is already the right-sized unit execute_plan is scoped to, and it lives exactly where the rest of the two-phase-write state (fingerprint, expiry, used-once) already lives. The localhost approval UI (#7) is the only approval surface: it acts on the same store directly, from its own (non-agent) HTTP surface.

Migrations always require approval

run_migration (#9) does not use the table above. The row-count thresholds are never consulted for it, at all — every run_migration preview comes back status: "awaiting_approval" unconditionally, even for a migration whose affected_rows is 0 and would be nowhere near approvalRequiredAboveRows. This is deliberate: a migration that touches zero rows (adding a column, dropping a table with no rows in it, creating an index) can still be the single most destructive thing this server does — the row count DELETE/UPDATE/INSERT use as a proxy for "how much is at stake" doesn't mean anything for schema changes, so it is never treated as one.

Mechanically, src/tools/runMigration.ts passes alwaysRequireApproval: true on every call to TwoPhaseWrite.preview() — a WriteMeta field preview() OR's into the same requiresApproval decision the row-count threshold makes for the other tools (src/writeCore.ts). This is hardcoded in the tool module's own code, not read from run_migration's arguments: there is no field in its MCP inputSchema (src/server.ts) that reaches it, so the calling agent has no parameter that weakens or bypasses it, and no write.approvalRequiredAboveRows/hardMaxRows misconfiguration can accidentally let a migration through — the flag doesn't consult either setting in the first place. From there, run_migration gets every other approval-mechanism guarantee for free, the same way delete_rows/insert_rows/update_rows do: the localhost approval UI is the only way to approve or reject it, a rejection permanently kills the token with a structured PLAN_REJECTED error on the next execute_plan attempt, and every preview/approval/execution/rejection is audited with the agent's reason.

run_migration also enforces the write allowlist before ever calling preview(): the statement's target table (or, for DROP TABLE, every table it names) is extracted from the statement text and checked with the exact same isTableWritable logic the data write tools use — see src/tools/ddlTarget.ts and DECISIONS.md for which DDL forms are supported and why DROP INDEX currently isn't.

Localhost approval UI

A small, plain-HTML page — no React, no build step — for a human to see what an agent wants to do above the approval threshold and say yes or no. It runs as its own local-only HTTP server (src/approvalServer.ts), separate from the MCP stdio transport, started alongside it by src/index.ts and bound to 127.0.0.1 only (never 0.0.0.0 — see DECISIONS.md). It shares the same in-memory TwoPhaseWrite instance as the MCP server, so an approval or rejection here is immediately visible to execute_plan on the MCP connection.

• Access: open the full URL the server prints once on stderr at startup — http://127.0.0.1:4319/?token=<per-session-token> (or whatever approvalServer.port is configured to) — in a browser on the machine the server runs on. It is unreachable from any other machine — there is no host/bind-address config option, on purpose. Every route requires the bearer token (0.4.0, safe-write-mcp-core#20): send it as Authorization: Bearer <token> or the ?token= query-string fallback a pasted URL already carries — the page's own Approve/Reject buttons send it via the header automatically. A request that fails the Host/Origin/Sec-Fetch-Site provenance checks gets 403 before the token is even checked; one that passes those but has no (or the wrong) token gets 401 UNAUTHORIZED. curl/fetch() against GET /api/plans need the header too, e.g. curl -H "Authorization: Bearer <token>" http://127.0.0.1:4319/api/plans. Set approvalServer.requireAuth: false to opt out (not recommended), or approvalServer.authToken for a fixed token instead of a generated one.
• What it shows: every plan currently awaiting_approval — the tool, the exact statement, the agent's stated reason, the exact rollback-preview affected-row count, a sample of the affected rows (sample_rows from the preview), and — when the preview extracted one (currently only run_migration) — a target naming the schema-qualified table/index the statement acts on, which is what a human reviews a DDL migration by in place of a row count. An expired plan disappears from this list rather than sitting there approvable. A machine-readable equivalent is at GET /api/plans (JSON), reachable with plain fetch()/curl — no browser or MCP client required, which is also how the acceptance tests in tests/approvalUi.test.ts and tests/runMigration.test.ts exercise it.
• Approve (POST /api/plans/:token/approve, optional { approvedBy } body) approves the plan directly on the in-memory plan store, in-process — not through any MCP tool. execute_plan on that token succeeds immediately afterward.
• Reject (POST /api/plans/:token/reject, optional { rejectedBy, reason } body) permanently kills the token on the store. It can never be approved or executed afterward, even by a later "approve" click or a second "reject" click (both are safely idempotent — no-ops beyond re-auditing). Because blocks while a plan awaits approval, a rejection surfaces immediately on that in-flight call — the blocked returns a structured, distinguishable error (not , not , not a generic failure) whose message includes the human's rejection reason when one was given, so the agent has something concrete to act on — narrow the statement and re-preview, rather than just retrying blindly.

Audit log

Every preview, approval, execution, and refusal the two-phase write core handles writes one row to mcp_audit.log, in the mcp_audit schema:

Writing the audit row never blocks or masks the outcome of the write it describes: a failed audit insert (e.g. a transient connection blip) is logged to stderr and swallowed, never thrown, so a lost audit row can't be confused with a database write that actually failed.

The append-only guarantee is enforced by Postgres, not by application code. docker/init/02-audit-log.sql (the committed migration, applied to both the disposable Docker test database and any other target Postgres) grants the writer role INSERT — and only INSERT — on mcp_audit.log, then explicitly REVOKEs UPDATE, DELETE, and TRUNCATE from it:

GRANT INSERT ON mcp_audit.log TO writer;
REVOKE UPDATE, DELETE, TRUNCATE ON mcp_audit.log FROM writer;

No bug in this server, and no SQL an agent could construct through the writer role, can rewrite or erase a row once it lands — Postgres refuses the UPDATE/DELETE outright with permission denied. This is asserted by a real test against Postgres (tests/auditLog.test.ts), not just documented. readonly gets SELECT only, so an operator can read the trail without being able to write to it.

Limitations

Stated plainly, not hidden:

• A rollback undoes table writes, not external side effects. A delete_rows/insert_rows/update_rows/run_migration preview really does roll back — but if a BEFORE/AFTER trigger on the affected table fires a NOTIFY, calls an external service, or writes through a foreign data wrapper (FDW) to another database, that side effect already happened and is not undone by the preview's ROLLBACK. Postgres transactions only ever guarantee the transactional table state comes back; they cannot retract a message already sent to something outside the database. If a table an agent can write to has such a trigger, treat the preview step itself as side-effecting, not as free.
• Rolled-back inserts (and previewed DDL) still consume sequence values. Postgres sequences are not transactional, so a rolled-back insert_rows preview — or a rolled-back CREATE TABLE ... GENERATED ... AS IDENTITY-style DDL preview — permanently advances any serial/identity sequence the affected columns default from. This shows up as gaps in serial columns purely from previews, whether or not they were ever executed. Harmless (Postgres sequences never guaranteed gap-free values to begin with) but worth knowing about; see Sequence values and rolled-back inserts above.
• The audit log's append-only guarantee is a property of the writer role's grants, not of this server's code, and it only holds for connections that go through that role. docker/init/02-audit-log.sql grants writer INSERT only on mcp_audit.log and revokes UPDATE/DELETE/TRUNCATE — see Audit log above — but a superuser connection is outside this model entirely: a Postgres superuser, the table's owner, or any role holding (or grantable) UPDATE/DELETE/TRUNCATE on mcp_audit.log — via table ownership, direct grant, or ALTER/GRANT rights over the writer role itself — can rewrite or erase audit rows regardless of what writer's own grants say. The guarantee is "this server, using the credentials it was configured with, cannot tamper with its own audit trail" — not "the audit trail is tamper-proof against whoever administers the database."
• No multi-tenancy. One server process talks to one Postgres database with one pair of / roles and one allowlist. There is no per-tenant credential scoping or per-tenant audit partitioning; running this for multiple tenants means running multiple server instances with separate config.

Publishing to the MCP Registry

server.json is the committed manifest for the official MCP Registry. tests/serverJson.test.ts checks it carries the registry schema URL and the key manifest properties (reverse-DNS name, version, npm package entry, stdio transport, required env vars), and keeps it in sync with package.json — its name must equal the mcpName field in package.json, which is the npm ownership-verification marker the registry checks on publish.

Publishing is a CLI flow (not a GitHub PR) and requires the npm package to already exist:

1. Publish to npm: npm run build && npm publish (see issue #24).
2. Install the publisher: brew install mcp-publisher, or download the release binary from modelcontextprotocol/registry.
3. Authenticate: mcp-publisher login github (device flow).
4. Publish: mcp-publisher publish — reads server.json and registers the server.

The manifest's name (io.github.jpka/sw-postgres-mcp) is namespaced under the GitHub account, so GitHub authentication is sufficient — no DNS challenge. The DATABASE_URL_READONLY / DATABASE_URL_WRITER environment variables it declares are the same connection strings the Configuration section describes.

License

MIT