mcp-sql-gateway
# mcp-sql-gateway
[](https://github.com/MateoCuenca09/mcp-sql-gateway/actions/workflows/ci.yml)


An **MCP (Model Context Protocol) server** that lets Claude or any MCP client query a SQL
database in natural language, **without handing the LLM the keys to the warehouse**.
Every request goes through a governance layer:
- 🔐 **Per-user data profiles**: which tables each user may read
- 🕶️ **Column masking**: sensitive columns (email, phone…) come back as stable hashes
- 🛡️ **SQL guard**: one read-only `SELECT` only; tables are checked against the profile, even inside subqueries; a row `LIMIT` is always enforced
- 📊 **Daily quotas** per user
- 🧾 **Audit log** of every attempt: accepted, rejected, failed or over quota, with the original and the executed SQL
- 🔒 **Read-only connection** as defense in depth
> This is a clean-room demo with a synthetic dataset. It shows the same architecture I use in
> production with Azure Synapse and Microsoft Entra ID sign-in.
## Architecture
```mermaid
flowchart LR
U[User in Claude / MCP client] -->|tool call| S[MCP server<br/>MCP Python SDK v2, stdio]
S --> I{Identity<br/>GATEWAY_USER<br/><i>prod: Entra ID token</i>}
I --> P[Profile registry<br/>profiles.yaml]
P --> Q[Quota check]
Q --> G[SQL guard<br/>sqlglot AST]
G -->|rewritten SQL + LIMIT| DB[(Data DB<br/>read-only)]
DB --> M[Column masking]
M -->|JSON rows| S
Q -. every attempt .-> A[(Audit log)]
G -. rejected .-> A
M -. ok .-> A
```
| Tool | What it does |
|---|---|
| `list_tables` | Tables and views the caller may query |
| `describe_table` | Columns and types, flagging masked ones |
| `run_query` | Validates, rewrites and runs one `SELECT`; returns rows, the executed SQL and the remaining quota |
| `my_recent_queries` | The caller's own audit trail |
## Quick start
```bash
git clone https://github.com/MateoCuenca09/mcp-sql-gateway.git
cd mcp-sql-gateway
python -m venv .venv && source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install -e ".[dev]"
python scripts/seed_demo_db.py # creates data/demo.db (synthetic)
```
### Connect it to Claude Desktop
Add this to `claude_desktop_config.json`, using absolute paths:
```json
{
"mcpServers": {
"sql-gateway": {
"command": "/path/to/mcp-sql-gateway/.venv/bin/mcp-sql-gateway",
"env": { "GATEWAY_USER": "ana.analyst@example.com" }
}
}
}
```
Then ask: *"Which product category sold the most in Q3 2025?"* or *"Show me the emails of
customers in Córdoba"*. As `ana.analyst` the emails come back masked; switch to
`fede.finance@example.com` and they don't.
You can also try it with the MCP Inspector: `npx @modelcontextprotocol/inspector mcp-sql-gateway`.
## Configuration
`config/profiles.yaml` maps users to profiles:
```yaml
profiles:
analyst:
tables: [orders, products, customers]
masked_columns: [email, phone]
max_rows: 200
daily_quota: 100
users:
ana.analyst@example.com: analyst
```
| Env var | Default |
|---|---|
| `GATEWAY_USER` | required, the caller's identity |
| `GATEWAY_PROFILES` | `config/profiles.yaml` |
| `GATEWAY_DATA_DB` | `data/demo.db` |
| `GATEWAY_AUDIT_DB` | `data/audit.db` |
## Tests
```bash
pytest -v
```
The tests cover write and multi-statement rejection, forbidden tables in subqueries, CTEs,
limit capping, masking per profile, quota exhaustion, audit entries for every outcome and the
read-only connection. CI runs ruff and pytest on Python 3.11–3.13.
## Taking it to production
- **Identity**: run over Streamable HTTP behind OAuth (e.g. Microsoft Entra ID) and take the
user from the validated token instead of an env var.
- **Database**: swap SQLite for Azure SQL / Synapse (`pyodbc`) and set `dialect="tsql"` in the
guard. Use a database login that only has `SELECT` on the exposed schemas.
- **Audit**: send the log to a durable store (Azure Table Storage, Log Analytics).
- **Secrets**: Key Vault + Managed Identity, no connection strings in config.
## License
MIT
TDQS
Scored across 4 tools
Each tool maps to a distinct step in the query workflow: list_tables (discover), describe_table (inspect schema), run_query (execute), my_recent_queries (audit review). There is no overlap in purpose or return shape, so an agent can select correctly on the first try.
All names are lowercase snake_case and three of four follow a verb_noun pattern (describe_table, run_query, list_tables). my_recent_queries is a noun phrase rather than verb-led, a minor deviation but still immediately readable and thematically consistent.
Four tools is well-scoped for a deliberately read-only SQL gateway; each tool covers a necessary phase of the workflow and none feels redundant. Nothing is padded or missing from a minimal viable surface.
The read-only lifecycle is fully covered: discovery, schema inspection, execution, and audit traceability, with no dead ends for the stated purpose. Minor gaps exist (no way to inspect indexes/keys or paginate the audit log beyond the default limit), but agents can work around these.