arrow_back

Connect to Cloud SQL from an Application in Kubernetes Engine

Join Sign in

Connect to Cloud SQL from an Application in Kubernetes Engine

1 hour 15 minutes 5 Credits

GSP449

Google Cloud self-paced labs logo

Overview

This lab shows how easy it is to connect an application in Kubernetes Engine to a Cloud SQL instance using the Cloud SQL Proxy container as a sidecar container. You will deploy a Kubernetes Engine cluster and a Cloud SQL Postgres instance and use the Cloud SQL Proxy container to allow communication between them.

While this lab is focused on connecting to a Cloud SQL instance with a Cloud SQL Proxy container, the concepts are the same for any Google Cloud managed service that requires API access.

This lab was created by GKE Helmsman engineers to help you gain a better understanding of Cloud SQL through a proxy container. You can view this demo on Github on the gke-networking-demos page. We encourage any and all to contribute to our assets!

What you'll learn: In this lab, you'll learn how to:

  • Protect your database from unauthorized access by using an unprivileged service account on your Kubernetes Engine nodes.
  • Put privileged service account credentials into a container running on Kubernetes Engine.
  • Use the Cloud SQL Proxy to offload the work of connecting to your Cloud SQL instance and reduce your applications knowledge of your infrastructure.

Unprivileged service accounts

All Kubernetes Engine nodes are assigned the default Compute Engine service account. This service account is fairly high privilege and has access to many Google Cloud services. Because of the way the Cloud SDK is setup, software that you write will use the credentials assigned to the compute engine instance on which it is running.

Since you don't want all of your containers to have the privileges that the default Compute Engine service account has, you need to make a least-privilege service account for your Kubernetes Engine nodes and then create more specific (but still least-privilege) service accounts for your containers.

Privileged service accounts in containers

