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

Setting	Env var	Default
Connection URI (required)	DATABASE_URI	—
Access mode	PG_MCP_ACCESS_MODE	restricted
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 / 1048576
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.

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.