·

What Is PostgreSQL MCP

Learn what PostgreSQL MCP is and how it lets your AI agent query, inspect, and manage databases.

PostgreSQL MCP servers give an AI coding agent a live, structured connection to a real database instead of a static schema dump pasted into a prompt. That distinction matters more than it sounds. A pasted pg_dump --schema-only snapshot goes stale the moment someone runs a migration; an MCP connection lets the agent query information_schema, run EXPLAIN, and check row counts at the exact moment it's reasoning about your code. This topic covers the two server implementations you'll actually run into in the wild — crystaldba/postgres-mcp (the feature-rich one, with index-tuning and health checks) and the reference @modelcontextprotocol/server-postgres (minimal, read-only by default) — plus the connection and guardrail setup you need before pointing either one at anything you care about.

Core Postgres MCP Tools: Schema Introspection, Query Execution, and Explain Plans

Every Postgres MCP server, regardless of vendor, converges on the same three tool categories because they map to the same three questions a developer asks when working with an unfamiliar or evolving database: what does the schema look like, what does this data actually contain, and why is this query slow.

Schema introspection. crystaldba/postgres-mcp exposes list_schemas, list_objects (tables, views, sequences, extensions per schema), and get_object_details (columns, types, constraints, indexes for one object). The reference server instead publishes schema as MCP resources — one resource per table, fetched lazily — which works but gives the agent less control over how much context it pulls in for a 200-table database.

Tool: list_objects
Args: { "schema_name": "public", "object_type": "table" }

Tool: get_object_details
Args: { "schema_name": "public", "object_type": "table", "object_name": "orders" }

Query execution. execute_sql runs arbitrary SQL and returns rows as JSON. This is the tool you gate hardest — see the Guardrails section below — because it's the one tool capable of DROP TABLE if you let it.

Tool: execute_sql
Args: { "sql": "SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;" }

Explain plans. explain_query runs EXPLAIN (FORMAT JSON) — or, with a flag, EXPLAIN ANALYZE — and returns the plan tree. crystaldba/postgres-mcp goes further with analyze_query_indexes and analyze_workload_indexes, which use the hypothetical-index extension HypoPG to simulate what an index would do to a plan without actually building it. That's the tool that turns "this query is slow" into "add this index" without a 40-minute CREATE INDEX CONCURRENTLY on a live table just to test a hypothesis.

-- what the agent sends underneath analyze_query_indexes
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';

Tips
- list_objects before get_object_details — pulling full column/constraint detail for every table in one shot burns context fast on schemas with 100+ tables.
- If the server only exposes resources (not tools) for schema, tell the agent explicitly which resource URIs to read; auto-discovery across hundreds of resources is slow and sometimes gets truncated by the client's resource-list limit.
- analyze_query_indexes requires the hypopg extension installed on the target Postgres (CREATE EXTENSION hypopg;) — it silently falls back to a plain EXPLAIN without it, so check for the extension first if suggestions seem generic.

Postgres MCP Connection Setup: Connection Strings, Roles, and Read-Only Users

Both servers take a standard libpq connection URI, either as a CLI arg or an environment variable:

postgresql://mcp_agent:CHANGE_ME@localhost:5432/app_production?sslmode=require

For crystaldba/postgres-mcp, run it via pipx, uvx, or Docker. The uvx path is the one I use day to day because it needs no persistent install:

uvx postgres-mcp --access-mode=restricted "postgresql://mcp_agent:CHANGE_ME@localhost:5432/app_production"

Docker, if your agent client only speaks stdio-over-container:

docker run -i --rm \
  -e DATABASE_URI="postgresql://mcp_agent:CHANGE_ME@host.docker.internal:5432/app_production" \
  crystaldba/postgres-mcp --access-mode=restricted

The reference server (@modelcontextprotocol/server-postgres) runs via npx:

npx -y @modelcontextprotocol/server-postgres "postgresql://mcp_agent:CHANGE_ME@localhost:5432/app_production"

Never hand either server your app's superuser or owner credentials. Create a dedicated role scoped to what the agent actually needs:

-- 1. Create a login role with no special privileges
CREATE ROLE mcp_agent LOGIN PASSWORD 'CHANGE_ME';

-- 2. Grant connect + read on the schema(s) the agent should see
GRANT CONNECT ON DATABASE app_production TO mcp_agent;
GRANT USAGE ON SCHEMA public TO mcp_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_agent;

-- 3. Make future tables readable too, so migrations don't silently break the agent
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_agent;

-- 4. Belt-and-braces: cap what a runaway query can do
ALTER ROLE mcp_agent SET statement_timeout = '15s';
ALTER ROLE mcp_agent SET idle_in_transaction_session_timeout = '30s';

If you're on managed Postgres (RDS, Cloud SQL, Supabase, Neon), the same GRANT/ALTER DEFAULT PRIVILEGES pattern works unchanged — you just run it through the console's SQL editor or psql against the provided host. Supabase specifically already ships a authenticated/anon role split; don't reuse those for the agent, they carry RLS assumptions that don't apply to a raw MCP connection.

Tips
- Put the connection string in an env var (POSTGRES_MCP_URI) referenced from your MCP client config, never hard-coded in a checked-in mcp.json — connection strings leak through git history forever.
- Test the role in isolation first: psql "postgresql://mcp_agent:...@host/db" -c "DROP TABLE orders;" should fail with a permissions error before you ever wire it into an agent.
- sslmode=require at minimum for anything not on localhost; sslmode=verify-full plus a pinned CA cert for production if your infra supports it.
- Rotate mcp_agent's password on the same cadence as any other service credential — it's a real login role, not a scoped API key, and doesn't expire on its own.

What AI Can Safely Automate with Database Access — and What It Must Never Touch

