# MCP Warehouse Server

An MCP server that gives AI agents **governed** access to a Postgres data warehouse —
not a free-for-all SQL tool. Every request is policy-checked, row-capped, and audited.

## Why

The default pattern for "let an agent query my database" is to hand it a SQL tool
and hope the prompt holds. That fails on the things data teams actually care about:
PII exposure, runaway queries, and no record of who asked what.

This server takes the opposite approach: the agent gets a narrow, discoverable
interface, and enforcement lives in the database, not the prompt.

## Tools

| Tool | Purpose |
|---|---|
| `list_tables` | Discover queryable tables and row counts |
| `describe_table` | Column names and types for one allowed table |
| `run_query` | Run a validated, row-capped read-only SELECT |
| `get_audit_log` | Read back the trail of what was asked and whether it was allowed |

## Defence in depth

Three independent layers, so no single failure leaks data:

1. **Database grants** — the `mcp_agent` role has `SELECT` on `analytics` only.
   It has no grant at all on the `raw` schema that holds SSN and date of birth.
   Even a perfectly crafted injection gets `permission denied for schema raw`.
2. **Policy layer** (`policy.py`) — single statement only, `SELECT`/`WITH` only,
   keyword blocklist, table allow-list, automatic `LIMIT` injection.
3. **Session guards** (`db.py`) — every query runs in a `READ ONLY` transaction
   with a `statement_timeout`.

**Known limitation:** the policy layer is regex-based, not a real SQL parser.
It is a filter, not a guarantee. The security guarantee comes from layer 1.
A production version would use a parser (e.g. `sqlglot`) and per-caller identity
from the transport's auth context instead of a hardcoded caller.

## Audit trail

Every tool call writes to `governance.query_audit`: timestamp, caller, tool,
the exact SQL executed, allow/deny, deny reason, rows returned, duration.

## Data model

- `raw.members` — PII. Never reachable by the agent.
- `analytics.dim_member`, `dim_provider`, `fct_claims` — the agent-safe star schema.
- `governance.query_audit` — the log.

Synthetic healthcare claims data (200 members, 40 providers, 3000 claims).

## Setup

```bash
python3.12 -m venv venv && source venv/bin/activate
pip install "mcp[cli]" "psycopg[binary]" python-dotenv

createdb warehouse
psql -d warehouse -f sql/schema.sql
psql -d warehouse -f sql/roles.sql   # change the password first
psql -d warehouse -f sql/seed.sql

cp .env.example .env                 # set your DSN
```

Test locally:

```bash
npx @modelcontextprotocol/inspector ./venv/bin/python server.py
```

Or add to `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "warehouse": {
      "command": "/absolute/path/venv/bin/python",
      "args": ["/absolute/path/server.py"]
    }
  }
}
```

## Stack

Python 3.12, MCP Python SDK 2.x (`MCPServer`), Postgres 17, psycopg 3.