Access to Google CloudSQL database

There are multiple ways to connect to a CloudSQL database. In the examples below, we will cover how to connect to an instance with a private IP and a Public IP. And we also show different ways to authenticate.

📘
PostgresSQL
The examples below use MySQL screenshots and commands, but the same Border0 flow applies to PostgreSQL. Where PostgreSQL differs (client choice, IAM username format, and the IAM auth flag), the doc calls it out explicitly.

If you want to expose a CloudSQL instance with a Private IP, you must run the border0 connector on a machine that is in the same network as the Instance, or one that has network access to the CloudSQL instance.

In this example, the CloudSQL instance has the following private IP: 10.59.32.3

📘
Public IP
To connect to a database instance with a public IP, you must first enable a public IP on the instance. See https://cloud.google.com/sql/docs/mysql/configure-ip
If you connect directly to the instance’s public IP (no Cloud SQL connector): • Add the client/host IP (the machine running the connector or client) to Authorized networks. • Configure SSL/TLS yourself if required, including client certificates if you enforce mutual TLS.
If you connect using the Cloud SQL connector: • Authorized networks are not required. • The connection is automatically encrypted with TLS, so you don’t need to create or distribute client certs.

Username/Password authentication:
Let's first create a Database socket in Border0, this can be done via the portal or the cli as show below.

The socket is now available in the Border0 network, to connect:

$ border0 client db --host sqltest4
? what is the name of the database schema: mysql
? choose a client: mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30312
Server version: 8.1.0 MysocketSQL (Google)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

TLS Authentication

For more secure connections, CloudSQL may require TLS authentication. For more information on how to configure TLS on the database instance and create a certificate, see: https://cloud.google.com/sql/docs/mysql/configure-ssl-instance

Once you have a certificate and key, you can create the socket with the certificates:

CloudSQL Connector

You can also use the built-in CloudSQL connector support within Border0. With the CloudSQL connector, you can create a secure tunnel to the CloudSQL instance. On a compute engine VM it will, by default, use the service account the VM is running as. The service account/VM needs to have the correct scopes to do this; see: https://cloud.google.com/sql/docs/mysql/connect-compute-engine#connect-gce-proxy

If the permissions are set you can configure the socket with the CloudSQL connector.

IAM Authentication

Now we have the connector running; we can also start using IAM database authentication.
With this, it will authenticate using the service account, so you don't have to configure passwords.

First, you need to enable IAM authentication for the instance, https://cloud.google.com/sql/docs/mysql/create-edit-iam-instances#configure-iam-db-instance.
And you need to add create the database user for the service account, see: https://cloud.google.com/sql/docs/mysql/add-manage-iam-users#creating-a-database-user
And grant the user access to the database; see https://cloud.google.com/sql/docs/mysql/add-manage-iam-users#grant-db-privileges

🚧
Username
When using IAM DB authentication with the Cloud SQL connector, the username format depends on the engine:
MySQL: **Use the IAM principal’s email without the @ and domain. Example: [email protected] → test-user. For service accounts, use the service account email without @project-id.iam.gserviceaccount.com. Example: [email protected] → my-sa.
PostgreSQL: Use the IAM user’s full email address as the username. Example: [email protected] → [email protected]. For service accounts, drop only the .gserviceaccount.com suffix and keep the rest (including @project-id.iam). Example: [email protected] → [email protected].

Configure the socket:

For more information about IAM authentication, see:
Postgres: https://cloud.google.com/sql/docs/postgres/authentication
Mysql: https://cloud.google.com/sql/docs/mysql/authentication