azurerm_postgresql_server_key - How do I revalidate a key for a replica?

[jgreat]

I'm stuck. It doesn't seem possible to use a Terraform workflow to create working PostgreSQL replica when you define a KeyVault (BYOK) key. The server remains in an Inaccessible state until the identity is created, the identity is given access to the Key Vault, and the key is "re-validated".

I can accomplish some of theses tasks with Terraform.

• :heavy_check_mark: Create Replica
• :warning: Apply Key Vault permissions to Server Identity (see #10480 - use az postgres server update --assign-identity as workaround)
• :x: Re-validate Key so Replica becomes available.

The az cli workflow is here. Revalidating the key is done by running a az postgres server key create command for the already associated key.

The azurerm_postgresql_server_key resource sees that a key is already been associated with the postgres server and error out with A resource with the ID ... already exists - to be managed via Terraform this resource needs to be imported into the State. If you import this resource, it appears to be in a valid state (but not the parent database) so Terraform doesn't do anything with it.

This does feel like an upstream (Azure) problem.

• Azure API should return some kind of state for the key object ("Needs Validation" or something). This doesn't really help the "existing resource" problem, but it would show the resource that something needs to be done.
• Azure should not automatically associate the key with the server unless its going to do the magic to validate it. Leave it up to the operator to pick the appropriate Key for the replica. This is more ideal IMHO.

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform (and AzureRM Provider) Version

Terraform v0.14.5
+ provider registry.terraform.io/hashicorp/azurerm v2.45.1

Affected Resource(s)

• azurerm_postgresql_server_key

Terraform Configuration Files

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "2.43.0"
    }
  }
  required_version = "~> 0.14"
}

provider "azurerm" {
  features {}
}

resource "azurerm_resource_group" "pg" {
  name     = "my-pg-resources"
  location = "canadacentral"
}

resource "azurerm_postgresql_server" "writer" {
  name                = "${azurerm_resource_group.pg.name}-writer"
  location            = azurerm_resource_group.pg.location
  resource_group_name = azurerm_resource_group.pg.name

  sku_name = "GP_Gen5_4"

  storage_mb                   = 51200
  backup_retention_days        = 7
  geo_redundant_backup_enabled = true
  auto_grow_enabled            = true

  administrator_login          = var.db_admin_username
  administrator_login_password = var.db_admin_password
  version                      = "11"

  identity {
    type = "SystemAssigned"
  }

  ssl_enforcement_enabled          = true
  ssl_minimal_tls_version_enforced = "TLS1_2"
  public_network_access_enabled    = false
}

resource "azurerm_postgresql_server" "reader" {
  name                = "${azurerm_resource_group.pg.name}-reader"
  location            = azurerm_resource_group.pg.location
  resource_group_name = azurerm_resource_group.pg.name

  create_mode               = "Replica"
  creation_source_server_id = azurerm_postgresql_server.writer.id
  sku_name                  = "GP_Gen5_4"

  storage_mb                   = 51200
  backup_retention_days        = 7
  geo_redundant_backup_enabled = true
  auto_grow_enabled            = true

  administrator_login          = var.db_admin_username
  administrator_login_password = var.db_admin_password
  version                      = "11"

  identity {
    type = "SystemAssigned"
  }

  ssl_enforcement_enabled          = true
  ssl_minimal_tls_version_enforced = "TLS1_2"
  public_network_access_enabled    = false
}

# Permission required to unwrap key
resource "azurerm_key_vault_access_policy" "writer" {
  key_vault_id = var.keyvault_id
  tenant_id    = data.azurerm_client_config.current.tenant_id
  object_id    = azurerm_postgresql_server.reader.identity.0.principal_id

  key_permissions    = ["get", "unwrapkey", "wrapkey"]
  secret_permissions = ["get"]
}

# will fail here on first run because there is no identity on reader. 
# See #10480 and workaround above.
resource "azurerm_key_vault_access_policy" "reader" {
  key_vault_id = var.keyvault_id
  tenant_id    = data.azurerm_client_config.current.tenant_id
  object_id    = azurerm_postgresql_server.reader.identity.0.principal_id

  key_permissions    = ["get", "unwrapkey", "wrapkey"]
  secret_permissions = ["get"]
}

data "azurerm_key_vault_key" "postgres" {
  name         = "postgres"
  key_vault_id = var.keyvault_id
}

resource "azurerm_postgresql_server_key" "writer" {
  server_id        = azurerm_postgresql_server.writer.id
  key_vault_key_id = data.azurerm_key_vault_key.postgres.id

  depends_on = [
    azurerm_key_vault_access_policy.writer
  ]
}

# Will fail here because "resource already exists"
# Importing will show a valid resource so TF will not resolve the re-validation problem and leaves the db in an "Inaccessible" state.
resource "azurerm_postgresql_server_key" "reader" {
  server_id        = azurerm_postgresql_server.reader.id
  key_vault_key_id = data.azurerm_key_vault_key.postgres.id

  depends_on = [
    azurerm_key_vault_access_policy.reader
  ]
}

Debug Output

Expected Behaviour

I should be able to create a PostgreSQL server Replica that uses a Key Vault key with a pure Terraform workflow.

Actual Behaviour

Azure makes this really hard since their API doesn't handle the re-validation of keys or provide a consumable state of the key.

Steps to Reproduce

1. terraform apply

References

• 10480 - Missing Identity block after

[emichellecarter]

I see the same behavior with bicep so this is not a Terraform specific issue.

[NeoTech]

I have the same issue in terraform, when creating a database in replica mode it fails..

[emichellecarter]

One possible way around this that I found was to create the primary and the replica databases without the customer managed key initially then update the primary and secondary databases with the customer managed key.

[github-actions[bot]]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues. If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.