Azure PostgreSQL should support managed identity

[eerhardt]

When provisioning a PostgreSQL database server today, we use an admin user name and password. This approach isn't as secure as using managed identity.

See

• https://azure.github.io/PSRule.Rules.Azure/en/rules/Azure.PostgreSQL.AAD/
• https://azure.github.io/PSRule.Rules.Azure/en/rules/Azure.PostgreSQL.AADOnly/

We should have an option for enabling managed identity / Entra ID when provisioning PostgreSQL database servers.

One could argue that we should do this by default, in the name of security. However, the problem is that the app connecting to this database is going to have to change to follow the steps in https://devblogs.microsoft.com/dotnet/using-postgre-sql-with-dotnet-and-entra-id/. So it would break apps when they published to Azure, if the app wasn't set up to work with Entra ID.

Proposal

Add a new enum that gets passed into .AsAzurePostgresFlexibleServer() that switches which kind of auth is used.

namespace Aspire.Hosting.Azure;

public enum AzurePostgresAuth
{
    Password = 0,
    ActiveDirectory = 1 // Note this is the name used in ARM, Bicep, CDK
}

// user code
var pgsqldb = builder.AddPostgres("pgsql")
    .AsAzurePostgresFlexibleServer(AzurePostgresAuth.ActiveDirectory)
    .AddDatabase("pgsqldb");

Alternative

We could add a new extension method .WithActiveDirectoryAuth() that disables the password based auth and switches the administrator to use managed identity.

var pgsqldb = builder.AddPostgres("pgsql")
    .AsAzurePostgresFlexibleServer()
    .WithActiveDirectoryAuth()
    .AddDatabase("pgsqldb");

The downfall of this is that it would need to hang off IResourceBuilder<PostgresServerResource> and if someone called it without calling AsAzurePostgresFlexibleServer first it wouldn't work well.

cc @mitchdenny @davidfowl @tg-msft @sebastienros @radical