BUG: postgres flexible server crates databases with azuresu user

simon-eon commented 2 weeks ago

When using Entra ID Authentication for a flexible server and there is no admin user created yet after creating the server, a database get's created with "azuresu" role. In that szenario the database is useless, since no other permissions can be granted on the database. When the user and the database is created in the same pipeline, it is random which role will be owner of the database.

To prevent this, databases should never be created with "azuresu" as the owner. One szenario would be, that the api allows to set a database user.

For now the documentation for the azurerm_postgresql_flexible_server_database should be adapted, to warn about this behavior and provide a workaround.

Is there an existing issue for this?

[X] I have searched the existing issues

Community Note

Please vote on this issue by adding a :thumbsup: reaction to the original issue to help the community and maintainers prioritize this request
Please do not leave comments along the lines of "+1", "me too" or "any updates", 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.9.7

AzureRM Provider Version

v4.8.0

Affected Resource(s)/Data Source(s)

azurerm_postgresql_flexible_server_database

Terraform Configuration Files

Problem:

resource "azurerm_postgresql_flexible_server" "this" {
  authentication {

    active_directory_auth_enabled = true
    password_auth_enabled         = false # Using this will prevent the azure ad managed identity from being the owner of databases, which locks us out of the database.
    tenant_id                     = "xxx"
  }
}

resource "azurerm_postgresql_flexible_server_database" "db" {
  name      = local.db_name
  server_id = azurerm_postgresql_flexible_server.this.id
}

Workaround:

resource "azurerm_user_assigned_identity" "this" {
  location            = "westeurope"
  name                = "DB-ADMIN"
  resource_group_name = data.azurerm_resource_group.this.name
}

resource "azurerm_postgresql_flexible_server_active_directory_administrator" "admin-managed-identity" {
  object_id           = database_admin_managed_identity.principal_id
  principal_name      = "DB-Admin"
  principal_type      = "ServicePrincipal"
  resource_group_name = data.azurerm_resource_group.this.name
  server_name         = azurerm_postgresql_flexible_server.this.name
  tenant_id           = "XXX

}

resource "azurerm_postgresql_flexible_server_database" "db" {
  name      = local.db_name
  server_id = azurerm_postgresql_flexible_server.this.id
  depends_on = [ azurerm_postgresql_flexible_server_active_directory_administrator.admin-managed-identity ] # make sure the admin is created before the database, so azure is not locking us out of the database.
}

Debug Output/Panic Output

Expected Behaviour

A useless Database is never created. (Owner = azuresu) If there is no Entra ID Admin or the current user is not an admin user in the database an error should occur. If there are more Entra ID Admin, and there is no owner name provied through the api, an error should occur.

Actual Behaviour

Database is created, but useless.

Steps to Reproduce

terraform apply (creating database on a recently created db server)

Important Factoids

No response

References

No response

neil-yechenwei commented 2 weeks ago

Thanks for raising this issue. It's by service API design. Terraform would always keep align with the behavior of service API. For more usage problem, suggest file Azure Support Ticket on Azure.

simon-eon commented 2 weeks ago

Hi Neil,

Can you please document the workflow for how this resource has to be used, in the different authentication szenarios / settings enabled on the server. That you have to figure this out your self is just frustrating. I spent a lot time to figure this out, because i was not expecting an api behaving like this.

So i expect you add to the docs something like this: "When password user is enabled, you need to use that user to manage the created database. It is discouraged to use password user for desaster recovery only." "When using Entra ID Authentication the first admin being added is the owner of all databases created with this resource" "Please make sure you add an administrator befor adding any database, because otherwise the databse would be useless".

Yes i already have support ticket open.

BR Simon

simon-eon commented 2 weeks ago

Update: Together with the Microsoft Support we figured out how to solve the specific problem:

ALWAYS Create the Server with Entra ID Authentication only and an Entra ID Adminstrator like discribed in the post above. Like that this Service Principal (which is a User Assigned Managed Identity) will be used for every database created through the API.
Now you can switch back to using both authentication methods. (Entra and Password).
User Assigned Managed Identity can be used to grant permissions to other users like the PW User or other Enrta Adminstrators.
Password User can be used for desaster recovery or migration service.
One can switch back to Entra Authentication only after doing a migration.

If you start by creating an the Server with both authentication methods, you will be forced to use that password user to initially grant permissions to other users for each database created through the API. So i would also recommend to not even create database through the API. The only reason i do this is, because i want to create server, admin, database and service connction with terraform in one go. And service connection needs the database to be created first.

hashicorp / terraform-provider-azurerm