MCP server over guarded warehouse queries

A Model Context Protocol server that gives an AI assistant read-only, allowlisted access to a TPC-H sales warehouse, and a differential attack suite that proves the query guards still hold when the caller is a model speaking JSON-RPC instead of a person calling a Python API.

Live demo: not deployed — runs locally over stdio inside any MCP client Stack: Python 3.13 · mcp 2.x SDK · DuckDB 1.5.5 · pandas · uv · pytest

1. The problem

Analysts want to ask an assistant questions of the warehouse. The obvious way to allow that, a "run this SQL" tool, hands a language model (and anything that can inject text into its context) a database connection. query-service already solved this for a human client with four guard layers: bound values, allowlisted identifiers, a single-read-statement check by DuckDB's own parser, and a read-only connection with a row cap. This project exposes that service over MCP and asks whether the guards survive the new boundary: the same payload sent through the protocol must get the same verdict as a direct call, with zero writes, zero over-cap results, and error text that never repeats what the caller sent.

Related MCP server: DB Insights MCP Server

2. The data

3. Architecture

flowchart LR
    C[MCP client<br/>assistant or test] -- JSON-RPC over stdio --> S[GuardedServer<br/>strict arguments,<br/>input-free errors]
    S --> G[Gateway<br/>classify exceptions<br/>into stable codes]
    G --> Q[QueryService<br/>vendored from #15]
    Q --> L1[catalog: typed params,<br/>allowlisted identifiers]
    Q --> L3[one read statement,<br/>checked by DuckDB's parser]
    Q --> L4[read-only connection,<br/>row cap 100k]
    L4 --> W[(TPC-H<br/>DuckDB file)]
    A[attack suite] -. same payload .-> S
    A -. same payload .-> Q

Every rejection is is_error: true with a JSON body {"code", "message"}, code one of UNSAFE_QUERY, UNKNOWN_QUERY, BAD_PARAM, QUERY_FAILED, INTERNAL.

4. Key decisions & tradeoffs

5. Results

All numbers below come from files in results/ and can be regenerated with the commands in §6. The warehouse is TPC-H scale factor 0.1 generated locally; the mini fixture is synthetic.

5.1 Attack suite: the first run failed, and that was the useful part

The pass rule was committed in EVAL_PLAN.md before the suite existed: 0 bypasses (no write, no result over the row cap, no payload the server accepts that the bare service rejects, no expect: reject payload accepted, and, added in Amendment 2, no result over the 4 MB size budget), 0 errors that repeat caller input, and a stable code on every tool-call rejection.

Every run uses both transports (in-process and a stdio subprocess). The final runs are at df38f15; the middle two rows are recorded in EVAL_PLAN.md Amendment 2, because their report files were overwritten by the reruns. The first-run report is a reproduction on a clean clone with the pre-fix server files; the original in-session output was not saved.

What the first run found, and what fixed it:

Two more problems surfaced outside the suite and were fixed before the rerun: the tool-selection eval (§5.3) showed that params: {"limit": 5} collided with QueryService.run(limit=...) and crashed as INTERNAL (a use_cache key would have reached the cache switch the same way), and testing the document-search resource showed that the SDK repeats an unknown resource URI in its error. The 8 payloads added after the first run are marked [A1] and listed in the plan's Amendment 1.

An independent review from a clean context then tried about 110 payloads by hand. It found no write, file read or row-cap bypass, and every number above matched results/, but it found what the suite had not tried, now covered by 9 [A2] payloads:

In the final runs, MCP and the bare service agree on 175 of 189 comparable results (92.6%). All 14 disagreements (7 payloads × 2 transports) are the server being stricter on purpose, each tagged with its reason in the suite: string, boolean and fractional limits (lm-04..lm-06), arguments outside the tool's schema (ua-01, ua-03), a params key naming the service's own use_cache switch (ps-02), and an empty date window (ex-08). The echo check excludes words the server itself publishes (tool, parameter and query names); without that exclusion it counts 106 hits, all names like end_date or run_sql. One payload, a lone UTF-16 surrogate (ex-07), cannot be sent over stdio at all (the client's JSON encoder refuses it), so it is judged in-process only.

5.2 Latency: what the protocol costs

p50 in ms over 30 timed calls after a warm-up, TPC-H sf 0.1 (full table). Not a quiet machine: five other projects were being built at the same time, so compare within a row, not with other runs. Measured at 25bca0f, before the per-query time limit existed; the worker thread and timer it adds to every query, cached or not, are not in these numbers.

The overhead is not the protocol hop, it is the rows. On a cache hit, the MCP layer adds about 3 ms to a 5-row answer and about 51 ms to a 1,000-row, 6-column one, almost all of it turning a DataFrame into JSON twice (structured content plus a text copy). The stdio pipe adds 0-5 ms per call. Starting a stdio server (spawn to a ready client) took 7-10 s here, so a client should keep one process alive, which is what desktop clients do.

5.3 Tool selection with a local model (secondary, not gated)

qwen2.5:7b-instruct (Q4, Ollama, temperature 0) got the tool list, the server instructions and the catalog, and one of 20 analyst questions frozen before the first call (results, tasks). Correct means the right query and every pinned parameter equal to the reference.

11/20 correct (Wilson 95% interval 0.34 to 0.74). It never picked the wrong query. Of the 9 misses:

• 7 are the same mistake: an inclusive end date (1995-12-31 for "1995" instead of 1996-01-01), although the tool description says end_date is exclusive and one task says "both days included". The server accepted all 7 and returned a plausible answer missing the last day. No guard can catch that: the call is valid.

• 1 added a filter the query does not have (rejected with BAD_PARAM).

• 1 put limit inside params, which exposed the crash described in §5.1.

The run replays offline from the recorded responses; after the fixes the score is the same and the crash is a coded BAD_PARAM. With n = 20 the interval is wide, so this says nothing about other models, only that a 7B model reads a half-open date window the way people do.

5.4 What this does and does not show

• It shows that, for these 111 payloads, the server's verdicts are at least as strict as the bare service's on both transports and on both warehouses, that nothing was written, and that no error repeated caller input. It does not prove that no other payload gets through: the first run found a class of hole (table functions) that the original #15 tests had not tried, and the review found another (resource exhaustion) that the suite had not tried.

• Known limits, not fixed here:

  • The time limit bounds how long the caller waits, not the work. An abandoned query keeps using CPU and memory until it ends, and the limit covers the query, not turning its rows into JSON. A hard bound needs the query in a separate process or limits in the database itself. This bit in hosted CI: ex-04's abandoned copies exhausted a 16 GB runner, so CI runs the suite with --exclude ex-04 (listed in its report) and the full suite runs locally.

  • DECIMAL and HUGEINT values arrive as float64 (the vendored service converts results through pandas): exact to about 15 significant digits. TPC-H's largest sum here has 14.

  • With --allow-adhoc, SELECT * FROM duckdb_databases() shows the warehouse file's absolute path. Metadata functions are not blocked.

  • Prompt calls the SDK rejects before the prompt function runs (missing or mistyped arguments) come back as an uncoded Internal server error: 4 of the suite's 12 prompt rejections.

  • SELECT 1; -- comment is rejected (QUERY_FAILED): the upstream limit wrapper strips only a final semicolon. It fails closed.

• The most damaging failure in this project is not an attack. It is a valid call with an off-by-one date that returns a wrong number with no error. §7 says what I would change.

6. How to run

Prerequisites: uv and Python 3.13 (uv installs it). Ollama only for the optional tool-selection eval.

git clone https://github.com/Prithv122/mcp-server.git
cd mcp-server
uv sync
uv run pytest                      # offline: uses the mini fixture
uv run mcpquery attack-suite       # the differential suite on the mini fixture, both transports (~16 GB+ RAM for ex-04)

The real warehouse and the measurements:

uv run mcpquery build-db --sf 0.1                                     # TPC-H, ~20 s; fetches DuckDB's tpch extension once
uv run mcpquery attack-suite --db data/warehouse.duckdb --out results/attacks/tpch_sf0.1.json
uv run mcpquery latency --db data/warehouse.duckdb --out results/latency_sf0.1.json
uv run mcpquery tool-eval --db data/warehouse.duckdb --replay         # re-scores the recorded model responses, no model needed

Use it from an MCP client: copy .mcp.json.example, set the repository path, and the client starts mcpquery serve over stdio. Add --allow-adhoc to the arguments to expose run_sql.

7. What I'd change at 100× scale

• Make the date window impossible to misread. The biggest measured error is a valid call with a wrong answer. Replace start_date/end_date with a period argument (1995, 1995-Q3, 1995-03) the server expands, or return the resolved window in every result ("window": "1995-01-01 <= o_orderdate < 1995-12-31") so the mistake is visible in the answer.

• Stream or page large results. Serialisation is the cost (51 ms for 1,000 rows here). At 100x the rows, return a page plus a cursor, send rows once instead of twice, and for analytics clients offer Arrow instead of JSON.

• Move layer 4 to the database. A read-only DuckDB file on one machine becomes a warehouse role with SELECT grants on named views only, no file or network functions, and a statement timeout. The protocol layer keeps its checks; the database stops trusting them.

• One server per user, not per process. stdio means one server process per client. Shared use needs the Streamable HTTP transport, authentication, per-user row limits and an audit log of every tool call, which is where the error-code contract pays off.

• Bound the work, not just the wait. Run each ad-hoc query in a worker process with a memory cap that can be killed, or push the limits into the database (statement timeout, memory limit per role). The thread-and-abandon design here keeps the caller responsive but cannot reclaim an abandoned query's CPU and memory.

• Run the attack suite against every engine upgrade. Layer 3 depends on DuckDB's parser and its statement types; a new DuckDB version could add a statement or table function the suite has not seen. The suite runs in CI on every push, so a dependency bump runs it too.

References

• Model Context Protocol specification and the official Python SDK (mcp, MIT).

• TPC-H benchmark specification (schema), via DuckDB's tpch extension.

• query-service (#15), the guarded service this server exposes.