Skip to main content
Glama
SafeSQL

SafeSQL

SafeSQL — chat-to-SQL with read-only guardrails and an MCP server

The model proposes, the database disposes.

Ask questions in plain language over a real Postgres database. The LLM only ever proposes SQL; deterministic code parses it, validates it, rewrites it, and runs it as a SELECT-only role with limits, timeouts and a full audit trail.

1. Problem

Teams want "just let people ask the database questions", but handing an LLM a database connection is how you lose a table. Text-to-SQL demos that trust the model are one hallucinated DROP away from an incident. The fix is boring and structural: let the model write SQL, and let code that cannot hallucinate decide what actually runs.

Related MCP server: postgres-mcp-lab

2. What it does

  • Chat UI: ask a question, get a one-sentence summary, a results table, and a collapsible "SQL that ran" block with the model's rationale and every rewrite the guard applied.

  • guard() — a pure function, no DB, no LLM — parses each proposal with sqlglot and blocks anything that is not one clean SELECT on allow-listed tables (exact rules below).

  • Execution runs as Postgres role readonly_app inside a READ ONLY transaction with a 5s statement timeout: three independent layers under the guard.

  • One repair attempt: on a database error, the error message is fed back to the model once; a second failure is an honest failed answer showing both attempts.

  • Every question is logged to a queries table, and every stage (generate, guard, execute, summarize) writes an audit row.

  • MCP server (mcp_server.py): tools describe_schema and query_readonly reuse the exact same guard and executor, so Claude Desktop / Claude Code can query the demo DB safely.

  • Demo data: the Chinook music store (artists, albums, tracks, invoices, customers).

▶ Watch the 30-second demo video · Try the live demo

Ask a question — results table plus a one-sentence summary built from the rows:

Good question: results table and summary

Expand "SQL that ran" — the model's rationale and every rewrite the guard applied:

SQL block with rationale and the LIMIT 200 rewrite

Ask for something destructive — blocked with the exact reason, and the attempted SQL shown:

Blocked DROP TABLE with reason "not a SELECT"

Every question lands in the history with its status and latency:

History panel with ok and blocked queries

3. Architecture

flowchart LR
  UI[static UI /] --> API[FastAPI /ask]
  MCP[Claude via MCP] --> G
  API --> R[aiforge-core Router]
  R --> P1[Gemini 2.5 Flash]
  R --> P2[Groq llama-3.3-70b]
  R -->|SqlProposal| G[guard.py sqlglot]
  G -->|blocked + reason| API
  G -->|rewritten SELECT| X[execute.py readonly_app READ ONLY 5s]
  X --> DB[(Postgres: chinook tables, queries, audit_log)]
  X --> S[summarize.py]
  S --> API

The model never sees the database. It sees a compact schema summary (GET /schema): table names, columns with types, foreign keys, three sample rows per table. Those table names are also the guard's allow-list.

Routes: POST /ask {question} → {summary, columns, rows, sql, rationale, rewrites, status, block_reason, attempts} · GET /queries history · GET /schema transparency · GET /health.

MCP server for Claude

Add to claude_desktop_config.json (or .mcp.json for Claude Code) — adjust the paths:

{
  "mcpServers": {
    "safesql": {
      "command": "/path/to/safesql/.venv/bin/python",
      "args": ["/path/to/safesql/mcp_server.py"],
      "env": { "DATABASE_URL": "postgresql://app:app@localhost:5432/app",
               "READONLY_DATABASE_URL": "postgresql://readonly_app:readonly@localhost:5432/app" }
    }
  }
}

Then ask Claude to "list the tables and count the invoices" — its queries go through the same guard and show up in the history with their status.

4. Guardrails

All deterministic, all tested, all enforced by parsing (sqlglot, postgres dialect) — never by regex on strings:

  • Unparseable SQL → blocked (unparseable); more than one statement → blocked (multi_statement).

  • The single statement must be a SELECT; CTEs are fine, data-modifying CTEs and any DML/DDL node anywhere in the tree are not (not_select).

  • No comments (comment), no SELECT ... INTO (select_into).

  • Denied functions: pg_sleep, pg_read_file, lo_import, dblink (denied_function).

  • No pg_catalog, information_schema, or pg_* relations (system_table).

  • Only allow-listed tables from the schema summary, compared on sqlglot-normalized identifiers, i.e. case-insensitively and quote-aware (unknown_table).

  • Missing LIMIT → LIMIT 200 injected; LIMIT > 200 → lowered. Every rewrite is returned and shown in the UI.

  • Even past the guard: SELECT-only role, READ ONLY transaction, 5000ms statement timeout, per-IP rate limit.

5. Limits

  • One database, one schema (public); no cross-schema or multi-DB routing.

  • The guard's allow-list check is table-level, not column-level.

  • Case-insensitive allow-list matching can accept a quoted spelling (e.g. "Artist") that Postgres itself will then reject; that surfaces as a normal execution error and repair attempt, never a security hole.

  • One repair attempt, then an honest failure — no agentic retry loops.

  • When READONLY_DATABASE_URL is not set (managed Postgres with a single connection string, e.g. Neon), execution connects with DATABASE_URL and drops privileges with SET LOCAL ROLE readonly_app inside the READ ONLY transaction — the SELECT-only role, read-only transaction and 5s timeout all still apply; there is just no separate login.

  • The offline demo (LLM_PROVIDER_ORDER=mock) uses canned proposals, so the answers rotate through a fixed set; real questions need a free Gemini or Groq key.

  • Chinook sample database © Luis Rocha, committed as db/chinook.sql (v1.4.5, lowercase identifiers) under its license.

