MCPg

# MCPg

**A production-grade [Model Context Protocol](https://modelcontextprotocol.io)
server for PostgreSQL.** Lets AI agents safely inspect, query, operate, and
tune a Postgres database — 254 tools spanning catalog introspection,
query intelligence, natural-language SQL, structural diffs, hybrid search,
graph queries, data movement, live ops, and more.


[![PyPI version](https://img.shields.io/pypi/v/mcpg.svg)](https://pypi.org/project/mcpg/)
[![Python versions](https://img.shields.io/pypi/pyversions/mcpg.svg)](https://pypi.org/project/mcpg/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://github.com/devopam/MCPg/blob/main/LICENSE)
[![CI](https://github.com/devopam/MCPg/actions/workflows/ci.yml/badge.svg)](https://github.com/devopam/MCPg/actions/workflows/ci.yml)
[![Stars](https://img.shields.io/github/stars/devopam/MCPg)](https://github.com/devopam/MCPg)
[![smithery badge](https://smithery.ai/badge/devopam/mcpg)](https://smithery.ai/servers/devopam/mcpg)
[![MCPg MCP server](https://glama.ai/mcp/servers/devopam/MCPg/badges/score.svg)](https://glama.ai/mcp/servers/devopam/MCPg)

> **Try it live:** point an MCP client — or the [MCP Inspector](https://github.com/modelcontextprotocol/inspector) — at the hosted, read-only demo endpoint `https://devopam-mcpg-demo.hf.space/mcp`. It serves read tools against throwaway demo data; for real use, run MCPg next to your own database (see [Quick start](#quick-start)).

### 📍 Listed On

- **[Official MCP Registry](https://registry.modelcontextprotocol.io/v0/servers?search=io.github.devopam/mcpg)**
- **[mcp.so](https://mcp.so/server/mcpg---production-grade-postgresql-mcp-server/Devopam%20Mittra)**
- **[mcpservers.org](https://mcpservers.org/servers/devopam/mcpg)**
- **[Smithery](https://smithery.ai/servers/devopam/mcpg)**
- **[Glama](https://glama.ai/mcp/servers/devopam/MCPg)**
---

| Aspect              | MCPg                              |
|---------------------|-----------------------------------|
| Safety              | Read-only default + AST validation |
| Transport           | stdio + HTTP/SSE                  |
| Install             | `pip install mcpg`                |
| Postgres Versions   | 14–19                             |
| Key Differentiator  | Production observability + multi-tenancy |

## Why MCPg

- **Safe by default.** Read-only access mode. Every user-supplied SQL
  statement parses through a validated AST allowlist before execution.
  Identifier interpolation flows through a strict
  `[A-Za-z_][A-Za-z0-9_]*` regex — a design constraint that means
  user input never reaches the database through string concatenation.
  Capabilities like DDL, shell, and `LISTEN/NOTIFY` are off until you
  opt in. Every tool publishes MCP `ToolAnnotations` (`readOnlyHint`,
  `openWorldHint`) derived from those same gates, so clients can
  auto-approve reads and gate writes without guessing.
- **One server, broad surface.** Application data access (queries, search,
  cursors, NL→SQL) *and* DBA-grade operations (health checks, index tuning,
  EXPLAIN analysis, locks, vacuum, dumps, replicas, migrations) in a
  single MCP server. Agents don't have to switch tools to switch tasks.
- **PostgreSQL-native everything.** No ORM, no abstraction tax — uses
  `psycopg3` directly, speaks every `pg_*` system view, integrates with
  TimescaleDB, pgvector, PostGIS, Apache AGE, and `pg_stat_statements`
  where they're available, and degrades gracefully when they aren't.
- **Production-shaped, not demo-shaped.** Connection pooling, per-request
  `SET ROLE` multi-tenancy, read-replica routing with degraded-host
  detection, server-side cursors with dedicated connections,
  rate-limiting, audit trail with regex redaction, PG TLS enforcement
  on startup, OIDC JWT bearer auth, per-session statement / lock
  timeouts.
- **Observability built in.** Prometheus `/metrics` endpoint on the
  HTTP transport surfaces `mcpg_tool_calls_total{tool,status}` +
  `mcpg_tool_duration_seconds`. Every tool call records a structured
  audit event with credential-redacted arguments.
- **Test-driven, multi-version.** 2,500+ unit tests plus an integration
  suite that runs against a real PostgreSQL container in CI — matrix
  covers PG **14, 15, 16, 17, 18** on every push, plus PG **19 (beta)**
  as an experimental (non-blocking) entry tracked under issue #120.

---

## Install

### From PyPI (recommended)

```bash
pip install mcpg
# or, in an isolated venv exposed globally:
uv tool install mcpg
```

Verify:

```bash
mcpg --version
```

### Docker

Pull the pre-built image from the GitHub Container Registry (published
on every tagged release — `:latest` tracks the newest, or pin a version
like `:0.6.5`):

```bash
docker pull ghcr.io/devopam/mcpg:latest
docker run --rm --name mcpg -p 8000:8000 \
    -e MCPG_DATABASE_URL=postgresql://user:pass@host:5432/db \
    -e MCPG_ACCESS_MODE=read-only \
    ghcr.io/devopam/mcpg:latest
```

On **Windows PowerShell** replace the trailing `\` with a backtick `` ` ``
(or put the command on one line); the [installation
guide](docs/installation.md#option-2--docker) has ready-to-copy
Linux/macOS, PowerShell, and Command Prompt blocks.

Or build it yourself from source:

```bash
docker build -t mcpg https://github.com/devopam/MCPg.git
```

Multi-stage image: runtime stage drops the build toolchain, runs as
`uid=10001 / gid=10001` with `nologin` shell, application files
root-owned and read-only to the runtime user.

### From source (developers)

```bash
git clone https://github.com/devopam/MCPg && cd MCPg
uv sync
```

`uv sync` creates a venv with all runtime + dev dependencies and exposes
the `mcpg` console script.

More detail in the [Installation Guide](https://github.com/devopam/MCPg/blob/main/docs/installation.md).

---

## Quick start

**One-click installs:**
[![Add to Cursor](https://img.shields.io/badge/Add_to-Cursor-000000?logo=cursor)](https://cursor.com/install-mcp?name=mcpg&config=eyJjb21tYW5kIjoidXZ4IiwiYXJncyI6WyJtY3BnIl0sImVudiI6eyJNQ1BHX0RBVEFCQVNFX1VSTCI6InBvc3RncmVzcWw6Ly91c2VyOnBhc3NAbG9jYWxob3N0OjU0MzIvbXlkYiJ9fQ%3D%3D)
[![Install in VS Code](https://img.shields.io/badge/Install_in-VS_Code-0098FF?logo=githubcopilot)](https://vscode.dev/redirect?url=vscode%3Amcp%2Finstall%3F%257B%2522name%2522%253A%2522mcpg%2522%252C%2522command%2522%253A%2522uvx%2522%252C%2522args%2522%253A%255B%2522mcpg%2522%255D%252C%2522env%2522%253A%257B%2522MCPG_DATABASE_URL%2522%253A%2522%2524%257Binput%253Adatabase_url%257D%2522%257D%252C%2522inputs%2522%253A%255B%257B%2522type%2522%253A%2522promptString%2522%252C%2522id%2522%253A%2522database_url%2522%252C%2522description%2522%253A%2522PostgreSQL%2520connection%2520URL%2520%2528postgresql%253A%252F%252Fuser%253Apass%2540host%253A5432%252Fdb%2529%2522%252C%2522password%2522%253Atrue%257D%255D%257D)
[![Claude Desktop](https://img.shields.io/badge/Claude_Desktop-.mcpb-D97757?logo=claude)](https://github.com/devopam/MCPg/releases/latest)
— setup for Windsurf, JetBrains, Zed, Cline, Antigravity, Qwen Code, Perplexity,
ChatGPT, Copilot Studio, Continue, and HTTP
clients in the [integrations guide](docs/integrations.md).

### One-click install in Claude Desktop (.mcpb)

Download `mcpg-<version>.mcpb` from the
[latest release](https://github.com/devopam/MCPg/releases/latest) and
double-click it (or drag it into Claude Desktop's Settings →
Extensions). You'll be prompted for your PostgreSQL connection URL —
stored in the OS keychain — and an access mode (defaults to
read-only). That's the whole install: the bundle is ~2 kB and the
host resolves the pinned `mcpg` release from PyPI for your platform.

### Or wire it up manually (stdio transport)

Drop this into your `claude_desktop_config.json` (macOS:
`~/Library/Application Support/Claude/claude_desktop_config.json`;
Windows: `%APPDATA%\Claude\claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "mcpg": {
      "command": "uvx",
      "args": ["mcpg"],
      "env": {
        "MCPG_DATABASE_URL": "postgresql://user:pass@localhost:5432/mydb"
      }
    }
  }
}
```

Restart Claude Desktop. The MCPg toolset is now available to the model.
You can ask Claude things like:

> *"What schemas exist in this database? For each one, summarise the
> three biggest tables."*

> *"Why is this query slow?
> `SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC`"*

### No interesting data yet? Seed the demo dataset

```bash
MCPG_DATABASE_URL=postgresql://... mcpg --demo
```

One command seeds a small, curated e-commerce dataset (3,000 orders,
900 product reviews, deliberately planted flaws) into an `mcpg_demo`
schema — engineered so the index advisor, query-plan analysis,
full-text search, PII audit, and graph projection all have something
real to find on your first try. See the
[guided tour](docs/demo.md) for a captured walkthrough, and remove it
any time with `mcpg --demo-drop`.

### Run as an HTTP server (for IDE integrations, web apps, etc.)

```bash
MCPG_DATABASE_URL=postgresql://user:pass@localhost:5432/mydb \
MCPG_TRANSPORT=streamable-http \
MCPG_HTTP_PORT=8000 \
mcpg
```

Then point any MCP-aware client at `http://localhost:8000/mcp` (or
`/sse` for the SSE transport). Set
`MCPG_HTTP_AUTH_TOKEN=...` for a static bearer, or
`MCPG_AUTH_MODE=oidc` for full JWT validation against an OIDC issuer.

---

## Configuration

MCPg is configured **entirely through environment variables** — no
config file, no flags (the CLI's `--version` / `--demo` / `--demo-drop`
are one-shot commands, not configuration). The only required one is
`MCPG_DATABASE_URL`; everything else has a safe default.

### Common scenarios

| Scenario | Set |
|---|---|
| Local exploration, read-only | `MCPG_DATABASE_URL` |
| Read-write app data access | `MCPG_ACCESS_MODE=restricted` |
| DBA toolkit (DDL, vacuum, etc.) | `MCPG_ACCESS_MODE=unrestricted` + `MCPG_ALLOW_DDL=true` |
| HTTP transport with bearer auth | `MCPG_TRANSPORT=streamable-http` + `MCPG_HTTP_AUTH_TOKEN=…` |
| Multi-tenant SaaS | `M