Postgres Model Context Protocol (MCP) Server + Github OAuth

This is a Model Context Protocol (MCP) server that enables you to chat with your PostgreSQL database, deployable as a remote MCP server with GitHub OAuth through Cloudflare. This is a production-ish ready MCP.

Key Features

• 🗄️ Database Integration with Lifespan: Direct PostgreSQL database connection for all MCP tool calls
• 🛠️ Modular, Single Purpose Tools: Following best practices around MCP tools and their descriptions
• 🔐 Role-Based Access: GitHub username-based permissions for database write operations
• 📊 Schema Discovery: Automatic table and column information retrieval
• 🛡️ SQL Injection Protection: Built-in validation and sanitization
• 📈 Monitoring: Optional Sentry integration for production monitoring
• ☁️ Cloud Native: Powered by Cloudflare Workers for global

scale

Transport Protocols

This MCP server supports both modern and legacy transport protocols:

• `/mcp` - Streamable HTTP (recommended): Uses a single endpoint with bidirectional communication, automatic connection upgrades, and better resilience for network interruptions
• `/sse` - Server-Sent Events (legacy): Uses separate endpoints for requests/responses, maintained for backward compatibility

For new implementations, use the /mcp endpoint as it provides better performance and reliability.

How It Works

The MCP server provides three main tools for database interaction:

1. `listTables` - Get database schema and table information (all authenticated users)
2. `queryDatabase` - Execute read-only SQL queries (all authenticated users)
3. `executeDatabase` - Execute write operations like INSERT/UPDATE/DELETE (privileged users only)

Other tools include:

1. `add` - Add two numbers the way only MCP can
2. `getCurrentUserInfo` - Get current user info from GitHub, via Octokit

Authentication Flow: Users authenticate via GitHub OAuth → Server validates permissions → Tools become available based on user's GitHub username.

Security Model:

• All authenticated GitHub users can read data
• Only specific GitHub usernames can write/modify data
• SQL injection protection and query validation built-in

Prerequisites

• Node.js installed on your machine
• A Cloudflare account (free tier works)
• A GitHub account for OAuth setup
• A PostgreSQL database (local or hosted)

Getting Started

You can use the command below to get the remote MCP Server template that this project was built on created on your local machine if you want to build from scratch:

npm create cloudflare@latest -- my-mcp-server --template=cloudflare/ai/demos/remote-mcp-github-oauth

Otherwise:

Step 1: Install Wrangler CLI

Install Wrangler globally to manage your Cloudflare Workers:

npm install -g wrangler

Step 2: Authenticate with Cloudflare

Log in to your Cloudflare account:

wrangler login

This will open a browser window where you can authenticate with your Cloudflare account.

Step 3: Clone and Setup

Clone the repo directly & install dependencies: npm install.

Environment Variables Setup

Before running the MCP server, you need to configure a few environment variables for authentication and database access.

Create Environment Variables File

1. Create your `.dev.vars` file from the example:

   cp .dev.vars.example .dev.vars

1. Configure all required environment variables in .dev.vars:

   # GitHub OAuth (for authentication)
   GITHUB_CLIENT_ID=your_github_client_id
   GITHUB_CLIENT_SECRET=your_github_client_secret
   COOKIE_ENCRYPTION_KEY=your_random_encryption_key

   # Database Connection
   DATABASE_URL=postgresql://username:password@localhost:5432/database_name

   # Optional: Sentry monitoring
   SENTRY_DSN=https://[email protected]/project-id
   NODE_ENV=development

Getting GitHub OAuth Credentials

1. Create a GitHub OAuth App for local development:

• Create a Github App
• Homepage URL: http://localhost:8788 (port can be changed in the wrangler.jsonc file)

• Authorization callback URL: http://localhost:8788/callback
• Click "Register application"

1. Copy your credentials:

• Copy the Client ID and paste it as GITHUB_CLIENT_ID in .dev.vars
• Click "Generate a new client secret", copy it, and paste as GITHUB_CLIENT_SECRET in .dev.vars

Generate Encryption Key

Generate a secure random encryption key for cookie encryption:

openssl rand -hex 32

Copy the output and paste it as COOKIE_ENCRYPTION_KEY in .dev.vars.

Database Setup

1. Set up PostgreSQL using a hosted service like:

• Supabase (recommended for beginners)
• Neon
• Postgres on railway (This is what i used)
• Or use local PostgreSQL/Supabase

1. Update the DATABASE_URL in .dev.vars with your connection string:

   DATABASE_URL=postgresql://username:password@host:5432/database_name

Database Schema Setup

The MCP server works with any PostgreSQL database schema. It will automatically discover:

• All tables in the public schema
• Column names, types, and constraints
• Primary keys and indexes

Testing the Connection: Once you have your database set up, you can test it by asking the MCP server "What tables are available in the database?" and then querying those tables to explore your data.

Local Development & Testing

Add your github username in this configuration in the src/index.ts file

// Add GitHub usernames for database write access
const ALLOWED_USERNAMES = new Set([
  'yourusername',    // Replace with your GitHub username
]);

Add the server to cladue desktop

{
  "mcpServers": {
    "math": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "http://localhost:8788/sse"
      ]
    }
  }
}

Run the server locally:

   wrangler dev

This makes the server available at http://localhost:8788

Testing with MCP Inspector

Use the MCP Inspector to test your server:

1. Install and run Inspector:

   npx @modelcontextprotocol/inspector@latest

