# postgres-mcp

A production-ready **MCP server for PostgreSQL** — built for Claude Desktop, Claude Code,
and any MCP-compatible AI agent.

Extends [crystaldba/postgres-mcp](https://github.com/crystaldba/postgres-mcp) with:
- **Aurora IAM auth** — connect to AWS Aurora Postgres without embedding passwords
- **Playbook mode** — chain diagnostic tools into named runbooks Claude executes end-to-end
- **Pre-packaged Claude Desktop config** — one paste and you're connected
- **rajivonai brand config** — opinionated defaults that pair with [pg-advisor](https://github.com/rajivonai/pg-advisor) and [easy-pg-lab](https://github.com/rajivonai/easy-pg-lab)

```
Claude → postgres-mcp → your Postgres (local, RDS, or Aurora)
```

---

## What this MCP server can do

| Tool exposed to Claude | What it does |
|---|---|
| `query` | Execute a read-only SQL query and return results |
| `explain` | Run EXPLAIN (ANALYZE, BUFFERS) and parse the plan |
| `schema` | Describe tables, columns, indexes, constraints in a database |
| `health_check` | Run a battery of health checks (vacuum, connections, cache hit ratio, replication) |
| `index_advisor` | Suggest indexes using cost-based simulation (no writes needed) |
| `playbook` | Execute a named diagnostic playbook end-to-end |

**What it does NOT do by default:**
- No INSERT / UPDATE / DELETE / DDL (read-only mode is the default)
- No cross-database access (scoped to the configured database)
- No credential storage (uses env vars or IAM)

You can enable write access with `--allow-writes` — but for production, read-only is recommended.

---

## What crystaldba/postgres-mcp already provides (and we inherit)

This project builds on [crystaldba/postgres-mcp](https://github.com/crystaldba/postgres-mcp).
Credit to the crystaldba team for:
- Real cost-based index simulation using the Anytime Algorithm
- EXPLAIN plan analysis and bottleneck identification
- Built-in health checks (buffer cache, vacuum, sequences, replication)
- Configurable read/write access controls

We do **not** fork or modify their core engine — we wrap it and add the layers described above.
If a feature request belongs in the core, we'll upstream it.

---

## Install

```bash
pip install postgres-mcp
# or from source:
git clone https://github.com/rajivonai/postgres-mcp && cd postgres-mcp
pip install -e .
```

Requires Python 3.11+.

---

## Quick start

### Standard Postgres (local or RDS password auth)

```bash
postgres-mcp serve \
  --host localhost \
  --port 5432 \
  --user postgres \
  --dbname mydb
```

Or via connection string:
```bash
postgres-mcp serve --url "postgresql://user:pass@host:5432/mydb"
```

### Aurora Postgres with IAM auth (no password needed)

```bash
postgres-mcp serve \
  --aurora-host my-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com \
  --aurora-region us-east-1 \
  --aurora-user myuser \
  --dbname mydb
```

Requires the `AmazonRDSReadOnlyAccess` IAM policy (or equivalent) on the calling role.

---

## Claude Desktop setup

Add this to `~/Library/Application Support/Claude/claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "postgres-mcp",
      "args": [
        "serve",
        "--url", "postgresql://postgres:postgres@localhost:5432/mydb"
      ]
    }
  }
}
```

For Aurora (uses AWS profile `default`):
```json
{
  "mcpServers": {
    "postgres-aurora": {
      "command": "postgres-mcp",
      "args": [
        "serve",
        "--aurora-host", "my-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com",
        "--aurora-region", "us-east-1",
        "--aurora-user", "myuser",
        "--dbname", "mydb"
      ]
    }
  }
}
```

Restart Claude Desktop after editing.

---

## Claude Code setup

```bash
# Add to your project's .claude/mcp.json
{
  "servers": {
    "postgres": {
      "command": "postgres-mcp",
      "args": ["serve", "--url", "${PG_URL}"]
    }
  }
}
```

Or run inline:
```bash
claude --mcp-server "postgres-mcp serve --url $PG_URL"
```

---

## Playbooks

Playbooks are named diagnostic sequences — Claude executes a structured workflow rather than
ad-hoc tool calls. Run them from the CLI:

```bash
postgres-mcp playbook slow-query --url "$PG_URL" --threshold-ms 500
postgres-mcp playbook pre-deploy  --url "$PG_URL"
postgres-mcp playbook health      --url "$PG_URL"
postgres-mcp playbook replica-lag --url "$PG_URL"
```

Or ask Claude directly (once the MCP server is running):
> "Run the slow-query playbook on my database"

### Available playbooks

| Playbook | What Claude does |
|---|---|
| `health` | Full cluster health check: vacuum, indexes, connections, config, replication |
| `slow-query` | Find queries over threshold, EXPLAIN the worst ones, recommend indexes |
| `pre-deploy` | Check for lock-heavy migrations, connection headroom, replication lag — safe to deploy? |
| `replica-lag` | Diagnose replication lag: network vs apply vs long query on replica |
| `bloat` | Estimate table and index bloat, rank by waste, recommend VACUUM or pg_repack |

---

## Security model

| Mode | What's allowed | Recommended for |
|---|---|---|
| `--read-only` (default) | SELECT, EXPLAIN, pg_stat_* views | Production |
| `--allow-writes` | + INSERT, UPDATE, DELETE | Dev/lab only |
| `--allow-ddl` | + CREATE, ALTER, DROP | Lab with caution |

The server **never** executes statements outside the configured access level.
All tool calls are logged to stderr.

---

## Part of the rajivonai polyglot database toolkit

[rajivonai.com](https://rajivonai.com) · [easy-pg-lab](https://github.com/rajivonai/easy-pg-lab) · [pg-advisor](https://github.com/rajivonai/pg-advisor) · [skills](https://github.com/rajivonai/skills)