Terraform-provider-google: Cannot provision a service account with sql role

[sjosegarcia]

As stated in the title, I cannot provision a service account with sql admin roles. I looked at the documents and I am a little lost.
Would someone explain what I might be doing wrong. The goal is to create a service account, which will contain the roles to launch/modify/delete the infra.
This is the error that appeared:

Error: Request "Create IAM Members roles/cloudsql.admin serviceAccount:[service_account] for \"project \\\"[project_id]\\\"\"" returned error: Error retrieving IAM policy for project "[project_id]": googleapi: Error 403: The caller does not have permission, forbidden

  on cloud_iam.tf line 38, in resource "google_project_iam_member" "terraform_sql_admin":
  38: resource "google_project_iam_member" "terraform_sql_admin" {



Error: Error, failed to create instance db: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

  on database.tf line 12, in resource "google_sql_database_instance" "db_instance":
  12: resource "google_sql_database_instance" "db_instance" {

resource "google_service_account" "service_account" {
  account_id   = "terraform"
  display_name = "Terraform Service Account"
  description  = "This is the terraform service account, which is used to create/modify/delete Infrastructure."
}

data "google_iam_policy" "terraform_sa_admin" {
  binding {
    role = "roles/iam.serviceAccountUser"

    members = [
      var.service_account
    ]
  }
}

resource "google_service_account_iam_policy" "terraform_account_iam" {
  service_account_id = google_service_account.service_account.name
  policy_data        = data.google_iam_policy.terraform_sa_admin.policy_data
}

data "google_iam_policy" "terraform_cloud_run_policy" {
  binding {
    role = "roles/run.invoker"
    members = [
      var.service_account
    ]
  }
}

resource "google_cloud_run_service_iam_policy" "cloud_run_policy" {
  location = google_cloud_run_service.x.location
  project = google_cloud_run_service.x.project
  service = google_cloud_run_service.x.name
  policy_data = data.google_iam_policy.terraform_cloud_run_policy.policy_data
}

resource "google_project_iam_member" "terraform_sql_admin" {
  project = var.project_id
  role    = "roles/cloudsql.admin"
  member  = var.service_account
}
/*
data "google_iam_policy" "terraform_sql_admin" {
  binding {
    role = "roles/cloudsql.admin"

    members = [
      var.service_account,
    ]
  }
}

resource "google_project_iam_policy" "project" {
  project     = var.project_id
  policy_data = data.google_iam_policy.terraform_sql_admin.policy_data
}*/

[upodroid]

Hello

You effectively broke your Google Project by using the google_project_iam_policy resource which removed every IAM role from your project and only assigned SQL Admin role to your service account.

Unless you had inherited roles from an Organization or a Folder, your permissions were revoked and the remaining steps of your apply failed with 403 errors.

Open the activity logs and reassign all the roles you removed and don't use this resource unless you understand it's what it does and how to reassign roles in an event of a mistake like this.

[edwardmedia]

@sjosegarcia does what @upodroid explain make sense to you? You may want to review the differences among _iam_policy, _iam_memebr and _iam_binding before hit apply.

[sjosegarcia]

I am sorry for the late response. Ill dive depeer later at the difference. But I have used just iam_policy before with the role that is contained in project_iam_policy.

[edwardmedia]

@sjosegarcia Closing this issue now. Please feel free to reopen it if you see a need to continue the conversation. Thanks

[hashibot[bot]]

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 👉 [email protected]. Thanks!