Looker PSC Southbound access to Cloud SQL PSC

1. Introduction

NOTE: Looker supports Southbound Producers in any region. For Northbound access to Looker, the consumer endpoint or backend must be in the same region as the Looker PSC instance.

In this codelab you will perform a southbound connection to Cloud SQL deployed with Private Service Connect as a Service Producer.

Private Service Connect is a capability of Google Cloud networking that allows consumers to access managed services privately from inside their VPC network. Similarly, it allows managed service producers to host these services in their own separate VPC networks and offer a private connection to their consumers. For example, when you use Private Service Connect to access Looker, you are the service consumer, and Google is the service producer, as highlighted in Figure 1.

Southbound access, also known as reverse PSC, enables the Consumer to create a Published Service as a Producer to allow Looker access to endpoints on-premises, in a VPC, to managed services and the Internet. Southbound connections can be deployed in any region, irrespective of where Looker PSC is deployed, as highlighted in Figure 2.

What you'll learn

• Create a Private Service Connect Cloud SQL instance producer service
• Create a Private Service Connect endpoint in Looker as a service consumer

What you'll need

• Google Cloud Project with Owner permissions
• Existing Looker PSC Instance

2. What you'll build

You'll establish a Cloud SQL PSC instance deployed as Service Producer associated with Looker PSC as a Service Consumer.

The following actions validate access to the Producer service:

• Create a PSC Endpoint in Looker associated with the Producer Service Attachment
• Use the Looker Console to create a database connection to Cloud SQL PSC
• Test connectivity to the Cloud SQL PSC by authenticating and accessing a predefined scheme

3. Codelab topology

4. Setup and Requirements

Self-paced environment setup

1. 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 always update it.
• The Project ID is 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 your Project ID (typically identified as PROJECT_ID). If you don't like the generated ID, you might generate another random one. Alternatively, you can try your own, and see if it's available. It can't be changed after this step and remains 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

Note: If you use a Gmail account, you can leave the default location set to No organization. If you use a Google Workspace account, choose a location that makes sense for your organization.

2. 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.

5. Before you begin

Enable APIs

Inside Cloud Shell, make sure that your project id is set up:

gcloud config list project
gcloud config set project [YOUR-PROJECT-ID]
project=[YOUR-PROJECT-ID]
region=[YOUR-REGION]
echo $project
echo $region

Enable all necessary services:

gcloud services enable compute.googleapis.com

6. Identify the Looker PSC Project

Creating a Cloud SQL PSC instance requires identification of allowed psc projects, therefore, the Looker PSC tenant project must be supplied at the time of Cloud SQL instance creation or updated if using an existing instance.

Determine Looker PSC Project using gcloud

Inside Cloud Shell, update [INSTANCE_NAME] with your looker PSC instance name to obtain the Looker PSC project id:

gcloud looker instances describe [INSTANCE_NAME] --region=$region --format=json | grep -i lookerServiceAttachmentUri

In the Example output,t7ec792caf2a609d1-tp, is the Looker PSC project:

gcloud looker instances describe looker-psc-instance --region=us-central1 --format=json | grep -i lookerServiceAttachmentUri
    "lookerServiceAttachmentUri": "projects/t7ec792caf2a609d1-tp/regions/us-central1/serviceAttachments/looker-psc-f51982e2-ac0d-48b1-91bb-88656971c183",

Determine Looker PSC Project using Cloud Console

In Cloud Console, navigate to:

Looker → Looker PSC Instance

7. Create Producer Cloud SQL instance

In the following section, you'll create a Cloud SQL PSC instance used for testing purposes deployed with a predefined password specifying the Looker PSC project in the allowed psc project list.

NOTE: The Cloud SQL PSC instance root password is predefined as password123. This instance is used for demonstrative purposes.

Cloud SQL PSC creation

Inside Cloud Shell, create an instance and enable Private Service Connect, update [INSTANCE_PROJECT] with your Looker PSC Project ID.

gcloud sql instances create cloud-sql-psc-demo \
--project=$project \
--region=$region \
--enable-private-service-connect \
--allowed-psc-projects=[INSTANCE_PROJECT] \
--availability-type=regional \
--no-assign-ip \
--cpu=2 --memory=4GB \
--database-version=MYSQL_8_0 \
--enable-bin-log \
--root-password=password123

Example:

gcloud sql instances create cloud-sql-psc-demo \
--project=$project \
--region=$region \
--enable-private-service-connect \
--allowed-psc-projects=t7ec792caf2a609d1-tp \
--availability-type=regional \
--no-assign-ip \
--cpu=2 --memory=4GB \
--database-version=MYSQL_8_0 \
--enable-bin-log \
--root-password=password123

Obtain the Cloud SQL Service Attachment

After creating a Cloud SQL instance with Private Service Connect enabled, get the service attachment URI used in a later step to create the Private Service Connect endpoint in Looker.

Inside Cloud Shell, perform the following:

gcloud sql instances describe [INSTANCE_NAME] --project=$project | grep -i pscServiceAttachmentLink

Example output produces the service attachment URI:

projects/o6222fa10e2a50865p-tp/regions/us-central1/serviceAttachments/a-39d809aaecae-psc-service-attachment-127fc34733744155

gcloud sql instances describe cloud-sql-psc-demo --project=$project | grep -i pscServiceAttachmentLink:
pscServiceAttachmentLink: projects/o6222fa10e2a50865p-tp/regions/us-central1/serviceAttachments/a-39d809aaecae-psc-service-attachment-127fc34733744155

