MCP Spotlight: Postgres MCP Pro — Configurable Read/Write Access, Schema-Aware Index Tuning & the Safety Pattern Every Database MCP Needs

Server: postgres-mcp by Crystal DBA License: MIT · Status: Most-installed database MCP server of 2026 Transport: stdio · DB support: PostgreSQL (primary), MySQL, SQLite, DuckDB (transitively via fork ecosystem) Coverage: Schema introspection · SQL exec · Index tuning · Health checks · Query plans · Migration generation MCP Tracker: glama.ai/mcp/servers?query=postgres-mcp

Postgres is the default database for AI-era applications. Every agent workflow that touches production data — debugging a query, designing a schema, optimizing an index, generating a migration, troubleshooting a slow report — needs to read and write Postgres tables. The naive "give the agent a psql shell" approach is dangerous: a hallucinated DROP TABLE or a misread WHERE clause can wipe production in seconds. The naive "give the agent read-only access" approach is too restrictive: you can't fix what you can't update.

Postgres MCP Pro by Crystal DBA is the database MCP server that resolves this tension. Configurable read/write access at the role level. Schema-aware introspection. Index-tuning recommendations driven by actual query patterns. Migration generation with diffs. Health checks with EXPLAIN plans. The safety pattern every database MCP server needs to copy.

The Architecture: Tools Organized by Workflow

The Postgres MCP Pro tool surface is organized around how developers actually interact with a database:

Category	Tools	Purpose
Schema	list_schemas, list_objects, get_object_details, execute_sql (DDL)	Inspect and modify structure
Data	execute_sql (DML/DQL), list_indexes	Read and write data
Index Tuning	analyze_db_health, analyze_query_indexes, optimize_query	Performance optimization
Migrations	generate_migration, apply_migration, rollback_migration	Schema evolution
Debug	explain_query, get_top_queries	Query analysis

The execute_sql tool is intentionally generic — the agent writes standard SQL and the server returns the results. The power is in the surrounding tools that make the SQL safer, faster, and more discoverable.

Configurable Read/Write Access: The Right Default

The defining feature of Postgres MCP Pro is configurable read/write access at the role level. The server reads the user's database role and enforces what that role can do:

# Configuration
postgres-mcp --db-url postgresql://readonly_user@host/db
# → Read-only access: SELECT queries work, DROP/DELETE/UPDATE blocked at server level

postgres-mcp --db-url postgresql://app_user@host/db
# → Standard app access: SELECT/INSERT/UPDATE work, DDL/DROP blocked

postgres-mcp --db-url postgresql://migrator_user@host/db
# → Migration access: DDL/DROP allowed, scoped to migration tools

postgres-mcp --db-url postgresql://readonly_user@host/db --pool-mode transaction
# → Read-only with transaction pooling (PgBouncer-compatible)

The configuration is per-server, not per-query. You start the MCP server with the appropriate database role for the workflow. The agent inherits that role's permissions. If the agent's prompt is compromised or hallucinated, the database role still enforces the ceiling.

This is the defense-in-depth pattern:

1. Database role — Postgres's own RBAC controls what SQL can be executed
2. Server configuration — the MCP server enforces read-only or read-write mode
3. Tool annotation — Facio's destructive-hint annotations gate specific operations (DROP, TRUNCATE, DELETE without WHERE)
4. Audit trail — Facio captures every SQL statement with parameters and timing

Four layers. The agent never gets a free pass to drop your tables.

Schema-Aware Index Tuning

The killer feature for performance-focused teams: the server analyzes your actual query patterns and recommends indexes.

User: "Our /api/reports endpoint is slow. Investigate and fix."

Agent:
  1. get_top_queries() → List of slowest queries in the last 24h
  2. explain_query(slow_query) → EXPLAIN ANALYZE output
  3. analyze_query_indexes(slow_query) → Recommended indexes
  4. generate_migration("CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at)")
  5. apply_migration() → Runs the migration in a transaction
  6. Re-runs the query → Reports the speedup

The analyze_db_health tool surfaces the higher-level picture:

• Missing indexes on foreign keys
• Tables with high sequential-scan ratio
• Bloated indexes and tables
• Unused indexes consuming write throughput
• Replication lag and connection-pool saturation

This is the kind of analysis a DBA does manually with pg_stat_statements and EXPLAIN ANALYZE. The MCP server exposes it as a tool. The agent does it in minutes.

Migration Generation with Diff

Schema changes are the highest-risk database operation. Postgres MCP Pro treats them with the same care:

User: "Add a 'subscription_tier' column to the users table,
       with default 'free' and NOT NULL."

Agent:
  1. get_object_details(table='users')
  2. generate_migration(
       change="Add subscription_tier TEXT NOT NULL DEFAULT 'free'",
       safety_checks=[
         "Default is constant (safe to apply to non-empty table)",
         "NOT NULL is satisfied by DEFAULT",
         "No FKs reference this column",
         "No NOT VALID constraints on this column"
       ]
     )
  → Returns migration with safety analysis
  3. Shows the migration SQL and the safety report
  4. await approval
  5. apply_migration() → Runs in transaction, rolls back on error
  6. rollback_migration() → If needed

The migration tool performs safety checks before applying — it catches the dangerous patterns that manual SQL misses:

• Adding NOT NULL without DEFAULT to a non-empty table
• Dropping a column that's referenced by other tables or views
• Creating a UNIQUE index concurrently is OK, creating a UNIQUE constraint without CONCURRENTLY blocks writes
• Renaming a column breaks applications that reference the old name

For teams running production Postgres, this is the migration discipline you wish your developers followed.

The EXPLAIN Tool: Query Plan Visibility

The explain_query tool runs EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) and returns the structured plan:

