warehouse-mcp

A read-only Model Context Protocol server that lets an AI assistant explore and query your database, with guardrails you can explain to a security reviewer.

Point Claude (or any MCP client) at Postgres, DuckDB or Snowflake. The assistant can list tables, read column descriptions and run SQL. It cannot write, lock, change settings, read files, run for ever, return a million rows, or see the columns you mark as personal data.

$ warehouse-mcp query -c examples/warehouse-mcp.yaml \
    "select channel, count(*) as orders, round(avg(discount_pct), 1) as avg_discount
     from orders where status not in ('cancelled', 'returned')
     group by channel order by orders desc"

| channel | orders | avg_discount |
|---|---|---|
| web | 9222 | 9.1 |
| app | 5703 | 8.7 |
| store | 1627 | 8.4 |

3 rows in 43.3 ms
SQL: SELECT orders.channel AS channel, COUNT(*) AS orders, ROUND(AVG(orders.discount_pct), 1)
AS avg_discount FROM main.orders AS orders WHERE NOT orders.status IN ('cancelled', 'returned')
GROUP BY orders.channel ORDER BY orders DESC LIMIT 101

$ warehouse-mcp query -c examples/warehouse-mcp.yaml "select * from customers"
[blocked_column] Column customers.email is restricted and cannot be queried (including
through SELECT *). Select the other columns by name.

This is exactly the text the model receives from the run_query tool (plus the same data as structured JSON).

Contents

• Quick start

• Tools

• Security model

• Configuration

• Connecting your database

• Running it remotely

• Tests

• Design decisions

• Status and limitations

Related MCP server: pg-readonly-mcp

Quick start

A synthetic online-shop database (customers with emails and phone numbers, orders, products, support tickets, and an hr schema that should stay hidden) is included for trying it out.

python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

warehouse-mcp demo-db duckdb://examples/demo/shop.duckdb     # ~2 seconds
warehouse-mcp tables -c examples/warehouse-mcp.yaml           # what the model will see
warehouse-mcp query  -c examples/warehouse-mcp.yaml "select count(*) from orders"
warehouse-mcp check  -c examples/warehouse-mcp.yaml "select email from customers"

Use it from Claude Code

claude mcp add shop-warehouse -- \
  warehouse-mcp serve -c "$PWD/examples/warehouse-mcp.yaml"

Then ask something like "Which product category has the best gross margin, and has it changed this year?". Once the package is on PyPI, the command can be uvx --from 'warehouse-mcp[duckdb]' warehouse-mcp serve -c ... with nothing installed first.

Use it from Claude Desktop

Add this to claude_desktop_config.json (Settings → Developer → Edit config). Full example in examples/claude_desktop_config.json:

{
  "mcpServers": {
    "shop-warehouse": {
      "command": "/ABSOLUTE/PATH/TO/.venv/bin/warehouse-mcp",
      "args": ["serve", "-c", "/ABSOLUTE/PATH/TO/examples/warehouse-mcp.yaml"]
    }
  }
}

Tools

All tools are annotated readOnlyHint: true, openWorldHint: false.

Resources: warehouse://policy (the active guardrails, so the model knows the rules) and warehouse://glossary (your business definitions, from a Markdown file). Prompt: answer_data_question, which walks the model through explore → query → check → answer with SQL.

Security model

The server assumes the model can be wrong or manipulated (prompt injection through a document, a web page, or even a value stored in the database) and still keeps the database safe. No single layer is trusted; each one below would stop the attack on its own.

The guard is tested against an attack corpus of 83 queries in two dialects. The leak tests go further: every attempt to read a blocked column is run through the guard and then executed against a database that does not protect the column itself (a plain DuckDB file, and Postgres as superuser), and the test fails if an email or phone number comes back. Layers 1 and 2 are tested separately with the guard switched off.

Building this found real bypasses that a first version missed: row_to_json(c) (a whole-row reference returns every column), customers AS x(a, b, e) (a column alias list hides which column e is), and DuckDB's COLUMNS('e.*') and #3. They are all in the corpus now.

What it does not protect against, so you can decide what else you need:

• Sensitive data in allowed columns. If full_name is allowed, the model can read names. Block or don't grant what you don't want read; use views to expose aggregates only.

• Data used as instructions. A support ticket that says "ignore your instructions" is returned as data like any other value. Keep a human in the loop for actions taken on the results.

• User-defined functions. A function in an allowed schema that reads a restricted table is not something a SQL parser can see into. The database role (layer 1) covers this: don't grant EXECUTE on such functions.

• Inference. Aggregates over allowed columns can still reveal things (for example, a count grouped by a rare value). This is a data-governance question, not a SQL one.

Configuration

examples/warehouse-mcp.yaml, annotated:

database: ${WAREHOUSE_URL}          # ${VAR} and ${VAR:-default} come from the environment

allowed_schemas: [public]           # empty = every non-system schema
denied_tables: ["*_staging", "audit.*"]
blocked_columns:                    # column | table.column | schema.table.column, * wildcards
  - email
  - customers.phone
  - "*.date_of_birth"

limits:
  max_rows: 100                     # default rows returned
  max_rows_cap: 1000                # the most the model may ask for
  max_cell_chars: 300
  max_result_bytes: 60KB            # about 15k tokens
  statement_timeout_s: 15
  max_explain_cost: 5000000         # Postgres planner units; omit to disable
  max_scan_bytes: 50GB              # Snowflake only
  queries_per_minute: 30

audit_log: logs/audit.jsonl         # relative paths are relative to this file
glossary: glossary.md               # served as warehouse://glossary
server_name: shop-warehouse

