Creating database with additional user using service account impersonation claims to require API activation in service account project

jwaibel-pons commented 1 month ago

TL;DR

Our terraform is executed using a dedicated service account and service account impersonation as described here

This terraform service account is part of a dedicated terraform project where only terraform stuff (like the storage for the state) is located.

When I use terraform and the impersonated service account user to create a new project, a database and some additional databases and users, everything works fine, except the creation of the additional users - I get an error that I have to activate the Clous SQL API on the terraform(!) project.

Expected behavior

The module should created the additional database users as expected.

Observed behavior

I'm getting this error. Note that project=850542690668 is the project where the service account is residing ("the terraform project"), NOT the target project for the database.

Error: Error, failed to insert user user0 into instance customdbinstance: googleapi: Error 403: Cloud SQL Admin API has not been used in project 850542690668 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=850542690668 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.
│ Details:
│ [
│   {
│     "@type": "type.googleapis.com/google.rpc.Help",
│     "links": [
│       {
│         "description": "Google developers console API activation",
│         "url": "https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=850542690668"
│       }
│     ]
│   },
│   {
│     "@type": "type.googleapis.com/google.rpc.ErrorInfo",
│     "domain": "googleapis.com",
│     "metadata": {
│       "consumer": "projects/850542690668",
│       "service": "sqladmin.googleapis.com"
│     },
│     "reason": "SERVICE_DISABLED"
│   }
│ ]
│ , accessNotConfigured
│
│   with google_sql_user.default[0],
│   on main.tf line 274, in resource "google_sql_user" "default":
│  274: resource "google_sql_user" "default" {

Terraform Configuration

Our terraform is generated by terragrunt, so it rather ugly.

Ths would be the input for the module:

inputs = {
  name          = "customdbinstance"
  tier          = "db-g1-small"
  db_name       = "databasename"
  user_name     = "user0"
  database_version = "POSTGRES_15"
  deletion_protection = false
  database_flags = [
  {
    name = "max_connections"
    value = "200"
  }]
  additional_databases = [
    {
      name = "customdbname"
      charset    = "UTF8"
      collation  = "en_US.UTF8"
    },
    {
      name = "anotherdbname"
      charset    = "UTF8"
      collation  = "en_US.UTF8"
    }
  ]
  additional_users = [
    {
      name = "user1"
      password = ""
      random_password = true
    },
    {
      name = "user2"
      password = ""
      random_password = true
    }
  ]
}

provider.tf (for impersonation)

provider "google" {
 alias = "impersonation"
 scopes = [
   "https://www.googleapis.com/auth/cloud-platform",
   "https://www.googleapis.com/auth/userinfo.email",
 ]
}
data "google_service_account_access_token" "default" {
 provider                       = google.impersonation
 target_service_account         = "terragrunt@<terraform-project>.iam.gserviceaccount.com"
 scopes                         = ["userinfo-email", "cloud-platform"]
 lifetime                       = "1200s"
}
provider "google" {
 project     = "terraform-project"
 region      = "europe-west4"
 zone        = "europe-west4-b"
 access_token           = data.google_service_account_access_token.default.access_token
 request_timeout        = "60s"
}


### Terraform Version

```sh
Terraform v1.8.4
on linux_amd64
+ provider registry.terraform.io/hashicorp/google v5.38.0
+ provider registry.terraform.io/hashicorp/google-beta v5.38.0
+ provider registry.terraform.io/hashicorp/null v3.2.2
+ provider registry.terraform.io/hashicorp/random v3.6.2

Additional information

A google_sql_database_instance has been created succesfully by terraform in the target project using the service account impersonation. Regarding this resource (any many others like vpc or k8s) this whole impersonation is working fine.

These resources are still planned:

  # google_sql_database.additional_databases["anotherdbname"] will be created
  # google_sql_database.additional_databases["customdbname"] will be created
  # google_sql_database.default[0] will be created
  # google_sql_user.additional_users["user1"] will be created
  # google_sql_user.additional_users["user2"] will be created
  # google_sql_user.default[0] will be created

As a failing example lets have a look at the creation of the additional user "user1".

  # google_sql_user.additional_users["user1"] will be created
  + resource "google_sql_user" "additional_users" {
      + host                    = (known after apply)
      + id                      = (known after apply)
      + instance                = "customdbinstance"
      + name                    = "user1"
      + password                = (sensitive value)
      + project                 = "schrom-project-with-tg-6857"    <<<<<<<<  This is my desired target project
      + sql_server_user_details = (known after apply)
    }

This is the debug output for the creation of this user. As you can the, the POST request is referring the correct target project (where the Cloud SQL Admin API of course has been activated), but the error message claims the API needs to activated on the home project of the terraform service account (once again: 850542690668 is the ID of the terraform project where the service account is located, NOT the target project "schrom-project-with-tg-6857").

2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: POST /sql/v1beta4/projects/schrom-project-with-tg-6857/instances/customdbinstance/users?alt=json&prettyPrint=false HTTP/1.1
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Host: sqladmin.googleapis.com
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: User-Agent: google-api-go-client/0.5 Terraform/1.8.4 (+https://www.terraform.io) Terraform-Plugin-SDK/2.33.0 terraform-provider-google/5.38.0 blueprints/terraform/terraform-google-sql-db:postgresql/v20.2.0
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Content-Length: 93
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Content-Type: application/json
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: X-Goog-Api-Client: gl-go/1.21.12 gdcl/0.185.0
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Accept-Encoding: gzip
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: {
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:  "instance": "customdbinstance",
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:  "name": "user1",
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:  "password": "randompassword"
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: }
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5
2024-07-23T13:28:20.579+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: -----------------------------------------------------
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: 2024/07/23 13:28:20 [DEBUG] Google API Response Details:
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: ---[ RESPONSE ]--------------------------------------
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: HTTP/2.0 403 Forbidden
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Alt-Svc: h3=":443"; ma=2592000,h3-29=":443"; ma=2592000
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Cache-Control: private
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Content-Type: application/json; charset=UTF-8
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Date: Tue, 23 Jul 2024 11:28:31 GMT
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Server: ESF
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Vary: Origin
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Vary: X-Origin
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: Vary: Referer
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: X-Content-Type-Options: nosniff
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: X-Frame-Options: SAMEORIGIN
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: X-Xss-Protection: 0
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:   "error": {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     "code": 403,
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     "message": "Cloud SQL Admin API has not been used in project 850542690668 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=850542690668 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     "errors": [
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:       {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "message": "Cloud SQL Admin API has not been used in project 850542690668 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=850542690668 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "domain": "usageLimits",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "reason": "accessNotConfigured",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "extendedHelp": "https://console.developers.google.com"
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:       }
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     ],
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     "status": "PERMISSION_DENIED",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     "details": [
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:       {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "@type": "type.googleapis.com/google.rpc.Help",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "links": [
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:           {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:             "description": "Google developers console API activation",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:             "url": "https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=850542690668"
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:           }
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         ]
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:       },
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:       {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "@type": "type.googleapis.com/google.rpc.ErrorInfo",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "reason": "SERVICE_DISABLED",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "domain": "googleapis.com",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         "metadata": {
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:           "service": "sqladmin.googleapis.com",
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:           "consumer": "projects/850542690668"
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:         }
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:       }
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:     ]
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5:   }
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: }
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5
2024-07-23T13:28:20.709+0200 [DEBUG] provider.terraform-provider-google_v5.38.0_x5: -----------------------------------------------------

NOTE: If I switch of the service account impersonation and run the very same terraform with my personal account, everything is working as expected.

imrannayer commented 1 month ago

@jwaibel-pons this is how it works. Project where your service account resides needs to have API enabled. This is not a bug

jwaibel-pons commented 1 month ago

Let me rephrase my question:

My service account can create VMs without having to enable the Compute Engine API on the service account project.
My service account can create k8s clusters without having to enable the Kubernetes API on the service account project.
My service account can create Buckets without having to enable the Storage API on the service account project.
My service account can even create SQL database instances without having to enable the SQL Admin API on the service account project.
and many more ...

but

My service account can not create additional users in SQL database instances unless the SQL Admin API on the service account project is enabled.

What is the logic and reasoning behind this behaviour?

imrannayer commented 1 month ago

@jwaibel-pons there are some services for which client side APIs are need for quota check etc. For those you need to enable API in client project. Then there are Some APIs that are enabled by default.

https://cloud.google.com/service-usage/docs/enabled-service#default

jwaibel-pons commented 1 month ago

@imrannayer

This is the state of the terraform project. The last one was added as a workaround for this issue.

$ gcloud services list --enabled --project <terraform-project>
NAME                                 TITLE
cloudbilling.googleapis.com          Cloud Billing API
cloudresourcemanager.googleapis.com  Cloud Resource Manager API
iam.googleapis.com                   Identity and Access Management (IAM) API
iamcredentials.googleapis.com        IAM Service Account Credentials API
serviceusage.googleapis.com          Service Usage API
sqladmin.googleapis.com              Cloud SQL Admin API

terraform-google-modules / terraform-google-sql-db