Passwordless Access to MySQL and Postgres on GCP Cloud SQL

This guide demonstrates how to secure a MySQL or PostgresSQL database on GCP Cloud SQL with passwordless access in BastionZero.

Introduction

This guide is intended for individuals looking to leverage the power of BastionZero for passwordless access to a GCP Cloud SQL MySQL or PostgreSQL database. You’ll create 3 service accounts within Google Cloud Platform (GCP):

1. Alice: This service account is given read/write access to the database. In GCP terms, it is assigned the roles of "Cloud SQL Client," "Cloud SQL Editor," and "Cloud SQL Instance User."

2. Bob: This service account is given read-only access to the database. It is assigned the roles of "Cloud SQL Client" and "Cloud SQL Instance User." While Bob can connect to the Cloud SQL instance and interact with it, unlike Alice, Bob doesn't have the ability to edit or update the data in the database due to the absence of the "Cloud SQL Editor" role.

3. Impersonator: This service account is given permissions adequate to assume the roles of Alice and Bob. This is also known as service account impersonation. Impersonation allows one service account (in this case, Impersonator) to perform actions on behalf of another service account (Alice or Bob).

In addition to creating these service accounts, you will also set up a Compute Engine instance on GCP that will serve as a host for the BastionZero agent. Following this, you will create a Cloud SQL instance, where you'll be able to manage your databases.

Once these components are in place, you will proceed with the installation of the BastionZero agent on your compute instance, effectively turning it into a secure bastion host. This agent will facilitate secure, passwordless access to your Cloud SQL database.

By the end of this guide, you will have a robust and secure setup that leverages the best of both Google Cloud Platform and BastionZero's security capabilities. This setup will ensure streamlined, secure, and passwordless access to your GCP Cloud SQL database.

Preparing Your BastionZero Account

For the purposes of this guide, we assume you have already signed up for a BastionZero account, have your BastionZero account integrated with your IDP, and have the ZLI installed on your local machine. Once that is complete, we’ll do a few things in our BastionZero account to set ourselves up for success as outlined below:

• Log in to BastionZero web app at cloud.bastionzero.com

• Create a registration key

We can grab a registration key from the BastionZero web interface at cloud.bastionzero.com. Once you sign in, select "Create" in the upper righthand corner and choose "API Key."

We'll use this registration key for installing the agent below. Hang on to it!

Create Service Accounts in Google Cloud

• Navigate to the IAM & Admin page in the Google Cloud Console.

• Click on "Service Accounts" in the left-hand menu.

• Click on "Create Service Account" at the top of the page.

• Enter a name and description for each of your service accounts (Impersonator, Alice, Bob) and click "Create."

• Assign the necessary roles to each service account:

  • Alice (read & write): "Cloud SQL Client," "Cloud SQL Editor," "Cloud SQL Instance User"

  • Bob (read only): "Cloud SQL Client," "Cloud SQL Instance User"

  • Impersonator: "Cloud SQL Client," "Cloud SQL Editor," "Cloud SQL Instance User," "Service Account Admin," "Service Account OpenID Connect Identity Token Creator," "Service Account Token Creator"

  
• Click "Continue" and then "Done" to create each service account.

Create a Compute Instance in Google Cloud

• Navigate to the Compute Engine page in the Google Cloud Console.

• Click on "Create Instance" at the top of the page.

• Enter a name for your instance and choose a region and zone.

• In the Machine Configuration section, choose "General-purpose" and select the "e2-small" series (2 vCPUs, 2 GB memory). NOTE: While this is the minimum spec required, organizations may elect to use larger compute configurations.

• In the Boot disk section, click "Change,” select "Debian" as the OS, and click "Select."

• In the Service account section, select the Impersonator service account from the dropdown menu.

• Click "Create" to create the instance.

Create a Cloud SQL Instance in Google Cloud

• Navigate to the SQL page in the Google Cloud Console.

• Click on "Create instance" at the top of the page.

• Choose "MySQL" and click "Next."

• Enter a name for your instance and choose a region.

• In the Configuration options section, choose a small machine type for a development database.

• In the Service account section, select the Impersonator service account from the dropdown menu.

Install the BastionZero Agent onto your Compute Node

• SSH into your compute instance.

• Download and install the Bastionzero agent according to the instructions provided by BastionZero here. You'll need your registration key from the "Preparing Your Account" section above.

• Verify that the target is available in the targets interface at cloud.bastionzero.com.

• Name the target to something memorable; we'll need it when we create a database target below.

