remote -with-auth

This MCP server enables you to chat with and operate a PostgreSQL database through MCP tools guarded by GitHub OAuth-based permissions. It exposes three core tools: listTables, queryDatabase, and executeDatabase. All read operations via listTables and queryDatabase are available to any authenticated user, while write operations through executeDatabase are restricted to privileged GitHub usernames. The server supports two transport endpoints: /mcp (streamable HTTP) and /sse (server-sent events). To get started, authenticate with GitHub through the OAuth flow, then use the MCP Inspector or any MCP client to call the tools and explore your database schema. For production usage, ensure your environment variables are configured and that write permissions align with your security requirements. The server automatically discovers your PostgreSQL schema, including tables, columns, primary keys, and indexes, and performs basic SQL injection protection and query validation before execution.

Prerequisites:\n- Node.js installed on your machine (recommended v14+).\n- A Cloudflare account and wrangler CLI for local development and deployment.\n- A PostgreSQL database (local or hosted).\n- GitHub OAuth application setup for authentication.\n\nStep 1: Install Wrangler CLI (Cloudflare Workers management)\nbash\nnpm install -g wrangler\n\n\nStep 2: Authenticate with Cloudflare\nbash\nwrangler login\n\n\nStep 3: Prepare environment and clone the project\nbash\ngit clone https://github.com/coleam00/remote-mcp-server-with-auth.git\ncd remote-mcp-server-with-auth\nnpm install\n\n\nStep 4: Configure environment variables\nCreate and populate a .dev.vars file (or use your preferred env method) with:\nbash\n# GitHub OAuth (for authentication)\nGITHUB_CLIENT_ID=your_github_client_id\nGITHUB_CLIENT_SECRET=your_github_client_secret\nCOOKIE_ENCRYPTION_KEY=your_random_encryption_key\n\n# Database Connection\nDATABASE_URL=postgresql://username:password@host:5432/database_name\n\n# Optional: Sentry monitoring\nSENTRY_DSN=https://your-sentry-dsn@sentry.io/project-id\nNODE_ENV=development\n\n\nStep 5: Run the server locally (Cloudflare Workers)\nbash\nwrangler dev\n\nThis will start a local server typically at http://localhost:8789 or a port shown by wrangler. You can test the MCP endpoint at http://localhost:8789/mcp or http://localhost:8789/sse depending on the transport you choose.\n\nStep 6: Test with MCP Inspector\nInstall and run the inspector: bash\nnpx @modelcontextprotocol/inspector@latest\n\nConnect to http://localhost:8789/mcp (streamable) or http://localhost:8789/sse (legacy). Authenticate via GitHub and explore the tools.\n

Tips and considerations:\n- Ensure GITHUB_CLIENT_ID and GITHUB_CLIENT_SECRET are kept secure and not committed to version control.\n- The DATABASE_URL must be accessible from your Cloudflare Worker environment.\n- For production, configure SENTRY_DSN or disable monitoring by leaving it undefined.\n- Review the list of allowed writers and restrict the executeDatabase tool to specific GitHub usernames.\n- If you upgrade PostgreSQL or change schemas, re-run schema discovery to refresh internal metadata.\n- For large datasets, prefer streaming /mcp mode to reduce latency and improve reliability.\n- When using Cloudflare Workers, be mindful of request size limits and KV usage if you store OAuth tokens or session data.\n