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

Tool	Description	Access
list_schemas	List schemas with table counts and sizes	readonly
list_tables	List tables with comments, row counts, sizes	readonly
describe_table	Full table description with columns, indexes, FKs, comments	readonly
search_objects	Search objects by name or comment	readonly
query	Execute SELECT queries	readonly
execute	Execute INSERT/UPDATE/DELETE/CREATE/etc	varies
explain_query	EXPLAIN (ANALYZE) with readable output	readonly*
table_stats	Table statistics, bloat, vacuum info	readonly
index_analysis	Index usage, unused indexes, missing index hints	readonly
database_health	Connections, cache ratio, long queries, bloat	readonly

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

Resources

URI	Description
postgres://schema/{name}	Full DDL for a schema (CREATE TABLE statements with comments)
postgres://extensions	Installed PostgreSQL extensions

Prompts

Prompt	Description
explore-database	Guided database exploration — schemas, tables, relationships
optimize-query	Analyze a slow query with EXPLAIN, indexes, and recommendations
health-check	Comprehensive database health assessment

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

Variable	Description
DATABASE_URL	PostgreSQL connection URL
POSTGRES_URL	Alternative connection URL
MCP_POSTGRES_ACCESS_LEVEL	Access level override
MCP_POSTGRES_ROW_LIMIT	Row limit override

Access Levels

Level	SELECT	INSERT/UPDATE/DELETE	CREATE/ALTER/DROP	TRUNCATE/DROP DATABASE
readonly	yes	no	no	no
readwrite	yes	yes	no	no
admin	yes	yes	yes	no
unrestricted	yes	yes	yes	yes

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