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. 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. 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 read-only mode), the server probes that a write inside a read-only transaction really fails with 25006 — and exits non-zero if it doesn't, rather than silently serving a read-only server that isn't. An adversarial test suite attempts every bypass listed above against a real PostgreSQL; 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

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.1.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.

Claude Desktop

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "-e", "DATABASE_URI", "kenning-pg-mcp:0.1.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 Hippocratic License 3.0 with the Mass Surveillance module — an ethical-source license. This project is source-available, not OSI open source. See LICENSE.