google_sql_user: CLOUD_IAM_SERVICE_ACCOUNT authentication Root resource was present, but now absent

[joshuasimon-taulia]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request.
• Please do not leave +1 or me too comments, they generate extra noise for issue followers and do not help prioritize the request.
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.
• If an issue is assigned to the modular-magician user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If an issue is assigned to a user, that user is claiming responsibility for the issue. If an issue is assigned to hashibot, a community member has claimed the issue already.

Terraform Version

Terraform v0.13.6
+ provider registry.terraform.io/hashicorp/google v3.75.0
+ provider registry.terraform.io/hashicorp/google-beta v3.69.0
+ provider registry.terraform.io/hashicorp/helm v1.3.2
+ provider registry.terraform.io/hashicorp/kubernetes v1.11.4
+ provider registry.terraform.io/hashicorp/local v2.1.0
+ provider registry.terraform.io/hashicorp/null v3.1.0
+ provider registry.terraform.io/hashicorp/random v3.1.0
+ provider registry.terraform.io/hashicorp/template v2.2.0
+ provider registry.terraform.io/okta/okta v3.11.1

Affected Resource(s)

• google_sql_user

Terraform Configuration Files

resource "google_service_account" "app" {
  account_id   = "${var.app_name}-${var.namespace}"
  display_name = "Used by ${var.app_name}-${var.namespace} to access cloudsql mysql"
}

module "mysql" {
  source               = "GoogleCloudPlatform/sql-db/google//modules/mysql"
  version              = "6.0.0"
  name                 = "${var.app_name}-${var.namespace}"
  random_instance_name = var.mysql_random_instance_name
  project_id           = var.project_id
  database_version     = var.mysql_version
  region               = var.region

  // Primary db configurations
  tier                            = var.mysql_tier
  zone                            = var.mysql_primary_zone
  availability_type               = "REGIONAL"
  maintenance_window_day          = 7 # Sunday
  maintenance_window_hour         = 7 # 12:00AM
  maintenance_window_update_track = "stable"

  deletion_protection = true
  disk_autoresize     = true
  disk_size           = 10

  database_flags = [
    { name = "max_allowed_packet", value = "1073741824" },
    { name = "cloudsql_iam_authentication", value = "on" },
  ]

  user_labels = {
    db = "${var.app_name}-${var.namespace}"
  }

  ip_configuration = {
    ipv4_enabled        = true
    require_ssl         = true
    private_network     = null
    authorized_networks = []
  }

  backup_configuration = {
    enabled                        = true
    binary_log_enabled             = true
    start_time                     = "21:00" # 9:00PM
    location                       = var.region
    transaction_log_retention_days = null
    retained_backups               = 365
    retention_unit                 = "COUNT"
    point_in_time_recovery_enabled = true
  }

  // Read replica configurations
  read_replica_name_suffix = "-read-"
  read_replicas = [
    {
      name             = "0"
      zone             = var.mysql_replica_zone
      tier             = var.mysql_tier
      ip_configuration = local.read_replica_ip_configuration
      database_flags   = [{ name = "max_allowed_packet", value = "1073741824" }]
      disk_autoresize  = true
      disk_size        = 10
      disk_type        = "PD_SSD"
      user_labels      = { db = "${var.app_name}-${var.namespace}" }
    },
  ]

  db_name      = local.schema_name
  db_charset   = "utf8mb4"
  db_collation = "utf8mb4_general_ci"

  user_name     = "vault"
  user_host     = "cloudsqlproxy~%"
  user_password = var.vault_mysql_bootstrap_password
}