A denied table is reported to the model as "does not exist or is not available", the same message as a missing table, so the policy does not reveal what exists. Typos in the config fail at start-up rather than silently loosening a limit.

Column and table descriptions come from the database (COMMENT ON in Postgres and DuckDB, COMMENT in Snowflake). They are the cheapest way to make the assistant's SQL better: the demo's orders.status description says revenue excludes cancelled and returned orders, and the glossary defines net revenue, AOV and margin.

Connecting your database

For Postgres, create a dedicated role with sql/postgres_readonly_role.sql: column-level grants for tables with personal data, a role-level statement_timeout, and nothing else. With that role, SELECT * on customers simply returns the granted columns.

For Snowflake, sql/snowflake_role.sql sets up a service user, a SELECT-only role, an extra-small warehouse with a resource monitor (a hard monthly credit cap), and a masking policy. Install with pip install "warehouse-mcp[snowflake]". Password via $SNOWFLAKE_PASSWORD, or key-pair auth with ?private_key_file=....

Running it remotely (HTTP)

For a shared team server or a hosted agent, run the streamable HTTP transport. It refuses to start without a token:

export WAREHOUSE_MCP_TOKEN=$(python -c "import secrets; print(secrets.token_urlsafe(32))")
export WAREHOUSE_URL=postgresql://mcp_reader:...@db.internal/analytics
warehouse-mcp serve --http --host 0.0.0.0 --port 8000 -c config.yaml

• Clients send Authorization: Bearer <token>; /healthz is open for load balancers.

• Stateless JSON mode: each request stands alone, so it runs behind any load balancer.

• Set WAREHOUSE_MCP_ALLOWED_HOSTS=mcp.example.com to turn on Host/Origin checking.

• Claude Code: claude mcp add --transport http shop https://mcp.example.com/mcp --header "Authorization: Bearer $TOKEN"

Docker. The image includes the demo database, so it runs with no configuration:

docker build -t warehouse-mcp .
docker run -p 8000:8000 -e WAREHOUSE_MCP_TOKEN=... warehouse-mcp                 # demo data
docker run -p 8000:8000 -e WAREHOUSE_MCP_TOKEN=... -e WAREHOUSE_URL=postgresql://... \
  -v $PWD/config.yaml:/config.yaml -e WAREHOUSE_MCP_CONFIG=/config.yaml warehouse-mcp

It honours $PORT, so it deploys unchanged to Fly.io, Render, Railway or Cloud Run; put the token and database URL in the platform's secrets. A static bearer token suits a team or a single agent. Per-user access (for example, as a claude.ai custom connector) needs OAuth in front of it, which this project does not include yet.

Tests

pytest                                                      # DuckDB tests: no services needed
WAREHOUSE_MCP_TEST_PG=postgresql://postgres:postgres@localhost/postgres pytest   # + Postgres

394 tests, passing on Python 3.10, 3.11, 3.12 and 3.13 against Postgres 16:

CI runs lint, the tests on Python 3.10–3.13 against a Postgres 16 service container, checks that docs/attack-corpus.md is up to date, and builds and smoke-tests the Docker image.

Design decisions

• Why parse SQL at all, if the database role is the real boundary? Because the guard gives the model a precise, fixable reason (Column customers.email is restricted…) before anything reaches the database, catches things a role can't (sleeping, huge cross joins, reading local files on DuckDB), and works on DuckDB files, which have no roles at all. And because defence in depth means assuming any one layer has a bug.

• Execute the rewrite, not the original. Checking one string and running another is how parser-differential bypasses happen. After checking, the syntax tree is regenerated as SQL with stars expanded against the catalog, so a restricted column cannot come back through * even if the database would allow it.

• Refuse rather than silently strip. SELECT * on a table with a blocked column is refused with a message, not quietly narrowed. The model then writes the query it means, and the answer never silently omits a column.

• limit + 1. The server fetches one row more than it returns, so it can say "truncated" honestly instead of guessing.

• Results as a Markdown table plus structured JSON. The table is compact for the model's context and readable for people; the JSON is there for programmatic clients.

• Catalog refresh. The catalog is cached for five minutes and refreshed once on an unknown-table error, so new tables appear without a restart.

Status and limitations

• Snowflake support is written against the connector's documented API and the guard is tested in the Snowflake dialect, but the adapter has not been run against a live account.

• BigQuery, Databricks SQL, MySQL: not supported yet. The adapter interface is four methods (load_tables, execute, explain, close); sqlglot already speaks their dialects.

• The guard only allows what sqlglot can parse. Some valid but unusual syntax (Postgres TABLE t, DuckDB USING SAMPLE combined with the added LIMIT) is refused or fails; the model gets the error and can rewrite the query.

• The rate limit is per server process: per client over stdio, global over HTTP.

Project layout

src/warehouse_mcp/
  server.py       MCP tools, resources and prompt (MCP Python SDK v2)
  warehouse.py    the service: policy, guard, cost check, limits, audit (no MCP types)
  sql_guard.py    sqlglot checks and rewrite
  catalog.py      tables and columns after the policy is applied
  adapters/       postgres.py, duckdb.py, snowflake.py
  http.py         streamable HTTP + bearer token
  demo.py         synthetic shop database
  cli.py          serve, check, query, tables, demo-db
tests/attack_corpus.py   the queries every dialect must refuse or allow
docs/attack-corpus.md    generated report
sql/                     least-privilege roles for Postgres and Snowflake

Licence

MIT. The demo data is synthetic; any resemblance to real people is coincidental.