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
All the examples below are based on Mysql but, equally apply to a Postgres CloudSQL instance.

Private IP

If you want to expose an CloudSQL instance with a Private IP, you must run border0 socket connect (or the border0 connector) in 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

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.

bas@ubuntu-focal-1:~$ border0 socket create --name sqltest --type database --upstream_type mysql
┌──────────────────────────────────────┬─────────┬──────────────────────┬─────────┬──────────┬─────────────┐
│ SOCKET ID                            │ NAME    │ DNS NAME             │ PORT(S) │ TYPE     │ DESCRIPTION │
├──────────────────────────────────────┼─────────┼──────────────────────┼─────────┼──────────┼─────────────┤
│ 1c2aa2b8-c3a5-4514-a285-cd84f3c69b6f │ sqltest │ sqltest.bas.toonk.nl │ 31352   │ database │             │
└──────────────────────────────────────┴─────────┴──────────────────────┴─────────┴──────────┴─────────────┘

Policies:
┌───────────────┬────────────────────────────┬───────────────────┐
│ POLICY NAME   │ POLICY DESCRIPTION         │ ORGANIZATION WIDE │
├───────────────┼────────────────────────────┼───────────────────┤
│ allow-border0 │                            │ Yes               │
│ allow-btt     │ Allow btt tests to connect │ Yes               │
└───────────────┴────────────────────────────┴───────────────────┘

Next, we connect this service to the Border0 infrastructure and make the database available:

bas@ubuntu-focal-1:~$ border0 socket connect sqltest \
       --upstream_username root \
       --upstream_password  mysqltest \
       --host 10.59.32.3 -p 3306

Welcome to Border0.com
sqltest - database://sqltest.bas.toonk.nl

=======================================================
Logs
=======================================================

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

$ border0 client db --host sqltest.bas.toonk.nl
? 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 connect the instance (in this example, we will use the same socket):

bas@ubuntu-focal-1:~$ border0 socket connect sqltest --upstream_username root  \
    --upstream_password mysqltest \
    --host 10.59.32.3 \
    --port 3306 \
    --upstream_certificate_filename client-cert.pem \
    --upstream_key_filename client-key.pem
Welcome to Border0.com
sqltest - database://sqltest.bas.toonk.nl

=======================================================
Logs
=======================================================

CloudSQL Connector

You can also connect with the CloudSQL connector as that is built-in the border0 CLI. 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 run the following command to connect using the CloudSQL connector.

bas@ubuntu-focal-1:~$ border0 socket connect sqltest --cloudsql-connector \
    --cloudsql-instance experiments-377315:europe-west4:mysqltest \
    --upstream_username root \
    --upstream_password mysqltest
Welcome to Border0.com
sqltest - database://sqltest.bas.toonk.nl

=======================================================
Logs
=======================================================

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
For a user account, this is the email address of the IAM user with the @ and domain string truncated. For example, if the IAM user's email address is [email protected], the username would be test-user. For a service account, this is the email address of the service account without the @project-id.iam.gserviceaccount.com domain.

And setup the tunnel:

bas@ubuntu-focal-1:~$ border0 socket connect sqltest \
    --cloudsql-connector \
    --cloudsql-instance experiments-377315:europe-west4:mysqltest \
    --cloudsql-with-iam --upstream_username 664776391586-compute
Welcome to Border0.com
sqltest - database://sqltest.bas.toonk.nl

=======================================================
Logs
=======================================================

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

To connect to a Google Cloud database (CloudSQL) with a public IP, you must manage the Authorised networks to your IP (the host running the connector, ie border socket connect). In addition, you also need to manage client certificates if TLS authentication is required.

For more information on how to configure TLS on the instance and create a certificate: https://cloud.google.com/sql/docs/mysql/configure-ssl-instance

Create a new database socket:

bas@ubuntu-focal-1:~$ border0 socket create --name sqltest --type database --upstream_type mysql
┌──────────────────────────────────────┬─────────┬──────────────────────┬─────────┬──────────┬─────────────┐
│ SOCKET ID                            │ NAME    │ DNS NAME             │ PORT(S) │ TYPE     │ DESCRIPTION │
├──────────────────────────────────────┼─────────┼──────────────────────┼─────────┼──────────┼─────────────┤
│ 1c2aa2b8-c3a5-4514-a285-cd84f3c69b6f │ sqltest │ sqltest.bas.toonk.nl │ 31352   │ database │             │
└──────────────────────────────────────┴─────────┴──────────────────────┴─────────┴──────────┴─────────────┘

