# postgresql-mcp-server – MCP Server for PostgreSQL

[![Node.js](https://img.shields.io/badge/Node.js-20%2B-339933.svg?logo=node.js)](https://nodejs.org)
[![TypeScript](https://img.shields.io/badge/TypeScript-5.7-3178C6.svg?logo=typescript)](https://www.typescriptlang.org)
[![PostgreSQL](https://img.shields.io/badge/PostgreSQL-16-4169E1.svg?logo=postgresql)](https://www.postgresql.org)
[![Docker](https://img.shields.io/badge/Docker-Compose-2496ED.svg?logo=docker)](https://www.docker.com)
[![MCP](https://img.shields.io/badge/MCP-Protocol-black.svg)](https://modelcontextprotocol.io)
[![License](https://img.shields.io/badge/License-MIT-yellow.svg)](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.

---

## Table of Contents

- [Features](#features)
- [Requirements](#requirements)
- [Quick Start (Docker)](#quick-start-docker)
- [Configuration](#configuration)
- [Tools Reference](#tools-reference)
- [Client Setup](#client-setup)
- [Transport Modes](#transport-modes)
- [Development](#development)
- [Project Structure](#project-structure)

---

## 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)

```bash
# 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`:

```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

```bash
# 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`:

```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):

```json
{
  "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

```bash
# 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

```bash
# 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

1. Create or edit a file in `src/tools/`
2. Export a `register*Tools(server: McpServer)` function
3. Import and call it in `src/index.ts`
4. `npm run build` — TypeScript strict mode catches issues at compile time
5. `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 |