DOCSEARCHbyAlgolia
Submit Docs

Remote MCP Server with Auth

repository·main·Indexed 43 days ago

https://github.com/coleam00/remote-mcp-server-with-auth

A production-ready Model Context Protocol (MCP) server deployed on Cloudflare Workers that enables LLMs to interact with a PostgreSQL database. It features GitHub OAuth for secure, role-based access control, supporting read-only queries for all authenticated users and write operations for privileged users. The server supports both Streamable HTTP and Server-Sent Events (SSE) transports and includes optional Sentry integration for error tracking.

Tokens
13K
Snippets
29
Records
51
Agent score
52%

What's inside remote-mcp-server-with-auth

  1. Overview of Cloudflare Remote PostgreSQL MCP Server

    main

    This project is a production-ready Model Context Protocol (MCP) server that allows you to chat with a PostgreSQL database. It is designed to be deployed as a remote server on Cloudflare Workers and uses GitHub OAuth for secure, role-based access control.

    Key features include:

    • Database Integration: Direct PostgreSQL connection for all tool calls.
    • Role-Based Access: Permissions are managed via GitHub usernames (read access for all authenticated users, write access for specific privileged users).
    • Schema Discovery: Automatic retrieval of table and column information.
    • Security: Built-in SQL injection protection and query validation.
    • Transport Support: Supports both modern Streamable HTTP (/mcp) and legacy Server-Sent Events (/sse).

  2. Differentiate between a PRP and a PRD

    main

    While a traditional PRD focuses on what a product does and why customers need it (avoiding implementation details), a PRP includes three critical layers required for AI-driven development:

    1. Context: Precise file paths, content, library versions, and code snippets. This often involves using an ai_docs/ directory to pipe in relevant library documentation.
    2. Implementation Details and Strategy: Explicit instructions on how to build the feature, including specific API endpoints, test runners, architectural patterns, typehints, and agent patterns (e.g., ReAct, Plan-and-Execute).
    3. Validation Gates: Deterministic checks to ensure quality, such as pytest, ruff, or static type passes. A validation gate might be defined as: "Each new function should be individually tested; Validation gate = all tests pass."

  3. Implement the Base MCP Server Class Pattern

    main

    When building an MCP server using TypeScript and Cloudflare Workers, extend the McpAgent class. This pattern centralizes server initialization, tool/resource registration, and lifecycle management (especially for Durable Objects).

    Key lifecycle methods to implement:

    • init(): Call this to trigger registerTools() and registerResources().
    • cleanup(): Critical for Durable Objects. Use this to close database connections and release resources.
    • alarm(): Use this to trigger the cleanup() method automatically via the Durable Object alarm mechanism.
    import { McpAgent } from "agents/mcp";
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { z } from "zod";

type Props = {
  login: string;
  name: string;
  email: string;
  accessToken: string;
};

export class CustomMCP extends McpAgent<Env, Record<string, never>, Props> {
  server = new McpServer({
    name: "Your MCP Server Name",
    version: "1.0.0",
  });

  async cleanup(): Promise<void> {
    try {
      await closeDb();
      console.log('Database connections closed successfully');
    } catch (error) {
      console.error('Error during database cleanup:', error);
    }
  }

  async alarm(): Promise<void> {
    await this.cleanup();
  }

  async init() {
    this.registerTools();
    this.registerResources();
  }

  private registerTools() { /* ... */ }
  private registerResources() { /* ... */ }
}

  4. Understand the MCP Server PRP system architecture

    main

    The PRP system is designed to produce MCP servers that follow a specific, secure architecture:

    • Runtime: Cloudflare Workers (serverless) with Durable Objects for stateful agent persistence.
    • Authentication: GitHub OAuth 2.0 flow using HMAC-signed cookies for secure approval and permission management.
    • Database: PostgreSQL integration with connection pooling and built-in SQL injection protection.
    • Validation: A multi-stage validation loop that checks syntax, local execution via wrangler dev, MCP Inspector compatibility, and OAuth flow integrity.

  5. Implement critical Cloudflare Workers and MCP patterns

    main

    When building an MCP server on Cloudflare Workers, you must follow these six critical patterns to ensure stability, security, and proper resource management:

    1. Durable Object Cleanup: Always implement a cleanup() method to close database connections and handle alarm() events.
    2. SQL Injection Protection: Use validateSqlQuery(sql) from src/database.ts to validate all queries before execution.
    3. Permission Validation: Explicitly check user permissions (e.g., against an allowed set of usernames) before performing sensitive operations.
    4. Connection Management: Use the withDatabase wrapper for all database operations to ensure proper connection lifecycle management.
    5. Input Validation: Use zod for all tool input schema definitions.
    6. Environment Interface: Ensure your Env interface in TypeScript exactly matches the environment variables provided by Cloudflare (e.g., DATABASE_URL, GITHUB_CLIENT_ID, OAUTH_KV).
    // 1. ALWAYS implement cleanup for Durable Objects
export class YourMCP extends McpAgent<Env, Record<string, never>, Props> {
  async cleanup(): Promise<void> {
    await closeDb(); // CRITICAL: Close database connections
  }

  async alarm(): Promise<void> {
    await this.cleanup(); // CRITICAL: Handle Durable Object alarms
  }
}

// 2. ALWAYS validate SQL to prevent injection (use existing patterns)
const validation = validateSqlQuery(sql); // from src/database.ts
if (!validation.isValid) {
  return createErrorResponse(validation.error);
}