{
  "Plan": {
    "Node Type": "Seq Scan",
    "Relation Name": "orders",
    "Filter": "(customer_id = 12345)",
    "Rows Removed by Filter": 999999,
    "Actual Rows": 1,
    "Actual Total Time": 1247.832
  }
}

The agent reads the plan, identifies the sequential scan, and proposes an index. The user sees the plan before any change is made. The loop is transparent.

Facio Integration

{
  "mcpServers": {
    "postgres-readonly": {
      "command": "npx",
      "args": ["-y", "@crystaldba/postgres-mcp"],
      "env": {
        "POSTGRES_URL": "${credentials.POSTGRES_READONLY_URL}"
      }
    },
    "postgres-migrator": {
      "command": "npx",
      "args": ["-y", "@crystaldba/postgres-mcp"],
      "env": {
        "POSTGRES_URL": "${credentials.POSTGRES_MIGRATOR_URL}"
      }
    }
  }
}

Facio's audit trail captures every SQL statement with the role, the query, the parameters, the EXPLAIN plan, the result count, and the timing. For a regulated environment (PCI-DSS, HIPAA, GDPR), this is the complete data-access record: "Agent ran SELECT * FROM customers WHERE id = 12345 at 14:32 UTC, returned 1 row, 12ms."

For HITL workflows, the multi-server pattern is the recommended setup:

Server	Role	Capabilities	Suggested Gate
postgres-readonly	Read-only DB role	SELECT, EXPLAIN, list_*, get_object_details	None — autonomous
postgres-app	App-level role	SELECT, INSERT, UPDATE on app tables	Soft confirm for UPDATE/DELETE
postgres-migrator	Migration role	DDL, DROP, ALTER, CREATE INDEX	Hard confirm for every migration

The execute_sql tool deserves special treatment — it's the universal SQL escape hatch. Facio's tool annotations should be configured to:

• Block DROP DATABASE, DROP SCHEMA unconditionally
• Block DROP TABLE, TRUNCATE without explicit confirmation
• Block DELETE without WHERE (defensive default)
• Block UPDATE without WHERE (defensive default)
• Soft-confirm DELETE / UPDATE with WHERE (review intent)
• Allow SELECT, INSERT (autonomous for known patterns)

For production environments, the read-only server is the default. The migrator server is only enabled when the user explicitly invokes a migration workflow. The app server is for in-app mutations during agent-driven workflows.

For multi-database setups (one DB per service, per tenant, per region), the pattern is one MCP server per database with the appropriate role. The agent switches context per database, the audit trail is per-database, and the HITL gating is per-database.

Quickstart

# 1. Install Postgres MCP Pro
npm install -g @crystaldba/postgres-mcp

# 2. Configure your MCP client (read-only role)
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@crystaldba/postgres-mcp"],
      "env": {
        "POSTGRES_URL": "postgresql://readonly_user:password@localhost:5432/myapp"
      }
    }
  }
}

# 3. First prompts
# "List all tables in the public schema and tell me their row counts."
# "Why is the /api/reports endpoint slow? Find the slow queries and recommend indexes."
# "Add a 'subscription_tier' column to users with default 'free'. Show me the migration before applying."
# "Find all foreign keys without indexes — generate the migrations to fix them."
# "What's the current database health score? Any bloated tables or unused indexes?"

Use Cases

Performance debugging: "Why is the /api/reports endpoint slow?" Get top queries → EXPLAIN → recommend indexes → apply → re-measure. The agent does in 5 minutes what takes a DBA half a day.

Schema exploration: "What tables do we have, and how are they related?" List schemas → list tables → get FK relationships → produce an ERD-style summary.

Migration generation: "Add a 'subscription_tier' column to users, with default 'free' and NOT NULL." Generate migration → safety check → apply → rollback if needed.

Index health audit: "Find all FK columns without indexes and generate the migrations to fix them." Cross-table query → index recommendations → migration files.

Query optimization: "Optimize this query: SELECT * FROM orders WHERE customer_id = 12345 ORDER BY created_at DESC LIMIT 10." EXPLAIN → recommend index → apply → EXPLAIN ANALYZE before/after.

Database health check: "What's the current health of our production database?" Run analyze_db_health → produce a report → flag critical issues.

Debugging production issue: "We're seeing 'too many connections' errors. Investigate." Check connection pool → list long-running queries → recommend settings.

Schema documentation: "Generate Markdown documentation for every table in the public schema, including column types, constraints, and FK relationships." Cross-table introspection → structured docs.

Data exploration: "How many users signed up in the last 30 days, broken down by country?" SQL query → results → structured summary.

Production data sampling: "Show me 10 representative records from the orders table for the last week — anonymize the customer info." SELECT with sampling + anonymization pattern.

Bottom Line

Postgres MCP Pro is the database MCP server that gets the safety / capability trade-off right. Configurable read/write access at the role level. Schema-aware index tuning. Migration generation with safety checks. EXPLAIN plan visibility. The defense-in-depth pattern every database MCP needs.

For any agent that touches production data, this is the bridge. The agent reads the schema, optimizes the queries, generates the migrations — all under Postgres's existing RBAC, with Facio's destructive-operation gating, with full audit trail.

For the broader MCP ecosystem, the configurable role-based access pattern is the design lesson every database MCP server should copy. Don't ship one tool with implicit permissions. Ship multiple server configurations, each scoped to the appropriate database role for the workflow. Read-only. App-level. Migrator. Each with its own MCP server, its own audit trail, its own HITL gating.

npx -y @crystaldba/postgres-mcp and your agent has Postgres — with the right safety net underneath.

---

MCP Spotlight is a series covering servers that give AI agents real capabilities. Every server is evaluated for safety design, capability depth, and integration fit with Facio's HITL-first agent runtime.