I tried the same github terraform which you added in my environment, with the authentication type as "Secret" and received the same error as shown:

As mentioned here, there appears to be a problem with the "secret authentication type" from the Terraform provider API. As a result, I updated it as follows and it worked for me successfully:

authentication {
    type   = "systemAssignedIdentity"
  }

main.tf:

terraform {
  required_providers {
    azurerm = {
      source = "hashicorp/azurerm"
      version = "3.35.0"
    }
  }
}
provider "azurerm" {
    features {
      resource_group {
        prevent_deletion_if_contains_resources = false
      }
   }
}
resource "azurerm_resource_group" "main" {
  name     = "xxxxRG"
  location = "xxxxx"
}
resource "azurerm_postgresql_server" "main" {
  name                             = "xxxxxserver"
  resource_group_name              = azurerm_resource_group.main.name
  location                         = azurerm_resource_group.main.location
  administrator_login              = "xxxxxuser"
  administrator_login_password     = "<Password of server>"
  backup_retention_days            = 7 // As per your requirements
  sku_name                         = "GP_Gen5_2"
  version                          = "11"
  storage_mb                       = 5120
  public_network_access_enabled    = false
  ssl_enforcement_enabled          = true
  ssl_minimal_tls_version_enforced = "TLS1_2"
}
resource "azurerm_postgresql_database" "main" {
  name                = "xxxxdb"
  server_name         = azurerm_postgresql_server.main.name
  resource_group_name = azurerm_postgresql_server.main.resource_group_name
  charset             = "utf8"
  collation           = "und-x-icu"
}
resource "azurerm_service_plan" "main" {
  name                = "xxxxxserviceplan"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  sku_name            = "P1v2"
  os_type             = "Linux"
}
resource "azurerm_linux_web_app" "main" {
  name                = "xxxxxwebapp"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  service_plan_id     = azurerm_service_plan.main.id
  site_config {}
}
resource "azurerm_app_service_connection" "main" {
  name               = "xxxxxserviceconnector"
  app_service_id     = azurerm_linux_web_app.main.id
  target_resource_id = azurerm_postgresql_database.main.id
  client_type        = "springBoot"
  authentication {
    type   = "systemAssignedIdentity"
  }
}

Output:-

Executed terraform init

Executed terraform plan:

Executed terraform apply:

Alternatively, If the requirement is only with the secret authentication type in your environment then you can create an app service connection by calling API (JSON request).