Skip to main content
Glama
mcp-sql-gateway

mcp-sql-gateway

README.md
# mcp-sql-gateway

[![CI](https://github.com/MateoCuenca09/mcp-sql-gateway/actions/workflows/ci.yml/badge.svg)](https://github.com/MateoCuenca09/mcp-sql-gateway/actions/workflows/ci.yml)
![Python](https://img.shields.io/badge/python-3.11%20%7C%203.12%20%7C%203.13-blue)
![License](https://img.shields.io/badge/license-MIT-green)

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

A3.6/5.0

Scored across 4 tools

Disambiguation5/5

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.

Naming Consistency4/5

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.

Tool Count5/5

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.

Completeness4/5

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.