Error when trying to create Multiple users on a Cloud SQL Instance

So here I’m trying to create multiple users to a SQL Instance in cloud SQL. I’m passing a list of values to the user Name on the root module. But I’m getting Inappropriate value for attribute “name”: string required. I’m declaring it as Set(strings) in the variable. So just want to know where i’m getting it wrong. Below is my code.

//Root Module

module “sql” {
source = “…/modules/sql_instance”
project = var.project

}

module “sql_user” {
source = “…/modules/users”
project = var.project
sql_users_depends_on = “${module.sql.instancename}”

usernames = {
    "user-east"  =   {
        "instance"         = "sqlinst01
        "name"             = ["sql_user01","sql_user02"]        
   
 }
      }

}

//SQL User Module

variable “usernames” {
type = map(object({
instance = string
name = set(string)

}))

}

resource “google_sql_user” “user_sql” {
for_each = var.usernames
project = var.project
provider = google-beta
name = each.value.name
instance = each.value.instance
password = random_password.test01_password[each.key].result
depends_on = [var.sql_users_depends_on]
}

C:\Terraform_Modules\Terraform_SQL_Repo\main>terraform plan
Refreshing Terraform state in-memory prior to plan…
The refreshed state will be used to calculate this plan, but will not be
persisted to local or remote state storage.


Error: Incorrect attribute value type

on …\modules\users\sql_users.tf line 18, in resource “google_sql_user” “user_sql”:
18: name = each.value.name
|----------------
| each.value.name is set of string with 2 elements

Inappropriate value for attribute “name”: string required.

Error: Incorrect attribute value type

on …\modules\users\sql_users.tf line 18, in resource “google_sql_user” “user_sql”:
18: name = each.value.name
|----------------
| each.value.name is set of string with 2 elements

Inappropriate value for attribute “name”: string required.

@chakravarthyakundi77 hi there :slight_smile:

The thing is that you iterate through elements within usernames level and not deeper. In your example, the element from usernames has following settings for key and value:

key = user-east
value = {
        "instance"         = "sqlinst01
        "name"             = ["sql_user01","sql_user02"]  
        }

So when you refer to instance via each.value.instance you get a string, because it is a string indeed. But when you refer to name via each.value.name you get its real value — the set, which is also technically correct. But this is not what you expect, I know.

You might try to flatten this object with flatten() function

@vasylenko thank you for the suggestion, I tried that …

locals {
sql_users = flatten([
for instance,name in var.usernames : [
for name in names : {
instance = instance
name = name
}]
])}

resource “google_sql_user” “user_sql” {
for_each = {
for sqlusers in local.sql_users : “$${sqlusers.instance}.${sqlusers.name}” => sqlusers
}
project = var.project
provider = google-beta
name = each.value.name
instance = each.value.instance
password = random_password.test01_password[each.key].result
depends_on = [var.sql_users_depends_on]

}

I’m getting the below error, maybe I’m still doing something wrong, maybe a simple mistake.

C:\Terraform_Modules\Terraform_SQL_Repo\main>terraform plan

Error: Invalid reference

