warehouse-mcp-server

A Model Context Protocol 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

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

In an MCP client's config:

{
  "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.