6. Run

cp .env.example .env      # add GEMINI_API_KEY, or set LLM_PROVIDER_ORDER=mock for keyless
make setup                # venv, deps, postgres via docker, migrations + chinook + roles
make demo                 # http://localhost:8000

Or fully containerized: docker compose up --build (defaults to the keyless mock provider).

Deploy (Vercel + Neon, free tier)

The repo is serverless-ready: api/index.py exposes the ASGI app, vercel.json routes everything to it, and requirements.txt holds the runtime deps. Cold starts only run idempotent migrations and build the schema summary (a handful of catalog queries) — the 600 KB Chinook dump is never loaded at cold start.

  1. Neon: create a free project, copy the pooled DATABASE_URL.

  2. Seed the remote database once from your machine (migrations + Chinook + readonly role):

    # bash:        DATABASE_URL="postgres://<neon-owner-url>" .venv/bin/python -m app.seed
    # PowerShell:  $env:DATABASE_URL="postgres://<neon-owner-url>"; .venv\Scripts\python.exe -m app.seed
  3. Vercel: import the repo, set env vars DATABASE_URL and LLM_PROVIDER_ORDER=mock (keyless demo) — no READONLY_DATABASE_URL needed; execution falls back to SET LOCAL ROLE readonly_app (see Limits).

  4. Confirm https://<app>.vercel.app/health returns {"ok": true}.

Deploy (Render + Neon, free tier)

  1. Neon: create a free project, enable the vector extension, copy DATABASE_URL; run db/roles.sql once and set READONLY_DATABASE_URL with the readonly_app credentials.

  2. Render: new Web Service from this repo, Docker runtime, add the env vars, health check /health.

  3. First boot runs migrations and loads Chinook automatically (idempotent).

  4. Confirm https://<app>.onrender.com/health returns {"ok": true}.

  5. Rate limit stays on; the free Gemini quota is enough for demo traffic.

7. Tests

make test — no API keys, no network; guard tests need no database at all.

  • test_guard_dml — DELETE/UPDATE/INSERT/DROP and data-modifying CTEs are blocked as not_select.

  • test_guard_multi — SELECT 1; DELETE … is blocked as multi_statement.

  • test_guard_limit — missing LIMIT injected, LIMIT 5000 lowered to 200, small limits untouched.

  • test_guard_system — pg_catalog, information_schema, bare pg_* views blocked as system_table.

  • test_guard_denylist — pg_sleep, pg_read_file, lo_import, dblink blocked as denied_function.

  • test_guard_allowlist — unknown tables blocked as unknown_table; quoted/cased spellings of allowed tables and CTE names pass.

  • test_guard_misc — unparseable input, comments, SELECT INTO, UNION handling.

  • test_repair_loop — a DB error triggers exactly one regenerate (the error is in the second prompt); a second error ends failed with both attempts in the response, the queries row, and the audit log; the happy path audits generate → guard → execute → summarize exactly once.

  • test_readonly_role — an INSERT as readonly_app raises InsufficientPrivilege against a real Postgres.

8. Keywords

chat to SQL, natural language to SQL, text-to-SQL guardrails, read-only database tool, SQL validation, MCP server, AI analytics assistant, PostgreSQL, sqlglot, FastAPI.

Available Tools

2 tools
describe_schemaA

Describe the demo database: tables, columns, foreign keys, sample rows.

Inputs: no named parameters. Returns: result

query_readonlyA

Run one SELECT through the SafeSQL guard (read-only role, LIMIT capped at 200).

Anything that is not a single clean SELECT on allow-listed tables is blocked.

Inputs: sql (required).

Tool Schema Changelog

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

Loading tool schema changelog…

    TDQS

    A4.1/5.0

    Scored across 2 tools

    Disambiguation5/5

    describe_schema and query_readonly have clearly distinct roles: one returns database metadata, the other executes read-only queries. No overlap exists, so an agent can select the intended tool without ambiguity.

    Naming Consistency4/5

    Both names use snake_case and a verb-first style, so the set is readable and predictable. However, query_readonly uses an adjective modifier rather than a noun object, which is a minor deviation from a strict verb_noun pattern.

    Tool Count3/5

    Two tools sit at the low end of a credible server surface, especially for anything beyond a simple demonstration. They are well-chosen for a minimal read-only SQL demo, but the set feels thin compared with most servers.

    Completeness4/5

    For a deliberately read-only SQL guard, schema description plus SELECT execution covers the core workflow and has no dead ends. Minor gaps include no explicit tool for inspecting allowed-table details or query errors, but the stated scope makes those optional.

    Maintenance

    ActivityStale
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      B
      maintenance
      Enables Claude Code to securely interact with PostgreSQL, MySQL, SQLite, and SQL Server databases, featuring read-only mode, query validation, SSH tunneling, and field redaction for production-safe data access.
      4
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      Provides Claude Code read-only access to PostgreSQL for relational queries, schema inspection, EXPLAIN plans, pgvector similarity searches, and Apache AGE graph traversals.
      6
      Apache 2.0
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables Claude to translate natural-language business questions into safe, read-only SQL queries by exposing tools for listing tables, inspecting schemas, and executing row-capped SELECT statements against PostgreSQL.
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables Claude to query and safely write to a Postgres database, driven entirely by one policy file with read-only access by default, column-precise PII masking, and an append-only audit log. SQL is parsed and validated before execution, writes use parameterized queries with dry-run/confirm and transactions, and every call is rate-limited and audited so destructive or exfiltrating requests are refused.
      MIT