Issue connection on Azure with passwordless authentication

[WilliamB17]

Hi,

I get an error when I try to connect to my database via passwordless authentication:

Error: Error connecting to PostgreSQL server psql-000.postgres.database.azure.com (scheme: postgres): pq: Service Principal oid mismatch for role[my_administrator_principal_name].

I use the latest provider version 1.22.0 and Terraform v1.7.5

data "azurerm_client_config" "current" {
}

resource "azurerm_postgresql_flexible_server" "pgsql" {
  # ...
  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = true
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }
}


resource "azurerm_postgresql_flexible_server_active_directory_administrator" "administrators" {
  object_id           = var.azure_config.object_id
  principal_name      = "my_administrator_principal_name"
  principal_type      = "ServicePrincipal"
  resource_group_name = var.resource_group.name
  server_name         = azurerm_postgresql_flexible_server.pgsql.name
  tenant_id           = azurerm_client_config.current.tenant_id
}

provider "postgresql" {
  host                = var.azurerm_postgresql_flexible_server.fqdn
  port                = 5432
  database            = "postgres"
  username            = var.active_directory_administrator.principal_name
  sslmode             = "require"
  azure_identity_auth = true
  azure_tenant_id     = azurerm_client_config.current.tenant_id
}

However, I manage to connect with psql as described here : https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication

[andrewpleasants-bjss-nhs]

Hi @WilliamB17

I ran into this issue today and found your post - so thought I'd share what I found (in case you haven't solved this yet - and for anybody else who finds this):

Our problem was that we were using user-assigned managed identities (UAMI) and the provider doesn't allow you to specify a UUID of an a UAMI, so therefore this call signs is as a system assigned managed identity.

As a workaround you can set the AZURE_CLIENT_ID environment variable to the UUID of the UAMI you want to use - but be aware that this will affect anything else that is using the Azure SDK.

In the long term, could probably add a configuration parameter to the provider