# mcp-data-gateway

[![CI](https://github.com/Ali-Chaghou/mcp-data-gateway/actions/workflows/ci.yml/badge.svg)](https://github.com/Ali-Chaghou/mcp-data-gateway/actions/workflows/ci.yml)
[![Pages](https://github.com/Ali-Chaghou/mcp-data-gateway/actions/workflows/pages.yml/badge.svg)](https://github.com/Ali-Chaghou/mcp-data-gateway/actions/workflows/pages.yml)

A production-minded [MCP](https://modelcontextprotocol.io) 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](docs/project-plan.md).

![mcp-data-gateway architecture](docs/assets/architecture.png)

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](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](docs/production-considerations.md).

## Quickstart

Requires Python 3.12+, Docker, and `make`.

```sh
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/`](docs/index.md) and is published with MkDocs
at <https://ali-chaghou.github.io/mcp-data-gateway/>:

- [Architecture](docs/architecture.md) — components and the request path
- [Security model](docs/security-model.md) — the defense-in-depth layers
- [Tool reference](docs/tool-reference.md) — the six MCP tools in detail
- [Operations](docs/operations.md) — configuration, container, logs, commands
- [Validation](docs/validation.md) — what is proven, and how
- [Production considerations](docs/production-considerations.md) — demonstrated vs. still required
- [Implementation journey](docs/implementation-journey.md) — how it was built, phase by phase
- [Demo walkthrough](docs/demo-walkthrough.md) — run it locally with Docker
- [Decisions](docs/decisions.md) — key design choices

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

## Development

```sh
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](docs/validation.md) for what each check proves.

Engineering conventions are described in
[docs/engineering-process.md](docs/engineering-process.md); design decisions are
captured as ADRs in [docs/decision-records/](docs/decision-records/).

## License

MIT