pgops
Safe, audited PostgreSQL operations for AI agents: queries, migrations, EXPLAIN, containers
Open source Open in the app JSON README (API)
About
Safe, audited PostgreSQL operations for AI agents: queries, migrations, EXPLAIN, containers
Details
- Kind
- MCP servers
- Topic
- Cloud & DevOps
- Publisher
- arzharch
- Origin
- official
- Category
- ferramentas
- Transport
- local
- Version
- 0.1.9
- Last push
- 2026-09-01T16:31:52Z
- Repository state
- ativo
- Language
- Python
- License
- MIT
- Added
- 2026-08-29 03:02:26
- Updated
- 2026-09-01 18:00:09
- Origin id
io.github.arzharch/pgops-mcp
README
# pgops-mcp
<!-- mcp-name: io.github.arzharch/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](https://github.com/arzharch/pgops-mcp/blob/main/docs/GETTING_STARTED.md) is a 15-minute guided
tour that assumes no MCP knowledge.
## Tool surface
| Group | Tools |
|---|---|
| Schema | `schema.inspect` |
| Queries | `query.read`, `query.write` (guarded), `query.explain` (parsed plan + verdict) |
| Performance | `index.advise`, `db.health` |
| Migrations | `migration.plan` (dry-run + lock analysis), `migration.describe` (plain English), `migration.apply`, `migration.rollback`, `migration.history` |
| Environment | `env.topology`, `env.correlate`, `container.logs`, `container.stats` |
| Gated | `container.restart`*, `container.exec`* |
\* 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
| Primitive | What's here |
|---|---|
| **Tools** | 17 — schema, query, explain, advise, migrate, environment |
| **Resources** | `pgops://schema`, `schema/summary`, `schema/{table}`, `health`, `migrations`, `audit/recent`, `config` |
| **Prompts** | `diagnose-slow-query`, `plan-safe-migration`, `incident-triage`, `review-index-health`, `explain-safety-model` |
| **Elicitation** | Dangerous actions ask the **user** directly, not via the agent; confirmation tokens are the fallback |
| **Sampling** | `migration.describe` turns English into a plan using *your* model — this server ships no API key |
| **Completions** | Table-name autocomplete for `pgops://schema/{table}` |
| **Progress / logging** | Best-effort notifications during long operations |
## 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:
```bash
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:**
```json
{
"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:
```json
{
"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:**
```bash
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](https://registry.modelcontextprotocol.io) 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](https://github.com/arzharch/pgops-mcp/blob/main/SETUP.md)** for configuration, HTTP transport, agent tokens and
troubleshooting, and [CONTRIBUTING.md](https://github.com/arzharch/pgops-mcp/blob/main/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**
| Doc | What's in it |
|---|---|
| [Getting started](https://github.com/arzharch/pgops-mcp/blob/main/docs/GETTING_STARTED.md) | First 15 minutes, no MCP knowledge assumed |
| [Tool reference](https://github.com/arzharch/pgops-mcp/blob/main/docs/API.md) | All 18 tools: parameters, returns, error codes, scopes |
| [Setup & configuration](https://github.com/arzharch/pgops-mcp/blob/main/SETUP.md) | Clients, HTTP auth, observability, troubleshooting |
| [Environment variables](https://github.com/arzharch/pgops-mcp/blob/main/.env.example) | Every knob, documented |
| [Security model](https://github.com/arzharch/pgops-mcp/blob/main/SECURITY.md) | What it can do, what it refuses, known limits |
| [Changelog](https://github.com/arzharch/pgops-mcp/blob/main/CHANGELOG.md) | What changed per release |
**How it works**
| Doc | What's in it |
|---|---|
| [Architecture](https://github.com/arzharch/pgops-mcp/blob/main/docs/ARCHITECTURE.md) | System design and trade-offs |
| [System design](https://github.com/arzharch/pgops-mcp/blob/main/docs/SYSTEM_DESIGN.md) | The safety pipeline, with diagrams |
| [Decision records](https://github.com/arzharch/pgops-mcp/blob/main/docs/adr/) | Why each choice was made, and what it cost |
| [Benchmarks](https://github.com/arzharch/pgops-mcp/blob/main/docs/BENCHMARKS.md) | What is measured, and against what |
**Contributing**
| Doc | What's in it |
|---|---|
| [Contributing](https://github.com/arzharch/pgops-mcp/blob/main/CONTRIBUTING.md) | Source checkout, gates, release process |
| [Module layout](https://github.com/arzharch/pgops-mcp/blob/main/LAYOUT.md) | What each module is for |
## 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](https://github.com/arzharch/pgops-mcp/blob/main/docs/adr/ADR-005.md)):
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.
| Suite | What it proves |
|---|---|
| Guardrails & classifier | Every refusal rule, against live Postgres |
| Property-based (Hypothesis) | The invariant itself, over inputs nobody thought to write |
| Red-team | 15 named attacks a hostile agent would try — each refused **and** audited |
| Live server | Real HTTP server, real JWTs, end to end |
| Benchmarks | Latency budgets as regression tripwires, published as CI artifacts |
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:
```bash
git clone https://github.com/arzharch/pgops-mcp && cd pgops-mcp
docker compose up -d
uvx pgops-mcp --selfcheck --dsn "postgresql://pgops:pgops_dev@localhost:5435/pgops_demo"
```
---
MIT licensed. Contributions welcome — see
[CONTRIBUTING.md](https://github.com/arzharch/pgops-mcp/blob/main/CONTRIBUTING.md).