Add Cloud SQL Client role for IAM users and service accounts

[jawnsy]

TL;DR

Add Cloud SQL Client at the project level alongside Cloud SQL Instance User, to permit connections via the Cloud SQL Auth Proxy

Terraform Resources

resource "google_project_iam_member" "iam_connect_binding" {
  for_each = {
    for iu in local.iam_users :
    "${iu.email} ${iu.is_account_sa}" => iu
  }
  project = var.project_id
  role    = "roles/cloudsql.client"
  member = each.value.is_account_sa ? (
    "serviceAccount:${each.value.email}"
    ) : (
    "user:${each.value.email}"
  )
}

If we want to make this optional, then we can add a count that checks a module variable.

This is similar to the existing code; it changes the resource name (from iam_binding to iam_connect_binding) and role (from roles/cloudsql.instanceUser to roles/cloudsql.client).

Detailed design

This module adds IAM users and service accounts to the instance users list, and also adds Cloud SQL Instance User at project scope: https://github.com/terraform-google-modules/terraform-google-sql-db/blob/f96f71ed00e590a56083bb4faffc5b54e4583603/modules/postgresql/main.tf#L218-L250

When using IAM authentication and connecting with the Cloud SQL Auth Proxy, we also require the Cloud SQL Client role, as described in the documentation:

Thus, I am proposing that we also add roles/cloudsql.client at project scope, or make it optional subject to some configuration option. Both predefined roles are required because we require all three of the permissions:

• cloudsql.instances.connect
• cloudsql.instances.get
• cloudsql.instances.login

Additional information

If we agree this design makes sense, I'd be happy to contribute a PR!

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 7 days

[czka]

As per GCP docs https://cloud.google.com/sql/docs/mysql/connect-admin-proxy#before_you_begin:

For a user or service account, make sure the account has the Cloud SQL Client role. This role contains the cloudsql.instances.connect permission, which authorizes a principal to connect to all Cloud SQL instances in a project.

[ykyr]

The whole IAM binding should be removed from the module. The reasoning is described in this issue https://github.com/terraform-google-modules/terraform-google-sql-db/issues/381

[ykyr]

@jawnsy When this PR https://github.com/terraform-google-modules/terraform-google-sql-db/pull/382 will merged. You can use something like this to grant required cloudsql roles. Obviously, you can wrap it up into a module for convenience.

locals {
  iam_user_emails = [
    "[email protected]",
    "[email protected]"
  ]
  cloudsql_iam_roles = [
    "roles/cloudsql.client",
    "roles/cloudsql.instanceUser"
  ]
  # Generate pairs of [member, iam_role] for creation of iam_bindings
  iam_user_role_bindings = [for iu in distinct(local..iam_user_emails) : [for role in local.cloudsql_iam_roles : {
    email : iu
    is_account_sa : trimsuffix(iu, "gserviceaccount.com") == iu ? false : true
    role : role
  }]]
  iam_user_role_bindings_flatten = { for o in flatten(local.iam_user_role_bindings) : "${o.email}|${trimprefix(o.role, "roles/")}" => o }
}

resource "google_project_iam_member" "iam_binding" {
  for_each = local.iam_user_role_bindings_flatten
  project  = var.project_id
  role     = each.value.role
  member = each.value.is_account_sa ? (
    "serviceAccount:${each.value.email}"
    ) : (
    "user:${each.value.email}"
  )
}

It will create resources for each pair. Example:

google_project_iam_member.iam_binding["cloudsql.client|[email protected]"]
google_project_iam_member.iam_binding["cloudsql.instanceUser|[email protected]"]

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 7 days

[jawnsy]

Closing as obsolete due to #381