Skip to main content
Glama
secure-mcp-readonly-db-proof

secure-mcp-readonly-db-proof

secure-mcp-readonly-db-proof

A read-only MCP server that lets an AI agent query a business database over MCP. Any client that speaks streamable HTTP and can send a bearer header can use it; the client exercised here is the MCP Python SDK's own. The agent can't write, can't read tables you didn't expose and can't pull unlimited rows. Every tool call that reaches the gateway is recorded, including the ones it refused.

The database is a small synthetic ERP in SQLite (customers, invoices, invoice lines and a payroll table the agent must never see). The guards are the part that carries over to a real system. Moving to SQL Server, Postgres or an identity provider lists what changes.

What the agent can and cannot do

The agent can

The agent cannot

List the named queries its token allows

Send SQL text (no tool accepts it)

Run one of those queries with validated parameters

Run a query outside its token's scope

Get up to the query's row cap, with a truncated flag

Write, ATTACH, PRAGMA, or read payroll / sqlite_master

Call anything without a valid, unexpired bearer token

Use a token issued for another server, even one that shares the same token file

Exceed its per-user call quota

Related MCP server: pgmoat

The guards, in the order a request meets them

#

Guard

Where

Proven by

1

Bearer token per user, checked by the MCP SDK's middleware before any tool runs: no token or a bad one gets HTTP 401, a token without the base scope gets 403, a token issued for another server gets 401

tokens.py, server.py

test_mcp_http.py

2

Tokens stored as SHA-256 only, each bound to the URL of one server, with expiry and revocation; the raw token is printed once at issue, and the token file is replaced atomically

tokens.py

test_tokens.py

3

Fail closed: if a tool runs without a verified token, it refuses instead of treating the caller as anonymous. Tested in-process for both tools; stdio takes the same path, since there is no bearer middleware there either

server.py

test_without_a_bearer_context_the_tools_fail_closed

4

Per-user quota (sliding window) on both tools, charged before the query name is looked up, so probing is rate-limited too

quota.py, gateway.py

test_gateway.py

5

Named-query allowlist: the only SQL that can run is in queries.py. At load time each entry is linted to be a single statement that starts with SELECT or WITH and has no write keyword. That lint reads text; guard 9 is what stops a write at runtime

queries.py

test_allowlist.py

6

Scope per query (erp:read, erp:finance); list_queries only shows what the token may run

queries.py, gateway.py

test_gateway.py, test_mcp_http.py

7

Parameter validation: type, range, enum, length and a character allowlist; unknown keys are rejected, True is not accepted as 1

params.py

test_params.py

8

Bound parameters only; LIKE wildcards in a search prefix are escaped, so % matches a literal %

queries.py

test_gateway.py

9

Read-only database, two independent layers: the file is opened with SQLite's mode=ro, and an authorizer allows only reads of three tables and a short list of functions (no writes, ATTACH, PRAGMA, payroll, sqlite_master, or functions such as hex). Each layer is tested with the other one removed

db.py

test_database_guards.py

10

Row caps: per query, plus a server-wide hard cap that wins if it is lower; the result says when it was truncated

gateway.py

test_gateway.py

11

Audit log: one JSON line per tool call that reaches the gateway, allowed or denied, with who (subject and client id), what and the outcome. Only allowlisted query names are written, parameters marked sensitive are stored as a SHA-256, and no token or returned row is written. If the line cannot be written, the caller gets an error and no data

audit.py, gateway.py

test_gateway.py, test_mcp_http.py

Both tools are also annotated readOnlyHint. The HTTP app runs in the SDK's stateless mode, so there are no server-side MCP sessions to fill up.

Evidence

python -m pytest                 # 84 passed
python scripts/mutants.py        # 30 of 30 mutants killed

scripts/mutants.py copies the repo to a temporary directory and removes or weakens one guard at a time, 30 small changes in all. Among them: drop mode=ro, drop the authorizer, allow every SQL function, make payroll readable, let the lint miss WITH … DELETE, skip the scope check or the quota, stop auditing a given outcome, write the raw query name or sensitive values to the log, accept expired tokens, fall back to an anonymous user. It runs the suite against each change. Every mutant makes at least one test fail, which means no guard in that list is there without a test that notices its absence.

The HTTP tests start the real server with uvicorn and talk to it with the MCP SDK's client over streamable HTTP, including a second server that shares the token file and must refuse the first server's tokens.

Audit log, as written

Two calls by the same user (token issued with the default --client-id local-cli): a search that ran, then a finance query her token doesn't allow.

{"client_id": "local-cli", "duration_ms": 0.43, "outcome": "ok", "params": {"name_prefix": "sha256:8d4f6c2a5c4e4b94b3dbaa65808d2325060f4d4b6ac32eb11e1a423f1ddf0a5a"}, "query": "customer_search", "rows": 3, "subject": "ana", "tool": "run_query", "truncated": false, "ts": "2026-11-13T18:29:58.417+00:00"}
{"client_id": "local-cli", "duration_ms": 0.03, "outcome": "denied:forbidden", "params": null, "query": "overdue_invoices", "rows": 0, "subject": "ana", "tool": "run_query", "truncated": false, "ts": "2026-11-13T18:29:58.417+00:00"}

Parameters are logged once they have passed validation. A call the gateway refuses before that step (no verified identity, quota, unknown query, scope, invalid parameters) logs "params": null.

