FastAPI Database MCP Server

Read-only SQL query API for Postgres and DuckDB, exposed as MCP tools for AI clients.

Live endpoint: db-mcp.tigzig.com

Security Hardening

This server is designed to run with a public, open MCP endpoint (/mcp) — no API key, no auth. A separate secured endpoint (/mcp-secure) uses Auth0 OAuth for controlled access. The open endpoint relies entirely on the defense stack below.

Defense Layers

1. Edge rate limiting — Cloudflare WAF or equivalent, configured per your needs (recommended before traffic hits origin)

2. Application rate limiting — per-IP and global rate limits via SlowAPI (configurable via env vars)

3. Per-IP concurrency cap — limits simultaneous in-flight queries per IP (default: 4)

4. Global concurrency cap — limits total simultaneous queries server-wide (default: 10)

5. SQL prefix allowlist — only SELECT, WITH, SHOW, DESCRIBE, EXPLAIN allowed

6. SQL keyword blocklist — INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, EXEC, COPY, GRANT, REVOKE, and 20+ more blocked (hardcoded in BLOCKED_KEYWORDS)

7. Resource exhaustion blocking — CROSS JOIN, REPEAT, REGEXP_REPLACE, MD5, SHA256 blocked to prevent CPU/bandwidth bombs

8. SQL parser structural validation (sqlglot) — regex-based checks cannot understand SQL structure (CTEs, aliases, nested subqueries). The sqlglot parser builds an AST (Abstract Syntax Tree) and validates structurally: blocks multiple table sources in a single SELECT (catches cartesian products via FROM a, b, CTE alias bypass like WITH t AS (...) SELECT FROM t a, t b, and aliased self-joins), detects blocked functions even when nested or aliased. Supports both Postgres and DuckDB dialects. Falls back to regex-only mode gracefully if sqlglot is not installed.

