{
  "markdown": "# MCP Gateway\n\n![Tests](https://github.com/PanosSalt/MCP-Gateway/actions/workflows/ci.yml/badge.svg)\n![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)\n![Python 3.12](https://img.shields.io/badge/python-3.12-blue.svg)\n\nThe production platform for MCP tools.\n\nClaude Desktop can connect to your internal tools — databases, filesystems, APIs, anything — through a single authenticated endpoint. You control who can use which tools, every action is logged, and no raw credentials ever leave your server.\n\nBuilt-in tools: SQL query (Postgres, MySQL, SQLite, MSSQL), filesystem access.\nCustom tools: plug in anything that implements the MCP tool interface.\n\n> **[See it in action](docs/media/local_demo.mp4)** — short demo of Claude Desktop querying a database through MCP Gateway.\n\n## Table of Contents\n\n- [Overview](#overview)\n- [Features](#features)\n- [Architecture](#architecture)\n- [Quick Start](#quick-start)\n- [Configuration](#configuration)\n- [Authentication](#authentication)\n- [API Reference](#api-reference)\n- [MCP Integration](#mcp-integration)\n- [Role-Based Access Control](#role-based-access-control)\n- [Entra ID / SSO](#entra-id--sso)\n- [Development](#development)\n- [Troubleshooting](#troubleshooting)\n- [Security](#security)\n- [Additional Documentation](#additional-documentation)\n\n---\n\n## Overview\n\nMCP Gateway sits between AI assistants and your databases. It:\n\n1. Authenticates users via password login, Microsoft Entra ID (Azure AD), or API keys\n2. Enforces role-based access control (viewer / analyst / admin)\n3. Exposes databases as MCP tools that AI assistants can discover and call\n4. Translates natural language questions into SQL via Claude, executes queries, and summarizes results\n5. Logs all activity to a structured audit trail\n\n```\nClaude Desktop / mcp-remote\n        │\n        │ MCP over SSE (OAuth 2.1 + PKCE)\n        ▼\n┌─────────────────────────────────────────────────────────┐\n│                      MCP Gateway                        │\n│                                                         │\n│  ┌──────────┐  ┌──────────┐  ┌───────────────────────┐ │\n│  │ Auth /   │  │  Admin   │  │   MCP SSE Endpoint    │ │\n│  │ OAuth    │  │   UI     │  │  /t/{slug}/mcp/sse    │ │\n│  └──────────┘  └──────────┘  └───────────────────────┘ │\n│                                          │              │\n│  ┌──────────────────────────────────────┐│              │\n│  │         Tool Providers               ││              │\n│  │  sql.py → get_schema / execute_sql   ││              │\n│  └──────────────────────────────────────┘│              │\n└─────────────────────────────────────────┼───────────────┘\n                                          │ Decrypted DSN\n                    ┌─────────────────────┼────────────────┐\n                    │   Your Databases    │                │\n                    │  Postgres  MySQL  MSSQL  SQLite      │\n                    └────────────────────────────────────  ┘\n```\n\n---\n\n## What you get out of the box\n\n**For your organisation**\n- One URL for Claude Desktop — users authenticate once, access everything they're allowed\n- Microsoft Entra ID SSO — roles assigned automatically from Azure AD groups\n- Full audit trail — every tool call, every query, every login, who did what and when\n\n**For your tools**\n- Drop any MCP tool into the gateway and it inherits auth, RBAC, and logging automatically\n- Per-tool role overrides — restrict SQL execution to analysts, filesystem writes to admins\n- Bundled: SQL tools (4 databases), filesystem tools (read, write, search, tree)\n\n**For your security team**\n- No credentials on employee machines\n- Tenant isolation — org A cannot see org B's tools or data\n- API keys for CI/CD, OAuth 2.1 + PKCE for human users\n\n### Supported Databases\n| Database | Driver | DSN Format |\n|----------|--------|------------|\n| PostgreSQL | psycopg2 | `postgresql://user:pass@host/db` |\n| MySQL / MariaDB | PyMySQL | `mysql+pymysql://user:pass@host/db` |\n| Microsoft SQL Server | pymssql | `mssql+pymssql://user:pass@host/db` |\n| SQLite | Built-in | `sqlite:///path/to/file.db` |\n\n### Filesystem Tools\n- Sandboxed file read/write/search exposed as MCP tools\n- Enabled via `FILESYSTEM_ALLOWED_DIRS` environment variable\n- Read operations (analyst+): `fs_read_file`, `fs_list_directory`, `fs_directory_tree`, `fs_search_files`, `fs_get_file_info`\n- Write operations (admin): `fs_write_file`, `fs_create_directory`, `fs_move_file`\n\n### Admin UI\n- Web interface served at `/admin/`\n- Manage connections, users, SSO config, API keys, and tool roles\n- View audit logs, generated SQL, and query results\n\n---\n\n## Architecture\n\n### Technology Stack\n\n| Layer | Technology | Version |\n|-------|-----------|---------|\n| API Framework | FastAPI | 0.131.0 |\n| ASGI Server | Uvicorn | 0.34.0 |\n| ORM | SQLAlchemy | 2.0.30 |\n| Migrations | Alembic | 1.13.1 |\n| Auth / JWT | PyJWT + bcrypt | 2.12.0 / 4.0.1 |\n| Encryption | cryptography (Fernet) | 46.0.5 |\n| LLM | Anthropic SDK | 0.42.0 |\n| MCP Protocol | mcp | 1.23.0 |\n| SQL Validation | sqlglot | 25.1.0 |\n| Rate Limiting | slowapi | 0.1.9 |\n| Frontend | React 18 + TypeScript + Vite | — |\n\n### Project Structure\n\n```\napp/\n├── main.py               # FastAPI app setup, middleware, routing\n├── config.py             # Environment config (Pydantic Settings)\n├── database.py           # SQLAlchemy engine + session factory\n├── api/\n│   ├── auth.py           # POST /auth/login\n│   ├── auth_entra.py     # Entra SSO (legacy admin UI paths)\n│   ├── oauth.py          # OAuth 2.1 endpoints (/t/{slug}/oauth/*)\n│   ├── connections.py    # DB connection CRUD\n│   ├── query.py          # Natural language query endpoint\n│   ├── tenants.py        # Tenant + user management\n│   ├── tools.py          # Tool listing + role overrides\n│   ├── mcp_sse.py        # MCP SSE transport\n│   ├── api_keys.py       # API key management\n│   └── audit_logs.py     # GET /audit-logs/ (admin)\n├── core/\n│   ├── auth.py           # JWT creation/validation, password hashing\n│   ├── dependencies.py   # FastAPI dependency injection\n│   ├── rbac.py           # Role hierarchy helpers\n│   ├── security.py       # Fernet encrypt/decrypt\n│   ├── api_keys.py       # API key generation + hashing\n│   ├── limiter.py        # slowapi rate limiter setup\n│   └── log_filter.py     # Health-check log noise filter\n├── constants.py          # Non-tunable application-wide constants (pagination caps, etc.)\n├── models/__init__.py    # All SQLAlchemy ORM models\n├── schemas/__init__.py   # All Pydantic request/response schemas\n├── services/\n│   ├── entra.py          # Microsoft Graph API client\n│   ├── llm.py            # Anthropic API (SQL gen + summarization)\n│   ├── mcp_client.py     # Direct SQLAlchemy schema introspection + query execution\n│   └── audit.py          # Audit log writer\n└── tools/\n    ├── __init__.py       # Tool provider framework + registry\n    ├── sql.py            # DB schema + execute_sql tools\n    ├── example.py        # Example custom tools\n    └── filesystem.py     # Sandboxed file read/write/search tools\n\nfrontend/src/\n├── App.tsx               # Root component, auth context, tab routing\n├── api.ts                # API client, token management\n├── types.ts              # TypeScript types (mirrors Pydantic schemas)\n├── constants.ts          # Frontend constants (timeouts, retry config)\n└── components/\n    ├── Login.tsx          # Sign-in form\n    ├── Setup.tsx          # Tenant registration\n    ├── Dashboard.tsx      # Tenant info + role display\n    ├── Connections.tsx    # DB connection management\n    ├── Query.tsx          # Natural language query UI\n    ├── Users.tsx          # User management (admin)\n    ├── SsoConfig.tsx      # Entra ID configuration (admin)\n    ├── Tools.tsx          # Tool browser + role overrides\n    ├── ApiKeys.tsx        # API key management\n    └── AuditLog.tsx       # Filterable audit log viewer (admin)\n```\n\n### Database Schema\n\n```\nTenants ─┬─► Users ──────► APIKeys\n         ├─► DBConnections\n         ├─► TenantEntraConfig\n         ├─► AuditLogs\n         ├─► OAuthStates\n         ├─► OAuthAuthorizationCodes\n         ├─► OAuthRefreshTokens\n         └─► ToolRoleOverrides\n```\n\n---\n\n## Quick Start\n\n### Prerequisites\n\n- Docker and Docker Compose\n- An Anthropic API key (for the `/query/` endpoint; not needed for raw MCP tool access)\n\n### 1. Clone and configure\n\n```bash\ngit clone <repo-url>\ncd MCP-Gateway\ncp .env.example .env\n```\n\nEdit `.env`:\n\n```bash\n# Required — generate unique values\nSECRET_KEY=<random 64-char string>\nENCRYPTION_KEY=<random string, min 32 chars — longer is better>\nPOSTGRES_PASSWORD=<strong password>\n\n# Required for natural language query\nANTHROPIC_API_KEY=sk-ant-...\n\n# Update to your server's public URL in production\nBASE_URL=http://localhost:8000\n```\n\nGenerate secure random values:\n\n```bash\n# SECRET_KEY\npython3 -c \"import secrets; print(secrets.token_hex(32))\"\n\n# ENCRYPTION_KEY (min 32 chars; full key consumed via BLAKE2b derivation)\npython3 -c \"import secrets; print(secrets.token_hex(32))\"\n```\n\n### 2. Start the stack\n\n```bash\ndocker compose up -d\n```\n\nServices started:\n- `api` on port **8000** (FastAPI + admin UI)\n- `db` on port 5432 (PostgreSQL, internal only)\n\n### 3. Register your first tenant\n\n```bash\ncurl -s -X POST http://localhost:8000/tenants/ \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"name\": \"My Organization\",\n    \"slug\": \"my-org\",\n    \"admin_email\": \"admin@example.com\",\n    \"admin_password\": \"SuperSecret123!\"\n  }' | jq\n```\n\nThe `slug` becomes part of your MCP URL: `http://localhost:8000/t/my-org/mcp/sse`\n\n### 4. Open the admin UI\n\nNavigate to **http://localhost:8000/admin/** and sign in with your admin credentials.\n\n### 5. Add a database connection\n\nIn the admin UI → **Connections** → **Create connection**, or via API:\n\n```bash\nTOKEN=$(curl -s -X POST http://localhost:8000/auth/login \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\"email\":\"admin@example.com\",\"password\":\"SuperSecret123!\"}' \\\n  | jq -r .access_token)\n\ncurl -s -X POST http://localhost:8000/connections/ \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"name\": \"Production DB\",\n    \"db_type\": \"postgres\",\n    \"connection_string\": \"postgresql://user:pass@host/mydb\",\n    \"min_role\": \"viewer\"\n  }' | jq\n```\n\n### 6. Connect Claude Desktop\n\nAdd to your Claude Desktop MCP config (`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):\n\n```json\n{\n  \"mcpServers\": {\n    \"my-org-gateway\": {\n      \"command\": \"npx\",\n      \"args\": [\n        \"-y\",\n        \"mcp-remote\",\n        \"http://localhost:8000/t/my-org/mcp/sse\"\n      ]\n    }\n  }\n}\n```\n\nRestart Claude Desktop. It will open a browser window for OAuth login. After authenticating, Claude can use your database tools.\n\n---\n\n## Configuration\n\nAll configuration is via environment variables. See `.env.example` for a template.\n\n### Required\n\n| Variable | Description |\n|----------|-------------|\n| `SECRET_KEY` | JWT signing secret — use a random 64-char string |\n| `ENCRYPTION_KEY` | Fernet AES key for DB credentials — minimum 32 characters; full key consumed via BLAKE2b |\n| `POSTGRES_PASSWORD` | PostgreSQL password — used by docker-compose for both the `db` service and `DATABASE_URL` |\n| `DATABASE_URL` | PostgreSQL DSN — set automatically by docker-compose; only needed for local (non-Docker) dev |\n\n### Optional\n\n| Variable | Default | Description |\n|----------|---------|-------------|\n| `ANTHROPIC_API_KEY` | — | Required for `/query/` NL query endpoint |\n| `BASE_URL` | `http://localhost:8000` | Public-facing URL (used in OAuth callbacks) |\n| `CORS_ORIGINS` | `BASE_URL` | Comma-separated allowed origins for CORS. Must be absolute URLs — wildcards (`*`) are rejected |\n| `ACCESS_TOKEN_EXPIRE_MINUTES` | `15` | JWT access token lifetime |\n| `REFRESH_TOKEN_EXPIRE_DAYS` | `30` | OAuth refresh token lifetime |\n| `OAUTH_STATE_TTL_MINUTES` | `10` | OAuth PKCE state validity window — increase for high-latency SSO providers |\n| `OAUTH_CODE_TTL_MINUTES` | `5` | OAuth authorization code validity window |\n| `LLM_MODEL` | `claude-sonnet-4-6` | Anthropic model for SQL generation |\n| `LLM_MAX_TOKENS_SQL` | `1024` | Max tokens for SQL generation |\n| `LLM_MAX_TOKENS_SUMMARY` | `500` | Max tokens for result summarization |\n| `FILESYSTEM_ALLOWED_DIRS` | — | Comma-separated directories the MCP filesystem tools may access. When empty, no filesystem tools are exposed |\n\n### Entra ID\n\n| Variable | Default | Description |\n|----------|---------|-------------|\n| `ENTRA_AUTHORITY_URL` | `https://login.microsoftonline.com` | Microsoft identity platform base URL |\n| `ENTRA_GRAPH_URL` | `https://graph.microsoft.com/v1.0` | Microsoft Graph API base URL |\n\n---\n\n## Authentication\n\n### Local login\n\n```http\nPOST /auth/login\nContent-Type: application/json\n\n{\n  \"email\": \"user@example.com\",\n  \"password\": \"SuperSecret123!\",\n  \"tenant_slug\": \"my-org\"   // optional, disambiguates if same email is in multiple tenants\n}\n```\n\nResponse:\n```json\n{\n  \"access_token\": \"eyJ...\",\n  \"token_type\": \"bearer\"\n}\n```\n\nInclude the token in subsequent requests:\n```\nAuthorization: Bearer eyJ...\n```\n\nAccess tokens expire after 15 minutes by default. Use the OAuth token endpoint with a refresh token to get a new pair.\n\n### API keys\n\nGenerate a key (requires authentication):\n\n```bash\ncurl -s -X POST http://localhost:8000/api-keys/ \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\"name\": \"CI pipeline\"}' | jq\n```\n\nThe `raw_key` in the response is shown **once only** — store it immediately:\n```json\n{\n  \"id\": \"...\",\n  \"name\": \"CI pipeline\",\n  \"prefix\": \"mgw_abcd1234\",\n  \"raw_key\": \"mgw_abcd1234...\",\n  \"created_at\": \"...\"\n}\n```\n\nUse via query parameter:\n```bash\ncurl \"http://localhost:8000/connections/?api_key=mgw_abcd1234...\"\n```\n\n### OAuth 2.1 (MCP browser clients)\n\nThe gateway implements RFC 8414 OAuth discovery. MCP clients follow this flow automatically:\n\n1. Client connects to `/t/{slug}/mcp/sse` — receives 401 + `WWW-Authenticate` header pointing to the OAuth discovery URL\n2. Client fetches `/.well-known/oauth-authorization-server/t/{slug}`\n3. Client registers dynamically via `POST /t/{slug}/oauth/register`\n4. Client opens browser → user logs in at `/t/{slug}/oauth/authorize`\n5. Client exchanges code + PKCE verifier for tokens via `POST /t/{slug}/oauth/token`\n6. Client reconnects with Bearer token\n\nNo manual configuration needed — just point mcp-remote at your tenant's SSE URL.\n\n### API key auth (MCP non-interactive clients)\n\nFor CI/CD, scripts, or when you want to skip the browser login, pass an API key in the URL:\n\n```json\n{\n  \"mcpServers\": {\n    \"gateway\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"mcp-remote\", \"http://localhost:8000/t/my-org/mcp/sse?api_key=mgw_...\"]\n    }\n  }\n}\n```\n\nThe SSE endpoint validates the key and establishes the session directly — no OAuth flow, no browser window. See [API Keys](docs/api-keys.md) for details.\n\n---\n\n## API Reference\n\nAll management endpoints are available at both their canonical paths (e.g. `/tenants/`) and the versioned prefix `/api/v1/` (e.g. `/api/v1/tenants/`). The unversioned paths are kept for backward compatibility with the current frontend; new integrations should use `/api/v1/`. Protocol-defined routes (OAuth `/t/{slug}/…`, MCP `/t/{slug}/…`, `/.well-known/`) and infrastructure routes (`/health`, `/admin`) are intentionally unversioned.\n\n### Tenants & Users\n\n| Method | Path | Role | Description |\n|--------|------|------|-------------|\n| `POST` | `/tenants/` | Public | Register new tenant + admin user |\n| `GET` | `/tenants/me` | Any | Get your tenant details |\n| `GET` | `/tenants/users` | Admin | List all users in your tenant |\n| `POST` | `/tenants/users` | Admin | Create a local user |\n| `PATCH` | `/tenants/users/{id}` | Admin | Update user role |\n\n**Register tenant:**\n```bash\ncurl -X POST http://localhost:8000/tenants/ \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"name\": \"Acme Corp\",\n    \"slug\": \"acme\",\n    \"admin_email\": \"admin@acme.com\",\n    \"admin_password\": \"SuperSecret123!\"\n  }'\n```\n\n**Create user:**\n```bash\ncurl -X POST http://localhost:8000/tenants/users \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"email\": \"analyst@acme.com\",\n    \"password\": \"AnotherSecret456!\",\n    \"role\": \"analyst\"\n  }'\n```\n\nRoles: `viewer` (default), `analyst`, `admin`. Passwords must be at least 12 characters.\n\n---\n\n### Connections\n\n| Method | Path | Role | Description |\n|--------|------|------|-------------|\n| `POST` | `/connections/` | Admin | Add a database connection |\n| `GET` | `/connections/` | Viewer+ | List accessible connections |\n| `PATCH` | `/connections/{id}` | Admin | Update connection |\n| `DELETE` | `/connections/{id}` | Admin | Soft-delete connection |\n\n**Add connection:**\n```bash\ncurl -X POST http://localhost:8000/connections/ \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"name\": \"Sales DB\",\n    \"db_type\": \"postgres\",\n    \"connection_string\": \"postgresql://user:pass@db-host/sales\",\n    \"description\": \"Production sales database\",\n    \"min_role\": \"analyst\"\n  }'\n```\n\n`min_role` controls who can query this connection. Users below this role cannot see or use it.\n\n---\n\n### Natural Language Query\n\n| Method | Path | Role | Rate Limit | Description |\n|--------|------|------|-----------|-------------|\n| `POST` | `/query/` | Analyst+ | 30/min | Execute NL query |\n| `GET` | `/query/history` | Admin | 60/min | Paginated query audit history |\n\n**Query:**\n```bash\ncurl -X POST http://localhost:8000/query/ \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"connection_id\": \"...\",\n    \"question\": \"What are the top 5 customers by total revenue this quarter?\"\n  }'\n```\n\nResponse:\n```json\n{\n  \"sql_generated\": \"SELECT customer_name, SUM(amount) AS total FROM orders ...\",\n  \"result\": [\n    {\"customer_name\": \"Acme Corp\", \"total\": 125000}\n  ],\n  \"summary\": \"The top customer this quarter is Acme Corp with $125,000 in revenue.\"\n}\n```\n\nThe query pipeline:\n1. Fetches schema from the database\n2. Sends schema + question to Claude → generates SQL\n3. Validates SQL is a `SELECT` statement (blocks all writes)\n4. Executes SQL (30-second timeout)\n5. Sends question + results to Claude → generates summary\n\n---\n\n### Tools\n\n| Method | Path | Role | Description |\n|--------|------|------|-------------|\n| `GET` | `/tools/` | Any | List MCP tools with role metadata |\n| `PATCH` | `/tools/{tool_name}` | Admin | Set or reset role override |\n\n**List tools:**\n```bash\ncurl http://localhost:8000/tools/ \\\n  -H \"Authorization: Bearer $TOKEN\"\n```\n\nResponse:\n```json\n[\n  {\n    \"tool_name\": \"execute_sql_sales-db_abcd1234\",\n    \"description\": \"Execute SQL on Sales DB\",\n    \"connection_id\": \"...\",\n    \"default_min_role\": \"analyst\",\n    \"effective_min_role\": \"admin\",\n    \"accessible\": false\n  }\n]\n```\n\n**Override tool role:**\n```bash\n# Restrict to admin only\ncurl -X PATCH \"http://localhost:8000/tools/execute_sql_sales-db_abcd1234\" \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\"min_role\": \"admin\"}'\n\n# Reset to connection default\ncurl -X PATCH \"http://localhost:8000/tools/execute_sql_sales-db_abcd1234\" \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\"min_role\": null}'\n```\n\n---\n\n### API Keys\n\n| Method | Path | Description |\n|--------|------|-------------|\n| `POST` | `/api-keys/` | Generate a new key |\n| `GET` | `/api-keys/` | List your keys |\n| `DELETE` | `/api-keys/{id}` | Revoke a key |\n\n```bash\n# Generate (expires_at is optional)\ncurl -X POST http://localhost:8000/api-keys/ \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\"name\": \"CI pipeline\", \"expires_at\": \"2027-01-01T00:00:00Z\"}'\n\n# List\ncurl http://localhost:8000/api-keys/ \\\n  -H \"Authorization: Bearer $TOKEN\"\n\n# Revoke\ncurl -X DELETE \"http://localhost:8000/api-keys/{id}\" \\\n  -H \"Authorization: Bearer $TOKEN\"\n```\n\n---\n\n### Audit Logs\n\n| Method | Path | Role | Rate Limit | Description |\n|--------|------|------|-----------|-------------|\n| `GET` | `/audit-logs/` | Admin | 60/min | List audit events (filterable) |\n\n```bash\n# All events (paginated)\ncurl \"http://localhost:8000/audit-logs/?limit=50\" \\\n  -H \"Authorization: Bearer $ADMIN_TOKEN\"\n\n# Filter by event type (comma-separated prefixes)\ncurl \"http://localhost:8000/audit-logs/?event_prefix=query,tool&limit=100\" \\\n  -H \"Authorization: Bearer $ADMIN_TOKEN\"\n```\n\nQuery parameters: `skip` (offset, default 0), `limit` (max 200, default 50), `event_prefix` (comma-separated, e.g. `query`, `login`, `fs`).\n\n---\n\n### Health Check\n\n```bash\ncurl http://localhost:8000/health\n# {\"status\": \"ok\"}\n```\n\nReturns `503` if the database is unreachable. Suitable for Kubernetes liveness and readiness probes.\n\n---\n\n## MCP Integration\n\n### Connecting Claude Desktop\n\nInstall mcp-remote:\n```bash\nnpm install -g mcp-remote\n```\n\nAdd to `claude_desktop_config.json`:\n```json\n{\n  \"mcpServers\": {\n    \"gateway\": {\n      \"command\": \"npx\",\n      \"args\": [\"mcp-remote\", \"http://localhost:8000/t/my-org/mcp/sse\"]\n    }\n  }\n}\n```\n\nOn first connection, a browser window opens for OAuth login. After authenticating, mcp-remote caches the tokens and reconnects automatically. Tokens refresh silently in the background.\n\n### Available MCP Tools\n\nFor each active database connection the user can access, the gateway exposes two tools:\n\n**`get_schema_{connection-name}_{id}`**\nReturns the full database schema (tables, columns, types, constraints, indexes). Claude calls this first to understand the data structure before generating SQL.\n\n**`execute_sql_{connection-name}_{id}`**\nExecutes a `SELECT` statement and returns rows as JSON. Any non-SELECT statement is rejected (INSERT, UPDATE, DELETE, DROP, etc.). Execution timeout: 30 seconds.\n\n**`list_connections`**\nReturns all database connections the user can access with their names and types.\n\n**`get_current_time`**\nReturns the current UTC time in ISO 8601 format. Available to all roles.\n\n**Filesystem tools** (only when `FILESYSTEM_ALLOWED_DIRS` is configured):\n\n| Tool | Role | Description |\n|------|------|-------------|\n| `fs_read_file` | Analyst+ | Read a file as UTF-8 text |\n| `fs_list_directory` | Analyst+ | List directory contents |\n| `fs_directory_tree` | Analyst+ | Recursive directory tree (JSON) |\n| `fs_search_files` | Analyst+ | Glob pattern search |\n| `fs_get_file_info` | Analyst+ | File metadata (size, timestamps) |\n| `fs_write_file` | Admin | Create or overwrite a file |\n| `fs_create_directory` | Admin | Create a directory (with parents) |\n| `fs_move_file` | Admin | Move or rename a file |\n\n### SSE Endpoints\n\n| Endpoint | Auth | Description |\n|----------|------|-------------|\n| `GET /t/{slug}/mcp/sse` | Bearer JWT or `?api_key=` | Tenant-scoped SSE (recommended) |\n| `POST /t/{slug}/mcp/messages` | Bearer JWT, `?api_key=`, or session ID | Tenant-scoped message handler |\n| `GET /mcp/sse` | `?api_key=` or `?token=` | Legacy SSE (deprecated, sunset 2026-06-01) |\n| `POST /mcp/messages` | Bearer JWT, `?api_key=`, or `?token=` | Legacy message handler (deprecated) |\n\n### OAuth Discovery Endpoints\n\n| Endpoint | RFC | Description |\n|----------|-----|-------------|\n| `GET /.well-known/oauth-authorization-server/t/{slug}` | RFC 8414 | Authorization server metadata |\n| `GET /.well-known/oauth-protected-resource/t/{slug}/mcp/sse` | RFC 9728 | Protected resource metadata |\n| `POST /t/{slug}/oauth/register` | RFC 7591 | Dynamic client registration |\n| `GET /t/{slug}/oauth/authorize` | RFC 6749 | Authorization endpoint (PKCE S256) |\n| `POST /t/{slug}/oauth/token` | RFC 6749 | Token endpoint (code + refresh_token) |\n\n---\n\n## Build your own tools\n\nAny Python function becomes an authenticated, audited MCP tool:\n\n```python\n# app/tools/my_tool.py\nfrom app.tools import register_tool, ToolContext\nfrom mcp.types import TextContent\n\n@register_tool(name=\"my_custom_tool\", min_role=\"analyst\")\nasync def my_tool(arguments: dict, ctx: ToolContext) -> list[TextContent]:\n    # ctx.user gives you the authenticated user + their role\n    # ctx.db gives you the database session\n    result = do_something(arguments[\"input\"])\n    return [TextContent(type=\"text\", text=result)]\n```\n\nRestart the gateway. The tool appears in Claude Desktop automatically, with auth and audit logging included.\n\n---\n\n## Role-Based Access Control\n\nThree roles in ascending order of permission: `viewer` → `analyst` → `admin`\n\n### Default permissions\n\n| Action | Viewer | Analyst | Admin |\n|--------|:------:|:-------:|:-----:|\n| View connections | ✓ | ✓ | ✓ |\n| Run NL queries | — | ✓ | ✓ |\n| Use filesystem tools (read) | — | ✓ | ✓ |\n| Use filesystem tools (write) | — | — | ✓ |\n| View audit logs | — | — | ✓ |\n| View query history | — | — | ✓ |\n| Manage connections | — | — | ✓ |\n| Manage users | — | — | ✓ |\n| Configure SSO | — | — | ✓ |\n| Manage API keys | ✓ | ✓ | ✓ |\n| Override tool roles | — | — | ✓ |\n\n### Per-connection roles\n\nEach connection has a `min_role`. Users below this role cannot see or use that connection, or the MCP tools it generates.\n\nExample: A sensitive production database with `min_role: admin` is invisible to analysts and viewers entirely — it won't appear in `/connections/` or `/tools/`, and its MCP tools won't be listed.\n\n### Per-tool overrides\n\nAdmins can override the effective minimum role for any MCP tool independently of the connection's `min_role`:\n\n```bash\n# Lock down SQL execution on prod, but keep schema browsing open\nPATCH /tools/execute_sql_prod-db_abcd1234  {\"min_role\": \"admin\"}\nPATCH /tools/get_schema_prod-db_abcd1234   {\"min_role\": \"analyst\"}\n\n# Reset to connection default\nPATCH /tools/execute_sql_prod-db_abcd1234  {\"min_role\": null}\n```\n\n---\n\n## Entra ID / SSO\n\n### Setup in Azure AD\n\n1. Register an application in Azure Active Directory (App registrations → New registration)\n2. Add redirect URIs:\n   - `http://<gateway-url>/auth/entra/callback` (admin UI SSO)\n   - `http://<gateway-url>/t/<slug>/oauth/entra-callback` (MCP OAuth flow)\n3. Under **API permissions**, add:\n   - **Delegated** (Microsoft Graph): `openid`, `profile`, `email`, `User.Read`, `GroupMember.Read.All`\n   - **Application** (Microsoft Graph): `Directory.Read.All` (required for role sync during token refresh)\n   - Grant **admin consent** for the delegated `GroupMember.Read.All` and the application `Directory.Read.All`\n4. Create a **Client secret** (Certificates & secrets → New client secret)\n5. Note your Azure tenant ID, app client ID, and the client secret value\n\n### Configure in MCP Gateway\n\nVia admin UI: **SSO Config** tab, or via API:\n\n```bash\ncurl -X POST http://localhost:8000/auth/entra/config \\\n  -H \"Authorization: Bearer $ADMIN_TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"entra_tenant_id\": \"your-azure-tenant-uuid\",\n    \"client_id\": \"your-app-client-id\",\n    \"client_secret\": \"your-client-secret\",\n    \"admin_group_id\": \"azure-group-uuid-for-admins\",\n    \"analyst_group_id\": \"azure-group-uuid-for-analysts\",\n    \"viewer_group_id\": \"azure-group-uuid-for-viewers\"\n  }'\n```\n\nGroup IDs are optional — configure only what you need. Users in multiple mapped groups get the highest role.\n\n### Login flow\n\nDirect users to: `http://<gateway>/auth/entra/login?tenant_slug=<slug>`\n\nThe gateway redirects to Microsoft. After authentication it:\n1. Fetches the user's profile from Microsoft Graph (`/me`)\n2. Fetches transitive group memberships (`/me/transitiveMemberOf`)\n3. Maps groups to roles (highest wins: admin > analyst > viewer)\n4. Creates the user if they don't exist (just-in-time provisioning)\n5. Returns a JWT\n\n---\n\n## Development\n\n### Local setup (without Docker)\n\n```bash\n# Python environment\npython3 -m venv .venv\nsource .venv/bin/activate\npip install -r requirements.txt\n\n# Configure\ncp .env.example .env\n# Edit .env: set DATABASE_URL to a local Postgres instance (or use SQLite for quick testing)\n\n# Run migrations\nalembic upgrade head\n\n# Start API (with auto-reload)\nuvicorn app.main:app --reload --port 8000\n```\n\n### Frontend development\n\n```bash\ncd frontend\nnpm install\nnpm run dev   # Vite dev server on port 5173 with API proxy\n```\n\nThe Vite dev server proxies all API paths to `http://localhost:8000`, so the frontend and API can run independently during development.\n\n### Sample databases\n\n```bash\ndocker compose --profile dev up -d\n```\n\nStarts pre-seeded sample databases:\n- `sample_postgres` on port **5433** → `postgresql://sampleuser:samplepass@localhost:5433/sampledb`\n- `sample_mysql` on port **3307** → `mysql+pymysql://sampleuser:samplepass@localhost:3307/sampledb`\n\nAdd these as connections in the admin UI to explore the natural language query feature.\n\n### Running tests\n\n```bash\n# All 198 tests (no external services required — uses SQLite in-memory)\n.venv/bin/python -m pytest\n\n# Verbose output\n.venv/bin/python -m pytest -v\n\n# Single test file\n.venv/bin/python -m pytest tests/test_connections_api.py -v\n\n# Single test\n.venv/bin/python -m pytest tests/test_oauth.py::test_token_endpoint_code_exchange -v\n```\n\n### Database migrations\n\n```bash\n# Apply all pending migrations\nalembic upgrade head\n\n# Create a new migration after changing models/__init__.py\nalembic revision --autogenerate -m \"describe your change\"\n\n# Roll back one step\nalembic downgrade -1\n\n# Show history\nalembic history --verbose\n```\n\n### Building for production\n\n```bash\n# Build frontend static files\ncd frontend && npm run build && cd ..\n\n# The Dockerfile builds both in a multi-stage build:\ndocker build -t mcp-gateway .\ndocker compose up -d\n```\n\n---\n\n## Troubleshooting\n\n### \"SECRET_KEY must be set\" / \"ENCRYPTION_KEY must be set\"\n\ndocker-compose uses `${VAR:?error message}` syntax — it fails fast if these are not set. Generate them:\n\n```bash\npython3 -c \"import secrets; print(secrets.token_hex(32))\"           # SECRET_KEY\npython3 -c \"import secrets; print(secrets.token_hex(32))\"           # ENCRYPTION_KEY\n```\n\nAdd to your `.env` file before running `docker compose up`.\n\n### API returns 503 on health check\n\nThe database is not reachable. Check:\n```bash\ndocker compose ps        # are all containers running?\ndocker compose logs db   # any Postgres startup errors?\ndocker compose restart api  # restart API if db was slow to start\n```\n\n### Claude Desktop doesn't open a browser for login\n\nEnsure mcp-remote is installed: `npm install -g mcp-remote`. Check that `BASE_URL` in `.env` matches the URL you put in `claude_desktop_config.json`. A mismatch causes the OAuth callback to fail silently.\n\n### Query returns `\"INVALID_QUERY\"`\n\nThe LLM could not generate a valid `SELECT` for your question, or it generated a non-SELECT statement (which is blocked). Try:\n- Be more specific in your question\n- Ensure your database has descriptive column and table names\n- Check that `ANTHROPIC_API_KEY` is set and valid\n\n### Entra login returns 400 \"Entra ID not configured for this tenant\"\n\nThe tenant doesn't have an Entra ID configuration. Add one via **Admin UI → SSO Config** or `POST /auth/entra/config`.\n\n### Entra callback returns 403 \"Not a member of any authorized group\"\n\nThe Azure AD user is not in any of the three groups configured for the tenant. Either:\n- Add the user to one of the mapped groups in Azure AD\n- Update the group IDs in the gateway config to match the user's actual groups (`POST /auth/entra/config`)\n\n### Refresh token rejected as \"Invalid or expired\"\n\nRefresh tokens are **single-use** — each use issues a new pair and revokes the old one. If two requests attempt to use the same refresh token simultaneously, the second fails. Re-authenticate to get a fresh pair.\n\n### Rate limit 429 responses\n\n| Endpoint | Limit |\n|----------|-------|\n| `POST /tenants/` | 5/min |\n| `POST /auth/login` | 10/min |\n| `POST /t/{slug}/oauth/login` | 10/min |\n| `POST /api-keys/` | 10/min |\n| `GET /auth/entra/login` | 20/min |\n| `GET /t/{slug}/oauth/authorize` | 30/min |\n| `POST /t/{slug}/oauth/token` | 30/min |\n| `POST /query/` | 30/min |\n| `GET /audit-logs/` | 60/min |\n| `GET /query/history` | 60/min |\n\nWait 60 seconds for the limit window to reset.\n\n---\n\n## Security\n\n### Credentials at rest\n\n| Data | Storage |\n|------|---------|\n| Passwords | bcrypt (never stored plain) |\n| JWT signing | `SECRET_KEY` (HS256) |\n| DB connection strings | Fernet AES-256 encrypted |\n| Entra client secrets | Fernet AES-256 encrypted |\n| API keys | HMAC-SHA-256 keyed with `SECRET_KEY` (raw key returned once, never stored) |\n| Refresh tokens | SHA-256 hash |\n\n### HTTP security headers\n\nAll responses include:\n- `X-Content-Type-Options: nosniff`\n- `X-Frame-Options: DENY`\n- `Strict-Transport-Security: max-age=31536000`\n- `Cache-Control: no-store` on auth endpoints\n\n### OAuth protections\n\n- **PKCE S256** — prevents authorization code interception attacks\n- **Single-use authorization codes** — codes expire after 5 minutes and are deleted on first use\n- **Rotating refresh tokens** — each refresh revokes the previous token (prevents replay)\n- **Loopback-only redirect URIs** — only `localhost`, `127.0.0.1`, and `::1` are accepted as redirect targets (per RFC 8252)\n\n### SQL safety\n\nThe `execute_sql` MCP tool rejects all non-SELECT statements via sqlglot AST parsing before any query reaches the database. INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE, and EXEC are all blocked regardless of how they are formatted.\n\n### Tenant isolation\n\nAll database queries are scoped to `current_user.tenant_id`. Foreign key constraints enforce isolation at the schema level — there is no code path that allows data from one tenant to appear in another tenant's responses.\n\n### Rotating secrets\n\n**Rotating `SECRET_KEY`:** All existing JWTs immediately become invalid. Users must re-authenticate. Refresh tokens (hashed separately) are also invalidated. **API keys are also invalidated** — they are HMAC-keyed with `SECRET_KEY`, so existing keys must be revoked and re-issued after rotation.\n\n**Rotating `ENCRYPTION_KEY`:** Requires re-encrypting all stored connection strings and Entra client secrets with the new key before the old key is removed. Plan this as a maintenance window — the gateway cannot serve connections during the rotation.\n\n### Audit log\n\nAll significant events are written to the `audit_logs` table:\n\n| Event | When |\n|-------|------|\n| `login.success` / `login.failure` | Every login attempt |\n| `oauth.login` / `oauth.entra_login` | OAuth authorization |\n| `oauth.token_issued` / `oauth.token_refreshed` | Token exchange and refresh |\n| `query.success` / `query.failure` | Every NL query |\n| `tool.execute_sql` / `tool.execute_sql.rejected` / `tool.execute_sql.error` | MCP SQL tool usage |\n| `fs.*` (e.g. `fs.fs_read_file`, `fs.fs_write_file.error`) | Filesystem tool usage |\n| `connection.created` / `connection.updated` / `connection.deleted` | Connection changes |\n| `tenant.created` | Tenant registration |\n| `user.deleted` / `user.role_updated` | User management |\n| `key.created` / `key.revoked` | API key lifecycle |\n\nQuery the audit log:\n```bash\ncurl \"http://localhost:8000/audit-logs/?limit=100\" \\\n  -H \"Authorization: Bearer $ADMIN_TOKEN\" | jq\n```\n\n---\n\n## Additional Documentation\n\n| Guide | Description |\n|-------|-------------|\n| [Testing with Claude Desktop](docs/testing-with-claude-desktop.md) | End-to-end walkthrough: local users + Entra SSO |\n| [Deployment Guide](docs/deployment.md) | Railway, Render, and generic Docker/VPS deployment |\n| [OAuth 2.1 Flow](docs/oauth2-flow.md) | Full PKCE flow, endpoints, token lifecycle |\n| [Filesystem Tools](docs/filesystem-tools.md) | Sandboxed file access via MCP |\n| [Audit Logging](docs/audit-logging.md) | Event catalog, API, and metadata reference |\n| [API Keys](docs/api-keys.md) | Key lifecycle, security model, usage |\n| [Tool Role Overrides](docs/tool-role-overrides.md) | Per-tool RBAC configuration |\n\n## What's Coming — SaltMine AI\n\nMCP Gateway is the open source foundation. A managed platform called **SaltMine AI** \nis currently in development, built on top of this project, following the same security principles and aimed at business teams \nwho want to query their data without any infrastructure to manage.\n\nPlanned features include:\n\n- Multi-datasource queries across databases, data lakes, and APIs in a single question\n- Business-user chat interface with visualisations — no SQL knowledge required\n- Data privacy controls with field-level masking and query-level audit trails\n- Zero Vendor Lock-in on AI\n- Understands Your Business Language\n\nIf you're interested in learning more, have a use case you'd like to discuss, \nor just want to follow the progress:\n\n- 📧 [salterisp@gmail.com](mailto:salterisp@gmail.com)\n- 💼 [linkedin.com/in/panagiotissalteris](https://www.linkedin.com/in/panagiotissalteris)",
  "bytes": 36718,
  "sha": "c38a7cee86163f7ba9166020502c4f6e1df32efe61e41330c06f72d89162ec60",
  "repo_slug": "panossalt/mcp-gateway",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_panossalt_mcp_gateway_35a0426b/readme"
}