Requests the SDK rejects before a tool runs are not in this log: HTTP 401 and 403, arguments that do not match the tool schema, and unknown tool names.

Quick start

Tested on Python 3.13 with mcp 2.3.0 (pinned in pyproject.toml).

python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate
pip install -e ".[dev]"
python -m secure_mcp init-db --db data/erp.sqlite
python -m secure_mcp issue-token --tokens tokens.json --subject ana --scope erp:read
python -m secure_mcp issue-token --tokens tokens.json --subject fin --scope erp:read --scope erp:finance
python -m secure_mcp serve --db data/erp.sqlite --tokens tokens.json --audit audit.jsonl

issue-token binds each token to --resource, which defaults to the URL serve listens on with its defaults, http://127.0.0.1:8765/mcp. If you serve on another host or port, issue the tokens with that URL. Connect any MCP client that speaks streamable HTTP and can send an Authorization: Bearer <token> header. With the Python SDK:

from mcp import Client
from mcp.client.streamable_http import create_mcp_http_client, streamable_http_client

http = create_mcp_http_client(headers={"Authorization": f"Bearer {token}"})
async with Client(streamable_http_client("http://127.0.0.1:8765/mcp", http_client=http)) as client:
    print(await client.call_tool("list_queries", {}))
    print(await client.call_tool("run_query", {"name": "customer_by_id", "params": {"customer_id": 3}}))

Revoke a user with python -m secure_mcp revoke --tokens tokens.json --subject ana.

Moving to SQL Server, Postgres or an identity provider

These are the swap points. None of them is implemented or tested here.

  • Database engine. Besides the demo seed (schema.py, init-db), three places in the request path know the engine is SQLite: db.py (connection and authorizer), gateway.py (conn.execute with :name bindings, rows turned into dicts, the sqlite3 error type) and the SQL text in queries.py (:name placeholders, substr, LIKE … ESCAPE). Moving engines means a new driver in db.py, those details in gateway.py, possibly another placeholder style (pyodbc takes ?), and a review of each allowlisted query. The SQLite authorizer has no direct equivalent elsewhere, so on SQL Server or Postgres the second read-only layer becomes a database login that only has SELECT on the views or schemas you expose. Parameter validation, scopes, row caps, quota and audit carry over as they are.

  • Identity provider / OAuth. StoreVerifier implements the SDK's TokenVerifier protocol. The main change is a verifier that validates JWTs from your IdP (signature, expiry, audience) and maps their claims to the erp:* scopes, plus pointing --issuer-url at that IdP. The tools read the identity from the verified token and do not care where it came from.

  • Quota across replicas. The quota is in memory, per process. Several replicas need a shared counter (Redis or the database) behind the same consume() call.

  • Audit retention. Ship the JSON lines to write-once storage or a log pipeline.

Limits: what this repo does not show

  • It runs on SQLite with synthetic data. It has not been run against SQL Server, Postgres or a real ERP, and it has not run in production.

  • There is no OAuth authorization flow. Tokens come from a local CLI, and --issuer-url defaults to a placeholder (https://idp.example.com) until you point it at a real IdP.

  • No TLS. Put it behind a reverse proxy that terminates HTTPS before exposing it beyond localhost.

  • The audit file is append-only by convention, not tamper-proof.

  • Sensitive parameters are hashed with plain SHA-256. That keeps them out of casual reading, but a short value such as a customer name can be recovered by hashing guesses; a keyed HMAC is the stronger option.

  • A query outside the token's scope is refused as forbidden, while an unknown name is refused as unknown_query, so a reader can tell which query names exist.

  • The row cap counts rows, not bytes; a query returning very wide rows needs its own column list kept small (the allowlist already names columns explicitly).

  • The quota resets when the process restarts.

  • This is a reference implementation with tests, not a penetration test or a security audit of your system.

Layout

src/secure_mcp/
  server.py    MCP tools (list_queries, run_query) and SDK auth settings
  gateway.py   the guards in order, audit on every outcome
  queries.py   the allowlist: named queries, scopes, row caps
  params.py    parameter specs and validation
  db.py        read-only connection and SQLite authorizer
  tokens.py    hashed token store and the TokenVerifier
  quota.py     per-user sliding-window quota
  audit.py     JSON-lines audit sink
  schema.py    synthetic ERP schema and seed
tests/         84 tests, including real HTTP end to end
scripts/mutants.py   mutation check over 30 guard changes

License

MIT. See LICENSE.

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
      C
      maintenance
      Enables AI agents to query a Postgres database read-only through a policy layer rather than a raw connection string. Enforces table and column allowlists, row limits, mandatory predicates, PII masking and append-only audit logging, while refusing writes, joins, subqueries and unlisted functions.
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Gives AI agents read-only access to Postgres with parser-checked queries, hidden tables and columns, row limits and timeouts, caller-bound Row-Level Security, and a tamper-evident audit log.
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables AI agents to explore and query a SQL database safely, answering questions with real data while blocking writes, hiding protected columns, restricting results to one tenant's rows, and logging every call to an audit trail.
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Enables AI agents to query DuckDB, Postgres, and BigQuery through a governed, read-only layer with a semantic model, SQL guard, role-based row policies, PII pseudonymization, audit logging, and cost caps.
      40 PyPI
      MIT