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.

Related MCP server: PostgreSQL MCP Server

Tool surface (v0.1)

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

Quickstart

uv sync
# point at your local Postgres in Docker:
export PGOPS_DSN="postgresql://user:pass@localhost:5432/mydb"
uv run pgops-mcp            # stdio transport for Claude Desktop / Cursor / VS Code

Add to Claude Desktop:

{
  "mcpServers": {
    "pgops": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/pgops-mcp", "pgops-mcp"]
    }
  }
}

Docs

• docs/PRD.md — what & why, goals, non-goals

• docs/SPEC.md — phased technical spec with hard gates

• docs/ARCHITECTURE.md — system design + trade-offs

• docs/TOOLS.md — full tool catalog with schemas & examples

• docs/adr/ — architecture decision records

• docs/flow.md — living progress log

• docs/interview_prep.md — growing interview Q&A

Status

Phases 0–6b complete (319 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).

migration.rollback is deliberately still open — see docs/TOOLS.md.

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 5433, to avoid colliding with a local Postgres on 5432):

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