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

[t0p4]

hey , has anyone successfully implemented authentication with managed identities ?

[rijulg]

I solved this issue by basically using the solution from here.

Since I am using terragrunt I was able to do the following:

# terragrunt.hcl
inputs  = {
 ...
 db_username = <MY USERNAME>
 db_password = run_cmd("--terragrunt-quiet", "az account get-access-token --resource-type oss-rdbms | jq -r .accessToken")
}

# provider.tf
provider "postgresql" {
  host      = var.server_endpoint
  port      = var.server_port
  database  = var.root_db
  username  = var.db_username
  password  = var.db_password
  superuser = false
  sslmode   = "require"
}

Attempting to do this via terraform alone was not trivial, as the data, local-exec and null resources all were providing the output only on apply.

I think a proper solution for this issue would be to allow specifying the azure auth credentials as part of the provider so that the token can be obtained appropriately. But since I was able to resolve my issue using terragrunt alone, I would not be spending time to resolve this "properly".