mcp-data-gateway



A production-minded MCP server that lets AI agents

query a PostgreSQL database through a small set of controlled, read-only tools.

The core idea: agents never get raw database access. Every interaction goes through

explicitly designed tools with validated inputs and a read-only SQL guard, backed by

a least-privilege database role as the authoritative enforcement layer.

Status: reference implementation with fixed MCP tools, read-only PostgreSQL

access, JSON-safe output, audit logging, live-DB integration, container image,

and documentation build covered by CI. See

docs/project-plan.md.

The gateway exposes a fixed, read-only MCP tool surface backed by a SELECT-only

PostgreSQL role.

Why

Giving an LLM agent a database connection string is easy — and dangerous. This project

demonstrates a safer pattern:

• Read-only by design — three independent layers: tool design, a SQL validation

guard as defense-in-depth, and a least-privilege database role as the authoritative

control. The guard is a deny-by-default first filter, not a full SQL parser; the

database role/session (SELECT-only, default_transaction_read_only) is what

ultimately enforces read-only and table access. See SECURITY.md.

• Small, purposeful tool surface — schema inspection, row lookup, and aggregate

stats. No generic "run any SQL" escape hatch for write operations.

• Boring, auditable stack — Python 3.12, psycopg, PostgreSQL, Docker Compose.

The demo dataset is the classic Titanic passenger list: small, well-known, and

low-risk to expose publicly.

For what this deliberately does not provide — and what a real deployment would

still need — see Production considerations.

Quickstart

Requires Python 3.12+, Docker, and make.

cp .env.example .env        # defaults work for local development
make up                     # start PostgreSQL via Docker Compose
make install                # create venv and install dependencies
make load-data              # create the schema, load sample data, set up the reader role
make smoke                  # verify end to end (incl. that direct writes are refused)
make run                    # start the MCP server on stdio

make load-data connects as the local admin (POSTGRES_*) only for setup: it

creates the passengers table with a small deterministic sample, then creates

the gateway_reader role with SELECT-only access. The server itself connects

via DATABASE_URL, which points at gateway_reader — never the admin user. The

script is idempotent, so you can re-run it safely.

To use with an MCP-capable client, register the server with a stdio transport

pointing at python -m mcp_data_gateway.server.

Repository layout

src/mcp_data_gateway/
  server.py            # MCP server entrypoint (stdio); registers the tools
  config.py            # environment-based configuration
  db.py                # connection handling; read-only session, timeout, row cap
  audit.py             # structured audit logging boundary
  serialization.py     # JSON-safe output boundary
  tools/               # the agent-facing tools
    schema.py          #   describe tables and columns
    passengers.py      #   look up passenger rows
    stats.py           #   aggregate statistics
  security/
    readonly_sql.py    # read-only SQL guard
scripts/               # data loading and smoke test
tests/                 # pytest suite
docs/                  # architecture, security, operations, decisions

Documentation

Full documentation lives in `docs/` and is published with MkDocs

at <https://ali-chaghou.github.io/mcp-data-gateway/>:

• Architecture — components and the request path
• Security model — the defense-in-depth layers
• Tool reference — the six MCP tools in detail
• Operations — configuration, container, logs, commands
• Validation — what is proven, and how
• Production considerations — demonstrated vs. still required
• Implementation journey — how it was built, phase by phase
• Demo walkthrough — run it locally with Docker
• Decisions — key design choices

make docs               # build the site into site/
make docs-serve         # preview locally with live reload

Development

make test               # run pytest
make lint               # ruff check + format check
make audit              # bandit + pip-audit
pre-commit install      # enable git hooks

CI runs these on every push and pull request, alongside a strict MkDocs build with

an internal link check, the opt-in live-DB integration tests (which prove the

read-only role and that direct writes are refused), and a container image build

that also checks the image runs as a non-root user. See

Validation for what each check proves.

Engineering conventions are described in

docs/engineering-process.md; design decisions are

captured as ADRs in docs/decision-records/.

License

MIT