pgops-mcp

A production-grade MCP server that gives AI agents safe, audited, expert-level control over a real PostgreSQL database and the Docker stack around it — no shell commands, no Python scripts, just tools.

Why

Existing Postgres MCP servers are thin query wrappers: introspect + SELECT. None handle migrations with lock-impact analysis, none diagnose performance from EXPLAIN + pg_stat_statements, none understand the containerized environment the database lives in. Agents operating databases today are flying blind and unsafe.

pgops-mcp is the operations brain: schema intelligence → guarded queries → migration engine → performance diagnosis → environment awareness, with a safety architecture that makes every action classifiable, confirmable, and auditable.

Tool surface

* Not registered at all unless the server runs with --approval-mode, and even then each call needs a confirmation token. container.exec additionally enforces a read-only diagnostic command allowlist — it does not offer a shell. The Docker socket is root-equivalent on the host, so the default is read-only access.

Safety model (the core differentiator)

• Separate read-only / read-write connection roles; tools bind to the right role
• Statement classification before execution — unbounded DELETE/UPDATE blocked
• Destructive actions require explicit confirmation tokens
• Every executed statement lands in an append-only audit log with timing and verdict
• Runaway-query cancellation with timeout tiers

MCP surface

Remote access & agent tokens

stdio needs no auth — the server is a subprocess your client spawns, with no open port. HTTP does, so it refuses to start without a key:

pgops-mcp keygen                                    # RS256 keypair
pgops-mcp issue-token --subject my-agent            # read-only by default
pgops-mcp issue-token --subject deploy-bot --scope pgops:read --scope pgops:write
pgops-mcp scopes                                    # which scope each tool needs

pgops-mcp --transport http --public-key ~/.pgops/keys/pgops_public.pem

The server holds only the public key, so it can verify tokens but never mint them. Scopes (pgops:read / pgops:write / pgops:admin) map to the same danger tiers as the guardrails, and a tool with no scope entry requires admin — deny by default. Binds loopback unless you say otherwise.

Install

pgops-mcp is an MCP server, not a Python library — nothing in it is meant to be imported, and pgops.* carries no API-stability promise. You install it the way you install any MCP server: point your client at it.

Claude Desktop / Cursor / VS Code:

{
  "mcpServers": {
    "pgops": {
      "command": "uvx",
      "args": ["pgops-mcp"],
      "env": { "PGOPS_DSN": "postgresql://user:pass@localhost:5432/mydb" }
    }
  }
}

uvx fetches and runs it in a throwaway environment — nothing to install first, and nothing added to your own project's dependencies.

Or run the container, if you would rather not put a Python toolchain on the machine that talks to your database:

{
  "mcpServers": {
    "pgops": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "PGOPS_DSN",
        "-v", "pgops-audit:/var/lib/pgops",
        "ghcr.io/arzharch/pgops-mcp:latest"
      ],
      "env": { "PGOPS_DSN": "postgresql://user:pass@host.docker.internal:5432/mydb" }
    }
  }
}

Two things the container changes: mount a volume at /var/lib/pgops or the audit log dies with the container, and localhost inside a container is the container itself — use host.docker.internal or a compose service name.

Check the connection before wiring a client to it:

uvx pgops-mcp --selfcheck --dsn "postgresql://user:pass@localhost:5432/mydb"

Both paths install the same server and are listed together in the MCP Registry entry — they fail for different people. uvx needs nothing preinstalled but assumes the host may run Python; the container assumes only Docker.

See SETUP.md for configuration, HTTP transport, agent tokens and troubleshooting, and CONTRIBUTING.md to run it from a source checkout.

Docs

For users:

• docs/API.md — full tool catalog: parameters, returns, error codes, scopes
• docs/BENCHMARKS.md — what the benchmarks measure and what they are compared against
• docs/GETTING_STARTED.md — first 15 minutes, guided tour
• SETUP.md — full setup guide: config, clients, HTTP auth, observability, troubleshooting
• .env.example — every environment variable, documented

Internal (design & process):

• internal/PRD.md, internal/SPEC.md — product requirements & phased spec
• internal/ARCHITECTURE.md, internal/SYSTEM_DESIGN.md — design + rendered diagrams
• internal/adr/ — architecture decision records
• internal/flow.md — living progress log
• internal/interview_prep.md — Q&A companion

Status

Phases 0–6f complete (436 tests, every guardrail, verdict and lock-impact rule proven against real Postgres via testcontainers — no mocks — plus end-to-end suites driving the server as a real MCP subprocess over stdio and as an authenticated HTTP server, verified through the MCP Inspector).

Sample of what migration.plan returns for a type change on the 1.2M-row orders:

ALTER TABLE "orders" ALTER COLUMN "total_cents" TYPE bigint
  op=table_rewrite  risk=high  estimate=4800ms  confidence=medium
  why:   rewrites every row and rebuilds every index, holding AccessExclusiveLock
  SAFER: add a new column of the target type, backfill in batches, sync with a
         trigger, swap the names, then drop the old column

Quickstart the dev database (host port 5435, to avoid colliding with a local Postgres on 5432):

docker compose up -d
export PGOPS_DSN="postgresql://pgops:pgops_dev@localhost:5435/pgops_demo"
uv run pgops-mcp --selfcheck