postgres-mcp

A Model Context Protocol (MCP) server for PostgreSQL integration. Give your AI assistant safe, controlled access to your databases.

Status: v0.7.0

Author: Claude + MOD

License: MIT

Org: ArktechNWA

---

Why?

Your AI assistant can write SQL but can't see your schema, can't run queries to verify, can't explore your data model. It's guessing.

"Just give it database credentials" — bad idea. One missing index + large table = hung query = frozen assistant. One hallucinated DELETE = disaster. No guardrails, no recovery.

postgres-mcp is an intelligent interface, not a connection wrapper:

Problem	postgres-mcp Solution
Queries can hang forever	NEVERHANG — adaptive timeouts, circuit breaker
No visibility into database health	Health monitoring with degraded state detection
Failures cascade	Circuit breaker opens, queries fail fast, auto-recovery
All-or-nothing access	Granular: read-only default, table blacklist, permission tiers
AI can't verify its SQL	Schema introspection + natural language queries

Prometheus tells you the database is on fire. NEVERHANG lets you walk through the fire without getting burned.

---

Philosophy

1. Safety first — Read-only by default, write explicitly enabled
2. Query safety — Statement timeouts, row limits, dangerous pattern blocking
3. Schema awareness — Introspection without data exposure
4. NEVERHANG — Circuit breaker, adaptive timeouts, health monitoring, graceful degradation
5. Natural language — Ask questions in plain English, get SQL + results

---

Features

Natural Language (v0.6)

• pg_ask — Ask questions in plain English, get SQL + results
• Powered by Claude Sonnet for accurate SQL generation
• Automatic schema context gathering
• Fallback mode works without API key (returns schema for caller to generate SQL)

Perception (Read)

• Execute SELECT queries
• Schema introspection (tables, columns, indexes, constraints)
• pg_schema — Unified table view (columns + indexes + constraints in one call)
• pg_sample — Sample rows with blacklist filtering
• Explain query plans
• Database statistics
• Active connections and locks

Action (Write)

• INSERT, UPDATE, DELETE (permission-gated)
• DDL operations (permission-gated)
• Transaction support

Reliability (v0.5 NEVERHANG + v0.7 A.L.A.N.)

• Circuit breaker with automatic recovery
• Adaptive timeouts based on query complexity
• Health monitoring with degraded state handling
• Connection pool management
• A.L.A.N. persistence: Circuit state and query history survive restarts

---

Permission Model

CRITICAL: Database access requires careful permission management.

Permission Levels

Level	Description	Default
read	SELECT queries, schema introspection	ON
write	INSERT, UPDATE, DELETE	OFF
ddl	CREATE, ALTER, DROP	OFF
admin	VACUUM, REINDEX, connection management	OFF

Table/Schema Filtering

{
  "permissions": {
    "read": true,
    "write": false,
    "ddl": false,
    "admin": false,

    "whitelist_schemas": ["public", "app"],
    "blacklist_schemas": ["pg_catalog", "information_schema"],

    "whitelist_tables": [],
    "blacklist_tables": [
      "users.password_hash",
      "secrets.*",
      "*.credentials"
    ]
  }
}

Rules:

• Blacklist always wins
• Column-level filtering supported
• Pattern matching: schema.table.column

Query Safety

{
  "query_safety": {
    "statement_timeout": "30s",
    "max_rows": 1000,
    "block_patterns": [
      "DROP DATABASE",
      "TRUNCATE",
      "DELETE FROM .* WHERE 1=1",
      "UPDATE .* SET .* WHERE 1=1"
    ],
    "require_where_clause": true
  }
}

Bypass Mode

postgres-mcp --bypass-permissions

Full database access. DANGER ZONE.

---

Authentication

{
  "connection": {
    "host": "localhost",
    "port": 5432,
    "database": "myapp",
    "user_env": "PGUSER",
    "password_env": "PGPASSWORD",
    "ssl": true
  }
}

Or connection string:

{
  "connection": {
    "url_env": "DATABASE_URL"
  }
}

Recommendation: Use a read-only database user for maximum safety.

---

Tools

Queries

pg_query

Execute a SELECT query.

pg_query({
  query: string,
  params?: any[],           // parameterized queries
  limit?: number,           // override max_rows
  timeout?: string          // override statement_timeout
})

Returns:

{
  "query": "SELECT name, email FROM users WHERE active = $1",
  "params": [true],
  "rows": [
    {"name": "Alice", "email": "alice@example.com"},
    {"name": "Bob", "email": "bob@example.com"}
  ],
  "row_count": 2,
  "execution_time": "12ms",
  "summary": "2 active users found"
}

