BigQuery Readonly MCP

BigQuery Readonly MCP is a FastAPI-based Custom MCP server for safely querying BigQuery from ChatGPT. It is designed as a reusable internal analytics MCP: each tool accepts project_id. The initial validation project was ice-sh; the current pilot default project is ice-mp.

Scope

• GitHub repository: Growth-Management/bigquery-readonly-mcp

• Cloud Run deploy project: ice-sh

• Initial BigQuery validation project: ice-sh

• Current pilot default BigQuery project: ice-mp

• Initial pilot user: sinohara@impress.co.jp

• Allowed email domain: impress.co.jp

• Allowed project IDs: empty for the current pilot, so the approved user may specify any project readable through their own BigQuery IAM

• Allowed dataset IDs: empty by default, so BigQuery IAM controls dataset access

• Default maximumBytesBilled: 1GB

• Default max_results: 1000

• Default query timeout: 60 seconds

• Current session backend: Firestore persistence enabled, with SESSION_TTL_SECONDS=3600

Related MCP server: Google Sheets MCP Server

Security Model

The MCP server uses Google OAuth and BigQuery calls run with the authenticated user's access token. It does not use a service account to impersonate users for BigQuery query execution.

Users should receive the minimum required IAM permissions:

• roles/bigquery.jobUser on the project where jobs are created

• roles/bigquery.dataViewer on only the datasets they may inspect

The server enforces readonly SQL before execution. run_readonly_query and dry_run_query only allow one SELECT or WITH statement and reject DML, DDL, EXPORT, LOAD, grants, revokes, and procedure calls.

The server can also enforce application-side allowlists:

• ALLOWED_PROJECT_IDS: when set, tool calls for projects outside the list are rejected before any BigQuery API call is made. When empty, project access is governed by the logged-in user's BigQuery IAM.

• ALLOWED_DATASET_IDS: when set, list_datasets is filtered and list_tables / get_table_schema calls outside the list are rejected before any BigQuery API call is made.

• ALLOWED_USER_EMAILS: when set, tool calls from users outside the list are rejected before any BigQuery API call is made.

Allowlist rejections are recorded in audit logs with success=false and a rejection_reason.

MCP Tools

• list_projects

• list_datasets

• list_tables

• get_table_schema

• dry_run_query

• run_readonly_query

All tools accept project_id where applicable. If omitted, the current pilot default is ice-mp.

Endpoints

• GET /healthz

• GET /health

• POST /mcp

• GET /oauth/authorize

• GET /oauth/callback

• POST /oauth/token

• GET /.well-known/oauth-authorization-server

Use /health for Cloud Run external health checks. Cloud Run can reserve /healthz before requests reach the container, causing Google Frontend 404 responses even when the FastAPI app is healthy.

Local Development

Create a local environment and install dependencies:

python -m venv .venv
. .venv/bin/activate
pip install -r requirements.txt

Set local environment variables. Do not commit real secrets. Local development may use memory; the pilot deployment uses Firestore.

export BASE_URL="http://localhost:8080"
export GOOGLE_OAUTH_CLIENT_ID="..."
export GOOGLE_OAUTH_CLIENT_SECRET="..."
export SESSION_SECRET="replace-with-random-value"
export ALLOWED_DOMAIN="impress.co.jp"
export DEFAULT_PROJECT_ID="ice-mp"
export ALLOWED_PROJECT_IDS=""
export ALLOWED_DATASET_IDS=""
export ALLOWED_USER_EMAILS="sinohara@impress.co.jp"
export MAXIMUM_BYTES_BILLED="1073741824"
export MAX_RESULTS="1000"
export QUERY_TIMEOUT_SECONDS="60"
export SESSION_TTL_SECONDS="3600"
export SESSION_STORE_BACKEND="memory"
export FIRESTORE_SESSION_COLLECTION="bigquery_mcp_sessions"

ALLOWED_DATASET_IDS is a comma-separated list of project_id:dataset_id values, for example:

export ALLOWED_DATASET_IDS="ice-mp:example_datamart,ice-mp:example_source"

Run the app:

uvicorn app.main:app --host 0.0.0.0 --port 8080 --reload

Run tests:

pytest

OAuth Setup

Configure a Google OAuth Web application in ice-sh:

• Consent screen: internal or equivalent organization-limited configuration

• Authorized domain: impress.co.jp

• Redirect URI: https://<cloud-run-url>/oauth/callback

• Scopes:

  • openid

  • email

  • profile

  • https://www.googleapis.com/auth/bigquery.readonly