The only two ways to get service account credentials are through:

  1. Your host instance (which you don't want)
  2. A credentials file

This lab will show you how to get the credentials file into your container running in Kubernetes Engine so your application has the privileges it needs.

Cloud SQL Proxy

The Cloud SQL Proxy allows you to offload the burden of creating and maintaining a connection to your Cloud SQL instance to the Cloud SQL Proxy process. Doing this allows your application to be unaware of the connection details and simplifies your secret management. The Cloud SQL Proxy comes pre-packaged by Google as a Docker container that you can run alongside your application container in the same Kubernetes Engine pod.

Architecture

The application and its sidecar container are deployed in a single Kubernetes (k8s) pod running on the only node in the Kubernetes Engine cluster. The application communicates with the Cloud SQL instance via the Cloud SQL Proxy process listening on localhost.

The k8s manifest builds a single-replica Deployment object with two containers, pgAdmin and Cloud SQL Proxy. There are two secrets installed into the Kubernetes Engine cluster: the Cloud SQL instance connection information and a service account key credentials file, both used by the Cloud SQL Proxy containers Cloud SQL API calls.

The application doesn't have to know anything about how to connect to Cloud SQL, nor does it have to have any exposure to its API. The Cloud SQL Proxy process takes care of that for the application. It's important to note that the Cloud SQL Proxy container is running as a 'sidecar' container in the pod.

Application flow in a Kubernetes cluster

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
  • Time to complete the lab---remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

How to start your lab and sign in to the Google Cloud Console

  1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

    • The Open Google Console button
    • Time remaining
    • The temporary credentials that you must use for this lab
    • Other information, if needed, to step through this lab
  2. Click Open Google Console. The lab spins up resources, and then opens another tab that shows the Sign in page.

    Tip: Arrange the tabs in separate windows, side-by-side.

    Note: If you see the Choose an account dialog, click Use Another Account.
  3. If necessary, copy the Username from the Lab Details panel and paste it into the Sign in dialog. Click Next.

  4. Copy the Password from the Lab Details panel and paste it into the Welcome dialog. Click Next.

    Important: You must use the credentials from the left panel. Do not use your Google Cloud Skills Boost credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.
  5. Click through the subsequent pages:

    • Accept the terms and conditions.
    • Do not add recovery options or two-factor authentication (because this is a temporary account).
    • Do not sign up for free trials.

After a few moments, the Cloud Console opens in this tab.

Note: You can view the menu with a list of Google Cloud Products and Services by clicking the Navigation menu at the top-left. Navigation menu icon

Activate Cloud Shell

Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.

  1. Click Activate Cloud Shell Activate Cloud Shell icon at the top of the Google Cloud console.

When you are connected, you are already authenticated, and the project is set to your PROJECT_ID. The output contains a line that declares the PROJECT_ID for this session:

Your Cloud Platform project in this session is set to YOUR_PROJECT_ID

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

  1. (Optional) You can list the active account name with this command:

gcloud auth list
  1. Click Authorize.

  2. Your output should now look like this:

Output:

ACTIVE: * ACCOUNT: student-01-xxxxxxxxxxxx@qwiklabs.net To set the active account, run: $ gcloud config set account `ACCOUNT`
  1. (Optional) You can list the project ID with this command:

gcloud config list project

Output:

[core] project = <project_ID>

Example output:

[core] project = qwiklabs-gcp-44776a13dea667a6 Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.

Set your region and zone

Certain Compute Engine resources live in regions and zones. A region is a specific geographical location where you can run your resources. Each region has one or more zones.

Run the following to set a region and zone for your lab (you can use the region/zone that's best for you):

gcloud config set compute/region us-central1 gcloud config set compute/zone us-central1-a

Copy the demo

  1. Run the following to copy the demo:
gsutil cp gs://spls/gsp449/gke-cloud-sql-postgres-demo.tar.gz . tar -xzvf gke-cloud-sql-postgres-demo.tar.gz
  1. Go into the directory for this demo:

cd gke-cloud-sql-postgres-demo

Task 1. Deployment

Deployment is fully automated. The script you will deploy takes the following parameters, in order:

  • A username for your Cloud SQL instance

  • A username for the pgAdmin console

  • USER_PASSWORD - the password to login to the Postgres instance

  • PG_ADMIN_CONSOLE_PASSWORD - the password to login to the pgAdmin UI

  1. You can create any username for the Cloud SQL instance and use any email for the pgAdmin console; the example here uses "dbadmin" and your temporary student email.

  2. Save your student account in a variable:

PG_EMAIL=$(gcloud config get-value account)
  1. Run the following to deploy the script and create the 2 usernames; you will be asked to create a password for dbadmin and $PG_EMAIL (your student@qwiklabs.net account) in the output:

./create.sh dbadmin $PG_EMAIL

The passwords will be used again later in the lab; they don't need to be difficult.

During the deployment, create.sh will run the following scripts:

  • enable_apis.sh - enables the Kubernetes Engine API and Cloud SQL Admin API.
  • postgres_instance.sh - creates the Cloud SQL instance and additional Postgres user. Note that gcloud will timeout when waiting for the creation of a Cloud SQL instance so the script manually polls for its completion instead.
  • service_account.sh - creates the service account for the Cloud SQL Proxy container and creates the credentials file.
  • cluster.sh - Creates the Kubernetes Engine cluster.
  • configs_and_secrets.sh - creates the Kubernetes Engine secrets and configMap containing credentials and connection string for the Cloud SQL instance.
  • pgadmin_deployment.sh - creates the pgAdmin4 pod.
Note: Deployment of the Cloud SQL instance can take up to 10 minutes. If you encounter an error in the automated deployment script, ensure your region and zone variables are set and then try re-running the create.shscript.

Next, use load balancer to expose the pod in order to connect to the instance, then delete the services when finished to avoid unauthorized access.

  1. Run the following to get the Pod ID:

POD_ID=$(kubectl --namespace default get pods -o name | cut -d '/' -f 2)
  1. Expose the pod via load balancer:

kubectl expose pod $POD_ID --port=80 --type=LoadBalancer
  1. Get the service IP address:

kubectl get svc

Output:

NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE kubernetes ClusterIP 443/TCP 96m SVC_NAME LoadBalancer 80:31789/TCP 45m Note: Run the previous command again until you see an external IP address for the pgAdmin service.
  1. Open a new tab and connect to pgAdmin in your browser the using th pgAdmin <SVC_IP>:

http://<SVC_IP>
  1. Sign in to the pgAdmin UI with the following:

  • <PGADMIN_USERNAME> (your temporary student@qwiklabs.net account) in the "Email Address" field
  • <PG_ADMIN_CONSOLE_PASSWORD> that you defined earlier
Note: If you're unsure what your full student account is, run gcloud config get-value account in your Cloud Shell and copy the output.
  1. In the pgAdmin console, from the left pane click Servers, then click Add New Server.

  2. On the General tab, give your server a name, then click on the Connection tab.

  3. Use the <DATABASE_USER_NAME>(dbadmin) and <USER_PASSWORD> you created earlier to connect to 127.0.0.1:5432:

  • Host name: 127.0.0.1
  • Username: <DATABASE_USER_NAME>(dbadmin)
  • Password: <USER_PASSWORD> you created

Connection tab with Host name/address, Username and Password fields populated

  1. Click Save.

Test completed task

Click Check my progress to verify your performed task. If you have successfully created required resources with the fully automated deployment, you will see an assessment score.

Create required resources with the fully automated deployment

Task 2. Validation

Validation is fully automated. The validation script checks for the existence of the Cloud SQL instance, the Kubernetes Engine cluster, and the running pod. All of these resources should exist after the deployment script completes.

  • In Cloud Shell, validate these three deployments by executing:

make validate

The script takes the parameters INSTANCE_NAME: The name of the existing Cloud SQL instance.

A successful output looks like this:

Cloud SQL instance exists GKE cluster exists pgAdmin4 Deployment object exists

Task 3. Teardown

Teardown is fully automated. The teardown script deletes every resource created in the deployment script.

  1. In order to teardown, run:

make teardown

The script takes the parameter INSTANCE_NAME: The name of the existing Cloud SQL instance.

teardown.sh runs the following scripts:

  • delete_resources.sh - deletes everything but the Cloud SQL instance

  • delete_instance.sh - deletes the Cloud SQL instance

Task 4. Troubleshooting in your own environment

When creating a Cloud SQL instance you get the error:

is the subject of a conflict: The instance or operation is not in an appropriate state to handle the request.

Resolution

You cannot reuse an instance name for up to a week after you have deleted an instance. For more information, refer to [Delete instances](https://cloud.google.com/sql/docs/mysql/delete-instance}.

Congratulations!

You connected an application in Kubernetes Engine to a Cloud SQL instance using the Cloud SQL Proxy container as a sidecar container. You then deployed a Kubernetes Engine cluster and a Cloud SQL Postgres instance and used the Cloud SQL Proxy container to allow communication between them.

Finish your quest

This self-paced lab is part of the Google Kubernetes Engine Best Practices quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll in this quest and get immediate completion credit. Refer to the Google Cloud Skills Boost catalog for all available quests.

Next steps / Learn more

Kubernetes Installation instructions to deploy a containerized application for multiple platforms.

Manual Last Updated: September 23, 2022

Lab Last Tested: September 23, 2022

Copyright 2022 Google LLC. This software is provided as-is, without warranty or representation for any use or purpose. Your use of it is subject to your agreement with Google.