cloudsql-customers-orders
README.md
# cloudsql-mcp-server
A small, production-style [MCP](https://modelcontextprotocol.io) server in Python that gives an AI agent
**read-only** access to two tables in one Google Cloud SQL (PostgreSQL) database.
| Tool | Table | What it does |
|-----------------------|-------------|------------------------------------------------------------------------------|
| `search_customers` | `customers` | Case-insensitive name search → `id, name, email, country, created_at` |
| `get_customer_orders` | `orders` | Orders for one customer, newest first, optional status filter, `total_amount` |
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.
## Project layout
| File | Purpose |
|-----------------------|----------------------------------------------------------------------------------|
| `server.py` | MCP server (`cloudsql-customers-orders` v0.1.0): tools, transports, auth middleware |
| `db.py` | Data layer: `cloudsql` (Unix socket, pg8000) or `sqlite` backend, `query(sql, params)` |
| `schema.sql` | PostgreSQL DDL for `customers` and `orders` plus commented least-privilege grants |
| `seed_cloudsql.sql` | Sample rows for PostgreSQL (idempotent) |
| `seed_local.py` | Creates `local.db` (SQLite) with the same tables and rows |
| `check_db.py` | Prints backend and row counts; proves database connectivity |
| `test_local.py` | MCP client test over stdio (tool list, three calls, one rejected call) |
| `test_http_auth.sh` | curl test of the HTTP bearer-token gate (200 / 401 / 401 / 200) |
| `Dockerfile` | `python:3.12-slim`, non-root, ships only `server.py`, `db.py`, `requirements.txt` |
| `.env.example` | Environment variables for local and Cloud SQL use (no real values) |
| `.github/workflows/deploy.yml` | CI (SQLite tests) and CD to Cloud Run, authenticating to GCP with WIF |
## Run locally
Requires Python 3.12 and network access to PyPI.
```bash
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:
```bash
# 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
| Variable | Default | Meaning |
|----------------------------|------------------|----------------------------------------------------------------------|
| `DB_BACKEND` | `cloudsql` | `cloudsql` or `sqlite` |
| `SQLITE_PATH` | `./local.db` | SQLite file (sqlite backend) |
| `INSTANCE_CONNECTION_NAME` | | `PROJECT:REGION:INSTANCE` (cloudsql backend) |
| `DB_USER` | | PostgreSQL user, e.g. `mcp_app` |
| `DB_PASS` | | That user's password (from Secret Manager; never commit it) |
| `DB_NAME` | | Database name |
| `DB_SOCKET_DIR` | `/cloudsql` | Directory holding the instance socket; override only for a local proxy |
| `MCP_TRANSPORT` | `stdio` | `stdio` or `streamable-http` |
| `PORT` | `8080` | HTTP port |
| `MCP_AUTH_TOKEN` | | Required in HTTP mode. Comma-separated list, each ≥ 32 characters |
| `MCP_ALLOWED_HOSTS` | | Comma-separated `Host` values; when set, enables DNS-rebinding protection |
| `LOG_LEVEL` | `INFO` | Python log level (logs go to stderr) |
## 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.
```bash
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
```bash
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)
```bash
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.
```bash
# 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:
```bash
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:
```bash
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`.
```sql
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:
```sql
-- 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`:
```sql
\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)
```bash
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](https://cloud.google.com/sql/docs/postgres/connect-auth-proxy)
yourself, so the code path being tested is exactly the deployed one.
Install the dependencies and start the proxy in one terminal:
```bash
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:
```bash
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:
```bash
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
```bash
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.
```bash
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
```bash
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:
```bash
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.
```bash
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*:
| Variable | Example |
|----------------------------|------------------------------------------------------------------------------------------------------|
| `PROJECT_ID` | `my-project` |
| `REGION` | `us-central1` |
| `AR_REPOSITORY` | `mcp` |
| `WIF_PROVIDER` | `projects/123456789/locations/global/workloadIdentityPools/github/providers/github-provider` |
| `DEPLOYER_SA` | `gh-deployer@my-project.iam.gserviceaccount.com` |
| `RUNTIME_SA` | `mcp-server-sa@my-project.iam.gserviceaccount.com` |
| `INSTANCE_CONNECTION_NAME` | `my-project:us-central1:customers-pg` |
| `DB_USER` | `mcp_app` |
| `DB_NAME` | `appdb` |
| `AUTH_TOKEN_SECRET` | `mcp-auth-token` |
| `DB_PASSWORD_SECRET` | `mcp-db-password` |
Print the provider resource name with:
```bash
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**:
| Field | Value |
|------------------------------|-----------------------------------------------------------------------|
| Name | `cloudsql-customers-orders` |
| URL | `https://<your-service>.run.app/mcp` |
| Authentication | **None** (ignore the "Always required — Detected" suggestion; that is the OAuth guess triggered by the 401) |
| Additional request headers | Name `Authorization`, value `Bearer <token>` — the word Bearer, one space, the token |
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)
```bash
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):
```bash
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.
```bash
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):
```bash
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.
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessNo issues