cloudsql-mcp-server

A small, production-style MCP server in Python that gives an AI agent read-only access to two tables in one Google Cloud SQL (PostgreSQL) database.

Each tool is bound to exactly one table, runs one parameterised SELECT, and is annotated readOnlyHint=true so the client knows it cannot change anything.

What is an MCP server?

The Model Context Protocol is a standard way for an AI assistant (such as Claude) to call tools that live outside the model. An MCP server publishes a list of tools with typed inputs; the assistant decides when to call them and receives structured results.

Request path for this project:

Claude ──(MCP over stdio or HTTPS + bearer token)──▶ MCP server ──(Unix socket + password)──▶ Cloud SQL Auth Proxy ──▶ Cloud SQL (PostgreSQL)
                                                                   └── the proxy is run by Cloud Run itself (--add-cloudsql-instances)

1. Claude calls search_customers(name_contains="asha") and gets back customer ids.

2. Claude calls get_customer_orders(customer_id=1, status="pending") and gets the orders.

The server never accepts raw SQL from the model. The SQL is fixed in server.py; the model only supplies validated arguments (pydantic Field constraints), which are bound as query parameters.

Related MCP server: Enterprise PostgreSQL MCP Server

Project layout

Run locally

Requires Python 3.12 and network access to PyPI.

python3.12 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt          # add --break-system-packages if pip refuses

python seed_local.py                     # creates ./local.db
DB_BACKEND=sqlite python check_db.py     # customers: 3, orders: 5
DB_BACKEND=sqlite python test_local.py   # MCP client test over stdio
bash test_http_auth.sh                   # HTTP mode + bearer-token checks

On Windows PowerShell, set variables with $env:DB_BACKEND = "sqlite" before each command, and run test_http_auth.sh from Git Bash or WSL.

Run the server by hand:

# stdio (what `claude mcp add` uses for local servers)
DB_BACKEND=sqlite python server.py

# streamable HTTP on :8080 (a token of at least 32 characters is mandatory)
export MCP_AUTH_TOKEN="$(python -c 'import secrets; print(secrets.token_urlsafe(48))')"
DB_BACKEND=sqlite MCP_TRANSPORT=streamable-http python server.py
curl -s localhost:8080/healthz                       # ok

Starting in HTTP mode without MCP_AUTH_TOKEN, or with a token shorter than 32 characters, exits immediately with a "Refusing to start" message.

To run the local tests against Cloud SQL instead of SQLite, export the Cloud SQL variables from .env.example and set DB_BACKEND=cloudsql; check_db.py and test_local.py respect it.

Environment variables

Deploy to GCP (Cloud Run + Cloud SQL)

All commands are copy-paste ready and were written for Cloud Shell, which already has gcloud, git and Python. Nothing in this repository creates GCP resources by itself.

Shell variables are lost whenever the Cloud Shell session restarts. Re-run this block at the start of every session; an empty variable produces an instance name like :: and a confusing connector error.

export PROJECT_ID=my-project                 # <- your project id
export REGION=us-central1                    # <- your instance's region
export INSTANCE=customers-pg                 # <- your Cloud SQL instance name
export DB_NAME=appdb
export SA_NAME=mcp-server-sa
export SA_EMAIL="$SA_NAME@$PROJECT_ID.iam.gserviceaccount.com"
export DB_USER=mcp_app                       # <- the PostgreSQL user the server logs in as
gcloud config set project "$PROJECT_ID"

echo "$PROJECT_ID:$REGION:$INSTANCE"          # sanity check: no empty segments

1. Enable APIs

gcloud services enable \
  sqladmin.googleapis.com \
  run.googleapis.com \
  cloudbuild.googleapis.com \
  artifactregistry.googleapis.com \
  secretmanager.googleapis.com \
  iam.googleapis.com

2. Create the PostgreSQL 16 instance (POC tier)

