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:

Mitigation	How it fails
"The prompt says read-only"	Prompts are advisory. A confused agent, or an injected instruction inside a row of data, ignores them
Regex check for DROP/DELETE	Defeated by DR/**/OP, by a keyword inside a string literal, by casing, by SELECT 1; DROP TABLE users
A read-only database role	Correct and necessary, but all-or-nothing: it cannot express "may update orders, never customers", nor mask a column, nor ask a human
Reviewing the agent's SQL afterwards	The row is already gone

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

---

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

Tool	Description
list_tables	Tables readable under the policy, with row estimates
describe_table	Columns, types, nullability, defaults; masked columns marked
query	A single SELECT, read-only transaction, row-limited
execute	A single INSERT/UPDATE/DELETE, dry-run then human approval

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

Variable	Default	Purpose
DATABASE_URL	—	Required
ALLOWED_SCHEMAS	public	Schemas visible at all
READABLE_TABLES	(all)	Readable tables; empty means every table in the allowed schemas
WRITABLE_TABLES	(none)	Writable tables; writes are opt-in
MASKED_COLUMNS	(none)	ssn or customers.email
WRITES_ENABLED	false	Master switch for mutations
REQUIRE_APPROVAL	true	Human confirmation per write
MAX_MUTATION_ROWS	100	Refuse a mutation above this blast radius
MAX_ROWS	200	Read result cap
STATEMENT_TIMEOUT_MS	10000	Server-enforced
MAX_CALLS_PER_MINUTE	60	Bounds an agent stuck in a retry loop
AUDIT_LOG_PATH	(stderr)	JSONL audit file

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