Store these values in Secret Manager rather than the repository:

• google-oauth-client-id

• google-oauth-client-secret

• bigquery-mcp-session-secret

For MCP clients such as ChatGPT custom connectors, this service acts as the OAuth server and bridges to Google OAuth internally:

• Authorization endpoint: https://<cloud-run-url>/oauth/authorize

• Token endpoint: https://<cloud-run-url>/oauth/token

• Metadata endpoint: https://<cloud-run-url>/.well-known/oauth-authorization-server

• MCP endpoint: https://<cloud-run-url>/mcp

The OAuth callback /oauth/callback is the Google OAuth redirect URI. MCP clients should be configured with their own callback URL in the client UI when prompted; the service preserves that callback through the OAuth state and returns an authorization code to the MCP client.

Session Storage

The current pilot deployment uses Firestore-backed session storage:

SESSION_STORE_BACKEND=firestore
FIRESTORE_SESSION_COLLECTION=bigquery_mcp_sessions
SESSION_TTL_SECONDS=3600

Firestore persistence keeps a post-login MCP session available across Cloud Run instance restarts, scale-out, and new revisions. This was validated on 2026-06-08: a session document was created in Firestore, the access token was stored only as encrypted ciphertext, and the same mcp_session cookie continued to work after new Cloud Run revisions and GitHub Actions deploys.

Security behavior:

• Access tokens are encrypted before being stored in Firestore.

• Encryption uses AES-GCM with a key derived from SESSION_SECRET.

• SESSION_SECRET rotation intentionally invalidates existing persisted sessions.

• Invalid or undecryptable session documents are deleted and treated as logged out.

• Firestore persistence does not grant BigQuery access. BigQuery calls still use the logged-in user's OAuth token and IAM permissions.

Operational limit:

• The server currently stores Google access tokens, not refresh tokens.

• Google access tokens can expire before a longer application session TTL.

• For this reason, the production pilot pins SESSION_TTL_SECONDS=3600.

• Longer sessions require refresh-token support, including encrypted refresh-token storage, expiry handling, and re-login fallback.

Short-lived OAuth authorization requests and authorization codes remain in memory, so a login flow that overlaps a revision restart may still need to be retried. This does not affect already-created Firestore MCP sessions.

Cloud Run Deployment

The GitHub Actions workflow expects these GitHub Secrets:

• GCP_PROJECT_ID: ice-sh

• GCP_WORKLOAD_IDENTITY_PROVIDER

• GCP_DEPLOY_SERVICE_ACCOUNT

• BASE_URL: deployed Cloud Run URL or custom domain

The deploy service account needs only deployment permissions, such as Artifact Registry write and Cloud Run deploy permissions. It is not used for BigQuery query execution.

Current pilot deployment settings:

• DEFAULT_PROJECT_ID=ice-mp

• ALLOWED_PROJECT_IDS=

• ALLOWED_DATASET_IDS=

• ALLOWED_USER_EMAILS=sinohara@impress.co.jp

• MAXIMUM_BYTES_BILLED=1073741824

• MAX_RESULTS=1000

• QUERY_TIMEOUT_SECONDS=60

• SESSION_STORE_BACKEND=firestore

• FIRESTORE_SESSION_COLLECTION=bigquery_mcp_sessions

• SESSION_TTL_SECONDS=3600

Deployment is managed per GCP project. The Cloud Run deployment project is ice-sh; the current default BigQuery project is ice-mp, but project access is governed by the logged-in user's BigQuery IAM because ALLOWED_PROJECT_IDS is empty.

See docs/cloud-run.md for the full Phase 5 deployment procedure, including required APIs, Artifact Registry, Secret Manager, manual deploy, /health, Cloud Run session storage, and Cloud Logging checks.

See docs/github-actions-deploy.md for the preferred GitHub Actions deployment path using Workload Identity Federation.

See docs/phase-7-ice-sh-validation.md for the Phase 7 validation record.

See docs/rollout-policy.md for the Phase 8 rollout policy covering rollout patterns, allowlists, per-project deployment ownership, validation, audit retention, session persistence, and follow-up hardening.

See docs/all-project-iam-user-pilot.md for the approved pilot change that leaves project access to the named user's BigQuery IAM while keeping ALLOWED_USER_EMAILS=sinohara@impress.co.jp.

Initial Validation On ice-sh

Use a user account in the allowed domain with the required BigQuery IAM permissions, then verify:

• MCP connects to /mcp

• Google OAuth login succeeds

• dataset list can be retrieved from ice-sh

