Secure BigQuery MCP Gateway

A public reference implementation for connecting an AI assistant to BigQuery without giving the assistant a Google credential. It runs a remote MCP server on Cloud Run and enforces read-only access, dataset boundaries, result limits, query budget limits, and audit-friendly BigQuery labels.

Portfolio project, not a client system. It contains no customer data, project IDs, service-account keys, or production endpoints.

The design problem

An AI assistant needs to answer questions from an analytics warehouse while a business owner is offline. The common mistake is to reuse a person's Google OAuth refresh token for every part of the connection. This creates a fragile and over-privileged integration.

This project splits the trust boundary:

AI assistant / scheduled workflow
  └── authenticates to the MCP endpoint
          └── Cloud Run MCP gateway
                  └── uses its own restricted service account for BigQuery

The caller can request an approved tool. It never receives direct Google Cloud credentials. The Cloud Run service identity can query only approved reporting data. It cannot modify the warehouse.

Related MCP server: bq-readonly-mcp

What it demonstrates

• Remote MCP over Streamable HTTP, the recommended transport for deployed MCP servers.

• Python MCP implementation with a Cloud Run-ready container.

• Separate inbound caller authentication and outbound Google service identity.

• OAuth/OIDC bearer-token verification with JWKS caching, falling back to a static bearer token for simple machine-to-machine callers — see identity.py.

• Role-based access control: each verified identity's role(s) resolve to a set of reachable datasets/schemas via ROLE_DATASET_MAP, enforced independently of, and in addition to, the gateway-wide allowlist.

• A second, independent read-only connector for Postgres (schema allowlist, SET TRANSACTION READ ONLY, statement timeout, row cap) — see postgres_service.py. Disabled by default; enabling it is one environment variable.

• A host-allowlisted REST connector for calling an approved external API without ever handing the caller, or the model, that API's credential.

• Best-effort PII masking applied to every connector's result rows before they leave the gateway (email/phone/SSN/card-shaped substrings) — a defence-in-depth layer, not a substitute for querying pre-masked views.

• Structured JSON audit logging of every tool call (subject, role, tool, decision, latency, row count, masked-field count) — one Cloud Logging entry per call, no raw query text or row contents logged.

• Per-identity rate limiting and a rolling daily byte budget, enforced in memory.

• Application Default Credentials rather than a service-account JSON key.

• BigQuery dry-run cost checks plus maximum_bytes_billed enforcement.

• SQL guardrails: one statement, SELECT/WITH ... SELECT only, dataset allowlist, row cap, timeout, and query labels.

• A production-minded recommendation to grant access to curated reporting views rather than raw operational tables.

See the architecture decision record for the security rationale and the threat model for what each control does and doesn't cover.

Inbound authentication: bearer token or OIDC

The reference build defaults to a bearer token injected from Secret Manager — appropriate for a controlled machine-to-machine scheduler, and it makes the identity separation visible in a small project. Setting OIDC_ISSUER, OIDC_AUDIENCE, and OIDC_JWKS_URL switches the same deployment to standards-compliant OIDC token verification with no code change: identity.py resolves roles from the configured claim (OIDC_ROLE_CLAIM, default roles) and RBAC/audit logging use the verified subject and roles either way. BigQuery access remains unchanged in both modes because it always comes from the Cloud Run service account, never from the caller's token.

Local development

python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"
cp .env.example .env
# Update .env with a non-production project, allowed reporting dataset, and random token.
python -m secure_bigquery_mcp_gateway.app

The MCP endpoint is available at http://localhost:8080/mcp. Supply:

Authorization: Bearer <MCP_BEARER_TOKEN>

or, once OIDC_ISSUER/OIDC_AUDIENCE/OIDC_JWKS_URL are set, a valid OIDC identity token from that issuer.

Four tools are exposed: execute_readonly_sql (BigQuery), execute_readonly_sql_postgres (Postgres, only when POSTGRES_DSN is set), call_allowed_rest_api (only hosts in REST_ALLOWED_HOSTS), and gateway_capabilities (reports the caller's own resolved roles and reachable datasets — useful for confirming an RBAC change took effect without guessing).

Run the tests:

pytest -q
# or, without pytest:
python -m unittest discover -s tests

Deploy to Cloud Run

Before deploying, create a dedicated service account and grant:

1. roles/bigquery.jobUser on the query project.

2. roles/bigquery.dataViewer on only the approved dataset or, preferably, curated views.

Build and deploy from the client-owned Google Cloud project:

gcloud builds submit --tag europe-west2-docker.pkg.dev/PROJECT_ID/mcp/secure-bigquery-mcp-gateway

gcloud run deploy secure-bigquery-mcp-gateway \
  --image europe-west2-docker.pkg.dev/PROJECT_ID/mcp/secure-bigquery-mcp-gateway \
  --region europe-west2 \
  --service-account claude-bq-mcp@PROJECT_ID.iam.gserviceaccount.com \
  --allow-unauthenticated \
  --set-env-vars GOOGLE_CLOUD_PROJECT=PROJECT_ID,ALLOWED_DATASETS=analytics_reporting \
  --set-secrets MCP_BEARER_TOKEN=mcp-bearer-token:latest

The application needs no GOOGLE_APPLICATION_CREDENTIALS file. Cloud Run supplies short-lived credentials for its attached service account automatically.

--allow-unauthenticated is intentional for this reference build: the remote MCP caller cannot usually mint a Google Cloud IAM token. The application protects /mcp with its own bearer-token layer, which is separate from the Cloud Run service identity used for BigQuery. For an OAuth/OIDC capable MCP client, replace this with a standards-compliant verifier and add edge protection.

Production checklist

• Use reporting views or an authorised view layer, not raw customer/event tables.

• Prefer OIDC over the static bearer token once the MCP client supports it; rotate whichever is active.

• Set a conservative byte cap and alert on BigQuery job labels.

• Move rate_limit.py's in-memory state to Memorystore/Redis before running more than one instance.

• Restrict Cloud Run ingress and use a custom domain/WAF when appropriate.

• Point ROLE_DATASET_MAP at the real roles your OIDC issuer will send, not just the reference service:*.

• If enabling Postgres, confirm the connecting database role is itself granted SELECT only — this gateway's read-only transaction is a second layer, not a substitute for that grant.

• Review REST_ALLOWED_HOSTS against the actual integrations you intend to expose; an empty allowlist (the default) means the REST tool can reach nothing.

• Confirm mask_pii behaves as expected against a sample of real column values before relying on it.

• Test a cold start, a denied write query, an over-budget query, an RBAC-denied dataset, and the real overnight caller.

• Keep deployment privileges separate from the running service account.

Project story for clients

I designed this gateway to solve the gap between an AI assistant's ability to call tools and a production data warehouse's need for least-privilege access. The key decision is separating the assistant's authentication to the MCP endpoint from the Cloud Run service account that executes constrained, read-only BigQuery queries. I extended it from a single-connector, bearer-token-only prototype into a multi-connector gateway (BigQuery, Postgres, and an allowlisted REST API) with OIDC support, per-role dataset access, PII-aware result masking, structured audit logging, and per-identity rate limits — the shape a client would actually need before trusting it with more than one data source or more than one caller.

Known issue worth knowing about

mcp>=2.0.0 removed mcp.server.fastmcp, which this project's server is built on. pyproject.toml pins mcp<2.0.0 accordingly — if a dependency bump ever breaks this, that's why.

License

MIT