Kenning PG MCP

A PostgreSQL Model Context Protocol 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:

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:

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):

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

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

Tools

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

{
  "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:

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

HTTP mode

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

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. Use it for anything.