Enables AI agents to safely interact with PostgreSQL databases using a two-phase write process with mandatory human approval for high-impact changes.
Also known as jpka/sw-postgres-mcp
- Stars
- 0
- Tools
- 8
- Downloads, 30 days
- —
- Latest version
- 0.3.1
- Last commit
- Sep 4, 2026
- License
- MIT
What is sw-postgres MCP?
sw-postgres enables AI agents to safely interact with PostgreSQL databases using a two-phase write process with mandatory human approval for high-impact changes. It exposes 8 tools, such as describe_schema, query, explain_plan.
Summary generated from the project's README
About sw-postgres MCP server
Developers looking to connect AI agents to PostgreSQL databases with strict safety guardrails can use this MCP server. It prevents accidental data loss by implementing a two-phase write process where every mutation must first be previewed and rolled back. This ensures that the agent only sees the exact number of rows a statement would actually affect, rather than relying on potentially inaccurate database estimates.
High-impact changes require human intervention through a local approval UI. While tools like describe_schema, query, and explain_plan operate via a read-only connection pool, write operations like insert_rows, update_rows, delete_rows, and run_migration generate a plan token. This token is cryptographically bound to the specific statement and parameters to prevent tampering. A user must manually approve these plans via a localhost interface before the execute_plan tool can commit the changes using a separate writer connection pool. The system also includes configurable row thresholds to automatically refuse massive operations or flag them for review.
What you can do with it
- 1Controlled data modificationsYou can allow an agent to update or delete specific rows while ensuring a human reviews the impact first.
- 2Preventing massive accidental deletionsSet a hard limit on the number of rows an agent can touch to stop catastrophic errors before they happen.
Written from the project's README. Every use case is checked against a quote from it.
How to install sw-postgres MCP
Formats verified 10 Nov 2026Add to CursorAdd to GooseInstall sw-postgres MCP in Claude Code
$ claude mcp add --env 'DATABASE_URL_READONLY=<YOUR_DATABASE_URL_READONLY>' --env 'DATABASE_URL_WRITER=<YOUR_DATABASE_URL_WRITER>' sw-postgres-mcp -- npx -y sw-postgres-mcpInstall sw-postgres MCP in Cursor
~/.cursor/mcp.json
{
"mcpServers": {
"sw-postgres-mcp": {
"command": "npx",
"args": [
"-y",
"sw-postgres-mcp"
],
"env": {
"DATABASE_URL_READONLY": "<YOUR_DATABASE_URL_READONLY>",
"DATABASE_URL_WRITER": "<YOUR_DATABASE_URL_WRITER>"
}
}
}
}Install sw-postgres MCP in VS Code
.vscode/mcp.json
{
"servers": {
"sw-postgres-mcp": {
"type": "stdio",
"command": "npx",
"args": [
"-y",
"sw-postgres-mcp"
],
"env": {
"DATABASE_URL_READONLY": "<YOUR_DATABASE_URL_READONLY>",
"DATABASE_URL_WRITER": "<YOUR_DATABASE_URL_WRITER>"
}
}
}
}Install sw-postgres MCP in VS Code CLI
$ code --add-mcp '{"name":"sw-postgres-mcp","type":"stdio","command":"npx","args":["-y","sw-postgres-mcp"],"env":{"DATABASE_URL_READONLY":"<YOUR_DATABASE_URL_READONLY>","DATABASE_URL_WRITER":"<YOUR_DATABASE_URL_WRITER>"}}'Install sw-postgres MCP in Claude Desktop
claude_desktop_config.json
{
"mcpServers": {
"sw-postgres-mcp": {
"command": "npx",
"args": [
"-y",
"sw-postgres-mcp"
],
"env": {
"DATABASE_URL_READONLY": "<YOUR_DATABASE_URL_READONLY>",
"DATABASE_URL_WRITER": "<YOUR_DATABASE_URL_WRITER>"
}
}
}
}Install sw-postgres MCP in Codex
$ codex mcp add sw-postgres-mcp --env 'DATABASE_URL_READONLY=<YOUR_DATABASE_URL_READONLY>' --env 'DATABASE_URL_WRITER=<YOUR_DATABASE_URL_WRITER>' -- npx -y sw-postgres-mcpInstall sw-postgres MCP in Codex config.toml
~/.codex/config.toml
[mcp_servers.sw-postgres-mcp] command = "npx" args = ["-y", "sw-postgres-mcp"] [mcp_servers.sw-postgres-mcp.env] DATABASE_URL_READONLY = "<YOUR_DATABASE_URL_READONLY>" DATABASE_URL_WRITER = "<YOUR_DATABASE_URL_WRITER>"
Install sw-postgres MCP in Gemini CLI
$ gemini mcp add -s user -e 'DATABASE_URL_READONLY=<YOUR_DATABASE_URL_READONLY>' -e 'DATABASE_URL_WRITER=<YOUR_DATABASE_URL_WRITER>' sw-postgres-mcp npx -- -y sw-postgres-mcpInstall sw-postgres MCP in Gemini settings.json
~/.gemini/settings.json
{
"mcpServers": {
"sw-postgres-mcp": {
"command": "npx",
"args": [
"-y",
"sw-postgres-mcp"
],
"env": {
"DATABASE_URL_READONLY": "<YOUR_DATABASE_URL_READONLY>",
"DATABASE_URL_WRITER": "<YOUR_DATABASE_URL_WRITER>"
}
}
}
}sw-postgres MCP tools 8
Detected from the READMEdescribe_schemaqueryexplain_plandelete_rowsinsert_rowsupdate_rowsrun_migrationexecute_plan
| describe_schema | — | |
| query | — | |
| explain_plan | — | |
| delete_rows | — | |
| insert_rows | — | |
| update_rows | — | |
| run_migration | — | |
| execute_plan | — |
Configuration
DATABASE_URL_READONLYRequiredSecretPostgres connection string for the read-only role.
DATABASE_URL_WRITERRequiredSecretPostgres connection string for the writer role.
SW_POSTGRES_CONFIG
| Name | Description | |
|---|---|---|
| DATABASE_URL_READONLY | Postgres connection string for the read-only role. | RequiredSecret |
| DATABASE_URL_WRITER | Postgres connection string for the writer role. | RequiredSecret |
| SW_POSTGRES_CONFIG | — |
Automated checks
Ran Nov 14, 2026Full report →Passed our automated checks. That is not a security audit: read the code and give the server the narrowest access it needs. How checks work
- Source repository linked and reachablejpka/sw-postgres-mcp is public and reachablegithub.com/jpka/sw-postgres-mcp ↗Pass
- Package names this repository as its sourcesw-postgres-mcp names jpka/sw-postgres-mcp as sourcenpmjs.com/package/sw-postgres-mcp ↗Pass
- Build provenance (Sigstore attestation)No build attestation published for this releaseNot checked
- Open source license (OSI)MITspdx.org/licenses/MIT.html ↗Pass
- Commit or release in the last yearLast commit or release 2026-09-04github.com/jpka/sw-postgres-mcp ↗Pass
- OpenSSF ScorecardNot checked
- Package name not close to a popular packageNo popular package one edit away from sw-postgres-mcpPass
- No critical advisory in the package or its dependencies114 resolved dependencies checked; no critical advisoryosv.dev ↗Pass
- No hidden instructions to the model8 tool descriptions and the README read; no override phrases, credential paths or hidden charactersPass
- Destructive tools are labelledNot checked
- Remote endpoint answers the MCP handshakeNot checked
README
View on GitHubSafe-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 viadescribe_schema/query. If empty, all tables are readable. Iftablesis non-empty, only those fully-qualified tables are listed.allowlist.write— schemas/tables the agent may mutate. Defaults to deny: if bothschemasandtablesare empty, nothing is writable. Add entries explicitly.write.planTtlMs— how long aplan_tokenstays valid (default60000). Overridable withSW_PLAN_TTL_MS.write.statementTimeoutMs— per-connectionstatement_timeoutfor write executions (default10000). Overridable withSW_STATEMENT_TIMEOUT_MS.write.approvalRequiredAboveRows— a preview whose exact rollback-preview affected-row count is at or below this returns a tokenexecute_planwill honour immediately, same as today. Above it, the preview instead returnsstatus: "awaiting_approval"and the token is refused byexecute_planuntil a human approves it out-of-band (see Approval threshold and hard row cap below — approval is deliberately not an agent-facing MCP tool). Default100. Overridable withSW_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. Default10000. Overridable withSW_HARD_MAX_ROWS. Must be>= write.approvalRequiredAboveRows;loadConfigthrows otherwise.approvalServer.enabled— whether the localhost approval UI starts alongside the MCP server (defaulttrue). Overridable withSW_APPROVAL_SERVER_ENABLED("true"/"false"). With it disabled, no plan can ever be approved, so any preview that would require approval (abovewrite.approvalRequiredAboveRows, or anyrun_migration) is refused outright with a structuredAPPROVAL_UNAVAILABLEerror — noplan_tokenis issued — rather than issuing a plan that would only blockexecute_planuntil expiry.approvalServer.port— port the localhost approval UI listens on, bound to127.0.0.1only (default4319). Overridable withSW_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 (defaulttrue). Loopback binding plus the Host/Origin/Sec-Fetch-Site provenance checks stop a hostile browser page; they do not stop a different local process that simply sends the expected headers, since a plan token alone was previously enough to approve or reject a plan — the bearer token is what gates that out (safe-write-mcp-core#20). Set tofalseonly to fall back to the pre-0.4.0 behaviour (not recommended). Overridable withSW_APPROVAL_SERVER_REQUIRE_AUTH("true"/"false").approvalServer.authToken— explicit bearer token for the localhost approval UI, sent asAuthorization: Bearer <token>or a?token=query-string fallback. When omitted (the default), the server generates a random token per startup and prints it once on stderr alongside the approval URL — the page's own Approve/Reject buttons already carry it. Set this when a fixed token is needed (e.g. scripted access). Overridable withSW_APPROVAL_SERVER_AUTH_TOKEN.callerId— identity recorded ascaller_idon every audit log row (default"unknown"). Overridable withSW_CALLER_ID. See Audit log.- Environment variables
DATABASE_URL_READONLY/DATABASE_URL_WRITERoverride the file.
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):
| group | rows | notes |
|---|---|---|
customers |
50,000 | |
products |
2,000 | |
orders |
60,000 | |
order_items |
~96,000 | 1-4 items/order |
inactive customers (last_login < 2025-01-01) |
40,000 | ~80% of customers |
test tenant (customers.segment = 'test_tenant') |
8 customers / 320 orders | a small, narrowly-queryable tenant entirely inside the inactive population |
orders.status = 'cancelled' |
13,200 | exceeds a 10,000-row hard cap, for exercising a hard-cap refusal |
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.
sw-postgres MCP questions
How do I add sw-postgres MCP to Claude Code?
Run claude mcp add --env 'DATABASE_URL_READONLY=<YOUR_DATABASE_URL_READONLY>' --env 'DATABASE_URL_WRITER=<YOUR_DATABASE_URL_WRITER>' sw-postgres-mcp -- npx -y sw-postgres-mcp in your terminal. Add --scope user to make it available in every project.
How do I add sw-postgres MCP to Cursor?
Open ~/.cursor/mcp.json and add the server under mcpServers, using the Cursor config shown in the install section above.
What tools does sw-postgres MCP provide?
It exposes 8 tools, including describe_schema, query, explain_plan, delete_rows, insert_rows.
How does the server ensure write safety?
It uses a two-phase write process where every write tool previews a change inside a transaction that always rolls back, requiring a separate execute_plan call with a specific token to commit.
Can an AI agent approve its own database changes?
No, the approval mechanism is intentionally kept out of reach of the agent; it requires a human to use a local web UI to approve or reject pending plans.
What happens if a write affects too many rows?
If the affected rows exceed a hard maximum threshold, the request is refused outright without issuing a plan token.
Does the server prevent SQL injection?
The server uses parameterized queries for structured DML tools like insert_rows and update_rows to mitigate injection risks, though DDL migrations are sent as raw statements.