# PostgreSQL MCP Server

A production-ready [Model Context Protocol (MCP)](https://modelcontextprotocol.io/) server that connects AI assistants to **any PostgreSQL database** — cloud-hosted or self-hosted. Built on [Cloudflare Workers](https://developers.cloudflare.com/workers/) and the [Agents SDK](https://developers.cloudflare.com/agents/), it provides 25 database tools with enterprise-grade authentication via [Cloudflare Access](https://developers.cloudflare.com/cloudflare-one/applications/) and role-based access control through OIDC identity provider groups.

## Key Features

- **Universal PostgreSQL compatibility** — connects to Neon, Supabase, Amazon RDS, Google Cloud SQL, Azure Database, self-hosted instances, or any PostgreSQL-compatible database
- **25 database tools** organized into 4 permission tiers (universal, read-only, read-write, admin)
- **Role-based access control** — tools are conditionally registered per session based on IdP group membership
- **SQL safety layer** — blocks dangerous operations (DROP DATABASE, privilege escalation, multi-statement injection) at the parser level
- **Enterprise SSO** — OAuth 2.1 with PKCE via Cloudflare Access, supporting any OIDC identity provider (Okta, Authentik, Azure AD, etc.)
- **Connection pooling** — [Hyperdrive](https://developers.cloudflare.com/hyperdrive/) manages connection pooling and TCP proxying to your database
- **Private database support** — reach databases on private networks via [Cloudflare Tunnel](https://developers.cloudflare.com/cloudflare-one/connections/connect-networks/)
- **Serverless** — runs on Cloudflare Workers with Durable Objects for per-user MCP sessions, no infrastructure to manage

## Architecture

```
MCP Client (Claude, Cursor, Windsurf, etc.)
    |
    | Streamable HTTP + OAuth 2.1
    v
Cloudflare Worker
    |-- OAuthProvider --> Cloudflare Access --> OIDC IdP (Okta, Authentik, Azure AD, ...)
    |-- McpAgent (Durable Object — one per authenticated session)
    |       |-- Role-based tool registration via IdP groups
    |       |-- SQL Safety Parser (blocks dangerous patterns)
    |       |-- Prompts (query assistant, schema explorer)
    |       |-- Resources (schema dump, health dashboard)
    |-- pg-client --> Hyperdrive --> PostgreSQL
                                        |
                            +-----------+-----------+
                            |                       |
                    Public databases         Private databases
                   (Neon, Supabase,        (via Cloudflare Tunnel
                    RDS, Cloud SQL)         + VPC Service)
```

## Database Compatibility

This MCP server works with **any PostgreSQL database** that Hyperdrive can reach:

| Database | Connection Method | Tunnel Required? |
|----------|------------------|-----------------|
| **Neon** | Direct (public endpoint) | No |
| **Supabase** | Direct (public endpoint) | No |
| **Amazon RDS** (public) | Direct | No |
| **Amazon RDS** (VPC) | Cloudflare Tunnel | Yes |
| **Google Cloud SQL** | Direct or Tunnel | Depends on config |
| **Azure Database for PostgreSQL** | Direct | No |
| **Self-hosted** (public IP) | Direct | No |
| **Self-hosted** (private network) | Cloudflare Tunnel | Yes |
| **Docker/local** | Cloudflare Tunnel | Yes |


## Role-Based Access Control

Tools are conditionally registered per MCP session based on the authenticated user's IdP groups. Users only see and can invoke tools matching their permission tier.

| IdP Group | Tools Available | Count |
|-----------|----------------|-------|
| *(any authenticated user)* | Universal | 3 |
| `pg-readonly` | Universal + Read-Only | 10 |
| `pg-readwrite` | Universal + Read-Only + Read-Write | 15 |
| `pg-admin` | All tools | 25 |

Groups are extracted from the `groups` claim in the OIDC ID token. Multiple group formats are supported (Authentik, Okta, comma-separated strings, arrays).

## Tools Reference

### Universal Tools (3) — Always Available

| Tool | Description |
|------|-------------|
| `userInfo` | Current user's email, name, groups, and computed access level |
| `connectionInfo` | PostgreSQL version, database name, server time, current database user |
| `healthCheck` | MCP server and database connectivity check with response time |

### Read-Only Tools (7) — Requires `pg-readonly`, `pg-readwrite`, or `pg-admin`

| Tool | Description |
|------|-------------|
| `listTables` | List all tables, views, and materialized views with row counts and sizes |
| `describeTable` | Full column definitions, indexes, and foreign key constraints |
| `executeQuery` | Execute validated read-only SQL (SELECT, WITH...SELECT) with parameterized values |
| `explainQuery` | EXPLAIN or EXPLAIN ANALYZE with JSON output for query optimization |
| `searchSchema` | Case-insensitive search across table and column names |
| `listIndexes` | Index listing with type, uniqueness, size, and scan count statistics |
| `getTableStats` | Live/dead row counts, dead row ratio, vacuum/analyze timestamps |

### Read-Write Tools (5) — Requires `pg-readwrite` or `pg-admin`

| Tool | Description |
|------|-------------|
| `insertRows` | Parameterized INSERT with optional RETURNING clause |
| `updateRows` | Parameterized UPDATE with WHERE clause (auto-adjusts parameter indices) |
| `deleteRows` | DELETE with mandatory confirmation flag as a safety check |
| `executeSql` | Execute arbitrary non-DDL SQL (INSERT, UPDATE, DELETE, SELECT) |
| `upsertRows` | INSERT ... ON CONFLICT DO UPDATE with conflict target and update columns |

### Admin Tools (10) — Requires `pg-admin`

| Tool | Description |
|------|-------------|
| `databaseHealth` | Connection utilization, buffer cache hit ratios, database size, uptime |
| `indexHealth` | Unused indexes, duplicate indexes, tables needing indexes |
| `vacuumHealth` | Dead row ratios, last vacuum timestamps, tables needing vacuum |
| `sequenceHealth` | Sequences approaching max value (prevents silent insert failures) |
| `createIndex` | Create indexes (CONCURRENTLY by default for non-blocking operations) |
| `dropIndex` | Drop indexes (CONCURRENTLY by default) |
| `executeDDL` | Execute DDL statements (CREATE TABLE, ALTER TABLE, etc.) |
| `analyzeTable` | Run ANALYZE to update query planner statistics |
| `getActiveQueries` | List running queries with duration, state, and wait events |
| `cancelQuery` | Cancel a running query by PID |

### Prompts (2) — Always Available

| Prompt | Description |
|--------|-------------|
| `queryAssistant` | Generate accurate SQL from natural language using live schema context |
| `schemaExplorer` | Guided database overview with tables, relationships, and exploration suggestions |

### Resources (2) — Always Available

| Resource URI | Description |
|-------------|-------------|
| `schema://tables` | Full schema dump with tables, columns, types, and indexes (JSON) |
| `health://overview` | Database health dashboard with connections, cache, and table stats (JSON) |

## SQL Safety

A dedicated SQL parser validates every query before execution:

**Always blocked (all roles):**
- `DROP DATABASE`, `DROP ROLE`, `DROP USER`
- `CREATE ROLE`, `CREATE USER`, `ALTER ROLE`, `ALTER USER`
- `GRANT`, `REVOKE`
- `COPY TO/FROM PROGRAM`
- Multi-statement injection (semicolons inside query body)

**Role-enforced:**
- **Read-only** — only `SELECT`, `EXPLAIN`, `SHOW`, and CTEs without mutation
- **Read-write** — adds `INSERT`, `UPDATE`, `DELETE` (no DDL)
- **Admin** — adds DDL (`CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, etc.)

## Getting Started

### Prerequisites

- A [Cloudflare account](https://dash.cloudflare.com/sign-up) (Workers paid plan for Durable Objects)
- [Node.js](https://nodejs.org/) 18+ and npm
- [Wrangler CLI](https://developers.cloudflare.com/workers/wrangler/install-and-update/) (`npm install -g wrangler`)
- A PostgreSQL database (any provider)
- An OIDC identity provider configured in Cloudflare Access

### Step 1: Clone and Install

```bash
git clone https://github.com/vnikhilbuddhavarapu/postgresql-mcp.git
cd postgresql-mcp
cp wrangler.jsonc.example wrangler.jsonc
npm install
```

### Step 2: Create Cloudflare Resources

**KV Namespace** (stores OAuth state):

```bash
wrangler kv namespace create OAUTH_KV
```

Copy the `id` into `wrangler.jsonc` under `kv_namespaces[0].id`.

**Hyperdrive Config** (connection pooling):

```bash
# For a publicly reachable database:
wrangler hyperdrive create pg-mcp \
  --connection-string="postgres://user:password@host:5432/dbname"

# For a private database via Cloudflare Tunnel:
# First create a VPC Service, then:
wrangler hyperdrive create pg-mcp \
  --connection-string="postgres://user:password@<PRIVATE_IP>:5432/dbname" \
  --vpc-id="<VPC_SERVICE_ID>"
```

Copy the Hyperdrive `id` into `wrangler.jsonc` under `hyperdrive[0].id`.

### Step 3: Configure Cloudflare Access (OIDC)

1. Go to **Cloudflare Zero Trust** > **Access** > **Applications** > **Add SaaS Application**
2. Choose **OIDC** and select your identity provider
3. Set the **Redirect URI** to `https://<your-worker>.workers.dev/callback`
4. Enable scopes: `openid`, `email`, `profile`, `groups`
5. Note down the following from the application configuration:
   - Client ID
   - Client Secret
   - Token Endpoint
   - Authorization Endpoint
   - JWKS/Key Endpoint
6. Configure your IdP to include a `groups` claim with values: `pg-readonly`, `pg-readwrite`, and/or `pg-admin`

### Step 4: Set Secrets

```bash
cp .dev.vars.example .dev.vars
# Edit .dev.vars with your Access credentials
```

### Step 5: Deploy

```bash
# Generate types
wrangler types

# Deploy
wrangler deploy

# Push secrets to production
wrangler secret bulk .dev.vars
```

### Step 6: Connect an MCP Client

Use the MCP endpoint URL with any compatible client:

```
https://<your-worker>.workers.dev/mcp
```

Or test with the MCP Inspector:

```bash
npx @modelcontextprotocol/inspector
# Enter your /mcp URL, authenticate via the OAuth flow
```

## Development

```bash
npm install          # Install dependencies
wrangler types       # Generate TypeScript types from bindings
npm run type-check   # TypeScript type checking
npm test             # Run Vitest unit tests (48 tests)
npm run lint         # Lint with oxlint
npm run format       # Format with oxfmt
wrangler dev         # Local development server (note: Hyperdrive requires deploy for full testing)
wrangler deploy      # Deploy to Cloudflare
```

## Project Structure

```
postgresql-mcp/
├── src/
│   ├── index.ts                  # McpAgent (Durable Object) + OAuthProvider entry point
│   ├── env.d.ts                  # Environment type declarations
│   ├── auth/
│   │   ├── access-handler.ts     # OAuth /authorize + /callback flow with Cloudflare Access
│   │   └── workers-oauth-utils.ts # CSRF, PKCE, state management, token exchange
│   ├── db/
│   │   ├── pg-client.ts          # PostgreSQL client wrapper (Hyperdrive connection pooling)
│   │   ├── sql-parser.ts         # SQL classification and safety validation
│   │   └── schemas.ts            # Zod schemas for all tool inputs and database types
│   ├── tools/
│   │   ├── universal.ts          # 3 tools — userInfo, connectionInfo, healthCheck
│   │   ├── readonly.ts           # 7 tools — listTables, describeTable, executeQuery, ...
│   │   ├── readwrite.ts          # 5 tools — insertRows, updateRows, deleteRows, ...
│   │   └── admin.ts              # 10 tools — databaseHealth, createIndex, executeDDL, ...
│   ├── prompts/
│   │   ├── query-assistant.ts    # Natural language to SQL with live schema context
│   │   └── schema-explorer.ts    # Guided database exploration
│   └── resources/
│       ├── schema.ts             # schema://tables — full schema dump
│       └── health.ts             # health://overview — database health dashboard
├── tests/
│   └── sql-parser.test.ts        # 48 unit tests for SQL safety parser
├── .dev.vars.example             # Template for environment secrets
├── wrangler.jsonc.example        # Template for Worker configuration (copy to wrangler.jsonc)
├── tsconfig.json                 # TypeScript configuration
└── package.json                  # Dependencies and scripts
```

## Tech Stack

- **Runtime**: [Cloudflare Workers](https://developers.cloudflare.com/workers/) + [Durable Objects](https://developers.cloudflare.com/durable-objects/)
- **MCP SDK**: [Agents SDK](https://developers.cloudflare.com/agents/) (`McpAgent`) + [`@modelcontextprotocol/sdk`](https://github.com/modelcontextprotocol/typescript-sdk)
- **Authentication**: [`@cloudflare/workers-oauth-provider`](https://github.com/cloudflare/workers-oauth-provider) + Cloudflare Access (Generic OIDC SaaS)
- **Database**: [Hyperdrive](https://developers.cloudflare.com/hyperdrive/) + [`pg`](https://node-postgres.com/) driver
- **Validation**: [Zod](https://zod.dev/) v4
- **Testing**: [Vitest](https://vitest.dev/)
- **Linting/Formatting**: [oxlint](https://oxc.rs/) + [oxfmt](https://oxc.rs/)

## Related Documentation

- [Model Context Protocol](https://modelcontextprotocol.io/)
- [Building Remote MCP Servers on Cloudflare](https://developers.cloudflare.com/agents/guides/remote-mcp-server/)
- [Cloudflare Hyperdrive — Connect to PostgreSQL](https://developers.cloudflare.com/hyperdrive/get-started/)
- [Cloudflare Access — SaaS Applications](https://developers.cloudflare.com/cloudflare-one/applications/configure-apps/saas-apps/)
- [Cloudflare Tunnel — Private Networks](https://developers.cloudflare.com/cloudflare-one/connections/connect-networks/)

## License

MIT