Create User on Azure MySQL Database with Terraform

After you create you Azure MySQL Server, you would like to create a Database, but for security you would like to have a new custom user added with permission. Unfortunately the Azure Providers do not provide this, so we need to use some other skills to get these users in.

First we can go ahead and create the MySQL Server and Database. We use the Random provider to generate a random password for the MySQL password, with the rest of the details as default. There are a few places I have put variables in to make it more flexible, which can be expanded on or reduced.

resource "random_password" "server_pwd" {
  length      = 20
  min_upper   = 2
  min_lower   = 2
  min_numeric = 2
  min_special = 2
}
resource "azurerm_mysql_server" "mysql_server" {
  name                = var.mysql_server_name
  location            = var.location
  resource_group_name = var.resource_group_name
  administrator_login          = "mysqladminun"
  administrator_login_password = random_password.server_pwd.result
  sku_name   = "B_Gen5_2"
  storage_mb = 5120
  version    = "5.7"
  auto_grow_enabled                 = true
  backup_retention_days             = 7
  geo_redundant_backup_enabled      = false
  infrastructure_encryption_enabled = false
  public_network_access_enabled     = true
  ssl_enforcement_enabled           = true
  ssl_minimal_tls_version_enforced  = "TLS1_2"
}
resource "azurerm_mysql_database" "mysql_database" {
  name                = var.database_name
  resource_group_name = var.resource_group_name
  server_name         = var.server_name
  charset             = var.database_charset
  collation           = var.database_collation
}

When creating the user it uses standard MySQL queries, therefore we not only need connections to the Database we also need to allow the running service through the MySQL Firewall. To do this we can update the Firewall with our IP Address. You can pass in the IP manually or through a different method, but here I am using a HTTP request to ‘http://ifconfig.me/ip‘ which returns the current IP of what is running the Terraform. We then update the Firewall settings with this IP Address.

data "http" "myip" {
  url = "http://ifconfig.me/ip"
}
resource "azurerm_mysql_firewall_rule" "mysql_firewall_clientip" {
  name                = "ClientIpAddress"
  resource_group_name = var.resource_group_nameE
  server_name         = var.mysql_server_name
  start_ip_address    = chomp(data.http.myip.body)
  end_ip_address      = chomp(data.http.myip.body)
}

At this point we would have an MySQL Server, MySQL Database and our service IP Address allowed on the Firewall. From here we can then use the MySQL provider in Terraform. https://www.terraform.io/docs/providers/mysql/index.html

First you need to setup the provider with you MySQL details. We assume the suffix of the endpoint URL as it is standard from Azure, but if you have a custom domain and/or port then you will need to update this. When I have used this it is in a different Terraform execution and therefore I get the details from Variables, Data Resources and from the Key Vault for the password. You will need to update these as per your setup.

provider "mysql" {
  endpoint = "${var.mysqlserver_name}.mysql.database.azure.com:3306"
  username = "${var.mysql_server_username}@${var.mysql_server_name}"
  password = var.mysql_server_password
  tls      = true
}

You can either pass in your own Username and Password for the details being added in the MySQL Server, or I use the Random Provider to generate these details.

resource "random_password" "database_pwd" {
  length      = 20
  min_upper   = 2
  min_lower   = 2
  min_numeric = 2
  min_special = 2
}
resource "random_string" "username" {
  length           = 5
  special          = false
  override_special = "/@£$"
}
locals {
  database_username = "dbuser_${random_string.username.result}"
  database_password = random_password.database_pwd.result
}

With them you can then create the User in the MySQL Server.

resource "mysql_user" "dbuser" {
  user               = var.database_user_name == "" ? local.database_username : var.database_user_name
  host               = "%"
  plaintext_password = local.database_password
}

And finally Grant the user permissions to the MySQL Database.

resource "mysql_grant" "useraccess" {
  user       = mysql_user.dbuser.user
  host       = mysql_user.dbuser.host
  database   = var.database_name
  privileges = ["SELECT", "UPDATE", "DELETE", "EXECUTE", "INSERT"]
}

There are a few places where you can increase flexibly by using more variables/logic and there are places you can remove them if it is going to be state. These are some of the variables I have used to configure the resources, which below is the variables.tf content.

Server

variable "resource_group_name" {
  type        = string
  description = "Resource Group Name"
}
variable "location" {
  type        = string
  description = "Resource Location"
}

variable "mysql_server_name" {
  type        = string
  description = "MySQL Server Name"
}
variable "mysql_server_username" {
  type        = string
  description = "MySQL Server Username"
  default     = "mysqladmin"
}
variable "mysql_server_password" {
  type        = string
  description = "MySQL Server Password. (If empty auto generated password will be set)"
  default     = ""
}
variable "mysql_server_sku" {
  type        = string
  description = "MySQL Server SKU"
  default     = "B_Gen5_2"
}
variable "mysql_server_storage" {
  type        = number
  description = "MySQL Server Storage in MB"
  default     = 5120
}
variable "mysql_server_version" {
  type        = string
  description = "MySQL Server Version"
  default     = "8.0"
}
variable "mysql_server_settings" {
  type = object({
    auto_grow_enabled                 = bool
    backup_retention_days             = number
    geo_redundant_backup_enabled      = bool
    infrastructure_encryption_enabled = bool
    public_network_access_enabled     = bool
    ssl_enforcement_enabled           = bool
    ssl_minimal_tls_version_enforced  = string
  })
  description = "MySQL Server Configuration"
  default = {
    auto_grow_enabled                 = true
    backup_retention_days             = 7
    geo_redundant_backup_enabled      = false
    infrastructure_encryption_enabled = false
    public_network_access_enabled     = false
    ssl_enforcement_enabled           = true
    ssl_minimal_tls_version_enforced  = "TLS1_2"
  }
}

Database

variable "resource_group_name" {
  type        = string
  description = "Resource Group Name"
}
variable "mysql_server_name" {
  type        = string
  description = "MySQL server Name"
}
variable "database_name" {
  type        = string
  description = "Database Name"
}
variable "database_user_name" {
  type        = string
  description = "MySQL Database User username"
  default     = ""
}
variable "database_user_permissions" {
  type        = list(string)
  description = "MySQL Database User Privileges"
  default     = ["SELECT", "UPDATE", "DELETE", "EXECUTE", "INSERT"]
}
variable "database_charset" {
  type        = string
  description = "Database Charset"
  default     = "utf8"
}
variable "database_collation" {
  type        = string
  description = "Database Collation"
  default     = "utf8_unicode_ci"
}