pgops

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_REQUIREDDELETE 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 keypairpgops-mcp issue-token --subject my-agent            # read-only by defaultpgops-mcp issue-token --subject deploy-bot --scope pgops:read --scope pgops:writepgops-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:[email protected]: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

471 tests, and the ones that matter run against a real PostgreSQL 16 in a container — not mocks. That is a deliberate decision (ADR-005): a guardrail proven only against a fake has been proven against the wrong thing. The interesting failures — default_transaction_read_only, lock escalation, transactional DDL, relfilenode changes on rewrite — are behaviours of the real database.

The red-team suite has found real bugs, which is the argument for having it: it caught a confirmation token issued for a refused statement being redeemable against a different one, and a pgops:read token that could call query.write because the scope table was documentation rather than enforcement.

Known limits

Stated here rather than left to be discovered:

• No per-session database isolation. Auth identifies the caller and scopes limit what they may do, but every caller shares one connection manager and one audit log. Built for one engineer and a few databases, not multi-tenant SaaS.
• index.advise names the table taking sequential scans, not the column to index — that needs per-statement plan inspection. It says so instead of inventing a CREATE INDEX.
• DROP INDEX / DROP CONSTRAINT cannot be rolled back, because the object's definition is not captured before the drop. The rollback refuses and explains why rather than reconstructing a guess.

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

Try it without a database of your own

A seeded stack with the 1.2M-row orders table used in every example above. Host port 5435, so it does not collide with a local Postgres on 5432:

git clone https://github.com/arzharch/pgops-mcp && cd pgops-mcpdocker compose up -duvx pgops-mcp --selfcheck --dsn "postgresql://pgops:pgops_dev@localhost:5435/pgops_demo"

MIT licensed. Contributions welcome — see CONTRIBUTING.md.