# mcp-postgres

An MCP server that gives an agent access to **one** PostgreSQL database, configured
**per project** rather than globally, and **read-only until you say otherwise**.

The agent can inspect the real schema, verify queries against a live plan, read data,
and — when permitted — modify data and tables.

## Why per project

Configuration comes from environment variables and a project-local `.env`, and the
server is registered in the project's `.mcp.json`. Two projects therefore get two
different databases with two different permission sets. There is no global config
file to leak one project's credentials into another's session.

## Tools

| Tool | What it does |
|---|---|
| `pg_info` | Database, role, schema, server version, and which operations are permitted. Call first. |
| `pg_list_schemas` | Non-system schemas with table/view counts. |
| `pg_list_tables` | Tables and views in a schema: column counts, estimated rows, size. |
| `pg_describe_table` | Columns, types, defaults, constraints, indexes, incoming FKs, view definition. |
| `pg_list_relationships` | Every foreign key touching a schema — the join graph in one call. |
| `pg_query` | One read-only statement. Runs in a `READ ONLY` transaction that is always rolled back. |
| `pg_explain` | Execution plan, optionally with `ANALYZE` timings. |
| `pg_execute` | Statements that change data or structure. Gated by the permission flags. |

## The safety model

Two independent layers, and the second one is the one that matters:

1. **Classification.** A SQL lexer strips comments and string literals, splits the batch
   into statements, and classifies each one as read / write / DDL / admin. Anything the
   configuration does not allow is refused with an explanation the agent can act on —
   before it reaches the database. Because it lexes rather than pattern-matches,
   `SELECT 'DROP TABLE users'` is a read, and `WITH d AS (DELETE ...) SELECT * FROM d`
   is a write.
2. **PostgreSQL.** `pg_query` runs inside `BEGIN READ ONLY` and always rolls back, so it
   cannot write even if layer 1 were wrong. Everything else is bounded by the privileges
   of the role you connect as.

**Layer 1 is a guardrail, not a security boundary. Layer 2 is.** Give the role only the
privileges the project actually needs — a read-only role for a read-only project.

Permission flags, all `false` by default, each unlocking only its own class:

| Flag | Unlocks |
|---|---|
| `PGMCP_ALLOW_WRITE` | `INSERT` / `UPDATE` / `DELETE` / `MERGE` |
| `PGMCP_ALLOW_DDL` | `CREATE` / `ALTER` / `COMMENT` / index / `VACUUM` |
| `PGMCP_ALLOW_DESTRUCTIVE` | `DROP`, `TRUNCATE`, and `DELETE`/`UPDATE` with no `WHERE` |
| `PGMCP_ALLOW_ADMIN` | `GRANT` / `REVOKE` / role management |

Enabling `WRITE` does not grant `DDL`; enabling `DDL` does not grant `DROP`. That is
deliberate — "let the agent fix a row" and "let the agent drop a table" are not the
same decision.

Refused unconditionally, with no flag that re-enables them: `COPY` (server-side file
access and `FROM PROGRAM`), `ALTER SYSTEM`, `SET ROLE` / `SET SESSION AUTHORIZATION`,
`LOAD`, and the file/remote functions `pg_read_file`, `lo_import`, `lo_export`, `dblink`
and friends. Those are host-compromise vectors, not database features.

Transaction control (`BEGIN`, `COMMIT`, `ROLLBACK`, `SET`) is also refused: the server
manages transactions so a half-open one can never be returned to the pool.

## Install it once

```bash
git clone <this repo> && cd mcp-postgres

python -m venv .venv
.venv/Scripts/activate          # Windows
# source .venv/bin/activate     # Linux/macOS
pip install -e .
```

For Docker installs, also build the image once:

```bash
docker build -t mcp-postgres:0.1.0 .
```

## Then set it up per project

From **the project you want the agent to work in**:

```bash
cd /path/to/your-project
mcp-postgres init
```

It asks for the six connection values and nothing else:

```
  PostgreSQL host: db.internal
  Port [5432]:
  User (role): app
  Password:
  Database name: shop
  Schema [public]:
```

`Port` defaults to `5432` and `Schema` to `public` — press Enter to accept either. The
other four are required; blank input just asks again.

Then it connects for real before writing anything, so a typo fails here rather than on
the agent's first query:

```
Verifying the connection...
  ok: PostgreSQL 17.10 -- 24 relations in schema 'public'
```

and writes three files into the project:

| File | Purpose |
|---|---|
| `.env` | Credentials, permissions and limits — everything tunable, with comments |
| `.mcp.json` | The server entry, merged in without disturbing other servers |
| `.gitignore` | Gains a `.env` line, unless it already ignores it |

