About this codelab
1. Introduction
In this codelab you will learn how to deploy a Private Service Connection for Cloud SQL and how to access the Cloud SQL service using the deployed Private Service Connect. .
You can get more information about Private Service Connect here.
Prerequisites
- A basic understanding of the Google Cloud Console
- Basic skills in command line interface and Google Cloud shell
What you'll learn
- How to deploy a Cloud SQL instance
- How to deploy a Private Service Connect
- How to connect from a VM to a Cloud SQL Instance via Private Service Connect
What you'll need
- A Google Cloud Account and Google Cloud Project
- A web browser such as Chrome
2. Setup and Requirements
Self-paced environment setup
- Sign-in to the Google Cloud Console and create a new project or reuse an existing one. If you don't already have a Gmail or Google Workspace account, you must create one.
- The Project name is the display name for this project's participants. It is a character string not used by Google APIs. You can update it at any time.
- The Project ID must be unique across all Google Cloud projects and is immutable (cannot be changed after it has been set). The Cloud Console auto-generates a unique string; usually you don't care what it is. In most codelabs, you'll need to reference the Project ID (it is typically identified as
PROJECT_ID
). If you don't like the generated ID, you may generate another random one. Alternatively, you can try your own and see if it's available. It cannot be changed after this step and will remain for the duration of the project. - For your information, there is a third value, a Project Number which some APIs use. Learn more about all three of these values in the documentation.
Caution: A project ID is globally unique and can't be used by anyone else after you've selected it. You are the only user of that ID. Even if a project is deleted, the ID can't be used again
- Next, you'll need to enable billing in the Cloud Console to use Cloud resources/APIs. Running through this codelab won't cost much, if anything at all. To shut down resources to avoid incurring billing beyond this tutorial, you can delete the resources you created or delete the project. New Google Cloud users are eligible for the $300 USD Free Trial program.
Start Cloud Shell
While Google Cloud can be operated remotely from your laptop, in this codelab you will be using Google Cloud Shell, a command line environment running in the Cloud.
From the Google Cloud Console, click the Cloud Shell icon on the top right toolbar:
It should only take a few moments to provision and connect to the environment. When it is finished, you should see something like this:
This virtual machine is loaded with all the development tools you'll need. It offers a persistent 5GB home directory, and runs on Google Cloud, greatly enhancing network performance and authentication. All of your work in this codelab can be done within a browser. You do not need to install anything.
3. Before you begin
Enable API
Please be aware that some resources you enable are going to incur some cost if you are not using the promotional tier. In normal circumstances if all the resources are destroyed upon completion of the lab the cost of all resources would not exceed $5. We recommend checking your billing and making sure the exercise is acceptable for you.
Inside Cloud Shell, make sure that your project ID is setup:
Usually the project ID is shown in parentheses in the command prompt in the cloud shell as it is shown in the picture:
gcloud config set project [YOUR-PROJECT-ID]
A window will pop up which asks for Cloud Shell Authorization. Please click on Authorize
Then set the PROJECT_ID environment variable to your Google Cloud project ID:
PROJECT_ID=$(gcloud config get-value project)
Set the REGION and ZONE environment variable to your preferred region and zone:
REGION=europe-west4
ZONE=europe-west4-a
Enable all necessary services:
gcloud services enable compute.googleapis.com \
sqladmin.googleapis.com \
dns.googleapis.com \
iam.googleapis.com
Expected output:
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud services enable compute.googleapis.com \ sqladmin.googleapis.com \ dns.googleapis.com \ iam.googleapis.com Operation "operations/acat.p2-577410439131-dfb33f74-3447-485c-bae2-bc130126c965" finished successfully.
4. Deploy a Cloud SQL for Postgres Instance
In the Cloud Shell you can use the command line to create a new Cloud SQL Postgres instance with Private Service Connect enabled:
gcloud sql instances create cloudsql-postgres \
--project=$PROJECT_ID \
--region=$REGION \
--enable-private-service-connect \
--allowed-psc-projects=$PROJECT_ID \
--availability-type=ZONAL \
--no-assign-ip \
--cpu=2 \
--memory=7680MB \
--edition=ENTERPRISE \
--database-version=POSTGRES_16
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud sql instances create cloudsql-postgres \ --project=$PROJECT_ID \ --region=europe-west4 \ --enable-private-service-connect \ --allowed-psc-projects=$PROJECT_ID \ --availability-type=ZONAL \ --no-assign-ip \ --cpu=2 \ --memory=7680MB \ --edition=ENTERPRISE \ --database-version=POSTGRES_16 Creating Cloud SQL instance for POSTGRES_16...done. Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/psc-cloud-sql-test02/instances/cloudsql-postgres1]. NAME: cloudsql-postgres DATABASE_VERSION: POSTGRES_16 LOCATION: europe-west4-b TIER: db-custom-2-7680 PRIMARY_ADDRESS: - PRIVATE_ADDRESS: - STATUS: RUNNABLE
Change password for database user postgres after successful installation of Cloud SQL for Postgres:
gcloud sql users set-password postgres \
--instance=cloudsql-postgres \
--password='changeme'
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud sql users set-password postgres \ --instance=cloudsql-postgres \ --password='changeme' Updating Cloud SQL user...done.
5. Setup Private Service Connect
For the following network related tasks the assumption is that a VPC named default is in place.
Reserve an internal IP address
Find the VPC subnet CIDR range in the GCP region referred to in the environment variable REGION and choose a free IP address in this CIDR range for the Privat Service Connect endpoint:
gcloud compute networks subnets describe default \
--region=$REGION --project=$PROJECT_ID \
--format="value(ipCidrRange)"
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute networks subnets describe default \ --region=$REGION --project=$PROJECT_ID \ --format="value(ipCidrRange)" 10.164.0.0/20
Reserve an internal IP address for the Private Service Connect endpoint in the derived VPC subnet CIDR range above:
gcloud compute addresses create cloudsql-psc \
--project=$PROJECT_ID \
--region=$REGION \
--subnet=default \
--addresses=10.164.0.10
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute addresses create cloudsql-psc \ --project=$PROJECT_ID \ --region=$REGION \ --subnet=default \ --addresses=10.164.0.10 Created [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/regions/europe-west4/addresses/cloudsql-psc].
Verify that the internal IP address is reserved and that the status RESERVED appears for the IP address.
gcloud compute addresses list --project=$PROJECT_ID \
--filter="name=cloudsql-psc"
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute addresses list --project=$PROJECT_ID \ --filter="name=cloudsql-psc" NAME: cloudsql-psc ADDRESS/RANGE: 10.164.0.10 TYPE: INTERNAL PURPOSE: GCE_ENDPOINT NETWORK: REGION: europe-west4 SUBNET: default STATUS: RESERVED
Get the service attachment URI
After creating a Cloud SQL instance with Private Service Connect enabled, get the service attachment URI and use it to create the Private Service Connect endpoint with the reserved internal IP address above.
gcloud sql instances describe cloudsql-postgres \
--project=$PROJECT_ID --format="value(pscServiceAttachmentLink)"
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud sql instances describe cloudsql-postgres --project=$PROJECT_ID \ --format="value(pscServiceAttachmentLink)" projects/l639336e2c716e3d8p-tp/regions/europe-west4/serviceAttachments/a-33446dfaf850-psc-service-attachment-e6471fc6708a6cfe
Create the Private Service Connect
Create the Private Service Connect endpoint and point it to the Cloud SQL service attachment URI:
gcloud compute forwarding-rules create cloudsql-psc-ep \
--address=cloudsql-psc \
--project=$PROJECT_ID \
--region=$REGION \
--network=default \
--target-service-attachment=projects/l639336e2c716e3d8p-tp/regions/europe-west4/serviceAttachments/a-33446dfaf850-psc-service-attachment-e6471fc6708a6cfe \
--allow-psc-global-access
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute forwarding-rules create cloudsql-psc-ep \ --address=cloudsql-psc \ --project=$PROJECT_ID \ --region=$REGION \ --network=default \ --target-service-attachment=projects/l639336e2c716e3d8p-tp/regions/europe-west4/serviceAttachments/a-33446dfaf850-psc-service-attachment-e6471fc6708a6cfe \ --allow-psc-global-access Created [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/regions/europe-west4/forwardingRules/cloudsql-psc-ep].
Verify that the endpoint can connect to the service attachment:
gcloud compute forwarding-rules describe cloudsql-psc-ep \
--project=$PROJECT_ID \
--region=$REGION \
--format="value(pscConnectionStatus)"
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute forwarding-rules describe cloudsql-psc-ep \ --project=$PROJECT_ID \ --region=$REGION \ --format="value(pscConnectionStatus)" ACCEPTED
Configure a DNS managed zone
To add the suggested DNS name for the Cloud SQL instance it is best to create a private DNS zone in the corresponding VPC network:
gcloud dns managed-zones create cloudsql-dns \
--project=$PROJECT_ID \
--description="DNS zone for the Cloud SQL instances" \
--dns-name=$REGION.sql.goog. \
--networks=default \
--visibility=private
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud dns managed-zones create cloudsql-dns \ --project=$PROJECT_ID \ --description="DNS zone for the Cloud SQL instances" \ --dns-name=$REGION.sql.goog. \ --networks=default \ --visibility=private Created [https://dns.googleapis.com/dns/v1/projects/psc-cloud-sql-test02/managedZones/cloudsql-dns].
Add a DNS record for the Private Service Connect
Get the suggested DNS record for the Cloud SQL instance:
gcloud sql instances describe cloudsql-postgres \
--project=$PROJECT_ID \
--format="value(dnsName)"
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud sql instances describe cloudsql-postgres --project=$PROJECT_ID --format="value(dnsName)" 33446dfaf850.1rrhex0himzzc.europe-west4.sql.goog.
Add the suggested DNS record to the DNS managed zone
gcloud dns record-sets create 3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog. \
--project=$PROJECT_ID \
--type=A \
--rrdatas=10.164.0.10 \
--zone=cloudsql-dns
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud dns record-sets create 3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog. \ --project=$PROJECT_ID \ --type=A \ --rrdatas=10.164.0.10 \ --zone=cloudsql-dns NAME: 3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog. TYPE: A TTL: 0 DATA: 10.164.0.10
6. Prepare Google Compute Engine Virtual Machine
Deploy Google Compute Engine Virtual Machine
The Virtual Machine (VM) will be used to connect to the Cloud SQL instance.
gcloud compute instances create cloudsql-client \
--zone=$ZONE \
--create-disk=auto-delete=yes,boot=yes,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" --format="value(name)") \
--scopes=https://www.googleapis.com/auth/cloud-platform \
--network-interface=no-address
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute instances create cloudsql-client \ --zone=$ZONE \ --create-disk=auto-delete=yes,boot=yes,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" --format="value(name)") \ --scopes=https://www.googleapis.com/auth/cloud-platform \ --network-interface=no-address Created [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/zones/europe-west4-a/instances/cloudsql-client]. NAME: cloudsql-client ZONE: europe-west4-a MACHINE_TYPE: n1-standard-1 PREEMPTIBLE: INTERNAL_IP: 10.164.0.2 EXTERNAL_IP: STATUS: RUNNING
Install Postgres Client
Create a Cloud NAT for outbound traffic to the internet that the VM is able to access the Linux repositories:
gcloud compute routers create cloud-nat-router \
--network=default \
--region=$REGION
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute routers create cloud-nat-router \ --network=default \ --region=$REGION Creating router [cloud-nat-router]...done. NAME: cloud-nat-router REGION: europe-west4 NETWORK: default
gcloud compute routers nats create cloud-nat \
--router=cloud-nat-router \
--region=$REGION \
--nat-all-subnet-ip-ranges \
--auto-allocate-nat-external-ips
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute routers nats create cloud-nat \ --router=cloud-nat-router \ --region=$REGION \ --nat-all-subnet-ip-ranges \ --auto-allocate-nat-external-ips Creating NAT [cloud-nat] in router [cloud-nat-router]...done.
Install the PostgreSQL client software on the deployed VM
Connect to the VM:
gcloud compute ssh --zone $ZONE "cloudsql-client" \
--tunnel-through-iap --project $PROJECT_ID
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute ssh --zone "europe-west4-a" "cloudsql-client" --tunnel-through-iap --project $PROJECT_ID WARNING: To increase the performance of the tunnel, consider installing NumPy. For instructions, please see https://cloud.google.com/iap/docs/using-tcp-forwarding#increasing_the_tcp_upload_bandwidth Warning: Permanently added 'compute.1355719684363734964' (ED25519) to the list of known hosts. Linux cloudsql-client 6.1.0-26-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.112-1 (2024-09-30) x86_64 The programs included with the Debian GNU/Linux system are free software; the exact distribution terms for each program are described in the individual files in /usr/share/doc/*/copyright. Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent permitted by applicable law. Creating directory '/home/student_org_altostrat_com'.
Install the software running command inside the VM:
sudo apt-get update
sudo apt-get install --yes postgresql-client
Expected Output
student@cloudsql-client:~$ sudo apt-get update sudo apt-get install --yes postgresql-client Get:1 file:/etc/apt/mirrors/debian.list Mirrorlist [30 B] Get:5 file:/etc/apt/mirrors/debian-security.list Mirrorlist [39 B] Get:7 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable InRelease [1321 B] Get:2 https://deb.debian.org/debian bookworm InRelease [151 kB] Get:3 https://deb.debian.org/debian bookworm-updates InRelease [55.4 kB] Get:4 https://deb.debian.org/debian bookworm-backports InRelease [59.0 kB] ...redacted... update-alternatives: using /usr/share/postgresql/15/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-client (15+248) ... Processing triggers for man-db (2.11.2-2) ... Processing triggers for libc-bin (2.36-9+deb12u8) ...
7. Connect to Cloud SQL for Postgres Instance via Private Service Connect
Connect to the Instance
psql "sslmode=disable dbname=postgres user=postgres host=3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog."
Expected Output
student@cloudsql-client:~$ psql "sslmode=disable dbname=postgres user=postgres host=3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog." Password for user postgres: psql (15.8 (Debian 15.8-0+deb12u1), server 16.4) WARNING: psql major version 15, server major version 16. Some psql features might not work. Type "help" for help. postgres=>
Create and test database
Create a database
CREATE DATABASE company;
Expected Output
postgres=> CREATE DATABASE company; CREATE DATABASE postgres=>
List all databases
\l
Expected Output
postgres=> \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges ---------------+-------------------+----------+------------+------------+------------+-----------------+----------------------------------------- cloudsqladmin | cloudsqladmin | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc | company | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc | postgres | cloudsqlsuperuser | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc | template0 | cloudsqladmin | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc | =c/cloudsqladmin + | | | | | | | cloudsqladmin=CTc/cloudsqladmin template1 | cloudsqlsuperuser | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc | =c/cloudsqlsuperuser + | | | | | | | cloudsqlsuperuser=CTc/cloudsqlsuperuser (5 rows) postgres=>
Connect to employees database
\c company
Expected Output
postgres=> \c company psql (15.8 (Debian 15.8-0+deb12u1), server 16.4) WARNING: psql major version 15, server major version 16. Some psql features might not work. You are now connected to database "company" as user "postgres". company=>
Create a table in the company database
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first VARCHAR(255) NOT NULL,
last VARCHAR(255) NOT NULL,
salary DECIMAL (10, 2)
);
Expected Output
company=> CREATE TABLE employees ( id SERIAL PRIMARY KEY, first VARCHAR(255) NOT NULL, last VARCHAR(255) NOT NULL, salary DECIMAL (10, 2) ); CREATE TABLE
Insert data into the table employees of company database
INSERT INTO employees (first, last, salary) VALUES
('Max', 'Mustermann', 5000.00),
('Anna', 'Schmidt', 7000.00),
('Peter', 'Mayer', 6000.00);
Expected Output
company=> INSERT INTO employees (first, last, salary) VALUES ('Max', 'Mustermann', 5000.00), ('Anna', 'Schmidt', 7000.00), ('Peter', 'Mayer', 6000.00); INSERT 0 3 company=>
Query employees table
SELECT * FROM employees;
Expected Output
company=> SELECT * FROM employees; id | first | last | salary ----+-------+------------+--------- 1 | Max | Mustermann | 5000.00 2 | Anna | Schmidt | 7000.00 3 | Peter | Mayer | 6000.00 (3 rows) company=>
Exit the Postgres database and the VM and return to Cloud Shell:
\q
exit
Expected Output
postgres=> \q student@cloudsql-client:~$ exit logout Connection to compute.1355719684363734964 closed. student@cloudshell:~ (psc-cloud-sql-test02)$
8. Clean up environment
Destroy all Google Cloud resources in the project when you are done with the lab.
Delete Cloud SQL Postgres instance
In the Cloud Shell delete the Cloud SQL for Postgres instance:
gcloud sql instances delete cloudsql-postgres --quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud sql instances delete cloudsql-postgres --quiet Deleting Cloud SQL instance...done. Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/psc-cloud-sql-test02/instances/cloudsql-postgres].
Delete Google Compute Engine Virtual Machine
In the Cloud Shell delete the VM:
gcloud compute instances delete cloudsql-client \
--zone=$ZONE \
--quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute instances delete cloudsql-client \ --quiet$ZONE \ --quiet Deleted [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/zones/europe-west4-a/instances/cloudsql-client].
Delete network components
Delete network related components: Cloud NAT, Cloud Router, Private Service Connect Endpoint, reserved internal IP address, DNS record and DNS managed zone.
Delete Cloud NAT:
gcloud compute routers nats delete cloud-nat \
--router=cloud-nat-router \
--region=$REGION \
--quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute routers nats delete cloud-nat \ --router=cloud-nat-router \ --region=$REGION \ --quiet Updated [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/regions/europe-west4/routers/cloud-nat-router].
Delete Cloud Router:
gcloud compute routers delete cloud-nat-router \
--region=$REGION \
--quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute routers delete cloud-nat-router \ --region=$REGION \ --quiet Deleted [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/regions/europe-west4/routers/cloud-nat-router].
Delete Private Service Connect Endpoint:
gcloud compute forwarding-rules delete cloudsql-psc-ep \
--project=$PROJECT_ID \
--region=$REGION \
--quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute forwarding-rules delete cloudsql-psc-ep \ --project=$PROJECT_ID \ --region=$REGION \ --quiet Deleted [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/regions/europe-west4/forwardingRules/cloudsql-psc-ep].
Release reserved internal IP address::
gcloud compute addresses delete cloudsql-psc \
--project=$PROJECT_ID \
--region=$REGION \
--quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud compute addresses delete cloudsql-psc \ --project=$PROJECT_ID \ --region=$REGION \ --quiet Deleted [https://www.googleapis.com/compute/v1/projects/psc-cloud-sql-test02/regions/europe-west4/addresses/cloudsql-psc].
Delete DNS record:
gcloud dns record-sets delete 3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog. \
--project=$PROJECT_ID \
--type=A \
--zone=cloudsql-dns
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud dns record-sets delete 3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog. \ --project=$PROJECT_ID \ --type=A \ --zone=cloudsql-dns Deleted [https://dns.googleapis.com/dns/v1/projects/psc-cloud-sql-test02/managedZones/cloudsql-dns/rrsets/3446dfaf850.1rrhex0himzzc.europe-west4.sql.goog./A].
Delete DNS managed zone:
gcloud dns managed-zones delete cloudsql-dns \
--project=$PROJECT_ID \
--quiet
Expected Output
student@cloudshell:~ (psc-cloud-sql-test)$ gcloud dns managed-zones delete cloudsql-dns \ --project=$PROJECT_ID \ --quiet Deleted [https://dns.googleapis.com/dns/v1/projects/psc-cloud-sql-test02/managedZones/cloudsql-dns].
9. Congratulations
Congratulations for completing the codelab.
What we've covered
- How to deploy a Cloud SQL instance
- How to deploy a Private Service Connect
- How to connect from a VM to a Cloud SQL Instance via Private Service Connect