# sqlgate-mcp

Read-only SQL access to your database for AI agents, over the
[Model Context Protocol](https://modelcontextprotocol.io), with guardrails you can read and an
audit log you can check.

Giving an agent a database connection string is easy. Knowing what it can do with it is not:
`pg_read_file('/etc/passwd')`, a `DELETE` hidden in a CTE, `SELECT *` on the users table with
its password hashes, or a cross join that runs for an hour. sqlgate sits between the agent and
the database and only lets through what your policy allows.

```
agent ──MCP──▶ sqlgate ──▶ guard (sqlglot AST) ──▶ database (read-only role, read-only txn, timeout)
                  │                                     │
                  └── audit log ◀───────────────────────┴── masking ◀── results
```

## Defence in depth

No single layer is trusted to be perfect:

1. **The guard** parses every query with [sqlglot](https://github.com/tobymao/sqlglot) in the
   database's dialect and refuses anything that isn't exactly one read-only query:
   - no INSERT, UPDATE, DELETE, DDL or COPY anywhere in the tree, including data-modifying CTEs;
     no `SELECT ... INTO` and no `FOR UPDATE` / `FOR SHARE` locks
   - no functions that read files or the network, sleep, run SQL from strings or change state
     (`pg_read_file`, `dblink`, `query_to_xml`, `nextval`, DuckDB's `read_csv`, `getenv`, ...)
   - only tables your policy allows, never the system catalogs
   - denied columns can't be selected, filtered on, or reached through `SELECT *` or a whole-row
     reference like `row_to_json(u)`
   - an outer `LIMIT` is added or tightened
2. **The database** enforces it again. Postgres queries run in a `READ ONLY` transaction with a
   `statement_timeout` and are always rolled back, optionally after an `EXPLAIN` cost check.
   BigQuery queries are dry-run against a bytes limit and carry `maximum_bytes_billed`. DuckDB
   files are opened read-only with external access switched off and a query timeout.
3. **Masking** transforms sensitive values on the way out. Lineage is followed through
   expressions, CTEs and subqueries, so `lower(email) AS e` is masked like `email`. Responses are
   also kept within a byte budget, so one wide row can't flood an agent's context.
4. **The audit log** records every query, what actually ran, and why anything was refused.

You should still connect with a role that can only `SELECT` what the agent is meant to see.

## Install

```bash
uv tool install 'sqlgate-mcp[postgres]'   # or [bigquery], or both
```

Python 3.11+, MCP Python SDK 2.x. DuckDB support is built in.

## Configure

```yaml
# sqlgate.yaml
database:
  type: postgres
  dsn: ${ANALYTICS_RO_DSN}        # from the environment, never the file
  statement_timeout_ms: 15000
  max_cost: 500000                # planner cost; EXPLAIN a few heavy queries to pick yours

policy:
  max_rows: 500
  allow_tables: ["public.*", "reporting.*"]
  deny_tables: ["public.*_audit"]
  deny_columns: ["*.password_hash", "users.mfa_secret"]
  mask_columns:
    "*.email": partial            # s•••@example.com
    "customers.phone": partial    # •••4567
    "customers.full_name": hash   # h:3f9a…, stable, so it still groups and joins
  deny_functions: [expensive_udf]

masking:
  key: ${SQLGATE_MASK_KEY}         # HMAC key for hash masking

audit:
  path: /var/log/sqlgate/audit.jsonl
```

For BigQuery:

```yaml
database:
  type: bigquery
  project: acme-analytics
  datasets: [reporting, marketing]   # unqualified tables resolve to the first
  location: EU
  max_gib_billed: 5
```

For a DuckDB file: `type: duckdb`, `path: warehouse.duckdb`, `timeout_s: 30`.

Table patterns are globs over `schema.table` (BigQuery: `project.dataset.table`); column
patterns over `table.column` or `schema.table.column`. Unqualified tables resolve to `public`
for Postgres and `main` for DuckDB unless `policy.default_schema` says otherwise.

Names are compared the way the database compares them. In Postgres `"Orders"` (quoted) is a
different table from `orders`, so allow patterns match it exactly; deny patterns and masks
ignore case everywhere, so any ambiguity ends in a refusal.

Check what the guard does with a query before an agent ever sends it:

```console
$ sqlgate-mcp check -c sqlgate.yaml "select email, count(*) from customers group by 1"
SELECT email, COUNT(*) FROM customers GROUP BY 1 LIMIT 501
masked output columns: #1 (partial)

$ sqlgate-mcp check -c sqlgate.yaml "with gone as (delete from orders returning *) select * from gone"
refused: DELETE is not allowed anywhere in the query, including inside WITH
```

## Connect a client

Claude Code:

```bash
claude mcp add analytics -- sqlgate-mcp serve -c /path/to/sqlgate.yaml
```

Claude Desktop (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "analytics": {
      "command": "sqlgate-mcp",
      "args": ["serve", "-c", "/path/to/sqlgate.yaml"],
      "env": { "ANALYTICS_RO_DSN": "postgresql://agent_ro@db/analytics" }
    }
  }
}
```

Or with Docker, where the container only ever sees the config and the secrets you pass it:

```bash
docker build -t sqlgate-mcp .
claude mcp add analytics -- docker run -i --rm \
  -v /path/to/sqlgate.yaml:/config/sqlgate.yaml:ro -e ANALYTICS_RO_DSN sqlgate-mcp
```

### Over HTTP

One server for a team, instead of one per laptop:

```bash
SQLGATE_TOKEN=$(openssl rand -hex 32) sqlgate-mcp serve -c sqlgate.yaml --transport http --host 0.0.0.0 --port 8000
claude mcp add --transport http analytics https://sqlgate.internal/mcp --header "Authorization: Bearer $TOKEN"
```

On `127.0.0.1` (the default) the SDK's DNS-rebinding protection is on; any other address
requires `SQLGATE_TOKEN`, and the server won't start without it. Put TLS in front.

## Tools

| tool | |
|---|---|
| `list_tables` | Tables the policy allows, with row estimates and descriptions |
| `describe_table` | Columns, types and comments of one table (denied columns left out) |
| `run_query` | One read-only SELECT; returns columns, rows, `truncated` and which columns were masked |
| `explain_query` | What would run, what would be masked, and the cost estimate, without running it |
| `sample_rows` | Up to 20 rows of a table through the same guard and masking, to see what values look like |

All five are annotated `readOnlyHint` and declare output schemas, so clients get
`structured_content` as well as text and can skip confirmation prompts. The visible schema is
also available as the `schema://overview` resource, for clients that attach context.

Refusals come back as tool errors that say what to change ("table hr.salaries is not available;
use list_tables..."), so agents recover instead of retrying blindly. Table and column comments
are passed through, so a well-commented schema is the best prompt you can give an agent.

## A read-only Postgres role

```sql
CREATE ROLE agent_ro LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB NOCREATEROLE;
ALTER ROLE agent_ro SET default_transaction_read_only = on;
GRANT USAGE ON SCHEMA public, reporting TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public, reporting TO agent_ro;
REVOKE SELECT ON public.users FROM agent_ro;
GRANT SELECT (id, email, created_at) ON public.users TO agent_ro;
```

## Audit log

One JSON object per line:

```json
{"ts": "2025-12-28T07:25:11.402+00:00", "tool": "run_query", "sql": "select count(*) from orders",
 "executed": "SELECT COUNT(*) FROM orders LIMIT 501", "outcome": "ok", "rows": 1, "truncated": false,
 "elapsed_ms": 3.8, "estimated_cost": 1834.5}
```

`outcome` is `ok`, `refused` (guard or cost check) or `failed` (database error). It's written
per line and survives logrotate.

## Development

```bash
uv sync --all-extras
uv run pytest                     # Postgres tests run when SQLGATE_TEST_PG_DSN is set
uv run ruff check && uv run ruff format --check
```

## License

MIT