Skip to content
Back to search

remote-mcp-server-with-auth

Template for a remote MCP server with GitHub OAuth - following best practices for building MCP servers so you can take this as a starting point for any MCP server you want to build!

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today
Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/coleam00--remote-mcp-server-with-auth/badge.svg)](/listings/coleam00--remote-mcp-server-with-auth)

HTML

<a href="/listings/coleam00--remote-mcp-server-with-auth"><img src="/api/listings/coleam00--remote-mcp-server-with-auth/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

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

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 427 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

  1. # Cloudflare Remote PostgreSQL Database MCP Server + GitHub OAuth
  2. This is a [Model Context Protocol (MCP)](https://modelcontextprotocol.io/introduction) server that enables you to **chat with your PostgreSQL database**, deployable as a remote MCP server with GitHub OAuth through Cloudflare. This is production ready MCP.
  3. ## Key Features
  4. - **🗄️ Database Integration with Lifespan**: Direct PostgreSQL database connection for all MCP tool calls
  5. - **🛠️ Modular, Single Purpose Tools**: Following best practices around MCP tools and their descriptions
  6. - **🔐 Role-Based Access**: GitHub username-based permissions for database write operations
  7. - **📊 Schema Discovery**: Automatic table and column information retrieval
  8. - **🛡️ SQL Injection Protection**: Built-in validation and sanitization
  9. - **📈 Monitoring**: Optional Sentry integration for production monitoring
  10. - **☁️ Cloud Native**: Powered by [Cloudflare Workers](https://developers.cloudflare.com/workers/) for global scale
  11. ## Modular Architecture
  12. This MCP server uses a clean, modular architecture that makes it easy to extend and maintain:
  13. - **`src/tools/`** - Individual tool implementations in separate files
  14. - **`registerAllTools()`** - Centralized tool registration system
  15. - **Extensible Design** - Add new tools by creating files in `tools/` and registering them
  16. This architecture allows you to easily add new database operations, external API integrations, or any other MCP tools while keeping the codebase organized and maintainable.
  17. ## Transport Protocols
  18. This MCP server supports both modern and legacy transport protocols:
  19. - **`/mcp` - Streamable HTTP** (recommended): Uses a single endpoint with bidirectional communication, automatic connection upgrades, and better resilience for network interruptions
  20. - **`/sse` - Server-Sent Events** (legacy): Uses separate endpoints for requests/responses, maintained for backward compatibility
  21. For new implementations, use the `/mcp` endpoint as it provides better performance and reliability.
  22. ## How It Works
  23. The MCP server provides three main tools for database interaction:
  24. 1. **`listTables`** - Get database schema and table information (all authenticated users)
  25. 2. **`queryDatabase`** - Execute read-only SQL queries (all authenticated users)
  26. 3. **`executeDatabase`** - Execute write operations like INSERT/UPDATE/DELETE (privileged users only)
  27. **Authentication Flow**: Users authenticate via GitHub OAuth → Server validates permissions → Tools become available based on user's GitHub username.
  28. **Security Model**:
  29. - All authenticated GitHub users can read data
  30. - Only specific GitHub usernames can write/modify data
  31. - SQL injection protection and query validation built-in
  32. ## Simple Example First
  33. Want to see a basic MCP server before diving into the full database implementation? Check out `src/simple-math.ts` - a minimal MCP server with a single `calculate` tool that performs basic math operations (add, subtract, multiply, divide). This example demonstrates the core MCP components: server setup, tool definition with Zod schemas, and dual transport support (`/mcp` and `/sse` endpoints). You can run it locally with `wrangler dev --config wrangler-simple.jsonc` and test at `http://localhost:8789/mcp`.
  34. ## Prerequisites
  35. - Node.js installed on your machine
  36. - A Cloudflare account (free tier works)
  37. - A GitHub account for OAuth setup
  38. - A PostgreSQL database (local or hosted)
  39. ## Getting Started
  40. ### Step 1: Install Wrangler CLI
  41. Install Wrangler globally to manage your Cloudflare Workers:
  42. ```bash
  43. npm install -g wrangler
  44. ```
  45. ### Step 2: Authenticate with Cloudflare
  46. Log in to your Cloudflare account:
  47. ```bash
  48. wrangler login
  49. ```
  50. This will open a browser window where you can authenticate with your Cloudflare account.
  51. ### Step 3: Clone and Setup
  52. Clone the repo directly & install dependencies: `npm install`.
  53. ## Environment Variables Setup
  54. Before running the MCP server, you need to configure several environment variables for authentication and database access.
  55. ### Create Environment Variables File
  56. 1. **Create your `.dev.vars` file** from the example:
  57. ```bash
  58. cp .dev.vars.example .dev.vars
  59. ```
  60. 2. **Configure all required environment variables** in `.dev.vars`:
  61. ```
  62. # GitHub OAuth (for authentication)
  63. GITHUB_CLIENT_ID=your_github_client_id
  64. GITHUB_CLIENT_SECRET=your_github_client_secret
  65. COOKIE_ENCRYPTION_KEY=your_random_encryption_key
  66. # Database Connection
  67. DATABASE_URL=postgresql://username:password@localhost:5432/database_name
  68. # Optional: Sentry monitoring
  69. SENTRY_DSN=https://your-sentry-dsn@sentry.io/project-id
  70. NODE_ENV=development
  71. ```
  72. ### Getting GitHub OAuth Credentials
  73. 1. **Create a GitHub OAuth App** for local development:
  74. - Go to [GitHub Developer Settings](https://github.com/settings/developers)
  75. - Click "New OAuth App"
  76. - **Application name**: `MCP Server (Local Development)`
  77. - **Homepage URL**: `http://localhost:8792`
  78. - **Authorization callback URL**: `http://localhost:8792/callback`
  79. - Click "Register application"
  80. 2. **Copy your credentials**:
  81. - Copy the **Client ID** and paste it as `GITHUB_CLIENT_ID` in `.dev.vars`
  82. - Click "Generate a new client secret", copy it, and paste as `GITHUB_CLIENT_SECRET` in `.dev.vars`
  83. ### Generate Encryption Key
  84. Generate a secure random encryption key for cookie encryption:
  85. ```bash
  86. openssl rand -hex 32
  87. ```
  88. Copy the output and paste it as `COOKIE_ENCRYPTION_KEY` in `.dev.vars`.
  89. ## Database Setup
  90. 1. **Set up PostgreSQL** using a hosted service like:
  91. - [Supabase](https://supabase.com/) (recommended for beginners)
  92. - [Neon](https://neon.tech/)
  93. - Or use local PostgreSQL/Supabase
  94. 2. **Update the DATABASE_URL** in `.dev.vars` with your connection string:
  95. ```
  96. DATABASE_URL=postgresql://username:password@host:5432/database_name
  97. ```
  98. #### Connection String Examples:
  99. - **Local**: `postgresql://myuser:mypass@localhost:5432/mydb`
  100. - **Supabase**: `postgresql://postgres:your-password@db.your-project.supabase.co:5432/postgres`
  101. ### Database Schema Setup
  102. The MCP server works with any PostgreSQL database schema. It will automatically discover:
  103. - All tables in the `public` schema
  104. - Column names, types, and constraints
  105. - Primary keys and indexes
  106. **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.
  107. ## Local Development & Testing
  108. **Run the server locally**:
  109. ```bash
  110. wrangler dev
  111. ```
  112. This makes the server available at `http://localhost:8792`
  113. ### Testing with MCP Inspector
  114. Use the [MCP Inspector](https://modelcontextprotocol.io/docs/tools/inspector) to test your server:
  115. 1. **Install and run Inspector**:
  116. ```bash
  117. npx @modelcontextprotocol/inspector@latest
  118. ```
  119. 2. **Connect to your local server**:
  120. - **Preferred**: Enter URL: `http://localhost:8792/mcp` (streamable HTTP transport - newer, more robust)
  121. - **Alternative**: Enter URL: `http://localhost:8792/sse` (SSE transport - legacy support)
  122. - Click "Connect"
  123. - Follow the OAuth prompts to authenticate with GitHub
  124. - Once connected, you'll see the available tools
  125. 3. **Test the tools**:
  126. - Use `listTables` to see your database structure
  127. - Use `queryDatabase` to run SELECT queries
  128. - Use `executeDatabase` (if you have write access) for INSERT/UPDATE/DELETE operations
  129. ## Production Deployment
  130. #### Set up a KV namespace
  131. - Create the KV namespace:
  132. `wrangler kv namespace create "OAUTH_KV"`
  133. - Update the `wrangler.jsonc` file with the KV ID (replace <Add-KV-ID>)
  134. #### Deploy
  135. Deploy the MCP server to make it available on your workers.dev domain
  136. ```bash
  137. wrangler deploy
  138. ```
  139. ### Create environment variables in production
  140. Create a new [GitHub OAuth App](https://docs.github.com/en/apps/oauth-apps/building-oauth-apps/creating-an-oauth-app):
  141. - For the Homepage URL, specify `https://mcp-github-oauth.<your-subdomain>.workers.dev`
  142. - For the Authorization callback URL, specify `https://mcp-github-oauth.<your-subdomain>.workers.dev/callback`
  143. - Note your Client ID and generate a Client secret.
  144. - Set all required secrets via Wrangler:
  145. ```bash
  146. wrangler secret put GITHUB_CLIENT_ID
  147. wrangler secret put GITHUB_CLIENT_SECRET
  148. wrangler secret put COOKIE_ENCRYPTION_KEY # use: openssl rand -hex 32
  149. wrangler secret put DATABASE_URL
  150. wrangler secret put SENTRY_DSN # optional (more on Sentry setup below)
  151. ```
  152. #### Test
  153. Test the remote server using [Inspector](https://modelcontextprotocol.io/docs/tools/inspector):
  154. ```
  155. npx @modelcontextprotocol/inspector@latest
  156. ```
  157. Enter `https://mcp-github-oauth.<your-subdomain>.workers.dev/mcp` (preferred) or `https://mcp-github-oauth.<your-subdomain>.workers.dev/sse` (legacy) and hit connect. Once you go through the authentication flow, you'll see the Tools working:
  158. <img width="640" alt="image" src="https://github.com/user-attachments/assets/7973f392-0a9d-4712-b679-6dd23f824287" />
  159. You now have a remote MCP server deployed!
  160. ## Database Tools & Access Control
  161. ### Available Tools
  162. #### 1. `listTables` (All Users)
  163. **Purpose**: Discover database schema and structure
  164. **Access**: All authenticated GitHub users
  165. **Usage**: Always run this first to understand your database structure
  166. ```
  167. Example output:
  168. - Tables: users, products, orders
  169. - Columns: id (integer), name (varchar), created_at (timestamp)
  170. - Constraints and relationships
  171. ```
  172. #### 2. `queryDatabase` (All Users)
  173. **Purpose**: Execute read-only SQL queries
  174. **Access**: All authenticated GitHub users
  175. **Restrictions**: Only SELECT statements and read operations allowed
  176. ```sql
  177. -- Examples of allowed queries:
  178. SELECT * FROM users WHERE created_at > '2024-01-01';
  179. SELECT COUNT(*) FROM products;
  180. SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
  181. ```
  182. #### 3. `executeDatabase` (Privileged Users Only)
  183. **Purpose**: Execute write operations (INSERT, UPDATE, DELETE, DDL)
  184. **Access**: Restricted to specific GitHub usernames
  185. **Capabilities**: Full database write access including schema modifications
  186. ```sql
  187. -- Examples of allowed operations:
  188. INSERT INTO users (name, email) VALUES ('New User', 'user@example.com');
  189. UPDATE products SET price = 29.99 WHERE id = 1;
  190. DELETE FROM orders WHERE status = 'cancelled';
  191. CREATE TABLE new_table (id SERIAL PRIMARY KEY, data TEXT);
  192. ```
  193. ### Access Control Configuration
  194. Database write access is controlled by GitHub username in the `ALLOWED_USERNAMES` configuration:
  195. ```typescript
  196. // Add GitHub usernames for database write access
  197. const ALLOWED_USERNAMES = new Set([
  198. 'yourusername', // Replace with your GitHub username
  199. 'teammate1', // Add team members who need write access
  200. 'database-admin' // Add other trusted users
  201. ]);
  202. ```
  203. **To update access permissions**:
  204. 1. Edit `src/index.ts` and `src/index_non_sentry.ts`
  205. 2. Update the `ALLOWED_USERNAMES` set with GitHub usernames
  206. 3. Redeploy the worker: `wrangler deploy`
  207. ### Typical Workflow
  208. 1. **🔍 Discover**: Use `listTables` to understand database structure
  209. 2. **📊 Query**: Use `queryDatabase` to read and analyze data
  210. 3. **✏️ Modify**: Use `executeDatabase` (if you have write access) to make changes
  211. ### Security Features
  212. - **SQL Injection Protection**: All queries are validated before execution
  213. - **Operation Type Detection**: Automatic detection of read vs write operations
  214. - **User Context Tracking**: All operations are logged with GitHub user information
  215. - **Connection Pooling**: Efficient database connection management
  216. - **Error Sanitization**: Database errors are cleaned before being returned to users
  217. ### Access the remote MCP server from Claude Desktop
  218. Open Claude Desktop and navigate to Settings -> Developer -> Edit Config. This opens the configuration file that controls which MCP servers Claude can access.
  219. 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.
  220. ```
  221. {
  222. "mcpServers": {
  223. "math": {
  224. "command": "npx",
  225. "args": [
  226. "mcp-remote",
  227. "https://mcp-github-oauth.<your-subdomain>.workers.dev/mcp"
  228. ]
  229. }
  230. }
  231. }
  232. ```
  233. Once the Tools (under 🔨) show up in the interface, you can ask Claude to interact with your database. Example commands:
  234. - **"What tables are available in the database?"** → Uses `listTables` tool
  235. - **"Show me all users created in the last 30 days"** → Uses `queryDatabase` tool
  236. - **"Add a new user named John with email john@example.com"** → Uses `executeDatabase` tool (if you have write access)
  237. ### Using Claude and other MCP Clients
  238. When using Claude to connect to your remote MCP server, you may see some error messages. This is because Claude Desktop doesn't yet support remote MCP servers, so it sometimes gets confused. To verify whether the MCP server is connected, hover over the 🔨 icon in the bottom right corner of Claude's interface. You should see your tools available there.
  239. #### Using Cursor and other MCP Clients
  240. To connect Cursor with your MCP server, choose `Type`: "Command" and in the `Command` field, combine the command and args fields into one (e.g. `npx mcp-remote https://<your-worker-name>.<your-subdomain>.workers.dev/sse`).
  241. Note that while Cursor supports HTTP+SSE servers, it doesn't support authentication, so you still need to use `mcp-remote` (and to use a STDIO server, not an HTTP one).
  242. You can connect your MCP server to other MCP clients like Windsurf by opening the client's configuration file, adding the same JSON that was used for the Claude setup, and restarting the MCP client.
  243. ## Sentry Integration (Optional)
  244. This project includes optional Sentry integration for comprehensive error tracking, performance monitoring, and distributed tracing. There are two versions available:
  245. - `src/index.ts` - Standard version without Sentry
  246. - `src/index_sentry.ts` - Version with full Sentry integration
  247. ### Setting Up Sentry
  248. 1. **Create a Sentry Account**: Sign up at [sentry.io](https://sentry.io) if you don't have an account.
  249. 2. **Create a New Project**: Create a new project in Sentry and select "Cloudflare Workers" as the platform (search in the top right).
  250. 3. **Get Your DSN**: Copy the DSN from your Sentry project settings.
  251. ### Using Sentry in Production
  252. To deploy with Sentry monitoring:
  253. 1. **Set the Sentry DSN secret**:
  254. ```bash
  255. wrangler secret put SENTRY_DSN
  256. ```
  257. Enter your Sentry DSN when prompted.
  258. 2. **Update your wrangler.toml** to use the Sentry-enabled version:
  259. ```toml
  260. main = "src/index_sentry.ts"
  261. ```
  262. 3. **Deploy with Sentry**:
  263. ```bash
  264. wrangler deploy
  265. ```
  266. ### Using Sentry in Development
  267. 1. **Add Sentry DSN to your `.dev.vars` file**:
  268. ```
  269. SENTRY_DSN=https://your-sentry-dsn@sentry.io/project-id
  270. NODE_ENV=development
  271. ```
  272. 2. **Run with Sentry enabled**:
  273. ```bash
  274. wrangler dev
  275. ```
  276. ### Sentry Features Included
  277. - **Error Tracking**: Automatic capture of all errors with context
  278. - **Performance Monitoring**: Full request tracing with 100% sample rate
  279. - **User Context**: Automatically binds GitHub user information to events
  280. - **Tool Tracing**: Each MCP tool call is traced with parameters
  281. - **Custom Error Handling**: User-friendly error messages with Event IDs
  282. - **Context Enrichment**: Automatic tagging and context for better debugging
  283. ## How does it work?
  284. #### OAuth Provider
  285. 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:
  286. - Authenticating MCP clients that connect to your server
  287. - Managing the connection to GitHub's OAuth services
  288. - Securely storing tokens and authentication state in KV storage
  289. #### Durable MCP
  290. Durable MCP extends the base MCP functionality with Cloudflare's Durable Objects, providing:
  291. - Persistent state management for your MCP server
  292. - Secure storage of authentication context between requests
  293. - Access to authenticated user information via `this.props`
  294. - Support for conditional tool availability based on user identity
  295. #### MCP Remote
  296. The MCP Remote library enables your server to expose tools that can be invoked by MCP clients like the Inspector. It:
  297. - Defines the protocol for communication between clients and your server
  298. - Provides a structured way to define tools
  299. - Handles serialization and deserialization of requests and responses
  300. - Maintains the Server-Sent Events (SSE) connection between clients and your server
  301. ## Testing
  302. This project includes comprehensive unit tests covering all major functionality:
  303. ```bash
  304. npm test # Run all tests
  305. npm run test:ui # Run tests with UI
  306. ```
  307. The test suite covers database security, tool registration, permission handling, and response formatting with proper mocking of external dependencies.

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.