Skip to main content
Glama
readonly-sql-mcp

readonly-sql-mcp

by EliasOlie

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 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.

Related MCP server: MCP DB Gateway

Quick start

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

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

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

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      B
      maintenance
      Provides a read-only PostgreSQL SQL surface for LLM agents via MCP, with defense-in-depth security layers for safe database queries.
      666 PyPI
      4
      MIT
    • A
      license
      Not graded
      quality
      D
      maintenance
      Provides governed, read-only PostgreSQL access for AI agents via MCP. Enforces schema/table allowlists, query limits, and audit events.
      1
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables MCP clients to execute validated read-only warehouse queries through a guarded SQL API, with enforced row limits and secret-safe audit metadata.
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables analytics agents to run validated read-only SQL against a warehouse API with enforced row limits, required JSON responses, and secret-safe audit metadata.
      MIT