postgres flexible server does not destroy when AD Authentication is selected present

[fardarter]

Is there an existing issue for this?

• I have searched the existing issues

There is also a hashicorp forum post on the issue: https://discuss.hashicorp.com/t/delete-postgresql-flexible-server-with-ad-admins-enabled/57042

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 and review the contribution guide to help.

Terraform Version

1.6.6

AzureRM Provider Version

3.87.0

Affected Resource(s)/Data Source(s)

azurerm_postgresql_flexible_server

Terraform Configuration Files

resource "azurerm_postgresql_flexible_server" "example" {
  name                = local.db_host_prefix # name must be globally unique
  location            = var.resource_groups.target.location
  resource_group_name = var.resource_groups.target.name

  sku_name   = "B_Standard_B1ms"
  version    = "15"
  storage_mb = 32768

  backup_retention_days = 7
  auto_grow_enabled     = true
  tags                  = var.tags
  zone                  = 3
  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = false
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }
}



resource "azurerm_postgresql_flexible_server_active_directory_administrator" "example" {
  server_name         = azurerm_postgresql_flexible_server.example.name
  resource_group_name = var.resource_groups.target.name
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = data.azurerm_client_config.current.object_id
  principal_name      = "name"
  principal_type      = "ServicePrincipal"
  depends_on          = [azurerm_postgresql_flexible_server.example]
}

Debug Output/Panic Output

│ Flexible Server Name: "example-psqlserver"
│ Object: "<guid>"): performing Delete: Delete "https://management.azure.com/subscriptions/<subguid>/resourceGroups/prototyping/providers/Microsoft.DBforPostgreSQL/flexibleServers/example-psqlserver/administrators/4901bd12-2f72-4952-b01c-f3a7407c40c2?api-version=2022-12-01": HTTP response was nil; connection may have been reset
│ 
│ 
╵
╷
│ Error: deleting Administrator (Subscription: "<subguid>"
│ Resource Group Name: "prototyping"
│ Flexible Server Name: "example-psqlserver"
│ Object: "<guid>"): performing Delete: Delete "https://management.azure.com/subscriptions/<subguid>/resourceGroups/prototyping/providers/Microsoft.DBforPostgreSQL/flexibleServers/example-psqlserver/administrators/<guid>?api-version=2022-12-01": HTTP response was nil; connection may have been reset
│ 
│ 
╵
╷
│ Error: deleting "Firewall Rule (Subscription: \"<subguid>\"\nResource Group Name: \"prototyping\"\nFlexible Server Name: \"example-psqlserver\"\nFirewall Rule Name: \"azure-all-customers-internal\")": polling after Delete: context deadline exceeded
│ 
│ 
╵
Releasing state lock. This may take a few moments...
##[error]Terraform command 'apply' failed with exit code '1'.
##[error]╷
│ Error: deleting "Firewall Rule (Subscription: \"<subguid>\"\nResource Group Name: \"prototyping\"\nFlexible Server Name: \"example-psqlserver\"\nFirewall Rule Name: \"saul\")": performing Delete: Delete "https://management.azure.com/subscriptions/<subguid>/resourceGroups/prototyping/providers/Microsoft.DBforPostgreSQL/flexibleServers/example-psqlserver/firewallRules/saul?api-version=2022-12-01": HTTP response was nil; connection may have been reset
│ 
│ 
╵
╷
│ Error: deleting Administrator (Subscription: "<subguid>"
│ Resource Group Name: "prototyping"
│ Flexible Server Name: "example-psqlserver"
│ Object: "<guid>"): polling after Delete: context deadline exceeded
│ 
│ 
╵

