How to create databases, users and grants with terraform mysql and output the created credentials?

Question

For a new deployment I want to create databases, users and grants on a previous created MySQL database on azure.

The following code used inside a module creates the environment as expected and I still struggle how to create the outputs of the module and so I get something like the following, to give it to the operations team:

module.test_cluster.mysql_users[cat].password = randompassword1
module.test_cluster.mysql_users[dog].password = randompassword2

Code example:

mysql_databases = [
  "foo",
  "bar"
]

mysql_users = [
  "stage-cat",
  "stage-dog",
  "stage-snake"
]

mysql_grants = {
  "cat-0" = {
    name     = "stage-cat"
    database = "foo"
    grant    = ["ALL"]
  },
  "cat-1" = {
    name     = "stage-cat"
    database = "bar"
    grant    = ["SELECT", "EXECUTE", "SHOW VIEW"]
  },
  "dog-0" = {
    name     = "stage-dog"
    database = "bar"
    grant    = ["ALL"]
  }
}

resource "mysql_database" "test" {
  for_each = var.mysql_databases
  name     = each.key
}

resource "mysql_user" "test" {
  for_each           = var.mysql_users
  user               = each.value
  host               = "%"
  tls_option         = "SSL"
  plaintext_password = random_password.test[each.value].result
}

resource "mysql_grant" "test" {
  for_each   = var.mysql_grants
  user       = mysql_user.test[each.value["name"]].user
  host       = "%"
  database   = each.value["database"]
  privileges = each.value["grant"]
}

resource "random_password" "test" {
  for_each = var.mysql_users
  length   = 32
  special  = false
}

Answer 1

To ensure separation and use in other environments, make sure to have an outputs file and set each password as an output. TF Output Docs. I haven't done much with for each as it was just released last time I used terraform. I would imagine a way to iterate over passwords and assign them as outputs.

once you have the output set for the module, I would then have my consuming terraform put those values in a password vault of some kind. You can use Azure's Key Vault. TF Azure Key Vault, or any other solution. Just ensure that the ops team has access to the destination. This way there isn't anything introduced between when/where the keys are generated and them being stored, reducing the chance for human error.

Answer 2

This is my solution how to output usernames and passwords:

In the module:

output "mysql_credentials" {
  value = [
    for user in var.mysql_users :
    { "username" = "${user}@${var.cluster_name}" // Hostname is for Azure MySQL service
      "password" = "${random_password.test[user]["result"]}"
    }
  ]
}

In the terraform file referencing the module:

output "mysql_service_credentials" {
  value = module.cluster.mysql_credentials
}

The output:

mysql_credentials = [
  {
    "Password" = "OecheeP1Iequ6zeis2aipai5eiyooL8g"
    "Username" = "stage-cat@stage"
  },
  {
    "Password" = "xaed1EiGh4ahbea4ohjeetouw0Geph8o"
    "Username" = "stage-dog@stage"
  },
  {
    "Password" = "Seu8ieciewoh4Ohl6Ut7em1aiVie1Ao0"
    "Username" = "stage-snake@stage"
  },
]