gcloud sql instances create "$INSTANCE" \
  --database-version=POSTGRES_16 \
  --edition=ENTERPRISE \
  --tier=db-f1-micro \
  --region="$REGION"

gcloud sql databases create "$DB_NAME" --instance="$INSTANCE"

db-f1-micro is a shared-core proof-of-concept tier; pick a larger tier for real traffic. Provisioning takes 5–10 minutes; gcloud sql instances list shows RUNNABLE when ready.

3. Service account and the application database user

The runtime service account needs cloudsql.client so Cloud Run's built-in Cloud SQL Auth Proxy can open the instance, and secretmanager.secretAccessor so it can read the database password and the bearer token at start-up.

# Runtime identity for Cloud Run
gcloud iam service-accounts create "$SA_NAME" --display-name="MCP customers/orders server"

for ROLE in roles/cloudsql.client roles/secretmanager.secretAccessor; do
  gcloud projects add-iam-policy-binding "$PROJECT_ID" \
    --member="serviceAccount:$SA_EMAIL" --role="$ROLE"
done

Now create the PostgreSQL user the server logs in as. Generate the password, store it in Secret Manager, and create the database user from the same value, so the password is never typed, echoed or kept in shell history:

python3 -c "import secrets; print(secrets.token_urlsafe(32))" \
  | tr -d '\n' \
  | gcloud secrets create mcp-db-password --data-file=- --replication-policy=automatic

gcloud sql users create "$DB_USER" --instance="$INSTANCE" \
  --password="$(gcloud secrets versions access latest --secret=mcp-db-password)"

tr -d '\n' matters: a trailing newline becomes part of the secret, and the login then fails with password authentication failed for a password that looks correct.

Trade-off. This is a stored password rather than IAM database authentication. It is what lets the connection be configured entirely at deploy time — the container speaks plain PostgreSQL to a socket and holds no Google credentials. The cost is a long-lived credential to rotate; see Security model below.

4. Create the tables, seed data, grant read-only access

Run the one-time DDL and grants as the built-in postgres owner, not as mcp_app. mcp_app owns neither the database nor the public schema, and on PostgreSQL 15+ non-owners have no CREATE there, so running schema.sql as mcp_app fails with ERROR: permission denied for schema public.

Set the admin password once (typed interactively; it is never stored in this project, the image, or Secret Manager — the MCP server never uses it), then connect:

gcloud sql users set-password postgres --instance="$INSTANCE" --prompt-for-password
gcloud sql connect "$INSTANCE" --user=postgres --database="$DB_NAME"

At the appdb=> prompt, confirm you are postgres, then enter the following one line at a time. \i is a psql meta-command that consumes the rest of the line, so pasting a whole block makes psql swallow the later statements as filenames and print \i: extra argument ... ignored.

SELECT current_user;              -- must print: postgres

\i schema.sql

\i seed_cloudsql.sql

Now the grants. mcp_app is used by Cloud Run and by the connectivity check below, so one pair of grants covers both:

-- the only privileges the server ever has: read two tables, nothing else
GRANT USAGE ON SCHEMA public TO mcp_app;
GRANT SELECT ON TABLE customers, orders TO mcp_app;

Without these the connectivity check fails with permission denied for table customers even though the login succeeded. GRANT CONNECT ON DATABASE is not needed: every role can connect by default.

Verify, then quit with \q:

\dt
SELECT (SELECT count(*) FROM customers) AS customers, (SELECT count(*) FROM orders) AS orders;   -- 3 | 5
SELECT grantee, table_name, privilege_type FROM information_schema.role_table_grants
 WHERE table_name IN ('customers','orders') AND grantee = 'mcp_app';

If a GRANT reports role "mcp_app" does not exist, the database user from step 3 is missing; create it in another terminal and re-run the grant.

(The same GRANT lines are included, commented out, at the bottom of schema.sql.)

Connect to psql as mcp_app (optional)

