# sql-guard-mcp

An MCP server that lets an AI agent (Claude, ChatGPT, Cursor or your own) answer questions from a SQL database **without being able to change data, read protected columns or see another customer's rows**, whatever SQL the model writes.

> "Which product categories earned the most this year, excluding returns?"
> The agent looks up the tables, writes the SQL, runs it through this server and answers with real numbers.

Giving a model a database connection is the fastest way to a demo and the slowest way to production. This repository shows the production version: a small, tested server where every safety rule is configuration, enforced twice.

## What the agent gets

Four task-level tools, all marked read-only:

| Tool | What it does |
|---|---|
| `list_tables` | Tables the agent may use, with descriptions and row counts |
| `describe_table` | Readable columns and types, plus three sample rows |
| `run_query` | Runs one SELECT and returns columns and rows (capped) |
| `explain_query` | Shows the SQL that would run and SQLite's query plan, without running it |

Errors come back as JSON the model can act on:

```json
{"error": {"code": "COLUMN_NOT_ALLOWED",
           "message": "Column 'email' on 'customers' is protected and cannot be read.",
           "retryable": true,
           "hint": "Readable columns of customers: id, store_id, full_name, city, joined_on."}}
```

The model fixes its query and tries again, instead of guessing or giving up.

## How it stays safe

```mermaid
flowchart LR
    A[AI agent] -->|MCP tool call: SQL| G
    subgraph S[sql-guard-mcp]
      G[1. SQL guard<br/>parse, check, rewrite] --> D[2. Read-only connection<br/>3. SQLite authorizer]
      G -.->|every call| L[(Audit log<br/>JSON lines)]
    end
    D --> DB[(Database)]
    P[policy.toml] -.-> G
    P -.-> D
```

| Layer | What it enforces |
|---|---|
| **1. SQL guard** (`guard.py`) | Exactly one statement; SELECT only; only allowed tables; no protected columns; no `SELECT *` where it would expose them; denied functions blocked; a row limit added; scoped tables rewritten to their scope views. Fails fast with a hint. |
| **2. Read-only connection** (`db.py`) | The file is opened with `mode=ro`. No statement can write, whatever gets through. |
| **3. SQLite authorizer** (`db.py`) | SQLite asks the server to approve every table and column read while compiling a statement. Disallowed tables, protected columns and direct reads of scoped tables are refused by the engine itself, so a gap in the parser cannot leak data. |
| **Limits** | Results capped (`max_rows`), runaway queries stopped (`timeout_ms`). |
| **Audit** | One JSON line per call: tool, SQL, executed SQL, outcome, rows, duration. |

### Row scoping (multi-tenant)

A table can carry a fixed `scope` predicate. The demo serves one store of two:

```toml
[tables.orders]
scope = "store_id = 1"
```

At start-up the server creates a temporary view per scoped table that applies the predicate and drops protected columns. The guard rewrites every reference to the view, and the authorizer refuses to read the underlying table directly. The tenant comes from configuration, never from the model, so `WHERE store_id = 2` simply returns nothing.

## Configuration

Access is a config file, not code. See [`examples/policy.toml`](examples/policy.toml):

```toml
[database]
path = "shop.db"

[limits]
max_rows = 200
timeout_ms = 2000

[audit]
path = "audit.log.jsonl"

[tables.customers]
description = "Customers of the store. Contact details are protected."
hidden_columns = ["email", "phone"]
scope = "store_id = 1"

[tables.products]
description = "Product catalogue shared by all stores."
```

The server checks the policy against the real schema at start-up and refuses to run if it names a table or column that does not exist.

## Quick start

Python 3.11 or newer.

```bash
git clone https://github.com/pratikpande87/sql-guard-mcp.git
cd sql-guard-mcp
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

python examples/make_demo_db.py      # builds examples/shop.db (fixed seed)
python examples/demo_client.py       # starts the server over stdio and calls it like an agent would
```

### Use it from Claude Desktop

Add this to `claude_desktop_config.json` (Settings, Developer, Edit Config), with absolute paths:

```json
{
  "mcpServers": {
    "shop-db": {
      "command": "/path/to/sql-guard-mcp/.venv/bin/sql-guard-mcp",
      "args": ["--policy", "/path/to/sql-guard-mcp/examples/policy.toml"]
    }
  }
}
```

Restart Claude Desktop and ask: *"Which customers in Pune ordered the most last quarter?"* or *"Compare monthly revenue for Electronics and Apparel in 2026."*

To serve over HTTP instead of stdio:

```bash
sql-guard-mcp --policy examples/policy.toml --transport streamable-http --port 8000
```

## Tests

```bash
pytest
```

30 tests cover the guard (write statements, stacked statements, protected columns, `SELECT *`, CTE tricks, scoping, limits), the engine-level backstop (queries that skip the parser are still refused), timeouts, the audit log and the MCP surface (tool list, annotations, error shape).

## Design notes

- **Few, task-named tools.** Four tools that match how an analyst works beat one tool per table or per endpoint: the model picks the right one more often.
- **Two independent enforcement points.** The parser gives good errors; the engine gives guarantees. Neither trusts the other.
- **Everything is configuration.** Onboarding a new database or tenant is a new policy file.

### Limitations

- SQLite only in this version. For PostgreSQL, the same design maps to a read-only role, `default_transaction_read_only`, `statement_timeout` and row-level security keyed on a session setting the server controls.
- SQLite flattens simple views, so a bare `COUNT(*)` that bypassed the parser could not be attributed to a scope view by the authorizer. Column reads always can. Queries through the server always go through the views.

## License

MIT