╷
│ Error: deleting "Firewall Rule (Subscription: \"<subguid>\"\nResource Group Name: \"prototyping\"\nFlexible Server Name: \"example-psqlserver\"\nFirewall Rule Name: \"build_server\")": performing Delete: Delete "https://management.azure.com/subscriptions/<subguid>/resourceGroups/prototyping/providers/Microsoft.DBforPostgreSQL/flexibleServers/example-psqlserver/firewallRules/build_server?api-version=2022-12-01": HTTP response was nil; connection may have been reset
│ 
│ 
╵
╷
│ Error: deleting Administrator (Subscription: "<subguid>"
│ Resource Group Name: "prototyping"
│ Flexible Server Name: "example-psqlserver"
│ Object: "<guid>"): performing Delete: Delete "https://management.azure.com/subscriptions/<subguid>/resourceGroups/prototyping/providers/Microsoft.DBforPostgreSQL/flexibleServers/example-psqlserver/administrators/4901bd12-2f72-4952-b01c-f3a7407c40c2?api-version=2022-12-01": HTTP response was nil; connection may have been reset
│ 
│ 
╵
╷
│ Error: deleting Administrator (Subscription: "<subguid>"
│ Resource Group Name: "prototyping"
│ Flexible Server Name: "example-psqlserver"
│ Object: "<guid>"): performing Delete: Delete "https://management.azure.com/subscriptions/<subguid>/resourceGroups/prototyping/providers/Microsoft.DBforPostgreSQL/flexibleServers/example-psqlserver/administrators/<guid>?api-version=2022-12-01": HTTP response was nil; connection may have been reset
│ 
│ 
╵
╷
│ Error: deleting "Firewall Rule (Subscription: \"<subguid>\"\nResource Group Name: \"prototyping\"\nFlexible Server Name: \"example-psqlserver\"\nFirewall Rule Name: \"azure-all-customers-internal\")": polling after Delete: context deadline exceeded
│ 
│ 
╵

Expected Behaviour

The resource should have been destroyed.

Actual Behaviour

The resource was not destroyed.

Steps to Reproduce

1. Deploy a postgres flexible server with only AD Authentication turned on and at least one Admin configured.
2. Attempt to destroy that server and admin (at the same time, as if in a module together)

Important Factoids

No response

References

No response

[neil-yechenwei]

Thanks for raising this issue. Seems I can't reproduce this issue. Could you try latest azurerm provider and below tf config that is similar with yours to see if the issue still exists? Thanks.

tf config:

provider "azurerm" {
  features {}
}

provider "azuread" {}

data "azurerm_client_config" "current" {}

data "azuread_service_principal" "test" {
  object_id = data.azurerm_client_config.current.object_id
}

resource "azurerm_resource_group" "test" {
  name     = "acctestRG-postgresql-test03"
  location = "eastus"
}

resource "azurerm_postgresql_flexible_server" "test" {
  name                = "acctest-fs-test03"
  location            = azurerm_resource_group.test.location
  resource_group_name = azurerm_resource_group.test.name

  sku_name   = "B_Standard_B1ms"
  version    = "15"
  storage_mb = 32768

  backup_retention_days = 7
  auto_grow_enabled     = true
  zone                  = 3

  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = false
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }

  tags = {
    ENV = "Test"
  }
}

resource "azurerm_postgresql_flexible_server_active_directory_administrator" "test" {
  server_name         = azurerm_postgresql_flexible_server.test.name
  resource_group_name = azurerm_resource_group.test.name
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = data.azuread_service_principal.test.object_id
  principal_name      = data.azuread_service_principal.test.display_name
  principal_type      = "ServicePrincipal"

  depends_on = [azurerm_postgresql_flexible_server.test]
}

[fardarter]

Thanks for raising this issue. Seems I can't reproduce this issue. Could you try latest azurerm provider and below tf config that is similar with yours to see if the issue still exists? Thanks.

tf config:

provider "azurerm" {
  features {}
}

provider "azuread" {}

data "azurerm_client_config" "current" {}

data "azuread_service_principal" "test" {
  object_id = data.azurerm_client_config.current.object_id
}

resource "azurerm_resource_group" "test" {
  name     = "acctestRG-postgresql-test03"
  location = "eastus"
}

resource "azurerm_postgresql_flexible_server" "test" {
  name                = "acctest-fs-test03"
  location            = azurerm_resource_group.test.location
  resource_group_name = azurerm_resource_group.test.name

  sku_name   = "B_Standard_B1ms"
  version    = "15"
  storage_mb = 32768

  backup_retention_days = 7
  auto_grow_enabled     = true
  zone                  = 3

  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = false
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }

  tags = {
    ENV = "Test"
  }
}

resource "azurerm_postgresql_flexible_server_active_directory_administrator" "test" {
  server_name         = azurerm_postgresql_flexible_server.test.name
  resource_group_name = azurerm_resource_group.test.name
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = data.azuread_service_principal.test.object_id
  principal_name      = data.azuread_service_principal.test.display_name
  principal_type      = "ServicePrincipal"

  depends_on = [azurerm_postgresql_flexible_server.test]
}