// 3. ALWAYS check permissions before sensitive operations
const ALLOWED_USERNAMES = new Set(["admin1", "admin2"]);
if (!ALLOWED_USERNAMES.has(this.props.login)) {
  return createErrorResponse("Insufficient permissions");
}

// 4. ALWAYS use withDatabase wrapper for connection management
return await withDatabase(this.env.DATABASE_URL, async (db) => {
  // Database operations here
});

// 5. ALWAYS use Zod for input validation
import { z } from "zod";
const schema = z.object({
  param: z.string().min(1).max(100),
});

// 6. TypeScript compilation requires exact interface matching
interface Env {
  DATABASE_URL: string;
  GITHUB_CLIENT_ID: string;
  GITHUB_CLIENT_SECRET: string;
  OAUTH_KV: KVNamespace;
  // Add your environment variables here
}

  6. Understand the Product Requirement Prompt (PRP) Concept

    main

    A Product Requirement Prompt (PRP) is a structured prompt designed to provide an AI coding agent with the minimum viable packet required to deliver a vertical slice of working software. It combines the goal-oriented scope of a traditional Product Requirements Document (PRD) with specific technical context and implementation instructions to prevent AI coding attempts from stalling.

    A PRP is defined as: PRD + curated codebase intelligence + agent/runbook.

  7. Security Best Practices for MCP Servers

    main

    To maintain a secure MCP server, follow these four pillars:

    1. Input Validation: Always use zod to parse and validate tool parameters. Never trust raw input.
    2. SQL Injection Prevention: Use validateSqlQuery() before execution and prefer parameterized queries over string concatenation.
    3. Access Control: Do not rely solely on tool registration. For sensitive operations, explicitly check user permissions (e.g., via a hasPermission(username, operation) helper) inside the tool implementation.
    4. Error Sanitization: Use formatDatabaseError() to ensure internal system details or raw database errors are not leaked to the LLM/client. Log detailed errors to the server console instead.

  8. How the MCP Server tools and security model work

    main

    The server provides three primary tools for interacting with your database. Access to these tools is determined by your GitHub authentication status and username:

    1. listTables: Retrieves database schema and table information. Available to all authenticated users.
    2. queryDatabase: Executes read-only SQL queries. Available to all authenticated users.
    3. executeDatabase: Executes write operations (e.g., INSERT, UPDATE, DELETE). Available to privileged users only (specific GitHub usernames configured in the server).

    Authentication Flow: Users authenticate via GitHub OAuth. The server validates the user's identity and grants tool access based on their GitHub username.

  9. Explore the PRP system file structure

    main

    The PRP system is organized into commands, templates, and an AI documentation library:

    • .claude/commands/: Contains the logic for /prp-mcp-create and /prp-mcp-execute.
    • PRPs/templates/: Contains the base templates (prp_mcp_base.md, prp_mcp_server.md) used to generate new requirements.
    • PRPs/ai_docs/: A library of specialized documentation used by the AI to ensure high-quality implementation:
      • mcp_patterns.md: Core development patterns.
      • cloudflare_workers_setup.md: Deployment and environment guides.
      • oauth_integration.md: GitHub OAuth and security patterns.
    • CLAUDE.md: The primary implementation guide for the project.

  10. Set up Cloudflare KV and Secrets

    main

    The server requires a KV namespace for OAuth state management and several secrets for security. Follow these steps:

    1. Create KV Namespace: Run the following command to create the OAUTH_KV namespace:

    wrangler kv namespace create "OAUTH_KV"

    Then, copy the returned ID into the kv_namespaces section of your wrangler.jsonc file.

    2. Set Production Secrets: Use Wrangler to upload sensitive credentials to Cloudflare:

    wrangler secret put GITHUB_CLIENT_ID
wrangler secret put GITHUB_CLIENT_SECRET
wrangler secret put DATABASE_URL
wrangler secret put COOKIE_ENCRYPTION_KEY

  11. Create and execute an MCP server using PRP commands

    main

    The Product Requirement Prompt (PRP) system allows you to automate the creation and deployment of production-ready MCP servers. You use two primary commands within Claude Code to manage the lifecycle of a new server.

    1. Create a PRP

    Use /prp-mcp-create followed by a description of your desired server. Claude will research patterns, use the PRPs/templates/prp_mcp_base.md template, and generate a comprehensive requirement document.

    2. Execute the PRP

    Once the document is generated, use /prp-mcp-execute [filename.md] to begin the implementation. This triggers a multi-stage process including project setup, tool implementation (standard, authenticated, or database-integrated), and a 6-level validation loop (TypeScript compilation, wrangler dev testing, MCP Inspector testing, OAuth verification, and production deployment).

    # 1. Create the PRP
/prp-mcp-create "Build an MCP server for weather data with caching"

# 2. Execute the generated PRP
/prp-mcp-execute weather-mcp-server.md

  12. Implement Conditional Tool Registration based on Permissions

    main

    To restrict access to sensitive tools, perform registration conditionally during the server's initialization phase. Check the user's identity (e.g., this.props.login) against an allowed list before calling this.server.tool().

    const ALLOWED_USERNAMES = new Set(['admin1', 'admin2']);

if (ALLOWED_USERNAMES.has(this.props.login)) {
  this.server.tool(
    "privilegedTool",
    "Tool only available to authorized users",
    { /* parameters */ },
    async (params) => {
      return { content: [{ type: "text", text: `Executed by: ${this.props.login}` }] };
    }
  );
}