# warehouse-mcp-server

A [Model Context Protocol](https://modelcontextprotocol.io) server that gives an LLM **safe, read-only access to a PostgreSQL warehouse** — schema introspection, guarded SQL, and result caps that tell the caller when they truncated.

```
npm install && npm test   # 33 tests, including a real MCP client↔server round-trip
```

The suite runs against **PGlite** (PostgreSQL compiled to WebAssembly), so the introspection queries, the read-only transaction and the row caps are exercised for real rather than mocked into agreement — no Docker, no service to start.

---

## Why the safety model is layered

Handing a language model a database connection is the most useful and most dangerous thing in this repo. A model can be talked into things; a prompt injection buried in a row of data is a real attack, not a thought experiment. So there are three layers, ordered by how much each deserves to be trusted:

**1. A read-only database role.** The actual boundary. The connection has `SELECT` on the `mart` schema and nothing else, so even a total bypass of everything below can only read what a dashboard could read. If you deploy this with a superuser connection string, nothing else here saves you.

**2. A read-only transaction and a statement timeout.** Every query runs inside `BEGIN; SET TRANSACTION READ ONLY; SET LOCAL statement_timeout = …`. Postgres refuses writes and kills runaway scans, independently of anything this process believes. There is a test that bypasses the static checks entirely to prove this layer holds on its own.

**3. Static SQL checks.** Reject bad queries *before* they are sent, so the model gets `only SELECT queries are allowed` instead of an opaque permission error it will start trying to work around.

Layer 3 is defence in depth and a UX feature. **It is deliberately not the thing standing between a prompt injection and your data** — a string-matching filter never should be.

---

## What the static checks actually catch

The parser strips comments and string literals first, replacing them with spaces, and every check runs on *that*. Without it the checks are trivially defeated and simultaneously produce false positives:

| Input | Verdict | Why |
|---|---|---|
| `SELECT * FROM mart.audit WHERE action = 'delete from ledger'` | allowed | The word is inside a string. A naive filter blocks this. |
| `SELECT 1; DROP TABLE core.fct_order_line` | rejected | Second statement — the classic way a "read-only" tool executes a DELETE. |
| `SELECT 1; /* nothing to see */ DROP TABLE x` | rejected | Comments cannot hide a statement separator. |
| `WITH gone AS (DELETE FROM t RETURNING *) SELECT * FROM gone` | rejected | A writable CTE. It starts with `WITH`, and it writes. |
| `SELECT * INTO new_table FROM mart.sales` | rejected | `SELECT … INTO` quietly creates a table. |
| `SELECT pg_read_file('/etc/passwd')` | rejected | Nothing an analytics question needs reaches outside the database. |
| `SET statement_timeout = 0` | rejected | No session tampering. |
| `/* nested /* deeper */ still comment */ DROP TABLE x` | rejected | Postgres block comments nest; the stripper does too. |

Row caps are applied by **wrapping**, not appending: `SELECT * FROM (⟨query⟩) AS _guarded LIMIT n`. Appending `LIMIT` to a query that already has one is a syntax error, and appending to a `UNION` silently limits the last branch only.

---

## Designing tools for a model rather than a human

The server fetches one row more than the cap so it can report `truncated: true` with a note. **A model that does not know it saw a partial result will reason as if it saw everything** — and then confidently tell someone the top location is whichever one happened to sort first.

Errors are written for the caller deciding what to do next:

> `schema "core" is not exposed. This connection serves: mart. Call list_tables to see what is available.`

rather than `permission denied for table dim_item`, which sends a model looking for a way around instead of somewhere useful.

Failures come back as tool *results* with `isError: true`, not protocol errors. A protocol error aborts the call; a readable tool result lets the model correct itself. Unexpected exceptions are logged for us and generalised for the caller, because a raw Postgres error can carry a connection string or an internal path.

---

## Tools

| Tool | Purpose |
|---|---|
| `list_tables` | Every table, view and materialized view in the exposed schemas, with descriptions. Cheap; call it first. |
| `describe_table` | Columns, types, nullability and column comments for one relation. |
| `run_query` | One read-only `SELECT`, capped, timed out, inside a read-only transaction. |

Table and column **comments are surfaced in the output**, which is the cheapest schema-documentation win available: a `COMMENT ON COLUMN` written once stops a model guessing what `net_amount` means for the rest of the model's life.

---

## Running it

```bash
DATABASE_URL='postgres://warehouse_reader:…@host/db' \
EXPOSED_SCHEMAS=mart \
DEFAULT_ROW_LIMIT=200 \
npx warehouse-mcp-server
```

In an MCP client's config:

```json
{
  "mcpServers": {
    "warehouse": {
      "command": "npx",
      "args": ["-y", "warehouse-mcp-server"],
      "env": {
        "DATABASE_URL": "postgres://warehouse_reader:…@host/db",
        "EXPOSED_SCHEMAS": "mart"
      }
    }
  }
}
```

Use a role that can only read the schemas you name. That role is the security boundary; everything in this process is the second and third lines of defence.

| Variable | Default | |
|---|---|---|
| `DATABASE_URL` | — | required; point it at a read-only role |
| `EXPOSED_SCHEMAS` | `mart` | comma-separated |
| `DEFAULT_ROW_LIMIT` | `200` | |
| `MAX_ROW_LIMIT` | `5000` | hard ceiling the caller cannot raise |
| `STATEMENT_TIMEOUT_MS` | `15000` | |

Note that `stdout` is the protocol channel — all logging goes to `stderr`, because a stray `console.log` corrupts the stream and produces a failure that is genuinely baffling to debug.

---

## Layout

```
src/
  sql-guard.js  literal/comment-aware stripper + read-only policy + row-limit wrapper
  tools.js      the three tools, transport-agnostic and independently testable
  server.js     MCP wiring — the thinnest file here, on purpose
test/
  guard.test.js     16 tests, every bypass above
  tools.test.js     12 tests against real Postgres via PGlite
  protocol.test.js  5 tests over a real MCP client↔server transport pair
```

Requires Node 20+. MIT licensed.