gcloud sql connect "$INSTANCE" --user="$DB_USER" --database="$DB_NAME"
# paste the password from: gcloud secrets versions access latest --secret=mcp-db-password

5. Prove connectivity before deploying

The application always connects through a Unix socket. On Cloud Run that socket is created by the platform; on your own machine you create the same thing by running the Cloud SQL Auth Proxy yourself, so the code path being tested is exactly the deployed one.

Install the dependencies and start the proxy in one terminal:

python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

curl -o cloud-sql-proxy \
  https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.14.1/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

mkdir -p /tmp/cloudsql
./cloud-sql-proxy --unix-socket /tmp/cloudsql "$PROJECT_ID:$REGION:$INSTANCE"

In a second terminal, point DB_SOCKET_DIR at that directory and run the check:

export DB_BACKEND=cloudsql
export DB_SOCKET_DIR=/tmp/cloudsql
export INSTANCE_CONNECTION_NAME="$PROJECT_ID:$REGION:$INSTANCE"
export DB_USER=mcp_app DB_NAME=appdb
export DB_PASS="$(gcloud secrets versions access latest --secret=mcp-db-password)"

python check_db.py

Expected output:

backend: cloudsql
customers: 3
orders: 5

Then run the MCP client test against the real database, which exercises both tools end to end:

python test_local.py        # same exported variables

DB_SOCKET_DIR is the only difference between this and production, where it defaults to /cloudsql. If you see Cloud SQL socket '...' does not exist, the proxy is not running or is using a different directory.

6. Generate the bearer token and store it in Secret Manager

python -c "import secrets; print(secrets.token_urlsafe(48))" | \
  gcloud secrets create mcp-auth-token --data-file=- --replication-policy=automatic

# Read it back when configuring a client:
gcloud secrets versions access latest --secret=mcp-auth-token

Treat the token like a password: never paste it into chat, commit it, or leave it in a shell history you share.

7. Deploy to Cloud Run

This is the manual equivalent of what the GitHub Actions workflow does in section 9.

gcloud run deploy cloudsql-mcp-server \
  --source . \
  --region="$REGION" \
  --service-account="$SA_EMAIL" \
  --allow-unauthenticated \
  --add-cloudsql-instances="$PROJECT_ID:$REGION:$INSTANCE" \
  --set-secrets=MCP_AUTH_TOKEN=mcp-auth-token:latest,DB_PASS=mcp-db-password:latest \
  --set-env-vars="MCP_TRANSPORT=streamable-http,DB_BACKEND=cloudsql,INSTANCE_CONNECTION_NAME=$PROJECT_ID:$REGION:$INSTANCE,DB_USER=$DB_USER,DB_NAME=$DB_NAME"

The first build takes 3–5 minutes; answer y if asked to create an Artifact Registry repository.

Notes:

• --add-cloudsql-instances is now required, not optional. It is what makes Cloud Run run the Cloud SQL Auth Proxy alongside the container and mount its socket at /cloudsql/$PROJECT_ID:$REGION:$INSTANCE/.s.PGSQL.5432. Without it the container starts but every query fails with Cloud SQL socket '...' does not exist. All connection setup now lives in this one flag; the application contains none of it.

• DB_PASS comes from Secret Manager, mounted as an environment variable at start-up. It is never baked into the image and never appears in a deploy command or a CI log.

• --allow-unauthenticated means the bearer token is the only network gate. Anyone who can reach the URL and does not present a valid token gets 401. Cloud Run IAM (--no-allow-unauthenticated plus roles/run.invoker) can be added later as a second lock once the client can send Google ID tokens.

• The proxy reaches the instance over its public IP by default; traffic is still TLS-encrypted. For private IP, add --network/--subnet (direct VPC egress) or a Serverless VPC connector — no application change is needed either way.

• Optionally set MCP_ALLOWED_HOSTS=<your-service-host> to turn on DNS-rebinding protection in the MCP SDK. Cloud Run already validates the Host header, so it is off by default.