• table list can be retrieved

• schema can be retrieved

• dry run returns estimated bytes processed

• SELECT executes within limits

• DML and DDL are rejected before BigQuery execution

• unauthorized projects return Access Denied

• audit logs are emitted to Cloud Logging

Current status as of 2026-06-04: Phase 7 validation is complete. ice-sh readonly checks, SQL guard checks, Cloud Logging audit checks, and unauthorized-project rejection have been verified. The unauthorized-project check used bq-mcp-access-denied-test-2 with a project-level IAM Deny policy for sinohara@impress.co.jp; MCP dry_run_query returned HTTP 403 Forbidden.

Current Pilot

Current status as of 2026-06-09: pilot operation is enabled for sinohara@impress.co.jp only. ALLOWED_PROJECT_IDS is empty after security-owner approval, so the named user may use any BigQuery project readable through their own Google account IAM. DEFAULT_PROJECT_ID=ice-mp keeps ordinary use oriented to the pilot project. ALLOWED_DATASET_IDS is empty, so dataset access is governed by the logged-in user's BigQuery IAM. Firestore-backed MCP session persistence is enabled and validated with SESSION_TTL_SECONDS=3600.

Before expanding beyond the initial pilot user, repeat the rollout checklist in docs/rollout-policy.md and decide whether ALLOWED_PROJECT_IDS, ALLOWED_DATASET_IDS, or additional named-user restrictions are needed.

Audit Logging

Every tool call writes a single-line JSON audit event to stdout. Cloud Run ingests stdout into Cloud Logging, where the message, event_type, severity, and tool-specific fields can be filtered.

Cloud Logging is the required audit source. BigQuery audit dataset export has been evaluated as an optional Phase 8 P2 hardening path, recommended through a Cloud Logging Log Router sink when retention, reporting, or dashboard requirements justify it. Query history UI is deferred until exported audit data and administrator review requirements are confirmed.

Each audit event includes:

• severity

• message

• timestamp

• event_type

• user_email

• tool

• project_id

• dataset

• table

• bytes_processed

• success

• error

• rejection_reason for failed or rejected calls

rejection_reason uses these categories:

• project_not_allowed: project_id is outside ALLOWED_PROJECT_IDS.

• dataset_not_allowed: project_id:dataset_id is outside ALLOWED_DATASET_IDS for dataset-targeting metadata tools.

• user_not_allowed: user email is outside ALLOWED_USER_EMAILS.

• sql_not_allowed: SQL guard rejected a non-readonly or unsafe query.

• bigquery_iam_denied: BigQuery returned a permission-denied response, including HTTP 403.

• bigquery_api_error: BigQuery or HTTP API failed for a non-403 API reason.

• execution_error: internal execution failed outside the known categories.

Some fields may be empty when the tool does not target a dataset or table, or when validation fails before a BigQuery job is created.

Example Cloud Logging filter:

jsonPayload.event_type="bigquery_mcp_tool_call"
jsonPayload.project_id="ice-mp"

To inspect rejected calls by category:

jsonPayload.event_type="bigquery_mcp_tool_call"
jsonPayload.success=false
jsonPayload.rejection_reason="project_not_allowed"

Current Phase Coverage

• Phase 1: FastAPI app, /healthz, /health, /mcp, OAuth authorize/callback skeleton, user email lookup

• Phase 2: six initial BigQuery tools

• Phase 3: readonly SQL guard, maximumBytesBilled, max_results, timeout, basic query error handling

• Phase 4: structured JSON audit logs are emitted to stdout for Cloud Logging ingestion

• Phase 5: Docker, env example, Secret Manager policy, Cloud Run deployment procedure, /health verification, and Firestore-backed session storage are complete for ice-sh

• Phase 6: GitHub Actions workflow, Workload Identity Federation, IAM, GitHub Secrets, deploy verification, and Firestore session env pinning are complete

• Phase 7: ice-sh OAuth, MCP, BigQuery tools, readonly guard, unauthorized-project rejection, and audit log validation are complete

• Phase 8: ALLOWED_PROJECT_IDS, ALLOWED_DATASET_IDS, ALLOWED_USER_EMAILS, structured audit rejection categories, allow/reject tests, current pilot defaults, and Firestore-backed session persistence are implemented and validated; project access is currently user-IAM-scoped for sinohara@impress.co.jp; BigQuery audit dataset export and query history UI are evaluated; rollout patterns, allowlist policy, per-project rollout record template, per-project validation, audit requirements, session policy, and follow-up backlog are documented in docs/rollout-policy.md