From Cloud Console you can obtain the Cloud SQL PSC Service Attachment:

In Cloud Console, navigate to:

SQL → SQL Instance → Connections

8. Establish a PSC Endpoint Connection in Looker

In the following section, you will associate the Cloud SQL Producers Service Attachment with Looker Core PSC through the use –psc-service-attachment flags in Cloud Shell for a single domain.

NOTE: Each time you run an update command with –psc-service-attachment flags, you must include every connection that you want to be enabled, including connections that were already enabled previously. Reference Looker PSC docs for an example.

NOTE: You're Looker PSC Instance needs to be enabled before proceeding.

Inside Cloud Shell, create the psc association by updating the following parameters to match your environment:

• INSTANCE_NAME: The name of your Looker (Google Cloud core) instance.
• DOMAIN_1: sql.database1.com
• SERVICE_ATTACHMENT_1: URI captured when describing the Cloud SQL PSC instance
• REGION: The region in which your Looker (Google Cloud core) instance is hosted

Inside Cloud Shell, perform the following:

gcloud looker instances update INSTANCE_NAME \
--psc-service-attachment  domain=DOMAIN_1,attachment=SERVICE_ATTACHMENT_URI_1 \
--region=REGION

Example:

gcloud looker instances update looker-psc-instance \
--psc-service-attachment  domain=sql.database1.com,attachment=projects/o6222fa10e2a50865p-tp/regions/us-central1/serviceAttachments/a-39d809aaecae-psc-service-attachment-127fc34733744155 \
--region=us-central1

Inside Cloud Shell, validate the serviceAttachments connectionStatus is "ACCEPTED". Ensure to update with your Looker PSC INSTANCE_NAME:

gcloud looker instances describe [INSTANCE_NAME] --region=$region --format=json

Example:

gcloud looker instances describe looker-psc-instance --region=$region --format=json

Example:

user@cloudshell$ gcloud looker instances describe looker-psc-instance --region=$region --format=json
{
  "adminSettings": {},
  "createTime": "2024-08-23T00:00:45.339063195Z",
  "customDomain": {
    "domain": "looker.cosmopup.com",
    "state": "AVAILABLE"
  },
  "encryptionConfig": {},
  "lookerVersion": "24.14.18",
  "name": "projects/project/locations/us-central1/instances/looker-psc-instance",
  "platformEdition": "LOOKER_CORE_ENTERPRISE_ANNUAL",
  "pscConfig": {
    "allowedVpcs": [
      "projects/project/global/networks/looker-psc-demo",
      "projects/project/global/networks/looker-shared-vpc"
    ],
    "lookerServiceAttachmentUri": "projects/t7ec792caf2a609d1-tp/regions/us-central1/serviceAttachments/looker-psc-f51982e2-ac0d-48b1-91bb-88656971c183",
    "serviceAttachments": [
      {
        "connectionStatus": "ACCEPTED",
        "localFqdn": "sql.database1.com",
        "targetServiceAttachmentUri": "projects/o6222fa10e2a50865p-tp/regions/us-central1/serviceAttachments/a-39d809aaecae-psc-service-attachment-127fc34733744155"
      }
    ]
  },
  "pscEnabled": true,
  "state": "ACTIVE",
  "updateTime": "2024-09-04T14:40:05.613026012Z"
}

Validate the PSC endpoint in Cloud Console

From Cloud Console you can validate the PSC Connection

In Cloud Console, navigate to:

Looker → Looker Instance → Details

9. Integrate Looker PSC with Cloud SQL PSC

In the following section you will use Looker Console to create a Database connection to the Cloud SQL PSC instance.

Navigate to ADMIN → DATABASE → CONNECTIONS → Select ADD CONNECTION

Fill out the connection details per the screenshot below, select CONNECT

• Ensure to match the HOST field in Looker Console with the previously supplied domain, sql.database1.com
• Demo requires the username ‘root' & password ‘password123'

The connection is now configured

10. Validate Looker connectivity

In the following section you will learn how to validate Looker connectivity to the Cloud SQL PSC instance using the Looker ‘test' action.

Navigate to the Data Connection ADMIN → DATABASE → CONNECTIONS → cloud-sql-psc-demo → Test

Once Test is selected Looker will connect to the postgres-database as indicated below:

Clean up

From a single Cloud Shell terminal delete lab components

gcloud sql instances delete cloud-sql-psc-demo -q

11. Congratulations

Congratulations, you've successfully configured and validated connectivity to Cloud SQL PSC using Looker Console powered by Private Service Connect.

You created Cloud SQL PSC instances as the Producer Service and Looker PSC endpoint that allowed connectivity to the Producer service.

Cosmopup thinks codelabs are awesome!!

What's next?

Check out some of these codelabs...

• Using Private Service Connect to publish and consume services
• Connect to on-prem services over Hybrid Networking using Private Service Connect and an internal TCP Proxy load balancer
• Access to all published Private Service Connect codelabs

Further reading & Videos

• Private Service Connect overview

Reference docs

• Connect to an instance using Private Service Connect | Cloud SQL for MySQL | Google Cloud
• gcloud sql instances create | Google Cloud CLI Documentation
• Create a Looker (Google Cloud core) Private Service Connect instance
• How to publish a service using Private Service Connect