pg_execute

Execute INSERT/UPDATE/DELETE. Requires write permission.

pg_execute({
  query: string,
  params?: any[],
  returning?: boolean       // add RETURNING *
})

Returns:

{
  "query": "UPDATE users SET active = $1 WHERE id = $2",
  "params": [false, 123],
  "affected_rows": 1,
  "execution_time": "5ms"
}

Natural Language (v0.6)

pg_ask

Ask a question in natural language — translates to SQL and executes.

pg_ask({
  question: string,           // "How many users signed up this month?"
  tables?: string[],          // limit to specific tables
  schema?: string,            // default: "public"
  timeout_ms?: number         // override timeout
})

Returns:

{
  "question": "How many users signed up this month?",
  "generated_sql": "SELECT COUNT(*) FROM users WHERE created >= DATE_TRUNC('month', CURRENT_DATE)",
  "rows": [{"count": "142"}],
  "row_count": 1,
  "execution_time": "3.2s"
}

Fallback mode: If ANTHROPIC_API_KEY is not set, returns schema context for the caller to generate SQL:

{
  "mode": "fallback",
  "message": "pg_ask fallback mode activated. To enable direct NL→SQL via Sonnet, add ANTHROPIC_API_KEY to ~/.claude.json under mcpServers.postgres-mcp.env",
  "question": "How many users?",
  "schema_context": "CREATE TABLE users (id, email, created...)",
  "instructions": { "step_1": "Analyze schema", "step_2": "Generate SQL", "step_3": "Use pg_query" }
}

Schema Introspection

pg_tables

List tables with metadata.

pg_tables({
  schema?: string,          // default: "public"
  pattern?: string          // table name pattern
})

Returns:

{
  "tables": [
    {
      "schema": "public",
      "name": "users",
      "type": "table",
      "row_estimate": 15420,
      "size": "2.3 MB",
      "description": "User accounts"
    }
  ]
}

pg_columns

Get column information for a table.

pg_columns({
  table: string,
  schema?: string
})

Returns:

{
  "table": "users",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "nullable": false,
      "default": "nextval('users_id_seq')",
      "primary_key": true
    },
    {
      "name": "email",
      "type": "varchar(255)",
      "nullable": false,
      "unique": true
    }
  ]
}

pg_indexes

Get index information.

pg_indexes({
  table?: string,
  schema?: string
})

pg_constraints

Get constraint information (PK, FK, unique, check).

pg_constraints({
  table?: string,
  schema?: string,
  type?: "PRIMARY KEY" | "FOREIGN KEY" | "UNIQUE" | "CHECK"
})

pg_schema

Get complete schema for a table (columns, indexes, constraints) in one call.

pg_schema({
  table: string,
  schema?: string            // default: "public"
})

Returns:

{
  "table": "users",
  "columns": [...],
  "indexes": [...],
  "constraints": [...]
}

pg_sample

Get sample rows from a table (respects column blacklist).

pg_sample({
  table: string,
  schema?: string,            // default: "public"
  limit?: number,             // default: 5, max: 20
  order_by?: string           // default: primary key
})

Returns:

{
  "table": "users",
  "sample_rows": [
    {"id": 1, "email": "alice@example.com", "created": "2025-01-01"},
    {"id": 2, "email": "bob@example.com", "created": "2025-01-02"}
  ],
  "columns_shown": 3,
  "columns_hidden": 1,
  "note": "password column hidden (blacklisted)"
}

Query Analysis

pg_explain

Get query execution plan.

pg_explain({
  query: string,
  params?: any[],
  analyze?: boolean,        // actually run (careful!)
  format?: "text" | "json"
})

Returns:

{
  "query": "SELECT * FROM users WHERE email = $1",
  "plan": {
    "node_type": "Index Scan",
    "index_name": "users_email_idx",
    "estimated_rows": 1,
    "estimated_cost": 0.42
  },
  "summary": "Uses index scan on users_email_idx, estimated 1 row"
}

Statistics

pg_stats

Get database/table statistics.

pg_stats({
  table?: string,           // specific table (omit for database)
  include_index_usage?: boolean
})

pg_connections

Get active connections.

pg_connections({
  include_queries?: boolean
})

pg_locks

Get current locks.

pg_locks({
  blocked_only?: boolean
})

Analysis

pg_analyze_query

AI-powered query analysis.

pg_analyze_query({
  query: string,
  use_ai?: boolean
})