9. SQL comment rejection — -- and /* blocked to prevent comment-based validation bypass

10. System catalog blocking — pg_catalog, information_schema, pg_stat, duckdb_tables(), and other system objects blocked (hardcoded in BLOCKED_PG_SOURCES and BLOCKED_DUCKDB_SOURCES)

11. Auto-append LIMIT — queries without an outer LIMIT automatically get one (configurable via MAX_JSON_ROWS / MAX_TSV_ROWS)

12. Response size limit — responses exceeding the byte limit are rejected with HTTP 413 (configurable via MAX_RESPONSE_BYTES, default: 1MB)

13. ORDER BY validation — function calls in ORDER BY blocked except whitelisted aggregates; subqueries in ORDER BY blocked

14. Subquery depth limit — max 3 SELECT keywords per query (hardcoded)

15. Data-generating function blocking — GENERATE_SERIES, RANGE, UNNEST blocked (hardcoded)

16. Query timeouts — configurable timeout on both Postgres and DuckDB, queries exceeding this are killed (default: 15s)

17. DuckDB query interrupt — on timeout, connection.interrupt() kills the C++ engine query, not just the Python coroutine

18. Postgres read-only mode — default_transaction_read_only = on + dedicated read-only database user

19. DuckDB read-only mode — read_only=True + enable_external_access=false

20. DuckDB resource limits — memory and thread limits (configurable via env vars, defaults: 512MB, 2 threads)

21. Container resource limits — Docker/container-level RAM and CPU caps (configured in your hosting platform)

22. Error message sanitization — generic error messages returned, no internal details leaked

23. Auth0 OAuth on /mcp-secure — JWT verification (RS256), audience/issuer validation, email whitelist (optional, enabled via env vars)

24. Failed-auth rate limiter — in-memory counter blocks IPs after repeated failed JWT attempts on /mcp-secure (configurable via AUTH_FAIL_MAX and AUTH_FAIL_WINDOW)

What's Hardcoded vs Configurable

Hardcoded in code (edit app.py to change):

• SQL keyword blocklist (BLOCKED_KEYWORDS)

• System catalog blocklists (BLOCKED_PG_SOURCES, BLOCKED_DUCKDB_SOURCES)

• Allowed SQL prefixes (SELECT, WITH, SHOW, DESCRIBE, EXPLAIN)

• ORDER BY allowed functions (SUM, COUNT, AVG, MIN, MAX, COALESCE, NULLIF, CASE)

• Subquery depth limit (3)

• sqlglot structural checks (multi-table per SELECT, blocked functions via AST)

Configurable via environment variables (see table below):

• Rate limits, concurrency caps, query timeouts, row limits, response size limit, connection pool settings, Auth0 config

Database-Level Hardening (Postgres)

If your backend connects to a hosted Postgres (Supabase, Neon, etc.), you should also harden at the database level:

• Enable RLS on all tables

• Revoke default grants from anon/authenticated roles

• Use a dedicated read-only database user (SELECT-only grants on specific tables)

• Set default_transaction_read_only = on on application roles

• Set statement_timeout on application roles (matches your PG_STATEMENT_TIMEOUT_MS)

• Add indexes on columns used in WHERE, GROUP BY, ORDER BY for large tables

DuckDB-Level Hardening

DuckDB runs in-process. The server opens it with read_only=True and enable_external_access=false, and sets memory_limit and threads to prevent a single query from consuming all resources. On timeout, connection.interrupt() + connection.close() kills the query at the C++ engine level.

Related MCP server: sql-explorer-mcp

What It Does

Two databases, two endpoints, one MCP server:

• Postgres (Supabase) — ~1M rows of ODI cricket ball-by-ball data (2013-2025)

• DuckDB — ~1M rows of T20 cricket ball-by-ball data (2013-2025)

Both tables have identical schemas (23 columns) covering match details, player info, runs, extras, and dismissals.

The endpoints are mounted as MCP tools via fastapi-mcp, so any MCP-compatible AI client (Claude Code, Claude Desktop, etc.) can connect and query directly.

Endpoints

Query Format

{
  "sql": "SELECT striker, SUM(runs_off_bat) as runs FROM ball_by_ball WHERE season = '2023' GROUP BY striker ORDER BY runs DESC LIMIT 10",
  "format": "json"
}

Set "format": "tsv" for compact tab-delimited output (~70% fewer tokens).

Connecting as MCP Client

Claude Code

claude mcp add --transport sse db-mcp https://your-server.com/mcp

Claude Desktop

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "db-mcp": {
      "type": "sse",
      "url": "https://your-server.com/mcp"
    }
  }
}

Local Development

git clone https://github.com/amararun/shared-fastapi-database-mcp.git
cd shared-fastapi-database-mcp

python -m venv .venv
source .venv/bin/activate  # or .venv\Scripts\activate on Windows

pip install -r requirements.txt

cp .env.example .env
# Edit .env with your database connection strings

uvicorn app:app --host 0.0.0.0 --port 8000

Environment Variables

Auth0 OAuth (Secured Endpoint)

The /mcp-secure endpoint adds Auth0 OAuth on top of all existing security layers. It is optional — if AUTH0_DOMAIN is not set, only the open /mcp endpoint is mounted.

How It Works

1. MCP client discovers OAuth metadata at /.well-known/oauth-authorization-server

2. Client redirects user to Auth0 login

3. Auth0 authenticates user, checks email whitelist, issues JWT

4. Client sends JWT as Bearer token with every request

5. Server validates JWT signature (RS256 via JWKS), audience, and issuer

Auth0 Setup

1. Create an Auth0 API with your server URL as the identifier (audience)

2. Create an Auth0 Application (Regular Web Application)

3. Add your MCP client's callback URLs (e.g., https://claude.ai/api/mcp/auth_callback)

4. Create a post-login Action with an email whitelist

5. Set the AUTH0 environment variables on the server

Connecting to the Secured Endpoint

Claude.ai (Web) — Settings > Connectors > Add custom connector:

• URL: https://your-server.com/mcp-secure

• OAuth Client ID: your Auth0 app's client ID

Claude Desktop:

{
  "mcpServers": {
    "db-mcp-secure": {
      "command": "npx",
      "args": ["mcp-remote", "https://your-server.com/mcp-secure", "8080"]
    }
  }
}

Demo Limitations

• JWKS cache does not auto-refresh (refresh on restart only)

• No token scope validation (any valid token grants access to all tools)

• Fake Dynamic Client Registration (returns pre-configured credentials for MCP spec compatibility)

• No token revocation handling (tokens accepted until expiry)

Stack

• FastAPI + uvicorn

• asyncpg (Postgres connection pool)

• DuckDB (read-only, thread-pool executor)

• fastapi-mcp v0.4.0 (MCP server mounting, OAuth support)

• sqlglot (SQL parser for structural validation)

• python-jose (JWT verification)

• SlowAPI (rate limiting)

Author

Built by Amar Harolikar

Explore 30+ open source AI tools for analytics, databases & automation at tigzig.com

License

MIT License