8. Verify the deployment

export SERVICE_URL="$(gcloud run services describe cloudsql-mcp-server --region="$REGION" --format='value(status.url)')"
echo "[$SERVICE_URL]"                          # must be a https://...run.app URL, not empty

TOKEN="$(gcloud secrets versions access latest --secret=mcp-auth-token)"
BODY='{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"search_customers","arguments":{"name_contains":"asha"}}}'
H='-H Content-Type:application/json -H Accept:application/json,text/event-stream'

curl -s "$SERVICE_URL/healthz"; echo                                                  # ok
curl -s -o /dev/null -w '%{http_code}\n' -X POST "$SERVICE_URL/mcp" $H -d "$BODY"      # 401 (no token)
curl -s -X POST "$SERVICE_URL/mcp" $H -H "Authorization: Bearer $TOKEN" -d "$BODY"     # JSON with Asha's row

Troubleshooting:

• A Google-branded HTML "404. That's an error" page did not come from this server (its own 404 is plain text). It means the request never reached Cloud Run — almost always an empty or mangled SERVICE_URL. Check the echo line and curl the literal URL printed by gcloud run deploy.

• HTTP 500 on the token call — read the logs: gcloud run services logs read cloudsql-mcp-server --region="$REGION" --limit=30. permission denied for table means the service-account GRANT in step 4 is missing.

• Build fails on an import error — the code targets MCP SDK v2 (MCPServer from mcp.server.mcpserver); mcp.server.fastmcp is the v1 API and no longer exists.

• Cloud SQL socket '...' does not exist — the service was deployed without --add-cloudsql-instances, or with a different instance connection name than the one in INSTANCE_CONNECTION_NAME. The two must match exactly.

9. Deploy from GitHub Actions (Workload Identity Federation)

.github/workflows/deploy.yml runs the SQLite tests on every pull request, and on a push to main builds the image, pushes it to Artifact Registry, deploys to Cloud Run and smoke-tests the result. It authenticates to GCP with Workload Identity Federation, so there is no service-account JSON key in the repository or in GitHub secrets.

One-time GCP setup

Run this once, locally, with an account that can administer IAM:

export GITHUB_REPO=cybage-devops/cloudsql-mcp-server
export POOL=github
export PROVIDER=github-provider
export DEPLOYER_SA=gh-deployer
export DEPLOYER_EMAIL="$DEPLOYER_SA@$PROJECT_ID.iam.gserviceaccount.com"
export PROJECT_NUM="$(gcloud projects describe "$PROJECT_ID" --format='value(projectNumber)')"

gcloud services enable iamcredentials.googleapis.com

# An Artifact Registry repository to push images to
gcloud artifacts repositories create mcp \
  --repository-format=docker --location="$REGION" \
  --description="Images for the MCP server"

# The identity pool and the GitHub OIDC provider
gcloud iam workload-identity-pools create "$POOL" --location=global \
  --display-name="GitHub Actions"

gcloud iam workload-identity-pools providers create-oidc "$PROVIDER" \
  --location=global --workload-identity-pool="$POOL" \
  --issuer-uri="https://token.actions.githubusercontent.com" \
  --attribute-mapping="google.subject=assertion.sub,attribute.repository=assertion.repository" \
  --attribute-condition="assertion.repository=='$GITHUB_REPO'"

The --attribute-condition is the security boundary: without it any GitHub repository in the world can mint tokens for this provider. Pin it to your repository.

Next, the deployer service account and its permissions. Keep it separate from the runtime service account: the deployer may create revisions, the runtime may only read the database.

gcloud iam service-accounts create "$DEPLOYER_SA" --display-name="GitHub Actions deployer"

for ROLE in roles/run.admin roles/artifactregistry.writer; do
  gcloud projects add-iam-policy-binding "$PROJECT_ID" \
    --member="serviceAccount:$DEPLOYER_EMAIL" --role="$ROLE"
