pgops-mcp

Let an AI assistant operate your PostgreSQL database — without letting it wreck anything.

Point Claude, Cursor, or VS Code at a database and ask questions in plain English. Your assistant gets 18 tools for inspecting schemas, running queries, diagnosing slow ones, and planning migrations. What it does not get is a way to quietly destroy your data.

What it actually looks like

Every number below is real output from the demo database in this repo — 1.2M rows, 114 MB.

You: I want to index orders.status

Assistant: (calls migration.plan)

CREATE INDEX "idx_orders_status" ON "orders" (status)
  risk=medium   estimate=4000ms   confidence=medium
  blocks:  writes yes, reads no
  SAFER:   CREATE INDEX CONCURRENTLY "idx_orders_status" ON "orders" (status)

Assistant: A plain index build blocks writes for roughly 4 seconds. CONCURRENTLY is slower overall but keeps writes working. Which do you want?

The tool didn't return a raw plan for the model to interpret — it returned a verdict, a duration estimate with an honest confidence level, and the safer alternative.

query.explain works the same way. Instead of handing back JSON, it says things like:

planner overestimated rows by 5833x (estimated 239,160, actual 40)
  -> run ANALYZE; if it persists the columns may be correlated, consider CREATE STATISTICS

And when something is genuinely dangerous

You: clear out the orders table

Assistant: (calls query.write with DELETE FROM orders)

CONFIRMATION_REQUIRED
DELETE has no WHERE clause and would affect every row in the table

Where your client supports it, the approval prompt goes to you — not to the assistant. Nothing runs until a human answers, and the refusal is written to the audit log whether or not you approve.

That last part is the point. The assistant cannot approve its own dangerous action, because it is not the one being asked. Where a client can't show a prompt, it degrades to a single-use token bound to that exact statement — never to "allowed".

Why this exists

Most Postgres MCP servers are thin query wrappers: introspect and SELECT. None handle migrations with lock-impact analysis, none diagnose performance from EXPLAIN and pg_stat_statements, and none understand the container the database runs in. Agents operating databases today are doing it blind, and without guardrails.

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.

Native AI/ML Extension Support: Because pgops builds on core Postgres catalogs rather than brittle regex parsing, it inherits native support for custom types and extensions like pgvector. Tools like migration.plan and query.explain understand vector types (vector(384)) and hnsw indexes out of the box, with zero configuration.

New here? docs/GETTING_STARTED.md is a 15-minute guided tour that assumes no MCP knowledge.

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

Links are absolute so they resolve from the PyPI project page as well as from GitHub.

Using it

How it works

Contributing

How it's verified