postgresql flexible server Azure AD authentication issue: DefaultAzureCredential: failed to acquire a token

[Marco10101]

I'am trying to setup Azure AD authentication in terraform for postgresql flexible server but it keeps giving an error. I think that everything is configured correctly. Hope that someone can help me out with this.

The error:

│ Error: DefaultAzureCredential: failed to acquire a token.
│ Attempted credentials:
│ 	EnvironmentCredential: missing environment variable AZURE_TENANT_ID
│ 	WorkloadIdentityCredential: no client ID specified. Check pod configuration or set ClientID in the options
│ 	ManagedIdentityCredential: no default identity is assigned to this resource
│ 	AzureCLICredential: ERROR: Please run 'az login' to setup account.
│ 
│ 
│   with provider["registry.terraform.io/cyrilgdn/postgresql"],
│   on main.tf line 65, in provider "postgresql":
│   65: provider "postgresql"

TF versions:

• Installing cyrilgdn/postgresql v1.21.0...
• Installed cyrilgdn/postgresql v1.21.0 (self-signed, key ID 3918DD444A3876A6)
• Installing hashicorp/azurerm v3.85.0...
• Installed hashicorp/azurerm v3.85.0 (signed by HashiCorp)
• Installing hashicorp/azuread v2.47.0...
• Installed hashicorp/azuread v2.47.0 (signed by HashiCorp)

The code:

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version =  ">=3.69.0"
      # version =  "3.70.0"
    }
    azuread = {
     source  = "hashicorp/azuread"
     version = ">=2.6.0"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = ">=1.12.0"
    }
  }
   backend "azurerm" {
 }
}

provider "azurerm" {
  features { 
    resource_group {
      prevent_deletion_if_contains_resources = false
      }
  }
}

data "azurerm_client_config" "current" {}
data "azuread_client_config" "current" {}
data "azuread_service_principal" "service_principal" {
  client_id = data.azuread_client_config.current.client_id
}

resource "azurerm_resource_group" "rg" {
  name     = "rg-dev-demo-app-01"
  location = "north europe"
}


resource "azurerm_postgresql_flexible_server" "pgsql" {
  name                         = "psql-dev-we-demo"
  location                     = "north europe"
  resource_group_name          = azurerm_resource_group.rg.name
  sku_name                     = "B_Standard_B1ms"
  version                      = "13"
  storage_mb                   = "32768"

  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" "administrators" {
  server_name         = azurerm_postgresql_flexible_server.pgsql.name
  resource_group_name = "rg-dev-demo-app-01"
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = data.azuread_service_principal.service_principal.object_id
  principal_name      = data.azuread_service_principal.service_principal.display_name
  principal_type      = "ServicePrincipal"
}

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

resource "postgresql_role" "readonly" {                                                                                                                                               
  name = "readonly"                                                                                                                                                                          
}

resource "postgresql_grant" "readonly_public" {                                                                                                                                      
  database    = "demodbwhatever"                                                                                                                                                               
  role        = postgresql_role.readonly.name                                                                                                                                                
  schema      = "public"                                                                                                                                                                     
  object_type = "table"                                                                                                                                                                      
  privileges  = ["SELECT"]   

[djr747]

@Marco10101 by any chance are you using a User Assigned Managed Identity (UAMI) on a VM in Azure for authentication with Entra Auth? We have the same issue with our Terraform agents and when we looked deeper it was related to the Azure Go SDK and how it handles trying to figure out the client_id. A VM can have multiple UAMIs but there is no property to set a default one so the SDK gives the error ManagedIdentityCredential: no default identity is assigned to this resource since it doesn't know which one to use.

It would be good if the provider had an azure_msi_client_id property added in the configuration so you could declare the UAMI that you want the provider to use. While you can set an environment variable AZURE_CLIENT_ID this then impacts all providers that could use the Azure Go SDK for authentication and we use different UAMIs for different access scopes on resources.

This is the function that would require the additional input and check to use NewManagedIdentityCredential instead of NewDefaultAzureCredential.
https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/provider.go#L269-L283

Here are the SDK details on using NewManagedIdentityCredential.
https://pkg.go.dev/github.com/Azure/azure-sdk-for-go/sdk/azidentity#NewManagedIdentityCredential

[adippel]

Can confirm @djr747 description and workaround when using a container, that has several UAMIs assigned, running in Azure Container App Environment. Using the env variables worked for us but adding a new provider parameter and some logic as suggested would be way more clean and less intrusive to other providers.

[the-redback]

While testing AAD, The following command is not working for me, (got it from here)

export PGPASSWORD=`curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net&client_id=CLIENT_ID' -H Metadata:true | jq -r .access_token`

It gives me error:

{"error":"invalid_request","error_description":"Identity not found"}/ #

But, the following works for me,

export PGPASSWORD="$(az account get-access-token --resource https://ossrdbms-aad.database.windows.net --query accessToken --output tsv)" 

As the error message is same in this provider, I think updating the endpoint should fix this issue. Unfortunately, we are blocked on this. :(