# mcp-postgres-guard

An [MCP](https://modelcontextprotocol.io) 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.

```jsonc
// 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](src/sql/analyze.ts)
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
```
```json
[{ "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:

```json
{"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

```bash
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:

```bash
npm run inspect
```

---

## Testing

```bash
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