mcp-postgres

MCP server for PostgreSQL. Gives AI agents schema intelligence, query execution, and DBA tooling — through the Model Context Protocol.

Unlike generic database MCP servers, mcp-postgres is Postgres-native. It extracts table/column comments, understands Postgres-specific catalog views, provides index analysis, and ships with configurable access levels so you don't hand an LLM unrestricted database access.

Features

Schema Intelligence

• List schemas, tables, views with sizes and row counts
• Full table descriptions: columns, types, constraints, indexes, foreign keys
• Extracts COMMENT ON metadata — gives the LLM semantic context about what columns mean
• Search objects by name or comment across the entire database

Query Execution

• Read-only query tool with automatic row limiting
• Write-capable execute tool gated by access level
• EXPLAIN ANALYZE with human-readable output

DBA Tooling

• Table stats: live/dead tuples, bloat percentage, vacuum history, scan patterns
• Index analysis: usage stats, unused index detection, missing index suggestions
• Database health: connections, cache hit ratio, long-running queries, throughput

Safety

• Four access levels: readonly, readwrite, admin, unrestricted
• SQL statement classification (SELECT, DML, DDL, admin) with enforcement
• Audit logging to stderr (JSON, one entry per query)

Quick Start

npx mcp-postgres --connection-string "postgres://user:pass@localhost:5432/mydb"

Or with environment variables:

DATABASE_URL="postgres://user:pass@localhost:5432/mydb" npx mcp-postgres

Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-postgres",
        "--connection-string",
        "postgres://user:pass@localhost:5432/mydb"
      ]
    }
  }
}

Claude Code

Add to your project's .mcp.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "mcp-postgres"],
      "env": {
        "DATABASE_URL": "postgres://user:pass@localhost:5432/mydb"
      }
    }
  }
}

Tools

*explain_query with analyze=true executes the query, so it respects the statement's access level.

Resources

Prompts

Configuration

CLI Options

--connection-string  PostgreSQL connection URL
--access-level       readonly|readwrite|admin|unrestricted (default: readonly)
--row-limit          Max rows returned per query (default: 500)
--schema             Default schema filter (default: public)
--audit              Enable query audit logging to stderr

Environment Variables

Access Levels

Default is readonly. Use the minimum level needed.

Audit Logging

Enable with --audit. Logs every tool invocation to stderr as JSON:

{"timestamp":"2026-04-03T12:00:00.000Z","tool":"query","sql":"SELECT * FROM users","statementType":"select","accessLevel":"readonly","allowed":true,"durationMs":12,"rowCount":42}

Pipe stderr to a file to capture: mcp-postgres --audit 2>audit.log

Architecture

src/
├── index.ts             Entry point and CLI
├── server.ts            MCP server setup
├── config.ts            Configuration parsing
├── db/
│   ├── pool.ts          Connection pool management
│   └── query.ts         Query execution with timing
├── tools/
│   ├── schema.ts        Schema exploration tools
│   ├── query.ts         Query execution tools
│   └── performance.ts   DBA and health tools
├── resources/
│   └── schema.ts        Schema DDL resources
├── prompts/
│   └── index.ts         Prompt templates
└── safety/
    ├── classifier.ts    SQL statement classification
    ├── access.ts        Access level enforcement
    └── audit.ts         Audit logging

Development

npm install
npm test           # run tests
npm run build      # compile TypeScript
npm run dev -- --connection-string "postgres://..."  # run in dev mode

License

MIT