Returns:

{
  "query": "SELECT * FROM orders WHERE user_id = 123",
  "plan_summary": "Sequential scan on orders (15M rows)",
  "synthesis": {
    "analysis": "This query performs a full table scan. The user_id column is not indexed.",
    "suggested_index": "CREATE INDEX orders_user_id_idx ON orders(user_id);",
    "estimated_improvement": "~10,000x faster",
    "confidence": "high"
  }
}

pg_suggest_schema

Get schema improvement suggestions.

pg_suggest_schema({
  table: string,
  use_ai?: boolean
})

---

NEVERHANG v2.0 Architecture

Database queries can hang indefinitely. A missing index + large table = disaster. NEVERHANG is a multi-layered reliability system that ensures postgres-mcp never blocks your AI assistant.

Circuit Breaker

• Automatic trip: 3 failures in 60s → circuit opens
• Cooldown: 5 minute recovery period
• Health states: healthy → degraded → unhealthy
• Graceful degradation: Returns cached/safe responses when circuit is open

Adaptive Timeouts

• Query complexity analysis: Simple queries get shorter timeouts
• Pattern recognition: Known-slow patterns (JOINs, subqueries) get longer timeouts
• Learning: Adjusts based on historical query performance
• Override: Per-query timeout always available

Health Monitor

• Continuous ping: Background health checks
• State tracking: Monitors connection pool health
• Recovery detection: Automatic circuit close when health returns
• Metrics: Success rate, average latency, failure patterns

Connection Management

• Pool limits: Configurable min/max connections
• Idle timeout: Releases unused connections (default: 60s)
• Connection timeout: Fast fail on connection issues (default: 10s)

Row Limits

• Default max: 1000 rows
• Auto-LIMIT injection: Adds LIMIT to unbounded SELECTs
• Prevents: Accidental SELECT * disasters

A.L.A.N. Persistence (v0.7)

As Long As Necessary — persistent memory for NEVERHANG:

• Circuit state survives restarts: No cold-start amnesia
• Query history tracking: 7 days of execution metrics
• P95 latency by complexity: Adaptive timeout learning
• Health check logs: 24 hours for trend analysis
• Location: ~/.cache/postgres-mcp/neverhang.db (XDG compliant)
• Auto-cleanup: Prunes old data on startup

{
  "neverhang": {
    "statement_timeout": "30s",
    "connect_timeout": "10s",
    "max_rows": 1000,
    "circuit_breaker": {
      "failures": 3,
      "window": 60000,
      "cooldown": 300000
    }
  }
}

---

AI Integration (v0.6)

pg_ask uses Claude Sonnet to translate natural language to SQL.

Configuration: Add ANTHROPIC_API_KEY to your MCP server environment:

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"],
      "env": {
        "PGHOST": "localhost",
        "PGDATABASE": "myapp",
        "ANTHROPIC_API_KEY": "sk-ant-..."
      }
    }
  }
}

Fallback mode: If no API key is set, pg_ask returns schema context with instructions for the caller to generate SQL. This allows the tool to provide value even without a separate API key.

---

Configuration

~/.config/postgres-mcp/config.json:

{
  "connection": {
    "host": "localhost",
    "port": 5432,
    "database": "myapp",
    "user_env": "PGUSER",
    "password_env": "PGPASSWORD"
  },
  "permissions": {
    "read": true,
    "write": false,
    "ddl": false,
    "admin": false,
    "blacklist_tables": ["*.password*", "*.secret*"]
  },
  "query_safety": {
    "statement_timeout": "30s",
    "max_rows": 1000,
    "require_where_clause": true
  },
  "fallback": {
    "enabled": false
  }
}

Claude Code Integration

{
  "mcpServers": {
    "postgres": {
      "command": "postgres-mcp",
      "env": {
        "PGUSER": "readonly_user",
        "PGPASSWORD": "secret"
      }
    }
  }
}

---

Installation

npm install -g @arktechnwa/postgres-mcp

---

Requirements

• Node.js 18+
• PostgreSQL 12+
• Optional: Anthropic API key for fallback AI

---

Security Considerations

1. Use read-only user — Create a DB user with SELECT-only grants
2. Blacklist sensitive tables — Passwords, secrets, PII
3. Statement timeout — Prevent runaway queries
4. Row limits — Prevent accidental data dumps
5. No credential exposure — Connection strings never logged

---

Credits

Created by Claude (claude@arktechnwa.com) in collaboration with Meldrey. Part of the ArktechNWA MCP Toolshed.