Add Service Accounts to Cloud SQL Database

• Navigate to the SQL page in the Google Cloud Console.

• Click on your Cloud SQL instance.

• Click on "Users" in the left-hand menu.

• Click on "Add user account" at the top of the page.

• Enter the details for each of your service accounts (Impersonator, Alice, Bob) and click "Add.”

Your CloudSQL Database Users should look something like this after you've addd your users to the database. Note the IAM email ID; you'll need this later for policy and connection.

Add a Testing Database to Cloud SQL

• Navigate to the SQL page in the Google Cloud Console.

• Click on your Cloud SQL instance.

• Click on "Databases" in the left-hand menu.

• Click on "Create Database" at the top of the page.

• Select either MySQL or PostgreSQL.

• Enter a name for your test database and click "Create."

Add a Database Target in BastionZero

• Navigate to the "Targets" section of the BastionZero Portal.

• Click the "Create" button in the top right and select "Database."

• Give your target a name (e.g., gcp-postgres).

• Select the Proxy Target or Proxy Environment as the proxy type. This should be either the target you installed the BastionZero agent on that has access to your database, or an environment with multiple targets with the BastionZero agent installed.

• Select "Service account injection" for the Authentication Type.

• Set the Database Type to "GCP Postgres" if using PostgresSQL or "GCP MySQL" if using MySQL. (In this example, we are using GCP Postgres.)

• In the "Remote Host" field, enter the instance connection name from GCP (e.g., se-demo-pwdb:us-west2:gcp-postgres). Based on the authentication method and database type chosen, BastionZero will prepend the protocol prefix if it is not provided with the instance name; so you can safely omit the gcp://.

• In the “Local Host” field, enter “localhost.”

• Set the “Local Port” to your desired local port (e.g., 9999). If you do not require this target to open on a specific port, we recommend you leave this field blank, and BastionZero will choose an available port for you at time of connection in step 10.

• Select the environment to place this database target into.

• Click "Save" to create the database target.

• You should see this target appear on the Targets page.

Add a Proxy Policy in BastionZero

• Navigate to the Policy section of the BastionZero Portal.

• Click on "Create" in the top right and select "Policy."

• In the “Policy Type” select “Proxy.”

• In the "Policy Name" field, enter a name for your policy.

• In the "Users" section, add users from your IdP who you’d like to be able to access the remote database (this can also include groups or service accounts if those need access, too).

• Under "Resource Type" - you can either opt to write a policy against the database target explicitly by using the "Targets" section or an environment. In this example, the below policy is written for targets in the Production environment.

• In the “Allowed Target Users” section, add the service accounts from GCP (e.g., [e-mail], [e-mail]).

• Click "Save" to create the policy.

Connect to Your Database

For MySQL

Step 1: Establish a Connection to the Database Using the zli

From your terminal, run the following command: zli connect {gcp IAM service account}@{database target name}.

In practice, this may look like: zli connect alice-744@gcp-mysql.

You are also able to connect using the full instance connection name if that better suits your workflow. That may look like: zli connect [e-mail]@gcp-mysql.

Step 2: Connect to Your Database Using Your DB Client of Choice

In your database client, the username is your GCP IAM service account (i.e., alice-744).

The server host will be localhost and whichever port you assigned when you created your database target. If you opted to let BastionZero assign an available port at the time of connection, the above zli connect command will output which port the database connection is running on.

For PostgresSQL

Step 1: Establish a Connection to the Database Using the ZLI

From your terminal, run the following command: zli connect {gcp IAM service account}@{abbreviated instance name that is comprised of all the content before and after "gserviceaccount.com"}.

In practice, this may look like: zli connect [e-mail]@gcp-postgres.

You are also able to connect using the full instance connection name if that better suits your workflow. That may look like: zli connect [e-mail]@gcp-mysql.

Step 2: Connect to Your Database Using Your DB Client of Choice

In your database client, the username is the abbreviated instance name that is comprised of all the content before gserviceaccount.com (i.e., [e-mail]).

The server host will be localhost and whichever port you assigned when you created your database target. If you opted to let BastionZero assign an available port at the time of connection, the above zli connect command will output which port the database connection is running on.

Conclusion

Congratulations! You have successfully completed all the necessary steps to set up your Google Cloud service accounts, compute and SQL instances, along with the BastionZero Agent and the associated databases and policies. You should now be able to establish a secure connection to your database using the ZLI and your specified service account. This marks the completion of your setup and configuration process. Your setup is now ready for efficient and secure operation.