Last active
January 16, 2025 18:22
-
-
Save aamir814/6156fc3b378bf39470875d01093feb63 to your computer and use it in GitHub Desktop.
Connect to a Cloud SQL instance using Private Service Connect
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| ##################################################################### | |
| # Purpose: Following is an example on how to connect to Cloud SQL instance | |
| # from different VPC using Private Service Connect. I followed | |
| # the instructions mentioned in Google document | |
| # (1st URL in refrence section). Created two VPCs | |
| # | |
| # REFERENCES | |
| # - https://cloud.google.com/sql/docs/postgres/configure-private-service-connect | |
| # - https://cloud.google.com/blog/products/databases/private-service-connect-for-cloud-sql-databases/?utm_source=convertkit&utm_medium=email&utm_campaign=GCP%20Newsletter%20#394%20-%2013627950 | |
| ##################################################################### | |
| 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 | |
| # CHANGEME (OPT) | |
| export GCP_REGION="us-central1" | |
| export GCP_ZONE="us-central1-a" | |
| export NETWORK_NAME="default" | |
| export SQL_NETWORK_NAME="cloudsql" | |
| export SUBNET_NAME="sql-psc" | |
| # enable apis | |
| gcloud services enable compute.googleapis.com \ | |
| storage.googleapis.com \ | |
| sqladmin.googleapis.com \ | |
| dns.googleapis.com | |
| # configure gcloud sdk | |
| gcloud config set compute/region $GCP_REGION | |
| gcloud config set compute/zone $GCP_ZONE | |
| ########## create VPC for Cloud SQL ########## | |
| gcloud compute networks create $SQL_NETWORK_NAME \ | |
| --subnet-mode=auto | |
| # default firewall rules | |
| gcloud compute firewall-rules create ${SQL_NETWORK_NAME}-allow-internal \ | |
| --action=ALLOW \ | |
| --direction=INGRESS \ | |
| --network=${SQL_NETWORK_NAME} \ | |
| --priority=1000 \ | |
| --rules=tcp:0-65535,udp:0-65535,icmp \ | |
| --source-ranges=10.128.0.0/9 | |
| gcloud compute firewall-rules create ${SQL_NETWORK_NAME}-allow-ssh \ | |
| --action=ALLOW \ | |
| --direction=INGRESS \ | |
| --network=${SQL_NETWORK_NAME} \ | |
| --priority=1000 \ | |
| --rules=tcp:22 \ | |
| --source-ranges=0.0.0.0/0 | |
| gcloud compute firewall-rules create ${SQL_NETWORK_NAME}-allow-icmp \ | |
| --action=ALLOW \ | |
| --direction=INGRESS \ | |
| --network=${SQL_NETWORK_NAME} \ | |
| --priority=1000 \ | |
| --rules=icmp \ | |
| --source-ranges=0.0.0.0/0 | |
| gcloud compute firewall-rules create ${SQL_NETWORK_NAME}-allow-db \ | |
| --action=ALLOW \ | |
| --direction=INGRESS \ | |
| --network=${SQL_NETWORK_NAME} \ | |
| --priority=1000 \ | |
| --rules=tcp:5432 \ | |
| --source-ranges=10.10.0.0/24 | |
| # add a subnet | |
| gcloud compute networks subnets create $SUBNET_NAME \ | |
| --network=$SQL_NETWORK_NAME \ | |
| --range=10.10.0.0/24 \ | |
| --region=$GCP_REGION | |
| ########## create a cloud sql instance ########## | |
| export DB_INSTANCE_NAME="pgtest" | |
| export DB_INSTANCE_VERSION="POSTGRES_15" | |
| gcloud sql instances create $DB_INSTANCE_NAME \ | |
| --project=$PROJECT_ID \ | |
| --region=$GCP_REGION \ | |
| --enable-private-service-connect \ | |
| --allowed-psc-projects=$PROJECT_ID \ | |
| --availability-type=regional \ | |
| --no-assign-ip \ | |
| --cpu=2 \ | |
| --memory=7680MB \ | |
| --database-version=$DB_INSTANCE_VERSION | |
| # To update an existing Cloud SQL Instance run the following | |
| # gcloud sql instances patch $DB_INSTANCE_NAME \ | |
| # --project=$PROJECT_ID \ | |
| # --allowed-psc-projects=$PROJECT_ID | |
| # create postgres database | |
| export DB_NAME="psc" | |
| gcloud sql databases create $DB_NAME \ | |
| --instance=$DB_INSTANCE_NAME | |
| # override postgres password | |
| gcloud sql users set-password postgres \ | |
| --instance=$DB_INSTANCE_NAME \ | |
| --prompt-for-password | |
| # verify db | |
| gcloud sql databases list --instance $DB_INSTANCE_NAME | |
| # Get the service attachment URI | |
| SVC_ATTACH=$(gcloud sql instances describe $DB_INSTANCE_NAME --format='value(pscServiceAttachmentLink)') | |
| ########## create PSC endpoint for each VPC ########## | |
| gcloud compute addresses create psc-sql-ep \ | |
| --project=$PROJECT_ID \ | |
| --region=$GCP_REGION \ | |
| --subnet=$SUBNET_NAME \ | |
| --purpose=GCE_ENDPOINT | |
| # verify the address and make a note of it. These will be used in creating DNS record | |
| gcloud compute addresses list --filter="name=( 'NAME' 'psc-sql-ep')" --project=$PROJECT_ID | |
| ########## create forwarding-rule ########## | |
| gcloud compute forwarding-rules create forward-sql-rule-1 \ | |
| --project=$PROJECT_ID \ | |
| --region=$GCP_REGION \ | |
| --network=$SQL_NETWORK_NAME \ | |
| --target-service-attachment=$SVC_ATTACH \ | |
| --address=psc-sql-ep | |
| # verify the forwarding-rules | |
| gcloud compute forwarding-rules describe forward-sql-rule-1 \ | |
| --project=$PROJECT_ID \ | |
| --region=$GCP_REGION | |
| # List PSC endpoints to validate | |
| gcloud compute forwarding-rules list --filter="name=( 'NAME' 'forward-sql-rule-1')" \ | |
| --regions=$GCP_REGION | |
| ########## Configure DNS managed zone ########## | |
| # get Cloud SQL DNS name | |
| SQL_DNS_NAME=$(gcloud sql instances describe $DB_INSTANCE_NAME --project=$PROJECT_ID --format='value(dnsName)') | |
| # create DNS zone in each VPC | |
| gcloud dns managed-zones create sql-zone \ | |
| --project=$PROJECT_ID \ | |
| --description="zone to connect to cloud sql in different vpc" \ | |
| --dns-name=$SQL_DNS_NAME \ | |
| --networks=$SQL_NETWORK_NAME \ | |
| --visibility=private | |
| # create DNS record for zone. Note the IP address is from endpoint | |
| # from above step | |
| gcloud dns record-sets create $SQL_DNS_NAME \ | |
| --project=$PROJECT_ID \ | |
| --type=A \ | |
| --rrdatas=10.10.0.4 \ | |
| --zone=sql-zone | |
| gcloud compute addresses describe psc-sql-ep | |
| ########## create VM in VPC to test Cloud SQL connection ########## | |
| # VM in cloudsql VPC | |
| gcloud compute instances create sql-vpc \ | |
| --project=$PROJECT_ID \ | |
| --zone=$GCP_ZONE \ | |
| --machine-type=e2-micro \ | |
| --network-interface=network-tier=PREMIUM,stack-type=IPV4_ONLY,subnet=$SQL_NETWORK_NAME \ | |
| --service-account=974657876698-compute@developer.gserviceaccount.com \ | |
| --create-disk=auto-delete=yes,boot=yes,device-name=sql-vpc,image=projects/debian-cloud/global/images/debian-11-bullseye-v20231010,mode=rw,size=10,type=projects/csql-project/zones/us-central1-a/diskTypes/pd-balanced \ | |
| --no-shielded-secure-boot \ | |
| --shielded-vtpm \ | |
| --shielded-integrity-monitoring \ | |
| --labels=goog-ec-src=vm_add-gcloud \ | |
| --reservation-affinity=any | |
| # ssh to each server | |
| gcloud compute ssh sql-vpc --zone=$GCP_ZONE | |
| # install psql client on both machines | |
| # sudo apt-get update | |
| # sudo apt-get install -y postgresql-client-13 | |
| # validate that you are in cloudsql VPC | |
| # curl "http://metadata.google.internal/computeMetadata/v1/instance/network-interfaces/?recursive=true&alt=text" -H "Metadata-Flavor: Google";echo | |
| # connect to database using DNS name | |
| # psql -h 2f78f3566258.3uwpf52ydytcp.us-central1.sql.goog. -U postgres -d psc | |
| ########## clean up ########## | |
| gcloud compute instances delete sql-vpc | |
| gcloud dns record-sets delete $SQL_DNS_NAME --type=A --zone=sql-zone | |
| gcloud dns managed-zones delete sql-zone | |
| gcloud compute forwarding-rules delete forward-sql-rule-1 | |
| gcloud compute addresses delete psc-sql-ep | |
| gcloud sql instances delete $DB_INSTANCE_NAME | |
| gcloud compute networks subnets delete $SUBNET_NAME | |
| gcloud compute firewall-rules delete ${SQL_NETWORK_NAME}-allow-ssh | |
| gcloud compute firewall-rules delete ${SQL_NETWORK_NAME}-allow-db | |
| gcloud compute firewall-rules delete ${SQL_NETWORK_NAME}-allow-icmp | |
| gcloud compute firewall-rules delete ${SQL_NETWORK_NAME}-allow-internal | |
| gcloud compute networks delete $SQL_NETWORK_NAME |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Nice!!!