{
  "markdown": "# pgops-mcp\n\n<!-- mcp-name: io.github.arzharch/pgops-mcp -->\n\n> **Let an AI assistant operate your PostgreSQL database — without letting it wreck\n> anything.**\n\nPoint Claude, Cursor, or VS Code at a database and ask questions in plain English. Your\nassistant gets 18 tools for inspecting schemas, running queries, diagnosing slow ones,\nand planning migrations. What it does *not* get is a way to quietly destroy your data.\n\n## What it actually looks like\n\nEvery number below is real output from the demo database in this repo — 1.2M rows,\n114 MB.\n\n> **You:** I want to index `orders.status`\n\n> **Assistant:** *(calls `migration.plan`)*\n\n```\nCREATE INDEX \"idx_orders_status\" ON \"orders\" (status)\n  risk=medium   estimate=4000ms   confidence=medium\n  blocks:  writes yes, reads no\n  SAFER:   CREATE INDEX CONCURRENTLY \"idx_orders_status\" ON \"orders\" (status)\n```\n\n> **Assistant:** A plain index build blocks writes for roughly 4 seconds. `CONCURRENTLY`\n> is slower overall but keeps writes working. Which do you want?\n\nThe tool didn't return a raw plan for the model to interpret — it returned a **verdict**,\na duration estimate with an honest confidence level, and the safer alternative.\n\n`query.explain` works the same way. Instead of handing back JSON, it says things like:\n\n```\nplanner overestimated rows by 5833x (estimated 239,160, actual 40)\n  -> run ANALYZE; if it persists the columns may be correlated, consider CREATE STATISTICS\n```\n\n### And when something is genuinely dangerous\n\n> **You:** clear out the orders table\n\n> **Assistant:** *(calls `query.write` with `DELETE FROM orders`)*\n\n```\nCONFIRMATION_REQUIRED\nDELETE has no WHERE clause and would affect every row in the table\n```\n\nWhere your client supports it, the approval prompt goes to **you** — not to the\nassistant. Nothing runs until a human answers, and the refusal is written to the audit\nlog whether or not you approve.\n\nThat last part is the point. **The assistant cannot approve its own dangerous action,\nbecause it is not the one being asked.** Where a client can't show a prompt, it degrades\nto a single-use token bound to that exact statement — never to \"allowed\".\n\n## Why this exists\n\nMost Postgres MCP servers are thin query wrappers: introspect and `SELECT`. None handle\nmigrations with lock-impact analysis, none diagnose performance from `EXPLAIN` and\n`pg_stat_statements`, and none understand the container the database runs in. Agents\noperating databases today are doing it blind, and without guardrails.\n\n`pgops-mcp` is the operations brain: **schema intelligence → guarded queries → migration\nengine → performance diagnosis → environment awareness**, with a safety architecture that\nmakes every action classifiable, confirmable, and auditable.\n\n**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.\n\n**New here?** [docs/GETTING_STARTED.md](https://github.com/arzharch/pgops-mcp/blob/main/docs/GETTING_STARTED.md) is a 15-minute guided\ntour that assumes no MCP knowledge.\n\n## Tool surface\n\n| Group | Tools |\n|---|---|\n| Schema | `schema.inspect` |\n| Queries | `query.read`, `query.write` (guarded), `query.explain` (parsed plan + verdict) |\n| Performance | `index.advise`, `db.health` |\n| Migrations | `migration.plan` (dry-run + lock analysis), `migration.describe` (plain English), `migration.apply`, `migration.rollback`, `migration.history` |\n| Environment | `env.topology`, `env.correlate`, `container.logs`, `container.stats` |\n| Gated | `container.restart`*, `container.exec`* |\n\n\\* Not registered at all unless the server runs with `--approval-mode`, and even then\neach call needs a confirmation token. `container.exec` additionally enforces a read-only\ndiagnostic command allowlist — it does not offer a shell. The Docker socket is\nroot-equivalent on the host, so the default is read-only access.\n\n## Safety model (the core differentiator)\n\n- Separate read-only / read-write connection roles; tools bind to the right role\n- Statement classification before execution — unbounded `DELETE`/`UPDATE` blocked\n- Destructive actions require explicit confirmation tokens\n- Every executed statement lands in an append-only audit log with timing and verdict\n- Runaway-query cancellation with timeout tiers\n\n## MCP surface\n\n| Primitive | What's here |\n|---|---|\n| **Tools** | 17 — schema, query, explain, advise, migrate, environment |\n| **Resources** | `pgops://schema`, `schema/summary`, `schema/{table}`, `health`, `migrations`, `audit/recent`, `config` |\n| **Prompts** | `diagnose-slow-query`, `plan-safe-migration`, `incident-triage`, `review-index-health`, `explain-safety-model` |\n| **Elicitation** | Dangerous actions ask the **user** directly, not via the agent; confirmation tokens are the fallback |\n| **Sampling** | `migration.describe` turns English into a plan using *your* model — this server ships no API key |\n| **Completions** | Table-name autocomplete for `pgops://schema/{table}` |\n| **Progress / logging** | Best-effort notifications during long operations |\n\n## Remote access & agent tokens\n\nstdio needs no auth — the server is a subprocess your client spawns, with no open port.\nHTTP does, so it refuses to start without a key:\n\n```bash\npgops-mcp keygen                                    # RS256 keypair\npgops-mcp issue-token --subject my-agent            # read-only by default\npgops-mcp issue-token --subject deploy-bot --scope pgops:read --scope pgops:write\npgops-mcp scopes                                    # which scope each tool needs\n\npgops-mcp --transport http --public-key ~/.pgops/keys/pgops_public.pem\n```\n\nThe server holds only the **public** key, so it can verify tokens but never mint them.\nScopes (`pgops:read` / `pgops:write` / `pgops:admin`) map to the same danger tiers as the\nguardrails, and a tool with no scope entry requires `admin` — deny by default. Binds\nloopback unless you say otherwise.\n\n## Install\n\n`pgops-mcp` is an MCP server, not a Python library — nothing in it is meant to be\nimported, and `pgops.*` carries no API-stability promise. You install it the way you\ninstall any MCP server: point your client at it.\n\n**Claude Desktop / Cursor / VS Code:**\n\n```json\n{\n  \"mcpServers\": {\n    \"pgops\": {\n      \"command\": \"uvx\",\n      \"args\": [\"pgops-mcp\"],\n      \"env\": { \"PGOPS_DSN\": \"postgresql://user:pass@localhost:5432/mydb\" }\n    }\n  }\n}\n```\n\n`uvx` fetches and runs it in a throwaway environment — nothing to install first, and\nnothing added to your own project's dependencies.\n\n**Or run the container**, if you would rather not put a Python toolchain on the machine\nthat talks to your database:\n\n```json\n{\n  \"mcpServers\": {\n    \"pgops\": {\n      \"command\": \"docker\",\n      \"args\": [\n        \"run\", \"-i\", \"--rm\",\n        \"-e\", \"PGOPS_DSN\",\n        \"-v\", \"pgops-audit:/var/lib/pgops\",\n        \"ghcr.io/arzharch/pgops-mcp:latest\"\n      ],\n      \"env\": { \"PGOPS_DSN\": \"postgresql://user:pass@host.docker.internal:5432/mydb\" }\n    }\n  }\n}\n```\n\nTwo things the container changes: mount a volume at `/var/lib/pgops` or the audit log\ndies with the container, and `localhost` inside a container is the container itself —\nuse `host.docker.internal` or a compose service name.\n\n**Check the connection before wiring a client to it:**\n\n```bash\nuvx pgops-mcp --selfcheck --dsn \"postgresql://user:pass@localhost:5432/mydb\"\n```\n\nBoth paths install the same server and are listed together in the\n[MCP Registry](https://registry.modelcontextprotocol.io) entry — they fail for different\npeople. `uvx` needs nothing preinstalled but assumes the host may run Python; the\ncontainer assumes only Docker.\n\nSee **[SETUP.md](https://github.com/arzharch/pgops-mcp/blob/main/SETUP.md)** for configuration, HTTP transport, agent tokens and\ntroubleshooting, and [CONTRIBUTING.md](https://github.com/arzharch/pgops-mcp/blob/main/CONTRIBUTING.md) to run it from a source checkout.\n\n## Docs\n\nLinks are absolute so they resolve from the PyPI project page as well as from GitHub.\n\n**Using it**\n\n| Doc | What's in it |\n|---|---|\n| [Getting started](https://github.com/arzharch/pgops-mcp/blob/main/docs/GETTING_STARTED.md) | First 15 minutes, no MCP knowledge assumed |\n| [Tool reference](https://github.com/arzharch/pgops-mcp/blob/main/docs/API.md) | All 18 tools: parameters, returns, error codes, scopes |\n| [Setup & configuration](https://github.com/arzharch/pgops-mcp/blob/main/SETUP.md) | Clients, HTTP auth, observability, troubleshooting |\n| [Environment variables](https://github.com/arzharch/pgops-mcp/blob/main/.env.example) | Every knob, documented |\n| [Security model](https://github.com/arzharch/pgops-mcp/blob/main/SECURITY.md) | What it can do, what it refuses, known limits |\n| [Changelog](https://github.com/arzharch/pgops-mcp/blob/main/CHANGELOG.md) | What changed per release |\n\n**How it works**\n\n| Doc | What's in it |\n|---|---|\n| [Architecture](https://github.com/arzharch/pgops-mcp/blob/main/docs/ARCHITECTURE.md) | System design and trade-offs |\n| [System design](https://github.com/arzharch/pgops-mcp/blob/main/docs/SYSTEM_DESIGN.md) | The safety pipeline, with diagrams |\n| [Decision records](https://github.com/arzharch/pgops-mcp/blob/main/docs/adr/) | Why each choice was made, and what it cost |\n| [Benchmarks](https://github.com/arzharch/pgops-mcp/blob/main/docs/BENCHMARKS.md) | What is measured, and against what |\n\n**Contributing**\n\n| Doc | What's in it |\n|---|---|\n| [Contributing](https://github.com/arzharch/pgops-mcp/blob/main/CONTRIBUTING.md) | Source checkout, gates, release process |\n| [Module layout](https://github.com/arzharch/pgops-mcp/blob/main/LAYOUT.md) | What each module is for |\n\n## How it's verified\n\n**471 tests**, and the ones that matter run against a real PostgreSQL 16 in a\ncontainer — not mocks. That is a deliberate decision ([ADR-005](https://github.com/arzharch/pgops-mcp/blob/main/docs/adr/ADR-005.md)):\na guardrail proven only against a fake has been proven against the wrong thing. The\ninteresting failures — `default_transaction_read_only`, lock escalation, transactional\nDDL, relfilenode changes on rewrite — are behaviours of the real database.\n\n| Suite | What it proves |\n|---|---|\n| Guardrails & classifier | Every refusal rule, against live Postgres |\n| Property-based (Hypothesis) | The invariant itself, over inputs nobody thought to write |\n| Red-team | 15 named attacks a hostile agent would try — each refused **and** audited |\n| Live server | Real HTTP server, real JWTs, end to end |\n| Benchmarks | Latency budgets as regression tripwires, published as CI artifacts |\n\nThe red-team suite has found real bugs, which is the argument for having it: it caught a\nconfirmation token issued for a refused statement being redeemable against a different\none, and a `pgops:read` token that could call `query.write` because the scope table was\ndocumentation rather than enforcement.\n\n## Known limits\n\nStated here rather than left to be discovered:\n\n- **No per-session database isolation.** Auth identifies the caller and scopes limit what\n  they may do, but every caller shares one connection manager and one audit log. Built\n  for one engineer and a few databases, not multi-tenant SaaS.\n- **`index.advise` names the table taking sequential scans, not the column** to index —\n  that needs per-statement plan inspection. It says so instead of inventing a\n  `CREATE INDEX`.\n- **`DROP INDEX` / `DROP CONSTRAINT` cannot be rolled back**, because the object's\n  definition is not captured before the drop. The rollback refuses and explains why\n  rather than reconstructing a guess.\n\nSample of what `migration.plan` returns for a type change on the 1.2M-row `orders`:\n\n```\nALTER TABLE \"orders\" ALTER COLUMN \"total_cents\" TYPE bigint\n  op=table_rewrite  risk=high  estimate=4800ms  confidence=medium\n  why:   rewrites every row and rebuilds every index, holding AccessExclusiveLock\n  SAFER: add a new column of the target type, backfill in batches, sync with a\n         trigger, swap the names, then drop the old column\n```\n\n### Try it without a database of your own\n\nA seeded stack with the 1.2M-row `orders` table used in every example above. Host port\n**5435**, so it does not collide with a local Postgres on 5432:\n\n```bash\ngit clone https://github.com/arzharch/pgops-mcp && cd pgops-mcp\ndocker compose up -d\nuvx pgops-mcp --selfcheck --dsn \"postgresql://pgops:pgops_dev@localhost:5435/pgops_demo\"\n```\n\n---\n\nMIT licensed. Contributions welcome — see\n[CONTRIBUTING.md](https://github.com/arzharch/pgops-mcp/blob/main/CONTRIBUTING.md).\n",
  "bytes": 12691,
  "sha": "f86ed9a95f42728b8aba7db0d8127fa170773c5c8c9e9395da6752a8ca62ab9a",
  "repo_slug": "arzharch/pgops-mcp",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_arzharch_pgops_mcp_52d03b72/readme"
}