# warehouse-mcp

**A read-only [Model Context Protocol](https://modelcontextprotocol.io) 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](#quick-start)
- [Tools](#tools)
- [Security model](#security-model)
- [Configuration](#configuration)
- [Connecting your database](#connecting-your-database)
- [Running it remotely](#running-it-remotely-http)
- [Tests](#tests)
- [Design decisions](#design-decisions)
- [Status and limitations](#status-and-limitations)

## 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.

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

```bash
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`](examples/claude_desktop_config.json):

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

## Tools

| tool | what it does |
|---|---|
| `list_tables(schema?)` | tables and views with row estimates and descriptions |
| `describe_table(table)` | columns, types, nullability, descriptions; restricted columns are flagged |
| `search_catalog(term)` | find tables and columns by name or description ("revenue", "refund") |
| `run_query(sql, max_rows?)` | run one SELECT; returns a Markdown table, structured rows, and the SQL that actually ran |
| `explain_query(sql)` | the plan without running it; on Postgres, the estimated cost against the limit |
| `sample_rows(table, n)` | a few example rows, restricted columns left out |

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.

| layer | what it enforces | where |
|---|---|---|
| **1. Database role** | SELECT only, and only on granted tables and columns. The recommended Postgres role cannot read `email` even if every other layer failed. | [`sql/postgres_readonly_role.sql`](sql/postgres_readonly_role.sql) |
| **2. Read-only connection** | Postgres: every query runs in `BEGIN READ ONLY` (the session default is read-only too) and is always rolled back. DuckDB: file opened `read_only`, external access disabled, configuration locked. | `adapters/` |
| **3. Timeouts and cost** | `statement_timeout` / `lock_timeout` per query (a timer interrupts DuckDB); optional `EXPLAIN` cost cap on Postgres and bytes-scanned cap on Snowflake. | `adapters/`, `warehouse.py` |
| **4. SQL guard** | parses with [sqlglot](https://github.com/tobymao/sqlglot); allows one SELECT only; rejects DML/DDL anywhere (including inside CTEs), `SELECT INTO`, `FOR UPDATE`, `SET`, `COPY`, `ATTACH`, dangerous functions (`pg_sleep`, `pg_read_file`, `dblink`, `query_to_xml`, `read_csv`, `duckdb_secrets`, …), system catalogs, schemas outside the allowlist, and **any** reference to a blocked column, including via `SELECT *`, `row_to_json(t)`, `t AS x(a, b, e)`, `COLUMNS('regex')` or `#3`. | [`sql_guard.py`](src/warehouse_mcp/sql_guard.py) |
| **5. Rewrite** | the SQL that runs is **regenerated from the checked syntax tree**: stars expanded, columns qualified, comments dropped, LIMIT applied. What was checked is what runs. | `sql_guard.py` |
| **6. Output limits** | row cap (fetches `limit + 1` to detect truncation), long values clipped, total response bytes capped, rate limit per minute. | `warehouse.py` |
| **7. Audit** | one JSON line per call: accepted, rejected or failed, with the original and executed SQL. | `audit.py` |

The guard is tested against an **[attack corpus of 83 queries](docs/attack-corpus.md)** 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`](examples/warehouse-mcp.yaml), annotated:

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

| | URL | status |
|---|---|---|
| Postgres (RDS, Aurora, Neon, Supabase, Cloud SQL…) | `postgresql://mcp_reader:…@host:5432/db` | tested (Postgres 16) |
| DuckDB file | `duckdb:///abs/path.duckdb` or `duckdb://relative.duckdb` | tested |
| Snowflake | `snowflake://USER@ACCOUNT/DATABASE/SCHEMA?warehouse=WH&role=MCP_READER` | **written, not yet run against a live account** |

For Postgres, create a dedicated role with
[`sql/postgres_readonly_role.sql`](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`](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:

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

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

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

| file | what |
|---|---|
| `test_guard.py` | the attack corpus in Postgres and DuckDB dialects; legitimate analytics queries still pass; limit handling; rewrites |
| `test_leaks.py` | every blocked-column attempt executed on an unprotected database; no personal data may come back |
| `test_postgres.py` | the read-only role and connection with the guard **off**: writes refused, restricted columns denied, timeouts, cost cap |
| `test_duckdb.py` | the locked-down DuckDB connection with the guard off: no files, no ATTACH, no config changes; timeout interrupt |
| `test_server.py` | a real MCP client in memory, and the CLI spawned over **stdio**: tools, errors, resources, prompt, rate limit, audit log |
| `test_http.py` | uvicorn + an MCP client over streamable HTTP; token required; Host header checking |
| `test_snowflake.py` | URL parsing and the guard in the Snowflake dialect (no live account) |
| `test_config_and_format.py` | config parsing, name patterns, value serialisation, result budgets |

CI runs lint, the tests on Python 3.10–3.13 against a Postgres 16 service container, checks
that [`docs/attack-corpus.md`](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.