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.

Related MCP server: PostgreSQL MCP Server

Tools

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:

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

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:

docker build -t mcp-postgres:0.1.0 .

Then set it up per project

From the project you want the agent to work in:

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:

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

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:

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

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

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

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.