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.
Last updated
This guide demonstrates how to secure a MySQL or PostgresSQL database on GCP Cloud SQL with passwordless access in BastionZero.
Last updated
Moving forward, we are natively rebuilding BastionZero’s technology as Cloudflare’s service.
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):
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."
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.
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, we assume you have already , have your , and have the . 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
Create a registration key
We can grab a registration key from the BastionZero web interface at . 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!
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.
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.”
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-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.
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., alice-744@se-demo-pwdb.iam.gserviceaccount.com, bob-240@se-demo-pwdb.iam.gserviceaccount.com).
Click "Save" to create the policy.
zliFrom 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 alice-744@se-demo-pwdb.iam.gserviceaccount.com@gcp-mysql.
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.
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 alice-744@se-demo-pwdb.iam@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 alice-744@se-demo-pwdb.iam.gserviceaccount.com@gcp-mysql.
In your database client, the username is the abbreviated instance name that is comprised of all the content before gserviceaccount.com (i.e., alice-744@se-demo-pwdb.iam).
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.
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.
Navigate to the IAM & Admin page in the .
Download and install the Bastionzero agent according to the instructions provided by BastionZero . You'll need your registration key from the "Preparing Your Account" section above.
Verify that the target is available in the targets interface at .
Navigate to the "" section of the BastionZero Portal.
Navigate to the section of the BastionZero Portal.
