# postgres-mcp
[](https://github.com/ArkTechNWA/postgres-mcp/actions/workflows/ci.yml)
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](https://github.com/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
```json
{
"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
```json
{
"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
```bash
postgres-mcp --bypass-permissions
```
Full database access. **DANGER ZONE.**
---
## Authentication
```json
{
"connection": {
"host": "localhost",
"port": 5432,
"database": "myapp",
"user_env": "PGUSER",
"password_env": "PGPASSWORD",
"ssl": true
}
}
```
Or connection string:
```json
{
"connection": {
"url_env": "DATABASE_URL"
}
}
```
**Recommendation:** Use a read-only database user for maximum safety.
---
## Tools
### Queries
#### `pg_query`
Execute a SELECT query.
```typescript
pg_query({
query: string,
params?: any[], // parameterized queries
limit?: number, // override max_rows
timeout?: string // override statement_timeout
})
```
Returns:
```json
{
"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.
```typescript
pg_execute({
query: string,
params?: any[],
returning?: boolean // add RETURNING *
})
```
Returns:
```json
{
"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.
```typescript
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:
```json
{
"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:
```json
{
"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.
```typescript
pg_tables({
schema?: string, // default: "public"
pattern?: string // table name pattern
})
```
Returns:
```json
{
"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.
```typescript
pg_columns({
table: string,
schema?: string
})
```
Returns:
```json
{
"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.
```typescript
pg_indexes({
table?: string,
schema?: string
})
```
#### `pg_constraints`
Get constraint information (PK, FK, unique, check).
```typescript
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.
```typescript
pg_schema({
table: string,
schema?: string // default: "public"
})
```
Returns:
```json
{
"table": "users",
"columns": [...],
"indexes": [...],
"constraints": [...]
}
```
#### `pg_sample`
Get sample rows from a table (respects column blacklist).
```typescript
pg_sample({
table: string,
schema?: string, // default: "public"
limit?: number, // default: 5, max: 20
order_by?: string // default: primary key
})
```
Returns:
```json
{
"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.
```typescript
pg_explain({
query: string,
params?: any[],
analyze?: boolean, // actually run (careful!)
format?: "text" | "json"
})
```
Returns:
```json
{
"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.
```typescript
pg_stats({
table?: string, // specific table (omit for database)
include_index_usage?: boolean
})
```
#### `pg_connections`
Get active connections.
```typescript
pg_connections({
include_queries?: boolean
})
```
#### `pg_locks`
Get current locks.
```typescript
pg_locks({
blocked_only?: boolean
})
```
### Analysis
#### `pg_analyze_query`
AI-powered query analysis.
```typescript
pg_analyze_query({
query: string,
use_ai?: boolean
})
```
Returns:
```json
{
"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.
```typescript
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
```json
{
"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:
```json
{
"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`:
```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
```json
{
"mcpServers": {
"postgres": {
"command": "postgres-mcp",
"env": {
"PGUSER": "readonly_user",
"PGPASSWORD": "secret"
}
}
}
}
```
---
## Installation
```bash
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](https://github.com/ArktechNWA).