Restart your MCP client, then ask it to call `pg_info`.

### Options

```
mcp-postgres init [--docker] [--http-port N] [--allow-write] [--allow-ddl]
                  [--project PATH] [--no-verify] [--force]
```

The install is read-only unless you pass `--allow-write` / `--allow-ddl`. You can always
flip the `PGMCP_ALLOW_*` flags in `.env` afterwards and restart. Destructive and admin
permissions have no flag on purpose — edit `.env` deliberately for those.

`--project` installs into a directory other than the current one. `--no-verify` skips the
connection check, for when the database is not up yet. `--force` overwrites an existing
`.env` without asking.

### Docker mode

```bash
cd /path/to/your-project
mcp-postgres init --docker --http-port 8765
docker compose -f docker-compose.mcp-postgres.yml up -d
```

`--docker` also writes a project-local `docker-compose.mcp-postgres.yml`, so **each
project runs its own container** against its own `.env`. It uses the prebuilt image, so
the project needs no copy of this source tree.

Two things the installer handles for you, because they are what usually breaks:

- **`localhost` is rewritten to `host.docker.internal`.** Inside a container, `localhost`
  is the container itself. The installer verifies the connection using the host you typed,
  then stores the one the container can actually reach — and tells you it did. The compose
  file maps `host-gateway` so the name resolves.
- **`PGMCP_ALLOWED_HOSTS` is filled in to match `--http-port`.** Streamable HTTP validates
  the `Host` header against it (DNS-rebinding protection); a mismatch surfaces as HTTP 421.
  If you change the published port later, change this too.

The port is published on `127.0.0.1` only, and the container runs read-only with
`cap_drop: ALL`. **Do not move that endpoint onto a shared network** — it has no
authentication and holds your database credentials.

### Doing it by hand

`env.example` and `mcp.json.example` show what `init` generates, if you would rather
write the files yourself. The one thing to get right in `.mcp.json` for a stdio install
is `cwd`: it must be **the project**, because that is what makes the server read that
project's `.env` and no other.

### Trying it against a throwaway database

This repo's own `docker-compose.yml` can start a scratch PostgreSQL:

```bash
docker compose --profile demo up -d
```

## Usage notes for the agent

Pass values separately instead of formatting them into SQL:

```
pg_query(sql="SELECT * FROM orders WHERE customer_id = %s AND created_at > %s",
         params=[42, "2026-01-01"])
```

Results are capped at `PGMCP_MAX_ROWS` (default 500) and long values at
`PGMCP_MAX_FIELD_CHARS`; the response says `truncated: true` when it hit the cap, so
page with `LIMIT`/`OFFSET`. Every statement is bounded by
`PGMCP_STATEMENT_TIMEOUT_MS` (default 30s).

`pg_execute` runs a whole batch in one transaction — if statement 3 fails, 1 and 2 roll
back with it. Pass `autocommit=true` only for statements Postgres refuses inside a
transaction (`CREATE INDEX CONCURRENTLY`, `VACUUM`).

## Development

```bash
pip install -e ".[dev]"
pytest                  # unit tests only — no database needed
ruff check .
```

The integration tests need a live PostgreSQL. A throwaway one:

```bash
docker run -d --name pgmcp-test -p 55432:5432 \
  -e POSTGRES_USER=testuser -e POSTGRES_PASSWORD=testpass -e POSTGRES_DB=testdb \
  postgres:17-alpine

pytest -m integration    # ~40s; spawns the server as a subprocess for the protocol tests
```

Override the target with `PGMCP_TEST_HOST`, `PGMCP_TEST_PORT`, `PGMCP_TEST_USER`,
`PGMCP_TEST_PASSWORD`, `PGMCP_TEST_DATABASE`. Everything is created in a dedicated
schema and dropped afterwards — still, never point them at real data.

Three layers, deliberately:

- `tests/test_safety.py`, `tests/test_config.py` — pure logic, no I/O.
- `tests/test_integration.py` — real SQL against a real server (this is where the
  read-only guarantee is actually proven, by watching PostgreSQL reject the write).
- `tests/test_mcp_protocol.py` — the server as a subprocess, driven over MCP, which
  is the only layer that catches wire-format and lifespan wiring mistakes.

### Platform note

On Windows, psycopg's async mode cannot run on the default `ProactorEventLoop`. The
server switches the policy to the selector loop at startup (`compat.py`), so nothing
extra is needed — but if you embed `build_server()` in your own process, call
`ensure_compatible_event_loop_policy()` before your event loop is created. Linux and
macOS, including every Docker deployment, are unaffected.

## Configuration reference

See `env.example` — every variable is listed there with its default.