# Kenning PG MCP

A PostgreSQL [Model Context Protocol](https://modelcontextprotocol.io) server
built on two convictions:

1. **Dependencies are pinned exactly.** Every dependency is resolved once,
   recorded in a committed lockfile, and upgraded only as a deliberate,
   reviewed act. The published package carries `==` pins; the Docker image is
   built with `uv sync --frozen`. Nothing resolves at launch time.
2. **Read-only means the database says no — not a regex.** Access control is
   transaction- and role-based. The server never inspects SQL text to decide
   whether a statement is "safe," because that model is unwinnable: a
   data-modifying CTE, a `VOLATILE` function that writes, or `COPY ... TO
   PROGRAM` all pass keyword filters. PostgreSQL itself is the enforcement.

Built on the MCP Python SDK v2 (2026-07-28 stateless protocol revision),
`psycopg` 3, and `pydantic-settings`. Serves stdio and streamable HTTP from
one set of handlers.

## The security model

Four layers, none of which parse SQL:

- **Read-only transactions.** Every read path runs inside
  `BEGIN ... READ ONLY`. PostgreSQL rejects any write attempt with SQLSTATE
  `25006` — including data-modifying CTEs, `SELECT ... FOR UPDATE`, and
  volatile functions that write.
- **One statement per call, enforced by the server.** Every user statement is
  executed through the extended query protocol, where PostgreSQL rejects
  multi-command strings outright. Statement stacking dies in the database,
  not in a parser.
- **A purpose-built role, checked at startup.** Connect as a minimal role (SQL
  below). This is what blocks `COPY ... TO PROGRAM`, `pg_read_file()`,
  `lo_export()`, and `pg_terminate_backend()` — capabilities that begin with
  `SELECT` or `COPY` and would sail through any keyword guard. A read-only
  transaction does **not** stop them: none writes to a relation, so `25006`
  never fires (verified against PostgreSQL 18, where `COPY (SELECT ...) TO
  PROGRAM` executes arbitrary commands inside `BEGIN TRANSACTION READ ONLY` as
  a superuser). Because documentation is not enforcement, the server verifies
  the role itself rather than trusting the deployment to have followed it.
  Privilege, not pattern matching.
- **Timeouts on every session.** `statement_timeout` and
  `idle_in_transaction_session_timeout` are set at connection time, so a
  badly planned query cannot pin a connection indefinitely.

On startup (in `restricted` mode) the server runs two probes before serving a
single query, and **exits non-zero** rather than silently serving a read-only
server that isn't:

1. A write inside a read-only transaction must fail with `25006`.
2. The connection role must not be a superuser and must hold none of
   `pg_read_server_files`, `pg_write_server_files`, or
   `pg_execute_server_program`. Override with `PG_MCP_ALLOW_SUPERUSER=true`
   on a trusted, disposable database; the override logs a prominent warning.

In `unrestricted` mode the privilege probe warns rather than refuses:
enabling writes is consent to modify data, not consent to read server files
or execute programs as the database OS user.

An adversarial test suite attempts every bypass listed above against a real
PostgreSQL as a low-privilege role; every case must fail at the database.

Write access is never inferred. In the default `restricted` mode the write
tool is not merely refused — it is not registered, so it never appears in
`tools/list`. Setting `PG_MCP_ACCESS_MODE=unrestricted` registers a single
DML tool; read tools still run read-only transactions.

### The role

**The requirement is negative: do not connect as a superuser.** An ordinary
application or reporting role already satisfies it — not a superuser, no
filesystem roles — and needs no configuration. `restricted` mode still blocks
every write such a role could otherwise make, because the transaction layer
does not care what the role is permitted to do. Point the server at the
least-privileged existing role that can see your data.

Creating a dedicated role, below, narrows what is *visible*. It is a
refinement for organizations that provision service accounts per consumer,
not a prerequisite for read-only safety:

```sql
CREATE ROLE mcp_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE mydb TO mcp_ro;
GRANT USAGE ON SCHEMA public TO mcp_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_ro;
ALTER ROLE mcp_ro SET default_transaction_read_only = on;
ALTER ROLE mcp_ro SET statement_timeout = '30s';
ALTER ROLE mcp_ro SET idle_in_transaction_session_timeout = '60s';
```

For read-write deployments, the equivalent role carries `INSERT`, `UPDATE`,
`DELETE` and nothing more. DDL is an operator decision, not a server feature.

## Install

The server is a deployable tool, not a library — install it isolated, where
exact pins are a feature:

```sh
uv tool install kenning-pg-mcp        # or: pipx install kenning-pg-mcp
```

Or use the Docker image (the primary distribution artifact — resolution
happens at build time, never at launch):

```sh
docker build -t kenning-pg-mcp:0.2.0 .
```

**Do not run this server via `uvx`.** `uvx` re-resolves against PyPI on every
invocation, which is the exact failure mode this project exists to eliminate.
Install once, pin the version, upgrade deliberately.

## Configuration

| Setting | Env var | Default |
|---|---|---|
| Connection URI (required) | `DATABASE_URI` | — |
| Access mode | `PG_MCP_ACCESS_MODE` | `restricted` |
| Allow a privileged role | `PG_MCP_ALLOW_SUPERUSER` | `false` |
| Transport | `PG_MCP_TRANSPORT` | `stdio` |
| HTTP bind host / port | `PG_MCP_HOST` / `PG_MCP_PORT` | `127.0.0.1` / `8000` |
| Max rows / response bytes | `PG_MCP_MAX_ROWS` / `PG_MCP_MAX_BYTES` | `1000` / `50000` |
| Statement timeout | `PG_MCP_STATEMENT_TIMEOUT` | `30s` |
| Pool min / max | `PG_MCP_POOL_MIN` / `PG_MCP_POOL_MAX` | `1` / `5` |
| Schema allowlist (comma-sep) | `PG_MCP_SCHEMAS` | all non-system |
| Log level | `PG_MCP_LOG_LEVEL` | `INFO` |

Flags `--access-mode`, `--transport`, `--host`, `--port` override the
environment. The effective configuration is logged at startup with the
connection password redacted.

## Tools

| Tool | Purpose |
|---|---|
| `list_schemas` | Schemas, honoring the allowlist. |
| `list_objects` | Tables, views, matviews, sequences in a schema. |
| `describe_object` | Columns, PK, FKs both directions, indexes, constraints, comments, approximate row count. |
| `execute_query` | One read statement in a read-only transaction; capped results with an explicit truncation notice (no `LIMIT` is ever injected into your SQL). |
| `explain_query` | `EXPLAIN (FORMAT JSON)`; `analyze=true` always runs inside a rolled-back transaction. |
| `list_extensions` | Installed and available extensions. |
| `server_info` | Version, role, database, access mode, key settings. |
| `execute_statement` | Single DML statement — registered only in `unrestricted` mode. |

Results serialize honestly: `numeric` → string (never float), timestamps →
ISO 8601 with timezone, `bytea` → base64 with a length note, `json`/`jsonb` →
nested structures, `NULL` → null.

`max_bytes` bounds the result **as the model receives it** — the
pretty-printed text block, columns, truncation object and notice included —
not the row payload and not compact JSON, both of which understate the real
cost. The default is sized against the model's **context** rather than the
transport: 50 KB is roughly 12k tokens, where the 1 MiB response limit hosts
commonly enforce would be ~250k — a result that transits successfully and then
consumes the conversation it was meant to inform. Truncation is
self-correcting, since the notice tells the model to add `LIMIT`, filter, or
aggregate.

## Claude Desktop

```json
{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "-e", "DATABASE_URI", "kenning-pg-mcp:0.2.0"],
      "env": {
        "DATABASE_URI": "postgresql://mcp_ro:PASSWORD@host.docker.internal:5432/mydb"
      }
    }
  }
}
```

Or with a tool install:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "/absolute/path/to/kenning-pg-mcp",
      "env": {
        "DATABASE_URI": "postgresql://mcp_ro:PASSWORD@localhost:5432/mydb"
      }
    }
  }
}
```

## HTTP mode

```sh
PG_MCP_TRANSPORT=http kenning-pg-mcp
```

Clients connect to `http://127.0.0.1:8000/mcp`. Binding beyond loopback
requires `PG_MCP_ALLOW_REMOTE=true`, and there is **no built-in
authentication** — put the server behind a reverse proxy that authenticates,
and set `PG_MCP_ALLOWED_HOSTS` / `PG_MCP_ALLOWED_ORIGINS` to match your
deployment.

## Development

```sh
uv sync --frozen
make check          # lint + type-check + full test suite
```

The test suite has three layers: unit (no database), integration against a
real PostgreSQL via testcontainers, and an adversarial layer that attempts
every write-bypass in the threat model as a low-privilege role — each case
must fail at the database. Docker is required for the latter two.

## License

Licensed under the [MIT License](LICENSE). Use it for anything.