Policies:
┌───────────────┬────────────────────────────┬───────────────────┐
│ POLICY NAME   │ POLICY DESCRIPTION         │ ORGANIZATION WIDE │
├───────────────┼────────────────────────────┼───────────────────┤
│ allow-border0 │                            │ Yes               │
│ allow-btt     │ Allow btt tests to connect │ Yes               │
└───────────────┴────────────────────────────┴───────────────────┘

Once you have a certificate/key you can connect the instance:

$ border0 socket connect sqltest --upstream_username root  \
    --upstream_password mysqltest \
    --host 34.141.247.239 \
    --port 3306 \
    --upstream_certificate_filename client-cert.pem \
    --upstream_key_filename client-key.pem
Welcome to Border0.com
sqltest - database://sqltest.bas.toonk.nl

=======================================================
Logs
=======================================================

CloudSQL Connector

There is another way to connect to CloudSQL: the CloudSQL Connector. The CloudSQL Connector will create a secure tunnel to your CloudSQL instance without the need to manage IP lists or certificates.

The CloudSQL Connector is built into the border0 CLI and can be enabled using the cloudsql-connector flag.
To use the CloudSQL Connector, specify the instance connection name you want to connect to with the cloudsql-instance flag.

The border0 CLI will try to find the google credentials needed to use the CloudSQL Connector. Optionally you may specify the credential file with the flag cloudsql-credentials-file.

In this example, we use a service account that has the following roles:

Cloud SQL Instance User
Cloud SQL Client

We also created a key for this service account that will be used to connect to the instance.

Connect the socket:

border0 socket connect sqltest \
    --cloudsql-connector \
    --cloudsql-instance experiments-377315:europe-west4:mysqltest \
    --cloudsql-credentials-file experiments-377315-bef1eec1b496.json \
    --upstream_username root \
    --upstream_password mysqltest

IAM authentication

And last but not least, we also support CloudSQL IAM authentication, which can be enabled with the cloudsql-with-iam flag. The advantage of using IAM authentication is that you don't need a static username and password to connect to the upstream database, instead we can use Google's IAM system.

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

Connect the socket:

border0 socket connect sqltest \
    --cloudsql-connector \
    --cloudsql-instance experiments-377315:europe-west4:mysqltest \
    --cloudsql-credentials-file experiments-377315-bef1eec1b496.json \
    --upstream_username sql-263 --cloudsql-with-iam

border0 Connector

The above examples are also available in the border0 connector.
For example, a static socket using IAM authentication:

   - sqltest:
      port: 3306
      type: database
      upstream_type: mysql
      upstream_user: sql-263
      cloudsql_connector: true
      cloudsql_instance: experiments-377315:europe-west4:mysqltest
      google_credentials_file: /path/to/credentials/experiments-377315-bef1eec1b496.json
      cloudsql_iam_auth: true

With username and password:

   - sqltest:
      port: 3306
      type: database
      upstream_type: mysql
      upstream_user: root
      upstream_password: mysqltest

With username and password and the CloudSQL connector:

   - sqltest:
      port: 3306
      type: database
      upstream_type: mysql
      upstream_user: root
      upstream_password: mysqltest
      cloudsql_connector: true
      cloudsql_instance: experiments-377315:europe-west4:mysqltest
      google_credentials_file: /path/to/credentials/experiments-377315-bef1eec1b496.json

Access to Google CloudSQL database

📘
PostgresSQL

Private IP

TLS Authentication

CloudSQL Connector

IAM Authentication

🚧
Username

Public IP

CloudSQL Connector

IAM authentication

border0 Connector

📘PostgresSQL

Private IP

TLS Authentication

CloudSQL Connector

IAM Authentication

🚧Username

Public IP

CloudSQL Connector

IAM authentication

border0 Connector

📘
PostgresSQL

🚧
Username