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.

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 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

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

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.