# readonly-postgres-mcp

A read-only [MCP](https://modelcontextprotocol.io) server for PostgreSQL that serves **many
databases from one process**, runs **locally over stdio or remotely over HTTP**, and enforces
read-only access with **PostgreSQL grants instead of SQL string matching**.

```bash
docker run -p 8000:8000 \
  -e PGMCP_DATABASES='[{"name":"app","url":"postgresql://mcp_ro:pw@db:5432/app"}]' \
  -e PGMCP_TOKENS='[{"name":"laptop","sha256":"<sha256 of your token>"}]' \
  ghcr.io/geolep/readonly-postgres-mcp:latest
```

```bash
claude mcp add --transport http pg --scope user \
  https://your-host/mcp --header "Authorization: Bearer $TOKEN"
```

## Why another Postgres MCP server

Most of them enforce "read-only" by pattern-matching the SQL string. That approach has already
failed in production: the official reference server was
[archived](https://chatforest.com/reviews/postgres-mcp-server/) after its read-only mode was found
to accept `DROP SCHEMA`. String matching cannot see through this, for example:

```sql
WITH deleted AS (DELETE FROM widgets RETURNING *) SELECT * FROM deleted
```

That statement *is* a `SELECT`. Every leading-keyword check accepts it, and it empties your table.

This server takes the guarantee out of the application entirely. It connects as a role that holds
`SELECT` and nothing else, with `default_transaction_read_only` set. A write does not fail because
the server rejected it — it fails because the database never granted it.

`readonly-postgres-mcp role-sql` generates that role for you, with statements to verify the grants
landed and to roll them back.

## The security model in one table

| Layer | What it stops | Where it lives |
| --- | --- | --- |
| Role holds `SELECT` only | every write, including ones the parser cannot see | PostgreSQL |
| `default_transaction_read_only = on` | data-modifying CTEs, `SELECT INTO`, `CREATE TEMP TABLE` | PostgreSQL |
| Read-only transaction per connection | a session that somehow reset the role default | this server |
| `statement_timeout`, `CONNECTION LIMIT` | one runaway query taking the database with it | PostgreSQL |
| Row cap with a `truncated` flag | a `SELECT *` flooding the model's context | this server |
| Statement guard | a clear error message instead of a driver stack trace | this server |
| Audit log with the token name | not knowing who read what | this server |

The first two rows are the security. The statement guard is **ergonomics** — see
[SECURITY.md](SECURITY.md) and [docs/security-model.md](docs/security-model.md).

This is not a claim; it is a test suite. `tests/test_readonly_enforcement.py` provisions a real
PostgreSQL with a role built by `role-sql`, then sends each attack **around** the guard, straight
to the driver:

```
INSERT / UPDATE / DELETE / TRUNCATE      → 25006 read_only_sql_transaction
WITH x AS (DELETE ... RETURNING *) ...   → 25006 read_only_sql_transaction
SELECT ... INTO / CREATE TEMP TABLE      → 25006 read_only_sql_transaction
DROP SCHEMA public CASCADE               → 25006 read_only_sql_transaction
COPY ... TO PROGRAM / pg_read_file()     → 42501 insufficient_privilege
```

## Tools

| Tool | Description |
| --- | --- |
| `list_databases` | Databases this token can reach, with connection status |
| `query` | One read-only statement, with a row cap and a `truncated` flag |
| `list_tables` | Tables and views, optionally for one schema |
| `describe_table` | Columns, constraints and indexes |
| `get_schema_ddl` | Schema-only DDL via `pg_dump` |
| `generate_readonly_role_sql` | The provisioning SQL for a database, as a tool |

## Setup

### 1. Provision the role

```bash
readonly-postgres-mcp role-sql app_production
```

Prints three blocks: **grant** (run it), **verify** (prove the grants landed) and **revoke**
(roll it back). To keep tables out of reach, add `--exclude-tables users api_keys` — the generator
then omits `ALTER DEFAULT PRIVILEGES`, so a table created later does not silently become readable.

### 2. Mint a token per consumer

```bash
readonly-postgres-mcp gen-token laptop --databases app --row-cap 500 --expires 2027-01-01
```

The token is shown once; only its SHA-256 goes in the config. One token per consumer means you can
revoke one without disturbing the others.

### 3. Run it

Locally over stdio, with no token needed because the operating system is the boundary:

```json
{
  "mcpServers": {
    "pg": {
      "command": "readonly-postgres-mcp",
      "args": ["serve"],
      "env": { "PGMCP_DATABASES": "[{\"name\":\"app\",\"url\":\"postgresql://mcp_ro:pw@localhost/app\"}]" }
    }
  }
}
```

Remotely over HTTP — see [docs/deploy-coolify.md](docs/deploy-coolify.md) for a full walkthrough,
or [docker-compose.example.yml](docker-compose.example.yml) for the short version. Put TLS in
front of it: the bearer token is a shared secret.

Full reference: [docs/configuration.md](docs/configuration.md).

## What it does not protect against

**Prompt injection.** Any agent holding a token can read everything its role can read. If the
agent also processes untrusted input — scraped pages, user messages, third-party data — assume
that input can steer the queries. Scope tokens to the fewest databases, and leave sensitive tables
out of the grant.

**Data reaching the model.** Every row returned goes into an LLM context. Treat each granted table
as disclosed to whatever the agent is connected to.

## Development

```bash
python -m venv .venv && .venv/bin/pip install -e ".[dev]"
.venv/bin/pytest              # integration tests need Docker
.venv/bin/pytest -m "not integration"
```

## License

Apache-2.0 — see [LICENSE](LICENSE).