The genuinely useful, low-risk automation clusters around three things: understanding, drafting, and diagnosing.

Understanding. Point the agent at an unfamiliar 80-table schema and ask it to explain how orders, order_items, inventory_reservations, and fulfillment_events relate. It'll trace foreign keys, notice the deleted_at soft-delete columns, and flag that orders.status is a free-text column instead of an enum — the kind of tribal knowledge that normally costs a new hire two weeks of Slack questions.

Drafting. Writing a first-pass migration, a reporting query, or an ORM model from a live schema is a great fit — the agent's draft is always checked by a human before it touches anything, so a wrong guess costs a review comment, not an incident.

Diagnosing. Feeding EXPLAIN ANALYZE output back to the agent and asking "why is this seq-scanning a 40M row table" is close to free value — it's exactly the kind of pattern-matching against thousands of known Postgres pathologies (missing index, bad selectivity estimate, stale statistics, wrong join order) that LLMs are strong at.

What it must never do unsupervised:

  • Run anything against production without a human reading the SQL first. Not because the model is unreliable in general, but because a single UPDATE without a WHERE clause is unrecoverable in a way that a bad code suggestion is not.
  • Apply schema migrations directly. Generate the migration file, yes. Run alembic upgrade head or python manage.py migrate against prod itself, no — that belongs in a reviewed PR and a deploy pipeline, not an agent loop.
  • Decide what counts as PII and act on that judgment alone. An agent asked to "clean up test accounts" might reasonably interpret that as DELETE FROM users WHERE email LIKE '%test%' — which will also delete sarah.testerman@realcompany.com. Scope the prompt, review the WHERE clause, every time.
  • Be trusted with connection strings that carry write access to a database it also has open network/file access from. If the same agent session can read arbitrary files and also has a writable DB connection, a poisoned prompt in a fetched web page or file can chain into a real write — treat DB write access and broad tool access as mutually exclusive in one session where you can help it.
Bad prompt to hand an agent with write access:
"Clean up the test data in the users table."

Better:
"Show me a SELECT that would match the rows you think are test accounts.
I'll review it before we talk about deleting anything."

Tips
- Default every new Postgres MCP connection to a read-only role. Add write access only for a specific task, in a specific session, and revoke it after.
- Ask the agent to show you the SELECT equivalent of any DELETE/UPDATE it proposes before you let it run the mutating version.
- Keep a human-reviewed migration PR as the only path to schema change in shared environments — the agent drafts, a person merges.

Guardrails: Statement Timeouts, Row Limits, and Blocking Destructive SQL

Three layers of defense, from cheapest to most robust:

1. Role-level statement timeout (shown above) — caps runaway queries regardless of what the agent sends:

ALTER ROLE mcp_agent SET statement_timeout = '15s';

2. Server access-mode flags. crystaldba/postgres-mcp ships --access-mode=restricted, which blocks the tool from executing INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, and other DDL/DML at the MCP layer — it rejects the request before it ever reaches Postgres. --access-mode=unrestricted lifts that for the rare case you deliberately want the agent applying migrations under close supervision. Default to restricted; flip to unrestricted only for a scoped session against a non-production database.

uvx postgres-mcp --access-mode=restricted "$POSTGRES_MCP_URI"

The reference @modelcontextprotocol/server-postgres server is read-only by design — it wraps every query in a transaction and issues ROLLBACK regardless of outcome, so even if the SQL is a DELETE, it never commits. Good default; still not a substitute for a role-level grant, because a client bug or a future version change could remove that behavior.

3. Row limits and query shaping at the prompt layer. Neither server truncates result sets by default, and a SELECT * on a 10M-row table will happily try to return all of it into the agent's context, blow past the model's token budget, and stall the session. Standing instruction in your MCP client's system prompt or project rules file:

When running SELECT queries via Postgres MCP:
- Always add LIMIT 100 unless the user asks for an aggregate or count.
- Prefer COUNT(*) or GROUP BY summaries over raw row dumps for exploration.
- Never run UPDATE, DELETE, DROP, TRUNCATE, or ALTER without explicit
  human confirmation of the exact SQL text.

Blocking destructive SQL entirely at the database level, belt-and-braces on top of the role grants: an event trigger that vetoes DDL from a specific role, or simply not granting INSERT/UPDATE/DELETE/DDL privileges to mcp_agent in the first place — which is the same GRANT SELECT-only pattern from the previous section and is honestly the guardrail that matters most, because it holds even if every other layer is misconfigured.

-- confirm the agent role genuinely can't write, independent of MCP-layer flags
SELECT has_table_privilege('mcp_agent', 'public.orders', 'UPDATE');  -- expect: false

Tips
- Treat --access-mode=restricted as a UX nicety, not your security boundary — the real boundary is the database role's grants. MCP flags can change between versions; GRANTs don't move unless you move them.
- Set statement_timeout short (10-15s) for exploration sessions; if the agent legitimately needs a long-running analytical query, bump it for that one session, not globally.
- Log every execute_sql call your MCP server makes — most implementations support a --log-file or forward to stderr, which you can pipe to your existing query-audit pipeline. Reviewing that log after a session catches surprises before they compound.

Tips

Tips
- Start every new Postgres MCP integration with a throwaway or staging database, not production — get comfortable with what the agent actually sends before it has a production connection string.
- crystaldba/postgres-mcp for anything involving index tuning or health checks; the plain reference server for a minimal read-only footprint you can audit in one file.
- The single highest-leverage guardrail is the one you set once and forget: a dedicated mcp_agent role with SELECT-only grants and a short statement_timeout. Everything else in this topic is defense in depth on top of that.

Next PostgreSQL MCP With Claude Code CLI and VS Code