mcp-postgres-guard

An MCP server that gives AI agents access to a PostgreSQL database without giving them the keys to it: least-privilege scopes, statement analysis by a real parser, PII masking, human approval for every write, and an append-only audit trail.

Works with Claude Desktop, Claude Code, or any MCP client.

// claude_desktop_config.json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "mcp-postgres-guard"],
      "env": {
        "DATABASE_URL": "postgres://readonly@localhost:5432/app",
        "READABLE_TABLES": "customers,orders",
        "MASKED_COLUMNS": "ssn,customers.email"
      }
    }
  }
}

That configuration is read-only with two tables visible and two columns redacted. Writes require three more variables, deliberately.

The problem

Handing an agent a database connection means handing it everything the connection can do. The usual mitigations do not hold up:

This server addresses each layer separately, so no single check has to be perfect.

Related MCP server: safedb-mcp

What it does

Statements are parsed, not pattern-matched

Every statement goes through a real PostgreSQL parser before it reaches the database. That yields a structural answer — statement class, every table touched, whether a mutation carries a WHERE clause — instead of a guess.

SELECT 1; DROP TABLE users
  → Expected a single statement, found 2. Stacked statements are refused.

SELECT * FROM logs WHERE message = 'DROP TABLE users'
  → allowed. It is a read; the keyword is data.

The second case matters as much as the first: a guard that produces false positives gets switched off.

Reads cannot write, twice over

A statement classified as a read still executes inside BEGIN READ ONLY. If the analyser ever misjudges a statement class, Postgres refuses the write on its own. Defence that does not depend on this codebase being correct is the only kind worth relying on here.

search_path is pinned to the exposed schemas, so an unqualified table name cannot resolve to something the policy check never saw.

Columns can be masked without being hidden

SELECT id, name, email, ssn FROM customers

[{ "id": 1, "name": "Ann Lee", "email": "***REDACTED***", "ssn": "***REDACTED***" }]

The agent still sees that email exists and can filter on it — queries stay valid — but the values never leave the process. Masking is applied to result rows rather than rewritten into the query, because a SELECT can surface a column under an alias, through *, or via an expression; output filtering catches all three.

Writes are measured, then approved by a human

A mutation is first executed inside a transaction that is always rolled back, purely to learn how many rows it really touches. Only then is a human asked, through MCP elicitation:

Approve this UPDATE?

Reason given: Customer confirmed delivery address
Rows affected: 1

UPDATE orders SET status = $1 WHERE id = $2

The count comes from an actual execution rather than EXPLAIN, because planner estimates are least reliable on stale statistics — exactly the situation where an approval prompt earns its keep.

UPDATE/DELETE with no WHERE clause is refused outright rather than sent for approval. A human scanning a wall of SQL misses a missing WHERE reliably, and it is the one mistake with no undo.

If the client does not support elicitation, the write is declined. "Nobody could be asked" is not "approved".

Everything is recorded

Every call — allowed, denied, approved, declined, failed — is appended as JSONL with the SQL, the tables, the row count, and the reason:

{"timestamp":"2026-07-28T14:03:11.204Z","tool":"query","outcome":"denied",
 "sql":"SELECT * FROM internal_secrets",
 "reason":"Table \"internal_secrets\" is not in the readable set."}

Denied calls are logged as carefully as successful ones. A log of successful queries alone cannot tell a well-behaved agent from one that probed for a way in and was stopped.

Tools

Denials return the reason to the agent. An agent told only "denied" retries the same query; one told "table secrets is not in the readable set" moves on.

Configuration

Two configurations are rejected at startup rather than failing confusingly later: writes enabled with no writable tables named, and a table that is writable but not readable — a change that cannot be reviewed cannot be approved.

Try it

npm install
docker run -d --name guard-demo -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:17

DEMO_DATABASE_URL=postgres://postgres:pw@localhost:5432/postgres \
  node --experimental-strip-types scripts/demo.ts

scripts/demo.ts connects as a real MCP client and walks through reads, masking, six kinds of denial, and an approved write — printing exactly what a connected agent would see.

Or point the MCP Inspector at it:

npm run inspect

Testing

npm test        # 64 tests
npm run typecheck

The test suite concentrates on the security boundary, because that is where a bug is expensive rather than annoying: stacked-statement rejection, keywords inside string literals, comment-split keywords, CTE names mistaken for tables, forbidden tables reached through a subquery, writes whose FROM clause reads a table the agent may not see, and masking applied through aliases.

scripts/demo.ts covers the protocol layer itself — that tools are registered with the schemas MCP expects, that denials come back as tool errors rather than crashes, and that declining an approval leaves the data untouched.

Deliberately not included

• Query rewriting — injecting predicates for row-level security is attractive and fragile. Postgres RLS does it properly; use it.

• Connection pooling per agent identity — belongs in the deployment, not here.

• Automatic PII detection — heuristics that guess which columns are sensitive are wrong in both directions. Name them explicitly.

License

MIT