Example connecting private Cloud Build pool to Cloud SQL private Postgres database on Google Cloud Platform (GCP)

gcp-cloud-build-cloud-sql.sh

#!/usr/bin/env bash

#####################################################################
# REFERENCES
# - https://cloud.google.com/sql/docs/postgres/configure-private-ip
# - https://cloud.google.com/build/docs/private-pools/create-manage-private-pools#gcloud
# - https://cloud.google.com/build/docs/private-pools/set-up-private-pool-to-use-in-vpc-network#setup-private-connection
#####################################################################

export PROJECT_ID=$(gcloud config get-value project)
export PROJECT_USER=$(gcloud config get-value core/account) # set current user
export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format="value(projectNumber)")
export IDNS=${PROJECT_ID}.svc.id.goog # workflow identity domain

export GCP_REGION="us-central1" # CHANGEME (OPT)
export GCP_ZONE="us-central1-a" # CHANGEME (OPT)
export NETWORK_NAME="default"

# configure gcloud sdk
gcloud config set compute/region $GCP_REGION
gcloud config set compute/zone $GCP_ZONE


#############################################################
# ORGANIZATION
# assumptions
# - one cloud build connects with multiple cloud sql instances in different projects
# projects
# - project 1: cloud sql tenant + cloud build
#############################################################
export FOLDER=$FOLDER # optional - my personal folder
export BILLING=$BILLING # optional - my billing ID shortcut
export PROJECT_1="mike-test-vpn-sql"
export PROJECT_2="mike-test-vpn-cicd"

# create projects
gcloud projects create $PROJECT_1 --folder $FOLDER

# link billing account
gcloud beta billing projects link --billing-account=$BILLING $PROJECT_1

# enable apis
gcloud services enable compute.googleapis.com \
    storage.googleapis.com \
    servicenetworking.googleapis.com \
    sqladmin.googleapis.com \
    cloudbuild.googleapis.com \
    --project $PROJECT_1


#############################################################
# NETWORKING
# - network 1: cloud sql (private tenant)
#   - reserved range: 10.200.0.0/16
# - network 2: cloud build (private pool)
#   - reserved range: 10.210.0.0/16
#############################################################
export NETWORK_1="database"
export NETWORK_1_RESERVED_RANGE_NAME="google-managed-services"
export NETWORK_1_RESERVED_RANGE="10.200.0.0"

# create networks (custom-mode)
gcloud compute networks create $NETWORK_1 \
    --subnet-mode=custom \
    --project $PROJECT_1

# allocate private ranges
gcloud compute addresses create $NETWORK_1_RESERVED_RANGE_NAME \
    --global \
    --purpose=VPC_PEERING \
    --addresses=$NETWORK_1_RESERVED_RANGE \
    --prefix-length=16 \
    --network=projects/$PROJECT_1/global/networks/$NETWORK_1 \
    --project $PROJECT_1

# create peering for managed services
gcloud services vpc-peerings connect \
    --service=servicenetworking.googleapis.com \
    --ranges=$NETWORK_1_RESERVED_RANGE_NAME \
    --network=$NETWORK_1 \
    --project=$PROJECT_1


#############################################################
# DATABASE (POSTGRES)
#############################################################
export POSTGRES_INSTANCE="test-db"
export POSTGRES_VERSION="POSTGRES_14"
export POSTGRES_TIER="db-f1-micro"
export POSTGRES_PORT=5432
export DB_USER="testuser"
export DB_NAME="widgets"
export DB_PASS=$(openssl rand -base64 32)

gcloud beta sql instances create $POSTGRES_INSTANCE \
    --database-version=$POSTGRES_VERSION \
    --tier=$POSTGRES_TIER \
    --network=projects/$PROJECT_1/global/networks/$NETWORK_1 \
    --no-assign-ip \
    --allocated-ip-range-name=$NETWORK_1_RESERVED_RANGE_NAME \
    --region=$GCP_REGION \
    --project=$PROJECT_1

# get internal IP
export POSTGRES_HOST=$(gcloud beta sql instances describe $POSTGRES_INSTANCE --format="value(ipAddresses.ipAddress)" --project $PROJECT_1)

# lock down postgres (admin) user [manually input at prompt]
gcloud sql users set-password postgres \
    --instance=$POSTGRES_INSTANCE \
    --prompt-for-password \
    --project=$PROJECT_1

# create test user
gcloud sql users create $DB_USER \
    --instance=$POSTGRES_INSTANCE \
    --password=$DB_PASS \
    --project=$PROJECT_1

# create database
gcloud sql databases create $DB_NAME \
    --instance=$POSTGRES_INSTANCE \
    --project=$PROJECT_1


#############################################################
# CLOUD BUILD (PRIVATE POOLS)
#############################################################
export PRIVATEPOOL_ID="myco-cicd"
export PRIVATEPOOL_MACHINE_TYPE="e2-standard-2"
export PRIVATEPOOL_DISK_SIZE_GB="100"

gcloud builds worker-pools create $PRIVATEPOOL_ID \
    --project=$PROJECT_1 \
    --region=$GCP_REGION \
    --peered-network=projects/$PROJECT_1/global/networks/$NETWORK_1 \
    --worker-machine-type=$PRIVATEPOOL_MACHINE_TYPE \
    --worker-disk-size=$PRIVATEPOOL_DISK_SIZE_GB \
    --no-public-egress

Private Cloud Build and Cloud SQL connectivity