done

# Required to deploy a service that *runs as* the runtime service account
gcloud iam service-accounts add-iam-policy-binding "$SA_EMAIL" \
  --member="serviceAccount:$DEPLOYER_EMAIL" --role=roles/iam.serviceAccountUser

# Let the pinned GitHub repository impersonate the deployer
gcloud iam service-accounts add-iam-policy-binding "$DEPLOYER_EMAIL" \
  --role=roles/iam.workloadIdentityUser \
  --member="principalSet://iam.googleapis.com/projects/$PROJECT_NUM/locations/global/workloadIdentityPools/$POOL/attribute.repository/$GITHUB_REPO"

GitHub configuration

Everything the workflow needs is a repository variable, not a secret — none of these values is sensitive, and having them visible in the run log is useful. Set them under Settings → Secrets and variables → Actions → Variables:

Print the provider resource name with:

gcloud iam workload-identity-pools providers describe "$PROVIDER" \
  --location=global --workload-identity-pool="$POOL" --format='value(name)'

The deploy job targets a GitHub environment named production; create it (Settings → Environments) if you want required reviewers or a branch restriction in front of deploys.

What the workflow does, and why

• The database password and the bearer token never enter CI. The workflow passes only the names of the Secret Manager secrets to --set-secrets; Cloud Run resolves them at start-up using the runtime service account. A compromised workflow run cannot read either.

• The image is tagged with the commit SHA, not latest, so every revision is traceable to a commit and rollback is a one-liner: gcloud run services update-traffic cloudsql-mcp-server --region=$REGION --to-revisions=<revision>=100.

• Pull requests run the tests but never deploy (if: github.event_name != 'pull_request'), and they never authenticate to GCP at all — the SQLite backend needs no cloud access.

• The smoke test asserts 401, not 200. That proves the revision is serving and that the bearer-token gate is active, without putting a real MCP token into CI.

• A failed smoke test rolls traffic back to the previous active revision, because deploy-cloudrun has already sent 100% of traffic to the new one by that point.

Connect a client

The URL must end in /mcp; the root path is not served.

claude.ai (web / desktop app, custom connector)

Settings → Connectors → Add custom connector:

Save, then in a new chat ask e.g. "Find customers named Asha and list their pending orders". If the connector errors on save, the two usual causes are a missing /mcp and a stray space or newline in the pasted token.

Claude Code (CLI)

export TOKEN="$(gcloud secrets versions access latest --secret=mcp-auth-token)"
claude mcp add --transport http cloudsql-customers-orders "$SERVICE_URL/mcp" \
  --header "Authorization: Bearer $TOKEN"
claude mcp list                                  # should show the server as connected

Add --scope user to make the server available in every project directory. On Windows PowerShell: $TOKEN = gcloud secrets versions access latest --secret=mcp-auth-token then the same claude mcp add line with the literal URL.

For a purely local setup (SQLite, stdio, no token needed):

claude mcp add cloudsql-customers-orders -e DB_BACKEND=sqlite -- "$PWD/.venv/bin/python" "$PWD/server.py"

Docs: https://docs.claude.com/en/docs/claude-code/mcp

Security model

1. Bearer token at the edge. In HTTP mode every request except GET /healthz must carry Authorization: Bearer <token>. Tokens are compared with hmac.compare_digest; failures return 401 {"error":"unauthorized"} with WWW-Authenticate: Bearer. The server refuses to start without a token or with a token shorter than 32 characters. Stdio mode has no network surface and needs no token.

2. Connectivity is configured at deploy time, not in code. Cloud Run runs the Cloud SQL Auth Proxy (--add-cloudsql-instances) and mounts its Unix socket into the container; the proxy authenticates to the instance with the runtime service account and encrypts the connection. The application ships no Google client libraries and holds no cloud credentials — it opens a socket and speaks PostgreSQL. The database login itself uses a password stored in Secret Manager and injected as DB_PASS. This is the one deliberate trade-off of this design: it buys a container with zero connection logic, at the cost of a long-lived credential. Rotate it (see below), and never commit it or pass it through CI.

