Add service account e-mail to google_sql_database_instance returned attributes

[patrykmikula]

Currently there is no option to reach google_sql_database_instance service account e-mail address.
Service account e-mail address is required to grant write permission to GCP storage bucket in order to export SQL data from instance there (SQL dump/CSV).

'google_storage_bucket_acl' role_entity supports SQL instance service account email:

role_entity = [
"WRITER:user-XXXXXXYYYYYZZZZZ@speckle-umbrella-11.iam.gserviceaccount.com",
]

but SQL instance e-mail string is unreachable for terraform.
As a consequence export command
gcloud sql instances export DB_NAME gs://BUCKET/DIR/FILE.sql causes Exporting Cloud SQL instance... ..failed. ERROR: (gcloud.sql.instances.export) ERROR_RDBMS
and Access denied for account XXXXXXYYYYYZZZZZ@speckle-umbrella-11.iam.gserviceaccount.com (permission issue?) visible in SQL instance logs as there is no way to automatically grant RW permission to bucket for each new SQL instance.

[WiseLin1125]

Hi there, I have encountered the same issue, and my question is where does the account like user-XXXXXXYYYYYZZZZZ@speckle-umbrella-11.iam.gserviceaccount.com", come from?

And I already created a service account as Project Owner access rights, in this case, my service account have the biggest privilege right?

But when I import csv from GCS to Cloud SQL(mysql) I still can't import successfully unless I add such as user-XXXXXXYYYYYZZZZZ@speckle-umbrella-11.iam.gserviceaccount.com", into my GCS bucket.

Is there any update about this issue?

[rtindru]

Bumping this question >> I have the same issue as @patrykmikula . For our pipeline to work, I need to grant Object Viewer permissions on GCS bucket to the service account generated by GCP for my CloudSQL instance.

It appears that terraform does not the service account generated for the google_sql_database_instance resource.

1. Do you plan to add support for this?
2. Is there a temporary workaround for my use-case?

[morgante]

To add some clarity here, the specific need would be to add the serviceAccountEmailAddress field from https://cloud.google.com/sql/docs/mysql/admin-api/v1beta4/instances#resource as an attribute on the google_sql_database_instance resource.

@rtindru As a temporary workaround you could add a small gcloud script to fetch the service account into your Terraform and then grant the required role for that account.

[jpaulgs]

I encountered the same issue as @patrykmikula... it's been 10 months since it was first reported is it likely to be fixed?

importing the service account isn't ideal as I really hate manual steps when creating new environments...

[amkartashov]

w/a is to use "null_resource" and "local_file", though this requires gcloud to be installed along with terraform. In below code gcloud uses another service account for auth, but you can use other methods.

resource "google_sql_database_instance" "mysql" { ... }

resource "google_storage_bucket" "mysql_backups" { ... }

data "local_file" "sql_instance_sa" {
  filename = "../sql_instance_sa"
  depends_on = ["null_resource.get_sql_instance_sa"]
}

resource "null_resource" "get_sql_instance_sa" {
  provisioner "local-exec" {
    command = <<EOF
export CLOUDSDK_CONFIG=$(mktemp -d)
gcloud auth activate-service-account --key-file ${var.admin_json}
gcloud config set project ${var.gcloud_project}
sa_name=$(gcloud --format='get(serviceAccountEmailAddress)' sql instances describe ${google_sql_database_instance.mysql.name})
echo -n $sa_name > ../sql_instance_sa
rm -rf $${CLOUDSDK_CONFIG}
EOF
  }
}

resource "google_storage_bucket_iam_member" "member" {
  bucket = "${google_storage_bucket.mysql_backups.name}"
  role        = "roles/storage.objectAdmin"
  member      = "serviceAccount:${data.local_file.sql_instance_sa.content}"
}

[ghost]

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 feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 hashibot-feedback@hashicorp.com. Thanks!