Connect DB Lab to a Runway production Cloud SQL instance

This runbook explains how to give DB Lab (GCP project gitlab-db-lab) network access to a Runway-managed Cloud SQL instance in gitlab-runway-production, so DB Lab can refresh from production with pg_dump / pg_restore.

It covers the MRs that are needed, the order to apply them in, how to verify each step, and how to roll back.

Note

The first instance onboarded this way is runway-db-artifact-registry-gke (postgres-ai/collaboration-tracking#23). It’s used as the worked example throughout. Sections marked (to be confirmed) will be updated once that rollout is complete (dbo#808).

Architecture

Runway GKE Cloud SQL instances are created by the Runway provisioner (modules/runtimes/gcp_gke/cloudsql_managed.tf) with:

Setting	Value	What it means for DB Lab
ipv4_enabled	false	No public IP.
psc_enabled	true	Reachable only through Private Service Connect (PSC). There is no private IP either.
psc_allowed_consumer_projects	["gitlab-runway-<env>"]	Only the Runway project can create PSC endpoints to it.
connector_enforcement	REQUIRED	Direct connections are refused. Clients must use the Cloud SQL Auth Proxy or a Cloud SQL connector.
ssl_mode	ENCRYPTED_ONLY	TLS is required.

So DB Lab needs four things:

1. Permission to connect: gitlab-db-lab added to the instance’s allowed PSC consumer projects.
2. A network path: a PSC endpoint in the db-lab VPC that points at the instance’s service attachment, plus a private DNS record for the instance’s PSC DNS name.
3. A proxy identity: a service account the Cloud SQL Auth Proxy on the DB Lab host can use, with roles/cloudsql.client on gitlab-runway-production.
4. The Cloud SQL Admin API enabled in gitlab-db-lab: the proxy’s API calls are billed to the service account’s own project, not to gitlab-runway-production.

lab-ar-cloudsql-01 (db-lab VPC, us-west1)
  └─ cloud-sql-proxy --psc  (127.0.0.1:5432, SA key)
       └─ PSC endpoint psc-cloudsql-<name> (db-lab, psc-us-east1 subnet 10.185.4.0/24, global access)
            └─ service attachment ──▶ Cloud SQL instance (gitlab-runway-production)

Why not VPC peering?

The instances have no private IP to route to, so peering db-lab with Runway’s gke-network wouldn’t reach them, and it would join two networks that don’t otherwise need to talk. A PSC endpoint is scoped to one instance, is created and removed in db-lab only, and matches how db-lab already reaches Vault and Teleport (environments/db-lab/private_service_connect.tf in config-mgmt).

Why a dedicated service account?

DB Lab VMs run as the shared terraform@gitlab-db-lab account, and the generic-stor module hard-codes access scopes that don’t include the Cloud SQL Admin API, which the proxy needs. Rather than widen the shared account or change the module (which needs a VM stop/start), the proxy gets its own account and uses --credentials-file.

Prerequisites

• The DB Lab host already exists (for the example: lab-ar-cloudsql-01-db-db-lab.c.gitlab-db-lab.internal, from config-mgmt!15466).
• Read access to gitlab-runway-production and gitlab-db-lab (DBREs get this through gcp-ops-sg).
• Approvers:
  • Provisioner: @gitlab-org/production-engineering/runway or @gitlab-com/gl-infra/data-access/dbo (co-owners of the Cloud SQL paths in CODEOWNERS).
  • config-mgmt: the usual reviewers for environments/db-lab and environments/env-projects, plus @gitlab-org/production-engineering/runway for environments/runway-production (CODEOWNERS).
• SSH access to the DB Lab host through the db-lab bastion, with sudo.
• Agreement from the service owner that their production data may be copied to DB Lab, and a decision on whether it needs masking before clones go to developers.
• Change management: follow the change management process for the production database user step. (to be confirmed: criticality)

Find the connection details

Replace INSTANCE with the Cloud SQL instance name.

INSTANCE=runway-db-artifact-registry-gke
PROJECT=gitlab-runway-production

# Version, region, connection name, PSC DNS name and service attachment
gcloud sql instances describe "$INSTANCE" --project "$PROJECT" \
  --format='yaml(databaseVersion,region,connectionName,dnsName,pscServiceAttachmentLink,settings.connectorEnforcement,settings.ipConfiguration.pscConfig)'

# Databases (the app database is the one that isn't "postgres")
gcloud sql databases list --instance "$INSTANCE" --project "$PROJECT"

# Existing users
gcloud sql users list --instance "$INSTANCE" --project "$PROJECT"

For the example instance:

Connection name	gitlab-runway-production:us-east1:runway-db-artifact-registry-gke
Postgres version	18
Database	gitlabhq_artifact_registry
PSC DNS name	bc31df7510c8.1j8qe9r0u8e30.us-east1.sql.goog.
Service attachment	projects/y6db9b88071371215p-tp/regions/us-east1/serviceAttachments/a-bc31df7510c8-psc-service-attachment-7ac7bb21ef9df237
Private IP	None. DB Lab uses the PSC endpoint created in step 2.

Steps

Apply the MRs in this order. Each step has a check; don’t start the next step until it passes.

1. Provisioner: allow gitlab-db-lab as a PSC consumer

Repo: Runway provisioner. Example: provisioner!2068.

In config/runtimes/gke/cloud-sql/managed.yml, under the instance’s env_configuration.production, add:

      psc_extra_consumer_projects: ["gitlab-db-lab"]

psc_extra_consumer_projects is added by provisioner!2068 (module variable, cloudsql_managed.tf and the JSON schema). Once that’s merged, onboarding the next instance is just this one line.

Check the Atlantis plan before applying: the only change for this instance is an in-place update of settings.ip_configuration.psc_config.allowed_consumer_projects. If the plan replaces the instance, stop. The plan may also show unrelated drift on main; check with Runway before applying it together.

Verify:

gcloud sql instances describe "$INSTANCE" --project "$PROJECT" \
  --format='value(settings.ipConfiguration.pscConfig.allowedConsumerProjects)'
# expect: gitlab-db-lab;gitlab-runway-production

2. config-mgmt: PSC endpoint in db-lab

Repo: config-mgmt. Example: config-mgmt!15864.

In environments/db-lab/private_service_connect.tf, add an entry to local.private_service_connect_consumer, using the values from Find the connection details:

    cloudsql-artifact-registry = {
      region      = "us-east1" # the instance's region
      target      = "<pscServiceAttachmentLink>"
      private_dns = "<dnsName, with the trailing dot>"
      allow_ports = [5432]
    }

This creates an internal address in the psc-<region> subnet, a forwarding rule with global access (so hosts in other regions can use it), and a private DNS zone and record for the instance’s PSC DNS name, which cloud-sql-proxy --psc resolves. If the region has no psc-<region> subnet yet, add its range to var.subnetworks in the same MR.

Expected plan: 4 to add (address, forwarding rule, DNS zone, DNS record), nothing changed or destroyed.

Verify:

gcloud compute forwarding-rules describe psc-cloudsql-artifact-registry \
  --region us-east1 --project gitlab-db-lab --format='value(IPAddress,pscConnectionStatus)'
# expect: 10.185.4.x  ACCEPTED

PENDING or REJECTED means step 1 isn’t applied or the consumer project is wrong.

3. config-mgmt: proxy service account and role

Example: config-mgmt!15865.

• environments/db-lab: a google_service_account for the proxy (example: dblab-ar-cloudsql-proxy).
• environments/runway-production/iam.tf: roles/cloudsql.client for that account, limited by an IAM condition to the one instance:

  condition {
    title      = "cloudsql_client_<instance>"
    expression = "resource.name == 'projects/${var.project}/instances/<instance>'"
  }

Apply environments/db-lab first (atlantis apply -d environments/db-lab), then environments/runway-production, because the IAM member needs the account to exist. This step doesn’t depend on steps 1 and 2.

Then create the key by hand, so it never lands in Terraform state. Store it as a Document item in the 1Password Production vault, delete the local copy, and place it on the DB Lab host.

umask 077 && gcloud iam service-accounts keys create /tmp/dblab-ar-cloudsql-proxy.json \
  --iam-account [email protected] --project gitlab-db-lab
op document create /tmp/dblab-ar-cloudsql-proxy.json --vault "Production" \
  --title "DBLab AR cloud-sql-proxy SA key (dblab-ar-cloudsql-proxy@gitlab-db-lab)"
rm -f /tmp/dblab-ar-cloudsql-proxy.json

Without the op CLI, drag the file into a new Document item in the 1Password app instead, then run the rm -f line. In the item’s notes, record the key ID, the tracking issue, and that it must be rotated if the host is rebuilt.

The local copy only exists for a moment and is created with umask 077. rm -P and shred don’t reliably overwrite files on SSD or APFS, so the protection is keeping the key only in 1Password and on the DB Lab host.

Verify:

gcloud projects get-iam-policy gitlab-runway-production \
  --flatten='bindings[].members' \
  --filter='bindings.members:[email protected]' \
  --format='table(bindings.role,bindings.condition.expression)'

# expect exactly one user-managed key
gcloud iam service-accounts keys list --managed-by=user \
  --iam-account [email protected] --project gitlab-db-lab

4. config-mgmt: enable the Cloud SQL Admin API in gitlab-db-lab

Example: config-mgmt!15878. Only needed once per consumer project.

The proxy calls the Cloud SQL Admin API to fetch the instance’s metadata and ephemeral certificate, and that call is billed to the service account’s own project. Without this step the proxy starts, but every connection fails with:

googleapi: Error 403: Cloud SQL Admin API has not been used in project 258165776685 before or it is disabled. (SERVICE_DISABLED)

In environments/env-projects/gitlab-db-lab.tf:

  api_services = setunion(local.api_services["common"], ["sqladmin.googleapis.com"])

Expected plan: 1 to add (google_project_service for sqladmin.googleapis.com). This grants no permissions.

Verify:

gcloud services list --enabled --project gitlab-db-lab --filter='config.name=sqladmin.googleapis.com'

5. DB Lab host: proxy, key and Postgres client

SSH to the host through the db-lab bastion:

ssh -J lb-bastion.db-lab.gitlab.com lab-ar-cloudsql-01-db-db-lab.c.gitlab-db-lab.internal

Install cloud-sql-proxy, pinned and checksum-verified (checksums are in the release notes):

curl -fsSL -o /tmp/cloud-sql-proxy \
  https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.27.0/cloud-sql-proxy.linux.amd64
echo "39ed2abcaa073c3b4714585e36a904e2b1d92218c5e07c18224fb73ef4bedde5  /tmp/cloud-sql-proxy" | sha256sum -c -
sudo install -m 0755 /tmp/cloud-sql-proxy /usr/local/bin/cloud-sql-proxy && rm /tmp/cloud-sql-proxy

Place the key from step 3 on the host, readable by root only. From your laptop, with the key downloaded from 1Password, stream it over SSH so it isn’t copied anywhere else, then delete the download:

ssh -J lb-bastion.db-lab.gitlab.com lab-ar-cloudsql-01-db-db-lab.c.gitlab-db-lab.internal \
  'sudo -n sh -c "umask 077; mkdir -p /etc/cloud-sql-proxy && cat > /etc/cloud-sql-proxy/dblab-ar-cloudsql-proxy.json"' \
  < ~/Downloads/dblab-ar-cloudsql-proxy.json
rm -f ~/Downloads/dblab-ar-cloudsql-proxy.json

Install a Postgres client that matches the server’s major version (18 for the example). Ubuntu’s own postgresql-client is too old: pg_dump refuses to dump a newer server. Use the PGDG repository. NEEDRESTART_MODE=l stops needrestart from prompting to restart services on the host:

sudo apt-get update
sudo NEEDRESTART_MODE=l apt-get install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo NEEDRESTART_MODE=l apt-get install -y postgresql-client-18

Verify the proxy end to end. Port 5433 keeps the test clear of anything already on 5432:

sudo cloud-sql-proxy --psc --credentials-file /etc/cloud-sql-proxy/dblab-ar-cloudsql-proxy.json --port 5433 \
  gitlab-runway-production:us-east1:runway-db-artifact-registry-gke > /tmp/csp.log 2>&1 &
sleep 5; pg_isready -h 127.0.0.1 -p 5433; cat /tmp/csp.log

Expect Accepted connection in the log with no failed to connect to instance after it. The error says which step is missing:

Error contains	Check
SERVICE_DISABLED	Step 4 (Admin API)
NOT_AUTHORIZED / 403 on cloudsql.instances	Step 3 (IAM binding and condition)
no such host	Step 2 (private DNS)
i/o timeout to the PSC address	Step 1 or 2 (endpoint not ACCEPTED)

The proxy handles TLS to the instance, so local connections to 127.0.0.1 use sslmode=disable.

6. Read-only source user

Create the user in SQL, not with gcloud sql users create or the console. Users created through the Cloud SQL API get cloudsqlsuperuser, which can’t be revoked without logging in as postgres anyway.

1. In the 1Password Production vault, create a Password item for the new user (example title: Cloud SQL Postgres: runway-db-artifact-registry-gke (production) - postgres_ai, username postgres_ai). Generate 32+ letters and digits, no symbols, so it’s safe in connection strings.

2. With the proxy from step 5 running, connect as the built-in postgres user. Its password is in the 1Password Production vault (example item: Cloud SQL Postgres: runway-db-artifact-registry-gke (production)). Don’t reset it: Runway manages it.

   psql "host=127.0.0.1 port=5433 user=postgres dbname=gitlabhq_artifact_registry sslmode=disable"

3. Create the role, then set its password with \password, which keeps it out of the terminal and the logs. Run \password on its own: if it’s pasted together with other lines, psql treats them as extra arguments and skips them.

   CREATE ROLE postgres_ai LOGIN;

   \password postgres_ai

4. Grant read access and check the result:

   GRANT pg_read_all_data TO postgres_ai;
   GRANT CONNECT ON DATABASE gitlabhq_artifact_registry TO postgres_ai;
   SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'postgres_ai';
   SELECT r.rolname AS member_of FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.roleid
     WHERE m.member = 'postgres_ai'::regrole;

Expect postgres_ai | f | f | f, and member_of showing only pg_read_all_data, with no cloudsqlsuperuser. Share the password from 1Password, never in Slack or an issue.

7. DB Lab configuration

In database-lab-scripts, add the instance directory and deploy job (example: deploy_dblab_ar). Unlike instances that dump from a private IP (such as customers), the job must run cloud-sql-proxy --psc on the host and point pg_dump at 127.0.0.1:5432. The host needs the proxy, the key and a matching pg_dump from step 5; install them through the deploy job too, so a rebuilt host gets them back. (to be confirmed once the first refresh runs)

Verify with a test dump:

pg_dump --schema-only "host=127.0.0.1 port=5432 user=postgres_ai dbname=gitlabhq_artifact_registry" | head

Rollback

Undo in reverse order. Each step is independent of the database’s own data.

1. DB Lab: stop the refresh job and the proxy on the host.
2. Source user: connected as postgres to gitlabhq_artifact_registry, run REVOKE CONNECT ON DATABASE gitlabhq_artifact_registry FROM postgres_ai; DROP ROLE postgres_ai;. DROP ROLE alone fails because of the CONNECT grant from step 6.
3. Host: remove /etc/cloud-sql-proxy/. The proxy binary and Postgres client can stay.
4. Admin API: usually leave it enabled; revert the step 4 MR only if nothing else in gitlab-db-lab uses it.
5. Proxy access: delete the service account key (gcloud iam service-accounts keys delete) and its 1Password item, then revert the step 3 MR.
6. Network path: revert the step 2 MR. This removes the endpoint and the DNS zone.
7. Consumer project: revert the psc_extra_consumer_projects line from step 1. This is an in-place update. Any endpoint that still exists in gitlab-db-lab is closed by the producer.

Security notes

• Connector enforcement stays REQUIRED and SSL stays ENCRYPTED_ONLY; nothing in this process relaxes them.
• The proxy account only has roles/cloudsql.client, and only on the one instance (IAM condition).
• The source user only has pg_read_all_data. It can read every table in every database on the instance, but can’t write or change settings.
• The service account key is a long-lived credential: keep it only in the 1Password Production vault and on the DB Lab host, and rotate it if the host is rebuilt.
• Decide with the service owner whether the data needs masking before clones are handed to developers.

Onboarding another instance

1. Collect the connection details for the new instance.
2. Step 1: one line in managed.yml.
3. Step 2: one entry in private_service_connect.tf. (open question: move this into a reusable module)
4. Step 3: one more IAM member (or one more condition) for the proxy account of that DB Lab host.
5. Step 4: nothing, as long as the new proxy account is also in gitlab-db-lab.
6. Steps 5 to 7 as above.

Cloud Run provisioned instances (modules/runtimes/gcp_cloud_run/cloudsql_managed.tf) use a different network setup and don’t have psc_extra_consumer_projects. (to be confirmed)

Ownership

This runbook is owned by Database Excellence (DBO) and reviewed jointly with Runway during the co-ownership period (runway/team#936).

Related

• dbo#808: this runbook
• postgres-ai/collaboration-tracking#23: Artifact Registry DB Lab request
• Restore/Backup Runway-managed Cloud SQL