postgres-mcp

A fast, self-hostable PostgreSQL MCP server. Explore your database (schemas, tables, columns, constraints, relationships, indexes, triggers, functions/procedures, views, enums, stats) and run guarded read or write queries — with a selectable access mode so the same server can be locked to read-only or opened up for edits.

Built with Python + uv, FastMCP and psycopg3. Runs equally well via uvx or Docker. Works with any PostgreSQL: local, Neon, Supabase, Cloud SQL, RDS, DigitalOcean, …

Naming note: this is an independent project. There is a separate, unrelated PyPI package also called postgres-mcp (Crystal DBA's "Postgres MCP Pro"). If you publish, pick a unique distribution name.

---

Quick start with Docker

# 1. Build the image
docker build -t postgres-mcp .

# 2. Add it to your MCP client (.mcp.json) — read-only by default:

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "-e", "DATABASE_URI", "-e", "PG_MCP_ACCESS_MODE", "postgres-mcp"],
      "env": {
        "DATABASE_URI": "postgresql://readonly_user:pass@host:5432/db",
        "PG_MCP_ACCESS_MODE": "readonly"
      }
    }
  }
}

The server speaks MCP over stdio, so the container must be run with -i. DATABASE_URI and PG_MCP_ACCESS_MODE are listed both in args (to forward the names into the container) and in env (their values), so secrets stay out of the image.

Try it instantly with a seeded demo database

docker compose --profile demo up -d demo-db   # Postgres on localhost:55432, pre-seeded
docker build -t postgres-mcp .
# Point DATABASE_URI at: postgresql://readonly_user:readonly_pw@host.docker.internal:55432/appdb

Run without Docker (uv)

uv sync
DATABASE_URI=postgresql://readonly_user:pass@host:5432/db \
PG_MCP_ACCESS_MODE=readonly \
uv run postgres-mcp

Or straight from a Git repo, no clone needed:

{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": ["--from", "git+https://github.com/<you>/postgres-mcp", "postgres-mcp"],
      "env": { "DATABASE_URI": "postgresql://readonly_user:pass@host:5432/db" }
    }
  }
}

---

Access modes

Set with PG_MCP_ACCESS_MODE. Each mode adds tools; higher modes include the lower ones. Choose the least privilege you need.

Mode	Read tools	execute_dml (INSERT/UPDATE/DELETE/MERGE)	execute_ddl (CREATE/ALTER/DROP/…)	DB session
readonly (default)	✅	—	—	forced read only
readwrite	✅	✅	—	normal
admin	✅	✅	✅	normal

The access mode is enforced two ways: write tools are not even registered in lower modes, and in readonly the database session itself rejects writes. For real safety, also connect with a DB role scoped to what you need.

---

Tools

Read (all modes)

Tool	Purpose
server_info	Current access mode + safety config
list_schemas	User schemas
list_tables	Tables/views/matviews with size & row estimate
describe_table	Columns, types, defaults, identity/generated, PK
list_constraints	PK / unique / FK / check / exclusion
get_relations	Incoming & outgoing foreign keys
list_indexes	Index definitions
list_triggers	Trigger definitions (schema or one table)
list_functions	Functions & procedures (signatures)
get_function_definition	Full source of a function/procedure
list_views	View / materialized-view definitions
list_enums	Enum types and labels
table_stats	Sizes, live/dead rows, vacuum/scan stats
run_select	Guarded read-only query runner

Write (mode-gated)

Tool	Mode	Purpose
execute_dml	readwrite, admin	INSERT / UPDATE / DELETE / MERGE
execute_ddl	admin	CREATE / ALTER / DROP / TRUNCATE / COMMENT / GRANT / REVOKE / REINDEX

How writes stay safe — the dry-run + confirm workflow

Every write tool defaults to confirm=false, which performs a dry run:

1. Understand the data flow first. The model is instructed to inspect describe_table, get_relations (cascading FKs) and list_triggers before changing anything, so cascade/side-effects are known up front.
2. Dry run. With confirm=false, the statement runs inside a transaction that is rolled back. Because Postgres has transactional DDL, this both validates the statement and returns the exact affected_rows — without persisting anything.
3. Review. If affected_rows is larger than expected, fix the WHERE clause and dry-run again. UPDATE/DELETE without a WHERE is refused unless allow_full_table_write=true.
4. Commit. Re-run with confirm=true to apply the change.

On top of this, your MCP client (Claude Code, etc.) prompts the human to approve each tool call — so a real person is always in the loop before a commit.

Statements that can't run in a transaction (CREATE INDEX CONCURRENTLY, CREATE/DROP DATABASE, VACUUM) can't be dry-run; execute_ddl tells you to re-run with confirm=true, non_transactional=true (no rollback safety).

---

Configuration

Env var	Default	Meaning
DATABASE_URI	—	postgresql://user:pass@host:5432/db (also DATABASE_URL)
PG_MCP_ACCESS_MODE	readonly	readonly / readwrite / admin
PG_MCP_STATEMENT_TIMEOUT_MS	15000	Per-statement timeout
PG_MCP_MAX_ROWS	1000	Hard cap on returned rows
PG_MCP_POOL_MAX	4	Max pooled connections

TLS works out of the box (e.g. Neon: append ?sslmode=require to the URI).

Recommended DB roles

Read-only:

CREATE ROLE readonly_user LOGIN PASSWORD 'change_me';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
ALTER ROLE readonly_user SET statement_timeout = '15s';

Read-write (add only what you need):

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

Add your own tools

Drop a function in src/postgres_mcp/server.py:

@mcp.tool()
def biggest_tables(schema: str = "public", top: int = 10) -> list[dict]:
    """Largest tables in a schema by total size."""
    return db.query(
        """SELECT c.relname AS name,
                  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
           FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE n.nspname = %s AND c.relkind IN ('r','p','m')
           ORDER BY pg_total_relation_size(c.oid) DESC LIMIT %s""",
        (schema, top),
    )

Roadmap

• ☐ Cloud SQL connectivity guide (Cloud SQL Auth Proxy + gcloud/service account)
• ☐ Provider notes: DigitalOcean Managed Databases, AWS RDS/Aurora
• ☐ Optional published images (GHCR) and PyPI release

License

MIT