# postgres-mcp

A fast, self-hostable **PostgreSQL MCP server**. Explore your database
(schemas, tables, columns, constraints, relationships, indexes, triggers,
functions/procedures, views, enums, stats) and run **guarded** read or write
queries — with a selectable **access mode** so the same server can be locked to
read-only or opened up for edits.

Built with Python + [uv](https://docs.astral.sh/uv/), FastMCP and psycopg3.
Runs equally well via `uvx` or Docker. Works with any PostgreSQL: local, Neon,
Supabase, Cloud SQL, RDS, DigitalOcean, …

> Naming note: this is an independent project. There is a separate, unrelated
> PyPI package also called `postgres-mcp` (Crystal DBA's "Postgres MCP Pro").
> If you publish, pick a unique distribution name.

---

## Quick start with Docker

```bash
# 1. Build the image
docker build -t postgres-mcp .

# 2. Add it to your MCP client (.mcp.json) — read-only by default:
```
```json
{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "-e", "DATABASE_URI", "-e", "PG_MCP_ACCESS_MODE", "postgres-mcp"],
      "env": {
        "DATABASE_URI": "postgresql://readonly_user:pass@host:5432/db",
        "PG_MCP_ACCESS_MODE": "readonly"
      }
    }
  }
}
```

The server speaks MCP over **stdio**, so the container must be run with `-i`.
`DATABASE_URI` and `PG_MCP_ACCESS_MODE` are listed both in `args` (to forward the
names into the container) and in `env` (their values), so secrets stay out of
the image.

### Try it instantly with a seeded demo database

```bash
docker compose --profile demo up -d demo-db   # Postgres on localhost:55432, pre-seeded
docker build -t postgres-mcp .
# Point DATABASE_URI at: postgresql://readonly_user:readonly_pw@host.docker.internal:55432/appdb
```

## Run without Docker (uv)

```bash
uv sync
DATABASE_URI=postgresql://readonly_user:pass@host:5432/db \
PG_MCP_ACCESS_MODE=readonly \
uv run postgres-mcp
```

Or straight from a Git repo, no clone needed:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": ["--from", "git+https://github.com/<you>/postgres-mcp", "postgres-mcp"],
      "env": { "DATABASE_URI": "postgresql://readonly_user:pass@host:5432/db" }
    }
  }
}
```

---

## Access modes

Set with `PG_MCP_ACCESS_MODE`. Each mode adds tools; higher modes include the
lower ones. Choose the **least** privilege you need.

| Mode | Read tools | `execute_dml` (INSERT/UPDATE/DELETE/MERGE) | `execute_ddl` (CREATE/ALTER/DROP/…) | DB session |
|------|:---------:|:------------------------------------------:|:-----------------------------------:|------------|
| `readonly` (default) | ✅ | — | — | forced `read only` |
| `readwrite` | ✅ | ✅ | — | normal |
| `admin` | ✅ | ✅ | ✅ | normal |

> The access mode is enforced two ways: write tools are **not even registered**
> in lower modes, and in `readonly` the database session itself rejects writes.
> For real safety, also connect with a **DB role** scoped to what you need.

---

## Tools

### Read (all modes)

| Tool | Purpose |
|------|---------|
| `server_info` | Current access mode + safety config |
| `list_schemas` | User schemas |
| `list_tables` | Tables/views/matviews with size & row estimate |
| `describe_table` | Columns, types, defaults, identity/generated, PK |
| `list_constraints` | PK / unique / FK / check / exclusion |
| `get_relations` | Incoming & outgoing foreign keys |
| `list_indexes` | Index definitions |
| `list_triggers` | Trigger definitions (schema or one table) |
| `list_functions` | Functions & procedures (signatures) |
| `get_function_definition` | Full source of a function/procedure |
| `list_views` | View / materialized-view definitions |
| `list_enums` | Enum types and labels |
| `table_stats` | Sizes, live/dead rows, vacuum/scan stats |
| `run_select` | Guarded read-only query runner |

### Write (mode-gated)

| Tool | Mode | Purpose |
|------|------|---------|
| `execute_dml` | `readwrite`, `admin` | INSERT / UPDATE / DELETE / MERGE |
| `execute_ddl` | `admin` | CREATE / ALTER / DROP / TRUNCATE / COMMENT / GRANT / REVOKE / REINDEX |

#### How writes stay safe — the dry-run + confirm workflow

Every write tool defaults to **`confirm=false`**, which performs a **dry run**:

1. **Understand the data flow first.** The model is instructed to inspect
   `describe_table`, `get_relations` (cascading FKs) and `list_triggers` before
   changing anything, so cascade/side-effects are known up front.
2. **Dry run.** With `confirm=false`, the statement runs inside a transaction
   that is **rolled back**. Because Postgres has transactional DDL, this both
   validates the statement and returns the **exact** `affected_rows` — without
   persisting anything.
3. **Review.** If `affected_rows` is larger than expected, fix the `WHERE`
   clause and dry-run again. `UPDATE`/`DELETE` **without a `WHERE`** is refused
   unless `allow_full_table_write=true`.
4. **Commit.** Re-run with `confirm=true` to apply the change.

On top of this, your MCP client (Claude Code, etc.) prompts the **human** to
approve each tool call — so a real person is always in the loop before a commit.

> Statements that can't run in a transaction (`CREATE INDEX CONCURRENTLY`,
> `CREATE/DROP DATABASE`, `VACUUM`) can't be dry-run; `execute_ddl` tells you to
> re-run with `confirm=true, non_transactional=true` (no rollback safety).

---

## Configuration

| Env var | Default | Meaning |
|---------|---------|---------|
| `DATABASE_URI` | — | `postgresql://user:pass@host:5432/db` (also `DATABASE_URL`) |
| `PG_MCP_ACCESS_MODE` | `readonly` | `readonly` / `readwrite` / `admin` |
| `PG_MCP_STATEMENT_TIMEOUT_MS` | `15000` | Per-statement timeout |
| `PG_MCP_MAX_ROWS` | `1000` | Hard cap on returned rows |
| `PG_MCP_POOL_MAX` | `4` | Max pooled connections |

TLS works out of the box (e.g. Neon: append `?sslmode=require` to the URI).

## Recommended DB roles

Read-only:
```sql
CREATE ROLE readonly_user LOGIN PASSWORD 'change_me';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
ALTER ROLE readonly_user SET statement_timeout = '15s';
```
Read-write (add only what you need):
```sql
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
```

## Add your own tools

Drop a function in `src/postgres_mcp/server.py`:

```python
@mcp.tool()
def biggest_tables(schema: str = "public", top: int = 10) -> list[dict]:
    """Largest tables in a schema by total size."""
    return db.query(
        """SELECT c.relname AS name,
                  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
           FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE n.nspname = %s AND c.relkind IN ('r','p','m')
           ORDER BY pg_total_relation_size(c.oid) DESC LIMIT %s""",
        (schema, top),
    )
```

## Roadmap

- ☐ Cloud SQL connectivity guide (Cloud SQL Auth Proxy + gcloud/service account)
- ☐ Provider notes: DigitalOcean Managed Databases, AWS RDS/Aurora
- ☐ Optional published images (GHCR) and PyPI release

## License

MIT