Skip to main content
Glama
readonly-sql-mcp

readonly-sql-mcp

by EliasOlie
README.md
# readonly-sql-mcp

**Give AI agents read access to a SQL database without giving them a way to break it.**

A small gateway that sits between an agent (Claude, Cursor, any MCP client — or plain REST) and a database, and guarantees four things:

1. **Only one `SELECT` gets through.** The query is parsed into an AST with [sqlglot](https://github.com/tobymao/sqlglot) and walked node by node. `DELETE` hidden in a CTE, `SELECT … INTO`, `FOR UPDATE`, stacked statements, `pg_sleep`, `dblink`, Oracle `DBMS_PIPE`… are rejected. Regex filters can be fooled by comments and string tricks; a parse tree cannot.
2. **The agent can't pull the whole table.** Every query is wrapped as `SELECT * FROM (<query>) q LIMIT n OFFSET m`, so a user-written `LIMIT 1000000` is irrelevant. One extra row is fetched to report `has_more` — no `COUNT(*)` over a 40-million-row legacy table.
3. **Identity is someone else's job, authorization is ours.** The caller is identified by a bearer token (local hashed tokens, or a central identity service via `POST /auth/verify`) and must hold `sql:read`. The identity provider never decides what that permission means.
4. **Everything leaves a trace.** Each call — allowed, forbidden, rejected or failed — is appended to a JSONL audit log where every line carries the SHA-256 of the previous one. Edit or delete a line and `/health` reports exactly where the chain breaks.

And underneath the guard, a second wall: the session runs in a read-only transaction with a statement timeout, and you should point it at a role that only has `SELECT` grants.

```
 agent ──Bearer──▶  auth ──▶ permission ──▶ AST guard ──▶ paginate ──▶ READ ONLY txn ──▶ DB
                     │          (sql:read)     │                          + timeout
                     └──────────────────────────┴──────────▶ hash-chained audit log
```

## Why

I built the production version of this for a food distribution & retail group in Brazil whose ERP runs on a legacy Oracle database with thousands of tables. Agents needed to answer "how much did branch 2 sell yesterday?" without anyone being able to `UPDATE` a price by accident — or by prompt injection. This repository is a clean-room, database-agnostic reimplementation of that pattern, with no company code or data.

## Quick start

```bash
pip install -e '.[server,postgres]'     # or .[server,oracle]

export SQL_DIALECT=postgres
export DATABASE_URL=postgresql://readonly_role:CHANGE_ME@localhost:5432/shop
# one hashed token per agent: name:sha256(token):permissions
export GATEWAY_TOKENS="bi-agent:$(printf 'my-token' | sha256sum | cut -d' ' -f1):sql:read"

uvicorn readonly_sql_mcp.server:app --port 8080
```

**REST**

```bash
curl -s localhost:8080/v1/query -H 'Authorization: Bearer my-token' \
  -d '{"sql": "SELECT branch, sum(total) FROM sales WHERE day = :d GROUP BY branch",
       "params": {"d": "2026-11-10"}, "purpose": "daily sales summary"}'
```

**MCP** — add `http://localhost:8080/mcp` as a remote MCP server with the same bearer token. Tools: `list_tables`, `describe_table`, `run_query`.

### Configuration

| Variable | Default | Meaning |
|---|---|---|
| `SQL_DIALECT` | `postgres` | `postgres`, `oracle` or `sqlite` (also the sqlglot dialect) |
| `DATABASE_URL` | — | Postgres/SQLite connection string |
| `ORACLE_USER` / `ORACLE_PASSWORD` / `ORACLE_DSN` | — | Oracle connection |
| `MAX_ROWS` | `500` | hard cap per page |
| `ALLOWED_TABLES` | *(any)* | comma-separated allowlist; include `information_schema.tables,information_schema.columns` if you use the metadata tools |
| `STATEMENT_TIMEOUT_MS` | `15000` | per-query timeout (Postgres) |
| `GATEWAY_TOKENS` | — | `name:sha256:perm1,perm2;…` |
| `AUTH_VERIFY_URL` | — | central identity service; overrides `GATEWAY_TOKENS` |
| `AUDIT_LOG` | `./audit/queries.jsonl` | audit file |

## Tests

```bash
pip install -e '.[dev]' && pytest
```

The suite covers write attempts hidden in CTEs and stacked statements, dangerous functions in Postgres and Oracle, row-cap bypass attempts, allowlists, pagination, permission checks and audit-chain tampering. Execution tests run against SQLite, so no database server is needed.

## Design notes

- **One code path.** REST and MCP both call `Gateway.query`; there is exactly one place to review.
- **Fail closed.** If the identity service is down, every request is denied.
- **Agents get reasons.** A rejected query returns *why* (`DELETE is not allowed in a read-only query`), so the agent can correct itself instead of retrying blindly.
- **`purpose` field.** Agents are asked to say why they need the data; it goes to the audit log, which makes reviewing agent behaviour much easier.

## License

MIT