readonly-postgres-mcp

A read-only MCP 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.

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

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 after its read-only mode was found to accept DROP SCHEMA. String matching cannot see through this, for example:

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.

Related MCP server: pg-mcp

The security model in one table

The first two rows are the security. The statement guard is ergonomics — see SECURITY.md and 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

Setup

1. Provision the role

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

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:

{
  "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 for a full walkthrough, or 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.

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

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.