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