3. Read-only, bounded tools. Only two tools exist, each tied to one table and one fixed SELECT. Inputs are validated with pydantic (limit ≤ 50 / ≤ 100, name ≤ 100 chars, status is an enum) and bound as parameters. The database user only has SELECT on the two tables, so even a bug in the server cannot write. Every transaction is rolled back.

4. Least privilege on GCP. The runtime service account has only cloudsql.client and secretmanager.secretAccessor. The CI deployer is a separate service account (run.admin, artifactregistry.writer) that can ship revisions but cannot read the database, and it is reachable only from this one GitHub repository via the WIF attribute condition. No service-account key exists for either.

Next steps for hardening:

• Database password rotation: add a new version to the mcp-db-password secret, gcloud sql users set-password "$DB_USER" --instance="$INSTANCE" --password=<new>, then redeploy so the new revision picks up :latest. Because --set-secrets pins latest at revision start-up, running revisions keep the old value until they are replaced — do the redeploy in the same change window.

• Private IP: put the Cloud SQL instance on private IP only and attach Cloud Run to the VPC (direct VPC egress or a Serverless VPC connector). The proxy follows automatically; no application change is needed.

• Token rotation: MCP_AUTH_TOKEN accepts a comma-separated list. Add a new secret version containing old,new, redeploy, switch clients to new, then add a version with only new and redeploy again.

• Statement timeout on the application role so a bad query cannot hang the server: ALTER ROLE mcp_app SET statement_timeout = '10s';

• Cloud Run IAM as a second lock (--no-allow-unauthenticated), an ingress restriction (--ingress=internal-and-cloud-load-balancing) or Cloud Armor in front of the service.

• Observability: Cloud Run request logs already record each 401; add alerting on the rate of 401 responses.

Tear down

The Cloud SQL instance bills by the hour even when idle.

gcloud run services delete cloudsql-mcp-server --region="$REGION" --quiet
gcloud sql instances delete "$INSTANCE" --quiet
gcloud secrets delete mcp-auth-token --quiet
gcloud secrets delete mcp-db-password --quiet
gcloud iam service-accounts delete "$SA_EMAIL" --quiet

If you also set up CI (section 9):

gcloud artifacts repositories delete mcp --location="$REGION" --quiet
gcloud iam service-accounts delete "$DEPLOYER_EMAIL" --quiet
gcloud iam workload-identity-pools delete github --location=global --quiet

Design choices worth knowing

• Cloud SQL connections are opened per query and closed right after (with a rollback). This is simple and safe on Cloud Run where instances scale to zero. Connecting over the local Unix socket is cheap because the proxy holds the real TLS session to the instance. If query volume ever makes the per-query connect measurable, add a sqlalchemy pool in db.py — nothing outside that module would change.

• The application has no cloud-specific code at all: db.py opens /cloudsql/<instance>/.s.PGSQL.5432 with pg8000 and authenticates with a password. DB_SOCKET_DIR exists so the identical code path can be exercised locally against a hand-started cloud-sql-proxy.

• ? placeholders are used in the shared SQL and rewritten to %s for pg8000. Because all values are bound parameters, the SQL text never contains user data.

• Blocking database calls run in a worker thread (anyio.to_thread) so the async MCP server stays responsive.

• server.py checks the installed SDK's signatures (inspect) for a couple of optional keyword arguments such as version, so minor SDK revisions do not break start-up.

• status is typed as an enum (pending/shipped/delivered/cancelled) instead of a free string so the model sees the valid values in the tool schema.

• get_customer_orders orders by ordered_at DESC, id DESC so results are stable when two orders share a timestamp.