[![CI](https://github.com/divinedev111/mcp-postgres/actions/workflows/ci.yml/badge.svg)](https://github.com/divinedev111/mcp-postgres/actions)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE)

# mcp-postgres

MCP server for PostgreSQL. Gives AI agents schema intelligence, query execution, and DBA tooling — through the [Model Context Protocol](https://modelcontextprotocol.io).

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

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

Or with environment variables:

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

### Claude Desktop

Add to your `claude_desktop_config.json`:

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

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

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

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

## License

MIT