postgresql-mcp-server – MCP Server for PostgreSQL

Node.js TypeScript PostgreSQL Docker MCP License

Full-featured MCP (Model Context Protocol) server that exposes 36 tools for interacting with PostgreSQL databases. Covers schema introspection, query execution, data exploration, performance monitoring, security auditing, and maintenance — all accessible from Claude Code, Claude Desktop, Cursor, and any MCP-compatible client.

Features

36 tools across 6 domains: schema, queries, data, monitoring, security, maintenance
Dual transport: HTTP (Docker/remote) and stdio (local subprocess)
Read-only enforcement: pg_query rejects non-SELECT statements at the application level
Destructive-op guards: DROP, TRUNCATE, DELETE-without-WHERE require explicit confirm_destructive: true
Pagination + truncation: all list tools respect limit/offset; responses capped at 25,000 chars
Dual output formats: every tool supports response_format: markdown (default) or json
Demo schema: first-run seed with users, products, orders, triggers, views, and indexes

Requirements

Docker + Docker Compose (recommended) — or Node.js 20+ for local run
PostgreSQL 13+ (16 included in Docker setup)
An MCP-compatible client (Claude Code, Claude Desktop, Cursor…)

Quick Start (Docker)

# 1. Clone / enter project
cd mcp-postgresql

# 2. Start PostgreSQL + MCP server
docker compose up -d

# 3. Verify both containers are healthy
docker compose ps

Default ports:

Service	Port	Description
PostgreSQL	`5432`	Exposed to host for direct psql access
MCP HTTP server	`3002`	MCP endpoint at `http://localhost:3002/mcp`

Default credentials

Host:     localhost:5432
Database: mcpdb
User:     mcpuser
Password: mcppassword

Override via .env:

POSTGRES_USER=myuser
POSTGRES_PASSWORD=mysecret
POSTGRES_DB=mydb
PG_PORT=5432
MCP_PORT=3002

Demo schema

On first start, init/01_demo_schema.sql is executed automatically, creating:

Tables: users, products, orders, order_items, audit_log
Views: active_users, order_summary
Function + triggers: update_updated_at()
7 indexes, seed data (5 users, 5 products)
Extensions: uuid-ossp, pg_stat_statements

Configuration

Environment variables

Variable	Default	Description
`DATABASE_URL`	—	Full connection string (overrides all PG_* vars)
`PG_HOST`	`localhost`	PostgreSQL host
`PG_PORT`	`5432`	PostgreSQL port
`PG_DATABASE`	`postgres`	Database name
`PG_USER`	`postgres`	PostgreSQL user
`PG_PASSWORD`	—	PostgreSQL password
`TRANSPORT`	`stdio`	`stdio` or `http`
`PORT`	`3000`	HTTP server port (when `TRANSPORT=http`)
`HOST`	`127.0.0.1`	HTTP bind address (`0.0.0.0` in Docker)

Tools Reference

Schema Introspection

Tool	Description
`pg_list_databases`	All databases with encoding and size
`pg_list_schemas`	Schemas with owner, table/view counts
`pg_list_tables`	Tables in schema with size and estimated row count
`pg_describe_table`	Full table description: columns, FK, indexes, check constraints
`pg_list_views`	Views and materialized views with optional SQL definitions
`pg_list_functions`	Functions, procedures, aggregates, window functions
`pg_list_indexes`	Indexes by schema/table with size and definition
`pg_list_extensions`	Installed PostgreSQL extensions
`pg_list_sequences`	Sequences with range, increment, and current value
`pg_list_triggers`	Triggers per table with timing and event
`pg_list_types`	Enums, composite types, domains, range types
`pg_list_partitions`	Partitioned tables and their child partitions
`pg_search_objects`	Search all DB objects by LIKE pattern across all types
`pg_get_ddl`	CREATE statement (DDL) for table, view, function, or index

Query Execution

Tool	Description
`pg_query`	Execute a SELECT query (rejects any non-read-only statement)
`pg_explain`	EXPLAIN or EXPLAIN ANALYZE with text or JSON output
`pg_execute`	Execute DML/DDL: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
`pg_transaction`	Execute multiple statements atomically in a single transaction

Data Exploration

Tool	Description
`pg_sample_rows`	Sample rows from a table with optional WHERE, ORDER BY, column filter
`pg_count_rows`	Exact COUNT(*) with optional WHERE clause
`pg_copy_csv`	Export query results as CSV with header row
`pg_table_stats`	Size, live/dead rows, vacuum dates, scan counts per table

Monitoring & Performance

Tool	Description
`pg_active_queries`	Currently running queries with duration and wait events
`pg_slow_queries`	Slow query analysis from `pg_stat_statements` (mean/total time, cache hit %)
`pg_index_usage`	Index scan counts — identify unused or underused indexes
`pg_bloat_report`	Tables with high dead-tuple ratios needing VACUUM
`pg_list_locks`	Active locks with blocking pair detection
`pg_replication_status`	Streaming replica status and replay lag
`pg_connection_stats`	Connection summary grouped by database, user, app, or state

