Azure postgres flexible server create user using terraform

Question

I am trying to create users with names same as that of the DB in postgres flexible server using terraform. The user creation code is as below.

resource "azurerm_postgresql_flexible_server_configuration" "create_users" {
  name      = "create_users_script"
  server_id = azurerm_postgresql_flexible_server.example.id
  count     = length(var.db_names)
  value     = <<-EOT
    CREATE USER ${var.db_names[count.index]} WITH PASSWORD 'password1';
  EOT
}

Everything seems good with terraform plan, with it showing the names of users to be created like:

  # module.eng_dev_pg.azurerm_postgresql_flexible_server_configuration.create_users[0] will be created
  + resource "azurerm_postgresql_flexible_server_configuration" "create_users" {
      + id        = (known after apply)
      + name      = "create_users_script"
      + server_id = "/subscriptions/XXXXX/resourceGroups/rsg-01/providers/Microsoft.DBforPostgreSQL/flexibleServers/pg-02"
      + value     = <<-EOT
            CREATE USER keycloak WITH PASSWORD 'password1';
        EOT
    }

But while terraform apply, it errors out saying:

Error: updating Configuration (Subscription: "XXXXX" Resource Group Name: "rsg-01" Flexible Server Name: "pg-02" Configuration Name: "create_users_script"): performing Update: unexpected status 400 with error: InvalidParameterValue: Invalid value given for parameter serverParameterValue. Specify a valid parameter value. │ with module.eng_dev_pg.azurerm_postgresql_flexible_server_configuration.create_users[0], on ../../modules/postgres/public-postgres-flexible.tf line 43, in resource "azurerm_postgresql_flexible_server_configuration" "create_users": 43: resource "azurerm_postgresql_flexible_server_configuration" "create_users" {

I tried a bit of like using for_each, but that's of no use as the current code is polling the required usernames properly as expected.

Expectation is to get the users created; about 12 users to be created. Error is the same for all user creations.

As the error says Invalid value given for parameter serverParameterValue I am looking for a way to specify the serverParameterValue.

NOTE: The db_names (user names) are provided from a module as:

db_names = ["keycloak", "provisioner", "account-development", "customer1-account-development", "customer1-file-development", "customer1-keycloak-development", "customer1-metadata-development", "entity-development", "file-development", "keycloak-development", "provisioner-development", "metadata-development"]
}

Answer 1

Azure postgres flexible server create user using terraform

As the postgresql flexible server configuration module is failing during the execution of terraform apply for creating users in PostgreSQL flexible database.

Alternatively, you can use Azure CLI commands within a null resource in Terraform.

Here is the updated code to create multiple users in a PostgreSQL flexible server database.

provider "azurerm" {
  features {}
}

resource "azurerm_resource_group" "example" {
  name     = "postgreysql-resources-demo1"
  location = "eastasia"
}

resource "azurerm_postgresql_flexible_server" "example" {
  name                   = "venkatflexibleserver5"
  resource_group_name    = azurerm_resource_group.example.name
  location               = azurerm_resource_group.example.location
  version                = "12"
  administrator_login    = "psqladmin"
  administrator_password = "H@Sh1CoR3!"
   zone                   = "1"


  storage_mb = 32768

  sku_name = "GP_Standard_D4s_v3"

  depends_on = [azurerm_resource_group.example]
}


resource "null_resource" "execute_az_cli" {
  provisioner "local-exec" {
    command = <<-EOT
      az postgres flexible-server connect -n "venkatflexibleserver5" -u "psqladmin" -p "H@Sh1CoR3!" -d "postgres"
      pwsh -Command "\$usernames = @('keycloak', 'provisioner', 'account_development', 'customer1_account_development', 'customer1_file_development', 'customer1_keycloak_development', 'customer1_metadata_development', 'entity_development', 'file_development', 'keycloak_development', 'provisioner_development', 'metadata_development'); foreach (\$user in \$usernames) { \$sqlCommand = \"CREATE USER \$user WITH PASSWORD 'Welcome@123'\"; az postgres flexible-server execute -n 'venkatflexibleserver5' -u 'psqladmin' -p 'H@Sh1CoR3!' -d 'postgres' -q \$sqlCommand --output table }"
    EOT
  }
  depends_on = [ azurerm_postgresql_flexible_server.example ]
}

Terraform Apply:

To check all users in a PostgreSQL database, Hers is the query.

    SELECT usename FROM pg_catalog.pg_user;

After running the above Terraform code, all the users specified in the script have been created in the database

Reference: az postgres flexible-server | Microsoft Learn