Postgres MCP server

MCP (Model Context Protocol) gives an agent a uniform way to call tools and read data. A Postgres MCP server sits between the agent and your database: it exposes schema metadata, read-only queries, and EXPLAIN output as tools, so the agent inspects the real catalog instead of guessing column names. This changes the failure mode of AI-generated SQL — most "hallucinated column" errors disappear once the model can check.

The server is only a transport. The actual safety boundary is the database role it connects with, covered below and in Safe SQL guardrails.

Choosing a server

Tool capabilities below were verified in 2026-08 against each project's repository; this ecosystem moves fast, so re-check before adopting.

Whichever server you pick, treat vendor feature lists as provisional: enable pg_stat_statements and hypopg only if you actually use the tuning tools, and pin the server version in your config so upgrades are deliberate.

Create the read-only role first

Before configuring any client, create a dedicated role that can only read:

CREATE ROLE readonly LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

ALTER ROLE readonly SET default_transaction_read_only = on;
ALTER ROLE readonly SET statement_timeout = '5s';

The ALTER DEFAULT PRIVILEGES line matters: without it, tables created later are invisible to the role and the agent's schema view silently drifts out of date. Add timeouts at the role level so a runaway query from an agent cannot hold resources — see Safe SQL guardrails for the full set (lock timeout, idle-in-transaction timeout, row bounds).

Claude Code

Project-scoped .mcp.json (commit it so the whole team gets the same server), or run claude mcp add for a user-scoped entry:

{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": [
        "postgres-mcp",
        "--access-mode=restricted",
        "postgres://readonly:secret@localhost:5432/myapp"
      ]
    }
  }
}

--access-mode=restricted keeps the server on read-only SQL even if the agent asks for writes (verified 2026-08 in the project README). Prefer injecting the connection string from an environment variable or secret manager over committing passwords.

Cursor

.cursor/mcp.json:

{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": ["postgres-mcp", "--access-mode=restricted"],
      "env": {
        "DATABASE_URI": "postgres://readonly:secret@localhost:5432/myapp"
      }
    }
  }
}

One database branch per PR

Neon and Supabase both support near-instant copy-on-write branches. Combined with MCP, each PR gets its own database the agent can migrate, query, and destroy:

1. Fork a branch from main (milliseconds, no data copy).
2. Run migrations and tests against real-shaped data; let the agent read EXPLAIN on realistic volumes.
3. Review the change in the PR; the branch is deleted on merge.

neon branches create --name pr-123 --parent main
export DATABASE_URI=$(neon connection-string --branch pr-123)
# start the MCP server against DATABASE_URI

This is where MCP pays off most: the agent validates migrations against a real catalog and real data distribution, without touching the production writer.

What to expose through the contract

A database MCP server answers "what does the schema look like" and "what does this query do" — it should not become a general-purpose data exfiltration channel. Keep the task-facing context small and explicit with a context contract: the agent gets the tables and columns relevant to the task, and the MCP server handles verification, not exploration of unrelated schemas.

Prompts that use it well

Using the postgres MCP:

1. `list_schemas` and `list_objects` to enumerate user tables.
2. For each business table, `get_object_details` to view columns, FKs, indexes.
3. Approximate row counts via `SELECT count(*)` (or `pg_class.reltuples` for large tables).
4. Flag:
 - Tables without a primary key
 - timestamp columns without timezone
 - FK columns without an index
 - Top 10 queries in pg_stat_statements
5. Output a one-page improvement list ranked by "risk × impact".

Read-only. No DDL/DML.

Using the postgres MCP:

I want to add a `tags text[]` column to `orders` and a GIN index. Please:

1. `get_object_details orders` to confirm structure and row count.
2. Estimate cost on production (use `reltuples` * per-row cost; cite official docs).
3. Produce a **zero-downtime migration** plan (CREATE INDEX CONCURRENTLY, batched backfill).
4. Output migration SQL (with rollback), split into statements with comments.
5. Do not execute ALTER — only output SQL for me to review.

Next

• Lock down what the agent may run → Safe SQL guardrails
• Shrink what the agent needs to know → Context contract
• Generate that context from the catalog → Schema retrieval