on …\modules\users\sql_users.tf line 16, in locals:
16: for name in names : {

A reference to a resource type must be followed by at least one attribute
access, specifying the resource name.

for some reason it is giving that “Missing superscript or subscript argument”
had to give extra $

@vasylenko any suggestions on the issue . I tired ot follow some of the suggestions in the below links. maybe I’m still missing something.


Given the following usernames variable:

variable "usernames" = {
  default = {
    "user-east" = {
      "instance" = "sqlinst01
      "name"     = ["sql_user01","sql_user02"]
    }
  }
}

You would want to flatten it like:

locals {
  sql_users = flatten([
    for username, user_cfg in var.usernames : [
      for name in lookup(user_cfg, "name", []) : {
          username = username
          instance = user_cfg["instance"]
          name     = name
      }
    ]
  ])
}

And then, iterate over the flattened structure to create uniquely named keys for your google_sql_user resources:

resource “google_sql_user” “user_sql” {
  for_each = {
    for sqluser in local.sql_users : “${sqluser.username}_${sqluser.instance}_${sqluser.name}” => sqluser
  }
  provider = google-beta

  project  = var.project
  name     = each.value.name
  instance = each.value.instance
  password = random_password.test01_password[each.key].result

  depends_on = [var.sql_users_depends_on]
}

It would work, but ${sqluser.username} and ${sqlusers.name} feel very redundant
(and as you can see, we are not using the username key/value anywhere in the for_each loop). I would therefore suspect you would want to make instance the key of your input map instead:

variable "sql_bindings" = {
  default = {
    "sqlinst01" = ["sql_user01","sql_user02"]
    "sqlinst02" = ["sql_user01","sql_user03"]
  }
}


locals {
  sql_bindings = flatten([
    for instance, users in var.sql_bindings : [
      for name in users : {
          instance = instance
          name = name
      }
    ]
  ])
}

resource “google_sql_user” “user_sql” {
  for_each = {
    for binding in local.sql_bindings: “${binding.instance}_${binding.name}” => binding
  }
  provider = google-beta

  project  = var.project
  name     = each.value.name
  instance = each.value.instance
  password = random_password.test01_password[each.key].result

  depends_on = [var.sql_users_depends_on]
}

@sleterrier thank you both options worked :grinning:.

But i’m now running into a problem with the password. I’m trying to get a separate password for each user, the code below which I had before worked when I had one user for each Instance. I do not think it will work for my scenario where I have multiple user for each Instance. Is there a way to get unique password for each user, which can be any number per instance.

resource “random_password” “test01_password” {
for_each = var.sql_bindings
length = 16
special = false
upper = true
lower = true
}

In the resource I’m calling it as …
password = random_password.test01_password[each.key].result

This gives a error…
Error: Invalid index

on …\modules\users\sql_users.tf line 65, in resource “google_sql_user” “user_sql”:
65: password = random_password.test01_password[each.key].result
|----------------
| each.key is “sqlinst02_sql_user01”
| random_password.test01_password is object with 2 attributes

The given key does not identify an element in this collection value.

@chakravarthyakundi77, you can achieve this by creating a random_password for each element of for binding in local.sql_bindings: “${binding.instance}_${binding.name}” => binding. Here’s how I would do it:

variable "sql_bindings" = {
  default = {
    "sqlinst01" = ["sql_user01","sql_user02"]
    "sqlinst02" = ["sql_user01","sql_user03","sql_user04"]
  }
}

locals {
  sql_binding_pairs = flatten([
    for instance, users in var.sql_bindings : [
      for name in users : {
          instance = instance
          name = name
      }
    ]
  ])

  sql_bindings = {
    for binding in local.sql_binding_pairs: “${binding.instance}_${binding.name}” => binding
  }
}

resource “random_password” “sql” {
  for_each = local.sql_bindings
  
  length = 16
  special = false
  upper = true
  lower = true
}

resource “google_sql_user” “user_sql” {
  for_each = local.sql_bindings
  provider = google-beta

  project  = var.project
  name     = each.value.name
  instance = each.value.instance
  password = random_password.sql[each.key].result

  depends_on = [var.sql_users_depends_on]
}

It give a error, I don’t know if the local value can take its own local value. *ignore the extra$

Error: Self-referencing local value

on …\modules\users\sql_users.tf line 51, in locals:
50: sql_bindings = {
51: for binding in local.sql_bindings: “$${binding.instance}_${binding.name}” => binding
52: }

Local value local.sql_bindings cannot use its own result as part of its
expression.

My bad,

  sql_bindings = {
    for binding in local.sql_bindings: “${binding.instance}_${binding.name}” => binding
  }

should be:

  sql_bindings = {
    for binding in local.sql_binding_pairs: “${binding.instance}_${binding.name}” => binding
  }

However, reading your initial post, I don’t think this is what you want to do. New iteration incoming :slight_smile:

I am now suspecting this might be what you’re attempting to do:

variable "usernames" = {
  default = {
    "user-east" = {
      "sqlinst01" = ["sql_user01","sql_user02"]
      "sqlinst02" = ["sql_user01","sql_user02"]
    }

    "user-west" = {
      "sqlinst01" = ["sql_user03","sql_user04"]
      "sqlinst03" = ["sql_user03","sql_user04"]
    }
  }
}

locals {
  sql_binding_pairs = flatten([
    for username, user_map in var.usernames : [
      for instance, users_list in user_map : [
        for name in users_list : {
          username = username
          instance = instance
          name     = name
        }
      ]
    ]
  ])

  sql_bindings = {
    for binding in local.sql_bindings_pairs: “${binding.username}_${binding.instance}_${binding.name}” => binding
  }
}

resource “google_sql_user” “user_sql” {
  for_each = local.sql_bindings
  provider = google-beta

  project  = var.project
  name     = each.value.name
  instance = each.value.instance
  password = random_password.test01_password[each.value.username].result

  depends_on = [var.sql_users_depends_on]
}

resource “random_password” “sql” {
  for_each = var.usernames
  
  length = 16
  special = false
  upper = true
  lower = true
}
  • This would generate a random password for each key of var.usernames (user-east and user-west in this case).
  • The same password will be used for all the sql_users/instance combinations you define under each key of var.usernames

I am not exactly sure that makes sense in practice, but that’s how you can do it with Terraform.

Actually I think its working as I want, I’m currently running it , will have to see after it is completed.

So it works but still have a issue. It Creates a Password for each user. But if we have same user for each instance, it creates a different password for the same user for each Instance, which is not what I want.

example,
“sqlInst01” = [“sql_user05”,“sql_user06”]
“sqlInst02” = [“sql_user05”,“sql_user07”,“sql_user08”]

sqlInst01 - sql_user05 - Pass1
sqlInst02 - sql_user05 - Pass2

Regarding your last post, yes that is what I’m attempting to do and you are right that is what I’m getting.

So I guess we cannot do anything about the password part right. Terraform cannot handle that functionality?.