1. Connect to your local server:

• Preferred: Enter URL: http://localhost:8788/mcp (streamable HTTP transport - newer, more robust- might not work with some clients)
• Alternative: Enter URL: http://localhost:8788/sse (SSE transport - legacy support)
• Click "Connect"
• Follow the OAuth prompts to authenticate with GitHub
• Once connected, you'll see the available tools

1. Test the tools:

• Use add to add two number together
• Use getCurrentUserInfo to get the user's information from github
• Use listTables to see your database structure
• Use queryDatabase to run SELECT queries
• Use executeDatabase (if you have write access) for INSERT/UPDATE/DELETE operations

Production Deployment

#### Set up a KV namespace

• Create the KV namespace:

wrangler kv namespace create "OAUTH_KV"

• Update the wrangler.jsonc file with the KV ID (replace <Add-KV-ID>)

#### Deploy Deploy the MCP server to make it available on your workers.dev domain

Create environment variables in production

Create a new GitHub OAuth App:

• For the Homepage URL, specify https://db-mcp-server.<your-subdomain>.workers.dev
• For the Authorization callback URL, specify https://db-mcp-server.<your-subdomain>.workers.dev/callback
• Note your Client ID and generate a Client secret.
• Set all required secrets via Wrangler:

wrangler secret put GITHUB_CLIENT_ID
wrangler secret put GITHUB_CLIENT_SECRET
wrangler secret put COOKIE_ENCRYPTION_KEY  # use: openssl rand -hex 32
wrangler secret put DATABASE_URL

wrangler deploy

You now have a remote MCP server deployed!

Database Tools & Access Control

Available Tools

#### 1. listTables (All Users) Purpose: Discover database schema and structure Access: All authenticated GitHub users Usage: Always run this first to understand your database structure

Example output:
- Tables: users, products, orders
- Columns: id (integer), name (varchar), created_at (timestamp)
- Constraints and relationships

#### 2. queryDatabase (All Users) Purpose: Execute read-only SQL queries Access: All authenticated GitHub users Restrictions: Only SELECT statements and read operations allowed

-- Examples of allowed queries:
SELECT * FROM users WHERE created_at > '2024-01-01';
SELECT COUNT(*) FROM products;
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

#### 3. executeDatabase (Privileged Users Only) Purpose: Execute write operations (INSERT, UPDATE, DELETE, DDL) Access: Restricted to specific GitHub usernames Capabilities: Full database write access including schema modifications

-- Examples of allowed operations:
INSERT INTO users (name, email) VALUES ('New User', '[email protected]');
UPDATE products SET price = 29.99 WHERE id = 1;
DELETE FROM orders WHERE status = 'cancelled';
CREATE TABLE new_table (id SERIAL PRIMARY KEY, data TEXT);

Access Control Configuration

Database write access is controlled by GitHub username in the ALLOWED_USERNAMES configuration:

// Add GitHub usernames for database write access
const ALLOWED_USERNAMES = new Set([
  'yourusername',    // Replace with your GitHub username
  'teammate1',       // Add team members who need write access
  'database-admin'   // Add other trusted users
]);

To update access permissions:

1. Edit src/index.ts file
2. Update the ALLOWED_USERNAMES set with GitHub usernames
3. Redeploy the worker: wrangler deploy

Security Features

• SQL Injection Protection: All queries are validated before execution
• Operation Type Detection: Automatic detection of read vs write operations
• User Context Tracking: All operations are logged with GitHub user information
• Connection Pooling: Efficient database connection management
• Error Sanitization: Database errors are cleaned before being returned to users

Access the remote MCP server from Claude Desktop

Open Claude Desktop and navigate to Settings -> Developer -> Edit Config. This opens the configuration file that controls which MCP servers Claude can access.

Replace the content with the following configuration. Once you restart Claude Desktop, a browser window will open showing your OAuth login page. Complete the authentication flow to grant Claude access to your MCP server. After you grant access, the tools will become available for you to use.

{
  "mcpServers": {
    "math": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "https://db-mcp-server.<your-subdomain>.workers.dev/sse"
      ]
    }
  }
}

Once the Tools (under 🔨) show up in the interface, you can ask Claude to interact with your database. Example commands:

• "What tables are available in the database?" → Uses listTables tool
• "Show me all users created in the last 30 days" → Uses queryDatabase tool
• "Add a new user named John with email [email protected]" → Uses executeDatabase tool (if you have write access)

How does it work?

#### OAuth Provider The OAuth Provider library serves as a complete OAuth 2.1 server implementation for Cloudflare Workers. It handles the complexities of the OAuth flow, including token issuance, validation, and management. In this project, it plays the dual role of:

• Authenticating MCP clients that connect to your server
• Managing the connection to GitHub's OAuth services
• Securely storing tokens and authentication state in KV storage

#### Durable MCP Durable MCP extends the base MCP functionality with Cloudflare's Durable Objects, providing:

• Persistent state management for your MCP server
• Secure storage of authentication context between requests
• Access to authenticated user information via this.props
• Support for conditional tool availability based on user identity

#### MCP Remote The MCP Remote library enables your server to expose tools that can be invoked by MCP clients like the Inspector. It:

• Defines the protocol for communication between clients and your server
• Provides a structured way to define tools
• Handles serialization and deserialization of requests and responses
• Maintains the Server-Sent Events (SSE) connection between clients and your server