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.

Related MCP server: Secure RDS Read-Only MCP Server

Tools

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

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:

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

Or add to claude_desktop_config.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.