How to connect to CloudSQL with IAM authentication

Did you even wonder how to connect to CloudSQL with IAM authentication? Since this year, CloudSQL for PostgreSQL allows IAM users and IAM service accounts to login as a database user. In this short blog I will show you how to do this using Terraform.

How does it work?

It is pretty straight forward:

• You enable IAM authentication on the instance
• Create a CloudSQL IAM user in the instance
• start the CloudSQL proxy with --enable_iam_login
• start psql!

It is that easy!

enable CloudSQL IAM authentication

The following Terraform snippet enables IAM authentication on the database instance:

resource "google_sql_database_instance" "master" {
  name                   = "demo"
  database_version = "POSTGRES_9_6"

  settings {
    tier = "db-f1-micro"

    database_flags {
      name  = "cloudsql.iam_authentication"
      value = "on"
    }
  }
}

As you can see, this is for a PostgreSQL database. I trust MySQL support is following soon.

Create a CloudSQL IAM user

To create a CloudSQL IAM user, use the following Terraform snippet:

resource "google_sql_user" "iam_user" {
  name     = "markvanholsteijn@binx.io"
  instance = google_sql_database_instance.master.name
  type     = "CLOUD_IAM_USER"
}

resource "google_project_iam_member" "iam_user_cloudsql_instance_user" {
  role   = "roles/cloudsql.instanceUser"
  member = format("user:%s", google_sql_user.iam_user.name)
}

resource "google_project_iam_member" "iam_user_cloudsql_client" {
  role   = "roles/cloudsql.client"
  member = format("user:%s", google_sql_user.iam_user.name)
}

As you can see, you specify the email address of the Google Cloud identity and specify the type as CLOUD_IAM_USER. The IAM user requires the roles cloudsql.instanceUser and cloudsql.client to connect. For service accounts, specify the type CLOUD_IAM_SERVICE_ACCOUNT.

start CloudSQL proxy

To start the CloudSQL Proxy, type:

$ CONNECTION=$(gcloud sql instances 
    describe demo --format 'value(connectionName)')

$ cloud_sql_proxy 
  --instances $CONNECTION=tcp:5432 --enable_iam_login  &

In this snippet above, I query the connection name using gcloud. The connection name normally has the format <project>:<region>:<name>. The CloudSQL Proxy will automatically refresh tokens for you behind the scenes.

start psql

To connect to the PostgreSQL database using your gcloud credentials, type:

$ psql "sslmode=disable dbname=postgres host=127.0.0.1 user=markvanholsteijn@binx.io"
psql (13.3, server 9.6.21)
postgres=>

As you can see, you connect with sslmode=disable. The CloudSQL proxy already encrypts the connection for you.

Conclusion

With CloudSQL IAM authentication support it is very easy to grant users access to a CloudSQL database without a password. There is no password to generate and distribute, so it is impossible to leak it. Combined with limiting the lifetime of the gcloud SDK credentials, this is a big security improvement. I recommend using CloudSQL IAM authentication for all your CloudSQL databases.

Image by markvanholsteijn from Pixabay