How to use BastionZero to set up passwordless access to GCP Cloud SQL Databases
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. The basic premise is that you’ll create three 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.
For the purposes of this guide, I’lll 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:
- 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 onto it!
- 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.
- 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.
- 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.
- 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.
- Name the target to something memorable; we'll need it when we create a database target below.
- 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.
- 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."
- Click the "Create" button in the top right and select "Database."
- Give your target a name (e.g., gcp-mysql).
- Select the Proxy Target from the drop down menu (this should be the target you installed the BastionZero agent on).
- Set “SplitCert” to “Yes.”
- Set the Database Type (optional, leave blank for MySQL).
- In the "Remote Host" field, enter the instance connection name from GCP (e.g., gcp://se-demo-pwdb:us-west2:gcp-mysql).
- In the "Remote Port" field, enter an arbitrary value. This value is ignored by BastionZero.
- In the “Local Host” field, enter “localhost.”
- Set the “Local Port” to your desired local port (e.g., “9999”). You can leave this blank and BastionZero will choose an open port for you at time of connection in step 10.
- Click "Add" to create the database target.
- 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.
- In the "Targets" section, select the database target you created in step 8.
- In the “Allowed Target Users” section, add the service accounts from GCP (e.g., [email protected], [email protected]).
- Click "Save" to create the policy.
- From your terminal, run:
zli connect {gcp IAM service account}@{database target name}
In your database client, you’ll need to use a specific username based on your database type:
- 1.Everything before the @ sign (e.g., alice-744).
PostgreSQL
- 1.
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.