# SQL Insights MCP Server

Read-only Postgres access for AI agents over the Model Context Protocol, with a
hard safety boundary between the model and the database.

Handing an agent a raw database connection answers the question "what were sales
last quarter" and also creates a way to `DELETE FROM customers`, hang the server
with a runaway query, or pull a million rows into the model's context. This
server exposes three narrow tools instead, and validates every statement before
it reaches Postgres.

## Status

Under construction, built in order:

- [x] **Guard layer** + adversarial test suite (no database required)
- [x] **Execution layer**: pool, read-only transaction, timeout, truncation
- [x] **Schema cache**: TTL-refreshed table and column metadata
- [ ] MCP surface: `list_tables`, `describe_table`, `run_query`
- [ ] Seed script and demo dataset
- [ ] PyPI packaging
- [ ] Hosted demo over HTTP

## The guard layer

`src/sql_insights/guard.py` is a pure function: SQL string in, verdict out. No
I/O, no database, no environment. It parses with `sqlglot` into an abstract
syntax tree rather than pattern-matching on text, because text-level checks lose
to anyone who knows how to hide a semicolon.

| Check | Rule |
| --- | --- |
| Statement count | Exactly one statement per call |
| Statement type | Root must be a SELECT or a set operation |
| Nested writes | No INSERT/UPDATE/DELETE/DDL anywhere in the tree, including inside CTEs |
| Banned functions | No `pg_read_file`, `pg_sleep`, `dblink`, `lo_import`, ... |
| System catalogs | `pg_catalog`, `information_schema`, and any `pg_*` table are blocked |
| Table allowlist | Every referenced table must be listed, when an allowlist is configured |
| Row limit | `LIMIT` is injected, or clamped, by editing the AST |
| Comments | Stripped before the SQL is re-emitted |

This is the first of three independent defenses, and it is not trusted alone.

## Three independent layers of read-only

A single defense is a single point of failure, so there are three, and each one
alone is sufficient:

1. **The guard** rejects the statement before it is sent.
2. **The pool** opens every connection with `default_transaction_read_only = on`
   and wraps every query in an explicit `BEGIN READ ONLY` transaction.
3. **The database role** holds `SELECT` grants and nothing else, so a write that
   somehow reached Postgres is refused by Postgres.

`tests/test_execution.py` asserts layer 3 directly, by sending writes straight
down the pool with the guard bypassed. They fail at the database, as they must.

## Development

Start a disposable Postgres (published on 5433, so it will not collide with any
Postgres already on the host):

```bash
docker compose up -d
```

The container provisions `readonly_user` on first start. Then:

```bash
uv sync --group dev
uv run pytest -q
```

The database-backed tests skip cleanly if no Postgres is reachable, so the guard
suite still runs on a machine without Docker.

## Configuration

| Variable | Required | Default | Meaning |
| --- | --- | --- | --- |
| `DATABASE_URL` | yes | - | Connection string for a read-only role |
| `QUERY_TIMEOUT_SECONDS` | no | `10` | Per-query `statement_timeout` |
| `MAX_ROWS` | no | `100` | Row cap enforced by the guard |
| `ALLOWED_TABLES` | no | all | Comma-separated allowlist |
| `TRANSPORT` | no | `stdio` | `stdio` or `http` |
| `SCHEMA_CACHE_TTL_SECONDS` | no | `300` | Schema cache refresh interval |

The connection string is read from the environment at startup and is never a
tool parameter, so the model cannot see it, change it, or be talked into
pointing the server somewhere else. Passwords are redacted from every log line.

## License

MIT