Passwordless user for Google Cloud SQL (MySQL) using Cloud SQL Proxy?

Question

The documentation About the Cloud SQL Proxy contains a line, “[B]ecause the proxy always connects from a hostname that cannot be accessed except by the proxy, you can create a user account that can be used only by the proxy. The advantage of doing this is that you can specify this account without a password without compromising the security of your instance or your data.”

Is there any practical way of connecting a MySQL client through the Google Cloud Proxy which is authenticated using a Service Account that is only allowed to access a specific MySQL user?

One potential way to restrict access when using the Cloud SQL Proxy is to create a MySQL user account with Cloud SQL Proxy IP address: '[USER_NAME]'@'cloudsqlproxy~[IP_ADDRESS]'. But this would not work for a service account that runs on multiple cloud VMs. This is basically authentication of the MySQL user by IP address instead of by Service Account.

A second way to restrict a Service Account to a MySQL user might be to use a MySQL user account '[USER_NAME]'@'cloudsqlproxy~%' and rely on IAM to restrict access. The Service Account running the Cloud SQL Client requires the roles/cloudsql.client role, which is equivalent to the cloudsql.instances.connect and cloudsql.instances.get permissions. Unfortunately, if you grant multiple Service Accounts this role, then it looks like there is no way to restrict them from using each other’s MySQL user.

A third potential way to enable passwordless authentication might be to use client certificates (gcloud sql ssl-certs create [CERT_NAME] client-key.pem --instance [INSTANCE_NAME]). But client private keys are even harder to manage than passwords, since they are all invalidated at the same time in under 1 year when you refresh the server certificate: “When you refresh your server cerficate, you must also generate new client certificates; the existing client certificates are revoked.” This makes them basically impossible to use in production.

Basically, it seems like the Cloud SQL Proxy does not enable authenticating to a specific MySQL user via an IAM Service Account. Is this correct?

Answer 1

It’s ironic that the so-called “Auth proxy” hasn’t provided database user authentication until now. Google Cloud SQL has finally added support for passwordless IAM login, where cloud-sql-proxy (or the golang, java, or python connector) authenticates the IAM user or service account and then connects to the Auth proxy server in front of MySQL with an ephemeral client certificate.

For Postgres, Cloud SQL added manual IAM Authentication in 2020 (Cloud SQL Release Notes: 2020-10-12), and they added automatic IAM Authentication and cloud-sql-proxy support in 2021 -enable_iam_login(cloud-sql-proxy changelog: 1.20.0 2021-02-24, Cloud SQL Release Notes: 2021-04-06, generally available on 2021-08-30), though that flag is replaced with --auto-iam-authn in 2.0.0.

For MySQL, Cloud SQL added manual IAM Authentication in 2021 (Cloud SQL Release Notes: 2021-07-08). Automatic IAM Authentication is not documented yet, but it was unlocked in golang cloud.google.com/go/cloudsqlconn 1.1.0 and cloud-sql-proxy 2.0.0-preview.4 (2022-12-12) with --auto-iam-authn.

Passwordless IAM authentication on Google Cloud SQL MySQL requires:

• roles/cloudsql.instanceUser role, or equivalently cloudsql.instances.get and cloudsql.instances.login permissions (MySQL IAM roles)

• --cloudsql_iam_authentication=on on the MySQL server (Instance configuration)

• User created with type set for IAM authentication: gloud sql users create --type=cloud_iam_user --instance=MY_INSTANCE USERNAME@DOMAIN, or gloud sql users create --type=cloud_iam_service_account --instance=MY_INSTANCE SERVICE@PROJECT.iam.gserviceaccount.com

• Either manual or automatic IAM authentication

  • Manual IAM authentication: requires the username to be the local-part of the email address, the password to be set to the result of gcloud sql generate-login-token, --enable-cleartext-plugin, and requires connecting using a certificate generated by gcloud sql ssl client-certs create (Log in with manual IAM database authentication)
  • Automatic IAM authentication: requires generating an ephemeral client certificate generateEphemeralCert with access_token set to the result of gcloud auth print-access-token, and requires connecting with the Cloud SQL Proxy protocol (port 3307) (it does not work as a client certificate on port 3306). This is all automated by cloud-sql-proxy or one of the language connectors. Then you connect to cloud-sql-proxy with the username set to the local-part of the email address, and the password is ignored. Example:

  # Download v2.0.0-preview.4 or higher
  wget https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.0.0-preview.4/cloud-sql-proxy.linux.amd64
  chmod +x cloud-sql-proxy.linux.amd64
  sudo mv cloud-sql-proxy.linux.amd64 /usr/local/bin/cloud-sql-proxy
  
  cloud-sql-proxy --port=3306 --auto-iam-authn $(gcloud sql instances describe MY_INSTANCE --format='value(connectionName)') &
  mysql --user=MY_USERNAME --password= --protocol=tcp --host=127.0.0.1 --port=3306 --ssl-mode=disabled