Sure, will try it and report. I'm not spotting the difference though?

As a ponder: I wonder if it matters if there are existing DBs or not or if non-admins have been given rights in those DBs.

[Leo-67]

Hi there,

I'm the one who initiated the forum post, I did not test it again since this summer (August 2023) but now the problem seems to be gone. I've checked within the different module update changelogs since August and did not notice any bugfix relating this.

I did test it again, trying to deploy a database with all three security principals type (User, group and service principal). I did not face any issues, either for deployment nor for destroying.

provider "azurerm" {
  features {}
}

provider "azuread" {}

data "azurerm_client_config" "current" {}

resource "azurerm_resource_group" "test" {
  name     = "acctestRG-postgresql-test03"
  location = "eastus"
}

resource "azurerm_postgresql_flexible_server" "test" {
  name                = "acctest-fs-test03"
  location            = azurerm_resource_group.test.location
  resource_group_name = azurerm_resource_group.test.name

  sku_name   = "B_Standard_B1ms"
  version    = "15"
  storage_mb = 32768

  backup_retention_days = 7
  auto_grow_enabled     = true
  zone                  = 3

  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = false
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }

  tags = {
    ENV = "Test"
  }
}

locals {

  admin_info = {
    "Group" = [{
        display_name = "group"
        object_id    = "group_id"
    }]
    "ServicePrincipal" = [{
        display_name = "sp"
        object_id = "sp_id"
    }]
     "User" = [{
         display_name = "user"
         object_id    = "user_id"
     }]
  }

  permissionsbyrole = flatten([
    for role, identities in var.admin_info : [
      for identity in identities : {
        role     = role
        identity = identity
      }
    ]
  ])
}

resource "azurerm_postgresql_flexible_server_active_directory_administrator" "test" {
  for_each            = { for permission in local.permissionsbyrole : "${permission.role}" => permission }
  server_name         = azurerm_postgresql_flexible_server.test.name
  resource_group_name = azurerm_resource_group.test.name
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = each.value.identity.object_id
  principal_name      = each.value.identity.display_name
  principal_type      = each.value.role

  depends_on = [azurerm_postgresql_flexible_server.test]
}

I cannot explain the resolution root cause but it now works for me. Would be interested in your comments @fardarter.

[fardarter]

@Leo-67 @neil-yechenwei I'm still having the same issues with destruction.

As an aside, I also can't redeploy under the same name after a destroy. Seems that the object is still persisting after reporting destruction.

Does it matter if the admin has specific rights over a database?

[Leo-67]

@fardarter @neil-yechenwei

Hello there, I was able to reproduce the issue.

If you just deploy and straight after destroy the resources, you will not face any issues.

However, if you apply any configuration within the postgresql instance, the issue then appears when trying to delete the resources.

I only connect with my PostgreSQL Entra ID admin account and create a "test" database and then I get the error:

azurerm_postgresql_flexible_server_active_directory_administrator.main["User"]: Still destroying... [id=/subscriptions/4f548d65-5821-416e-b13a-...s/XXX, 29m22s elapsed]
azurerm_postgresql_flexible_server_active_directory_administrator.main["User"]: Still destroying... [id=/subscriptions/XXX-5821-416e-b13a-...s/XXX6, 29m32s elapsed]
azurerm_postgresql_flexible_server_active_directory_administrator.main["User"]: Still destroying... [id=/subscriptions/XXX-5821-416e-b13a-...s/XXX, 29m42s elapsed]
azurerm_postgresql_flexible_server_active_directory_administrator.main["User"]: Still destroying... [id=/subscriptions/XXX-5821-416e-b13a-...s/XXX, 29m52s elapsed]
╷
│ Error: deleting Administrator (Subscription: "XXX"
│ Resource Group Name: "acctestRG-postgresql-test03"
│ Flexible Server Name: "acctest-fs-test03"
│ Object: "a0266ba3-XXX-XXX-XXX-503cb1e9a326"): polling after Delete: context deadline exceeded

[fardarter]

@Leo-67 Thanks

That actually explains why the destroy was working before I configured any DB roles/permissions.

[saksham99del]

Any suggested fix for this issue since terraform does not destroy flexible server completely when an ad admin is present?

Also,Is there any alternative way to destroy the flexible server in the mean time?