# pgmcp

A hardened, **read-only** [Model Context Protocol](https://modelcontextprotocol.io)
server over a PostgreSQL database. It gives an LLM client a guarded SQL query
tool plus schema introspection — and nothing else. No writes, no filesystem, no
admin surface.

## Why

Handing an LLM a raw database connection is dangerous. pgmcp puts a database
between the model and your data with defense-in-depth:

1. **Least-privilege role.** pgmcp connects as a role with `SELECT`-only grants
   on exactly the schemas you allow — *not* `pg_read_all_data`. **Grants are the
   security boundary.**
2. **Single-statement SELECT guard.** A pglast AST guard rejects everything that
   isn't one read-only `SELECT`: multi-statement SQL (the `COMMIT; DROP …`
   escape), DML, DDL, `COPY`, `SELECT … INTO`, data-modifying CTEs, and
   filesystem/admin functions.
3. **READ ONLY transaction envelope** around every query.
4. **EXPLAIN cost pre-gate** on the large schemas you designate — a runaway
   cartesian join is rejected before it runs.
5. **Row cap** with a truncation flag in the response.
6. **Per-identity audit log** — one structured JSON line per call, attributed to
   the authenticated user.

Everything domain-specific is configuration.

## Tools

| Tool | Purpose |
|---|---|
| `run_query(sql)` | Run one guarded, read-only SELECT; returns columns + rows + truncation flag. |
| `list_objects(schema)` | List tables/views in one allowlisted schema. |
| `describe_table(schema, table)` | Column names, types, nullability for one table/view. |

## Quickstart

```bash
# 1. Create the least-privilege role and grants (see db/README.md).
# db/03_grants.sql and db/02_curated_views.sql are examples to edit for your
# own schema; PGMCP_ALLOWED_SCHEMAS below must match whatever you actually grant.
psql -f db/01_role.sql
psql -f db/03_grants.sql
psql -c "ALTER ROLE pgmcp_ro PASSWORD 'choose-a-secret'"

# 2. Install
uv venv && uv pip install -e .

# 3. Configure and run
export PGMCP_DSN="postgresql://pgmcp_ro:choose-a-secret@127.0.0.1:5432/mydb"
export PGMCP_ALLOWED_SCHEMAS="app,analytics,public"
export PGMCP_COST_GATED_SCHEMAS="app,analytics"
pgmcp    # or: python -m pgmcp.server
```

The server binds `127.0.0.1:8765` and speaks Streamable HTTP. It trusts an
`X-Auth-User` header injected by a reverse proxy — see `deploy/` for an nginx +
systemd example that adds TLS and per-user bearer auth.

## Configuration

| Env var | Meaning | Default |
|---|---|---|
| `PGMCP_DSN` | libpq conninfo — `service=pgmcp_ro` or `postgresql://…` | `service=pgmcp_ro` |
| `PGMCP_ALLOWED_SCHEMAS` | comma list of queryable schemas (**required**) | — |
| `PGMCP_COST_GATED_SCHEMAS` | subset that gets the EXPLAIN pre-gate | *(none)* |
| `PGMCP_SCHEMA_CONTRACT` | path to a TOML schema-contract; asserted on boot | *(unset → skip)* |
| `PGMCP_INSTRUCTIONS_FILE` | path to model instructions; overrides the default | *(unset → default)* |
| `PGMCP_ROW_CAP` | max rows returned | `10000` |
| `PGMCP_EXPLAIN_COST_LIMIT` | cost-gate ceiling | `100000000` |
| `PGMCP_IDENTITY_HEADER` | trusted identity header | `X-Auth-User` |
| `PGMCP_HOST` / `PGMCP_PORT` | bind address (loopback) | `127.0.0.1` / `8765` |
| `PGMCP_ALLOWED_HOSTS` / `PGMCP_ALLOWED_ORIGINS` | DNS-rebinding / Origin allowlists | hosts: loopback; origins: empty |

`PGMCP_ALLOWED_SCHEMAS` must mirror the grants in `db/03_grants.sql`. The server
refuses to boot with an empty allowlist.

## Exposing a table with some sensitive columns

Don't grant the base table. Create a view in a `curated` schema that projects
only the safe columns, grant the view, and add `curated` to
`PGMCP_ALLOWED_SCHEMAS`. See `db/02_curated_views.sql`. The base table stays
unreachable through pgmcp.

## Optional: schema contract

Point `PGMCP_SCHEMA_CONTRACT` at a TOML file (see `examples/schema-contract.toml`)
declaring expected column types. On boot pgmcp asserts them against
`information_schema` and refuses to start on drift — a guard against a query
tool silently outliving the schema it was written for.

## Development

```bash
uv pip install -e ".[dev]"
pytest tests/            # unit tests need no database
PGMCP_DSN=... pytest tests/test_live.py   # opt-in end-to-end checks
```

## Design

Architecture decisions and their rationale are in `docs/adr/`. Start with
`CONTEXT.md` (glossary) and `SETUP.md` (setup runbook).

## License

Released under the MIT License — see [`LICENSE`](LICENSE).