Security & Access Control

Tool	Description
`pg_list_roles`	All roles with attributes: superuser, login, replication, bypassRLS, memberships
`pg_list_grants`	Privileges on tables, views, sequences, functions
`pg_list_policies`	Row Level Security policies and RLS-enabled tables
`pg_kill_query`	Cancel (SIGINT) or terminate (SIGTERM) a backend by PID

Maintenance & Configuration

Tool	Description
`pg_server_info`	Server version, uptime, connection counts, key settings
`pg_list_settings`	`pg_settings` filtered by name/category; shows source and pending restarts
`pg_vacuum`	Run VACUUM, VACUUM ANALYZE, or VACUUM FULL on a table

Client Setup

Claude Code

# Register via HTTP (Docker must be running)
claude mcp add --transport http postgresql-mcp-server http://localhost:3002/mcp

Claude Desktop

Claude Desktop requires stdio transport. Edit ~/Library/Application Support/Claude/claude_desktop_config.json:

{
  "mcpServers": {
    "postgresql": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-postgresql/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgres://mcpuser:mcppassword@localhost:5432/mcpdb"
      }
    }
  }
}

Restart Claude Desktop after editing.

Cursor

Edit ~/.cursor/mcp.json (global) or .cursor/mcp.json (project-level):

{
  "mcpServers": {
    "postgresql": {
      "url": "http://localhost:3002/mcp"
    }
  }
}

Transport Modes

Mode	When to use	How to run
HTTP	Docker, remote servers, multiple clients	`TRANSPORT=http docker compose up`
stdio	Claude Desktop, local subprocess, single client	`node dist/index.js` (default)

DNS rebinding note: when running HTTP locally (outside Docker), the server binds to 127.0.0.1 by default. In Docker, set HOST=0.0.0.0 (already set in docker-compose.yml).

Development

# Install dependencies
npm install

# Build TypeScript
npm run build

# Run locally (stdio, connects to DATABASE_URL)
DATABASE_URL=postgres://user:pass@localhost:5432/mydb npm start

# Run as HTTP server
TRANSPORT=http PORT=3000 DATABASE_URL=... npm start

# Watch mode (dev)
DATABASE_URL=... npm run dev

Docker commands

# Start
docker compose up -d

# Stop (keep volumes)
docker compose down

# Stop and wipe database
docker compose down -v

# Rebuild after code changes
docker compose up --build -d

# Follow logs
docker compose logs -f mcp-server

Adding tools

Create or edit a file in src/tools/
Export a register*Tools(server: McpServer) function
Import and call it in src/index.ts
npm run build — TypeScript strict mode catches issues at compile time
docker compose up --build -d to deploy

Project Structure

mcp-postgresql/
├── src/
│   ├── index.ts              # Entry point — registers all tools, starts transport
│   ├── constants.ts          # CHARACTER_LIMIT, defaults, ResponseFormat enum
│   ├── types.ts              # TypeScript interfaces for all DB result rows
│   ├── db.ts                 # pg.Pool, dbQuery, quoteIdentifier, validateIdentifier
│   ├── tools/
│   │   ├── schema.ts         # pg_list_databases/schemas/tables/views/functions/indexes/extensions/sequences/triggers/types/partitions/search/ddl/describe
│   │   ├── query.ts          # pg_query, pg_explain, pg_execute, pg_transaction
│   │   ├── data.ts           # pg_sample_rows, pg_count_rows, pg_table_stats, pg_copy_csv
│   │   ├── advanced.ts       # pg_server_info, pg_list_locks, pg_get_ddl
│   │   ├── monitoring.ts     # pg_active_queries, pg_slow_queries, pg_index_usage, pg_bloat_report, pg_replication_status
│   │   ├── security.ts       # pg_list_types/grants/policies/partitions, pg_copy_csv, pg_kill_query
│   │   └── maintenance.ts    # pg_list_roles, pg_list_settings, pg_vacuum, pg_connection_stats
│   └── utils/
│       ├── errors.ts         # PostgreSQL error formatting, isPgError type guard
│       └── format.ts         # formatMarkdownTable, truncateIfNeeded, formatBytes
├── init/
│   └── 01_demo_schema.sql    # Auto-loaded on first container start
├── dist/                     # Compiled JavaScript (gitignored)
├── Dockerfile                # Multi-stage: builder → runtime (node:20-alpine)
├── docker-compose.yml        # postgres:16-alpine + mcp-server
├── .env                      # Local overrides (not committed)
├── .dockerignore
├── package.json
└── tsconfig.json

Safety Model

Operation	Protection
SELECT via `pg_query`	Rejects INSERT/UPDATE/DELETE/DDL at app level
DROP / TRUNCATE	Requires `confirm_destructive: true`
DELETE without WHERE	Requires `confirm_destructive: true`
VACUUM FULL	Requires `confirm_full: true` (warns about exclusive lock)
`pg_kill_query`	Shows target query before acting; requires explicit mode
Identifier injection	`validateIdentifier()` + `quoteIdentifier()` on all dynamic table/schema names