resource "google_sql_user" "users" {
  name     = google_service_account.app.email
  instance = module.mysql.primary.name
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

Debug Output

https://gist.github.com/joshuasimon-taulia/e62263a8d19ef10ce8239366fb5f3ca6

Panic Output

Expected Behavior

terraform should create the cloudsql mysql IAM users and exit cleanly

Actual Behavior

terraform creates the cloudsql mysql IAM users but throws an error after apply

Steps to Reproduce

1. create mysql CLOUD_IAM_SERVICE_ACCOUNT google_sql_user
2. terraform apply
3. if the user doesn't exist Root resource was present, but now absent. error
4. if the user does exist Error: Error, failure waiting for insertion of myapp-staging@bla123-integration.iam.gserviceaccount.com into myapp-staging: error

Important Factoids

leveraging the new https://cloud.google.com/blog/products/databases/iam-database-authentication-comes-to-cloud-sql-for-mysql

[edwardmedia]

I can repro the issue using below config

resource "google_sql_user" "users" {
  name     = "issue9553@myproject.iam.gserviceaccount.com"
  instance = "mysqlinstance-12345"
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

[megan07]

Hi @joshuasimon-taulia ! Sorry you're experiencing this issue! Can I see your configuration for google_service_account.app.email please?

Also, can I ask for clarification on a couple of things:

if the user doesn't exist Root resource was present, but now absent. error

This would be a provider error, typically an eventual consistency error, and I can dig into this once I'm able to reproduce the issue.

if the user does exist Error: Error, failure waiting for insertion of myapp-staging@bla123-integration.iam.gserviceaccount.com into myapp-staging: error

This seems to be an error internal to Google, is this an error you are expecting?

Thanks!

[revionics-ryan-felder]

I am having the same issue exactly. Here's my slightly redacted code, hopefully it helps.

resource "google_sql_database" "demoissue" {
  name     = "demoissue-${var.territory}-${var.customer}-${var.sdlc_tier}"
  instance = var.demoissue_db_instance
}

resource "google_service_account" "demoissue" {
  account_id   = "demoissue-${var.territory}-${var.customer}-${var.sdlc_tier}"
  display_name = "demoissue-${var.territory}-${var.customer}-${var.sdlc_tier}"
  description  = "Service account for demoissue SQL Cloud Auth Proxy"
  project      = var.project_id
}

resource "google_service_account_key" "demoissue" {
  service_account_id = google_service_account.demoissue.email
}

resource "google_sql_user" "demoissue" {
  name     = google_service_account.demoissue.email
  instance = var.demoissue_db_instance
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

resource "google_project_iam_member" "demoissue_cloudsql" {
  project = var.project_id
  role    = "roles/cloudsql.client"
  member  = "serviceAccount:${google_service_account.demoissue.email}"
}

resource "google_project_iam_member" "demoissue_instance_user" {
  project = var.project_id
  role    = "roles/cloudsql.instanceUser"
  member  = "serviceAccount:${google_service_account.demoissue.email}"
}

resource "google_project_iam_member" "demoissue_service_account_user" {
  project = var.project_id
  role    = "roles/iam.serviceAccountUser"
  member  = "serviceAccount:${google_service_account.demoissue.email}"
}

[joshuasimon-taulia]

Hi @joshuasimon-taulia ! Sorry you're experiencing this issue! Can I see your configuration for google_service_account.app.email please?

i've updated the issue with the sa creation logic

resource "google_service_account" "app" {
  account_id   = "${var.app_name}-${var.namespace}"
  display_name = "Used by ${var.app_name}-${var.namespace} to access cloudsql mysql"
}

[joshuasimon-taulia]

Hi @joshuasimon-taulia ! Sorry you're experiencing this issue! Can I see your configuration for google_service_account.app.email please?

Also, can I ask for clarification on a couple of things:

if the user doesn't exist Root resource was present, but now absent. error

This would be a provider error, typically an eventual consistency error, and I can dig into this once I'm able to reproduce the issue.

on the first terraform apply, after creation of the google_sql_user, i hit Root resource was present, but now absent. error

if the user does exist Error: Error, failure waiting for insertion of myapp-staging@bla123-integration.iam.gserviceaccount.com into myapp-staging: error

This seems to be an error internal to Google, is this an error you are expecting?

Thanks!

on a subsequent terraform apply, i hit Error: Error, failure waiting for insertion of [myapp-staging@bla123-integration.iam.gserviceaccount.com](mailto:myapp-staging@bla123-integration.iam.gserviceaccount.com) into myapp-staging: error. fixing the above eventual consistency error will probably prevent this scenario from happening.

[megan07]

Thank you @revionics-ryan-felder and @joshuasimon-taulia . I'm unable to recreate the same error you're getting, however, I am running into an error with it. The error I get is:

Error: Error, failed to insert user issue-9553@hc-terraform-testing.iam.gserviceaccount.com into instance issue-9553-1: googleapi: Error 400: Invalid request: Database username for Cloud IAM service account should be created without ".gserviceaccount.com" suffix., invalid

When I changed name = google_service_account.demoissue.email to name = google_service_account.demoissue.account_id it seemed to work. Could you give that a try?

[revionics-ryan-felder]

I tried this, but got the following...

Error: Error, failed to insert user demoissue-extra-words-here into instance demoissue-f3d27030: googleapi: Error 400: Invalid request: Database user cannot be created. Provided Cloud IAM service account "demoissue-extra-words-here" is not in valid format. Please specify full email address Eg: serviceaccount@projectid.iam.gserviceaccount.com.., invalid

[revionics-ryan-felder]

Also, here is my mysql instance declaration.

resource "google_sql_database_instance" "demoissue" {
  provider         = google-beta
  database_version = "MYSQL_8_0"
  name             = "demoissue-${random_id.db_name_suffix.hex}"
  region           = "us-central1"

  settings {
    tier              = "db-f1-micro"
    availability_type = "REGIONAL"

    ip_configuration {
      ipv4_enabled       = false
      private_network    = var.sql_network
    }

    backup_configuration {
      enabled            = true
      binary_log_enabled = true
    }

    database_flags {
      name  = "cloudsql_iam_authentication"
      value = "on"
    }
  }
}

resource "random_id" "db_name_suffix" {
  byte_length = 4
}

[revionics-ryan-felder]

@megan07 If I had to guess, I'd guess our configs for the google_sql_user resource are different.
Can you verify you are passing the following variable to your cloud_sql_user resource?

type     = "CLOUD_IAM_SERVICE_ACCOUNT"

[megan07]

I've got that

resource "google_sql_user" "demoissue" {
  name     = google_service_account.demoissue.account_id
  instance = google_sql_database_instance.instance.name
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

(The only change I've made is the account_id) I have no idea why it would make a difference, but I am running with Terraform version 1.0, trying to make it work with 0.13, just working through some old set-ups to get that fired up now.

[revionics-ryan-felder]

For what it's worth, I'm using Terraform version 0.15.1

[megan07]

I was able to repro it now. I likely won't have more time today to do it, but will continue working on it tomorrow. Thanks for your quick responses and your help today!

[revionics-ryan-felder]

Thanks!

[megan07]

I found the bug here - it's a combination of two things really, and I'll have to dig into how to solve it, as I'm not an expert on the nuances of the SQL resources. I'll explain what's happening, and if anyone can point me to any documentation that might help me fix our assumptions, that'd be greatly appreciated!

There isn't a way to GET a sql user, so we use LIST and filter the list to find the user we want. Currently, we're filtering on 2 things - does the name in the configuration match the name returned? If it does, does the host match (or is host empty, in the case of POSTGRES).

In this particular case (and I need to clarify what the case is that makes it this way), host is being returned as % (all hosts) and the name is being returned with the short name, whereas in the config we have the <name>@<project>.iam.gserviceaccount.com.

I'll dig through the docs and see if this is specific to CLOUD_IAM_SERVICE_ACCOUNT types (that's my hunch), or if we need to be aware of this in other cases and I should make a more general filter.

Thanks for your patience on this!

[mustafakirimli]

As per below 3 documentations when creating CloudSql user as IAM authentication type CLOUD_IAM_SERVICE_ACCOUNT or CLOUD_IAM_USER we specify org email (johndoe@site.com) or iam service account <name>@<project>.iam.gserviceaccount.com but we use only username when we connect to the database.

As you mentioned there is no GET method for sql user and when use LIST method it does not return full service account address in any field. But when we visit CloudSql GCP console its showing that value there. I am guessing that REST api not been updated yet with this new feature.

as you could find, below is the sample response and this is the type/contract;

{
    "etag": "xx",
    "host": "%",
    "instance": "myinstance",
    "kind": "sql#user",
    "name": "report-app",
    "project": "reports",
    "type": "CLOUD_IAM_SERVICE_ACCOUNT"
  }

we need report-app@<project>.iam.gserviceaccount.com value but project part for service account is missing, it returns project field but that represents db instance's project not the service account's.

https://cloud.google.com/blog/products/databases/iam-database-authentication-comes-to-cloud-sql-for-mysql
https://cloud.google.com/sql/docs/mysql/authentication
https://cloud.google.com/sql/docs/mysql/iam-logins

[megan07]

Closed by GoogleCloudPlatform/magic-modules#4984

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.