Consider building an Azure Postgres client integration

mitchdenny commented 1 week ago

Is there an existing issue for this?

[x] I have searched the existing issues

Describe the bug

Using the WaitForSandbox.AppHost with the following code (just replace Program.cs):

// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.

var builder = DistributedApplication.CreateBuilder(args);

var db = builder.AddAzurePostgresFlexibleServer("pg")
                .AddDatabase("db");

var dbsetup = builder.AddProject<Projects.WaitForSandbox_DbSetup>("dbsetup")
                     .WithReference(db).WaitFor(db);

var backend = builder.AddProject<Projects.WaitForSandbox_ApiService>("api")
                     .WithExternalHttpEndpoints()
                     .WithHttpHealthCheck("/health")
                     .WithReference(db).WaitFor(db)
                     .WaitForCompletion(dbsetup)
                     .WithReplicas(2);

builder.AddProject<Projects.WaitFor_Frontend>("frontend")
       .WithReference(backend).WaitFor(backend);

#if !SKIP_DASHBOARD_REFERENCE
// This project is only added in playground projects to support development/debugging
// of the dashboard. It is not required in end developer code. Comment out this code
// or build with `/p:SkipDashboardReference=true`, to test end developer
// dashboard launch experience, Refer to Directory.Build.props for the path to
// the dashboard binary (defaults to the Aspire.Dashboard bin output in the
// artifacts dir).
builder.AddProject<Projects.Aspire_Dashboard>(KnownResourceNames.AspireDashboard);
#endif

builder.Build().Run();

The main change is that instead of using a local container I want to use the Azure Provisioner. What I'm seeing is that when I launch the app host, the dbsetup program returns an error when trying to connect to the database:

2024-10-09T20:44:28
 Unhandled exception. Npgsql.NpgsqlException (0x80004005): No password has been provided but the backend requires one (in cleartext)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.AuthenticateCleartext(String username, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.Authenticate(String username, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|213_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.UnpooledDataSource.Get(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|42_0(Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
2024-10-09T20:44:28
    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreatedAsync(CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreatedAsync(CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Program.<Main>$(String[] args) in C:\Code\aspire\playground\waitfor\WaitForSandbox.DbSetup\Program.cs:line 12
2024-10-09T20:44:28
    at Program.<Main>(String[] args)

If I look at the connection string this is what I see (with redactions):

Host=[resourcenameredacted].postgres.database.azure.com;Username=[myalias];Database=db

Obviously the password is missing - but this should be using token auth now right so that shouldn't be a problem?

Expected Behavior

Using Azure Provisioner I should be able to create an Azure Postgres Flexible Server and connect to it using a token credential.

Steps To Reproduce

See above.

Exceptions (if any)

2024-10-09T20:44:28
 Unhandled exception. Npgsql.NpgsqlException (0x80004005): No password has been provided but the backend requires one (in cleartext)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.AuthenticateCleartext(String username, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.Authenticate(String username, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|213_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
2024-10-09T20:44:28
    at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.UnpooledDataSource.Get(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|42_0(Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
2024-10-09T20:44:28
    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreatedAsync(CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreatedAsync(CancellationToken cancellationToken)
2024-10-09T20:44:28
    at Program.<Main>$(String[] args) in C:\Code\aspire\playground\waitfor\WaitForSandbox.DbSetup\Program.cs:line 12
2024-10-09T20:44:28
    at Program.<Main>(String[] args)

.NET Version info

No response

Anything else?

No response

davidfowl commented 1 week ago

I think this is by design. You need to change app code to make managed identity work with npgsql

eerhardt commented 1 week ago

From the docs on AddAzurePostgresFlexibleServer

https://github.com/dotnet/aspire/blob/87827cf4ee5a2cc4e693f6bd962484eea525c36f/src/Aspire.Hosting.Azure.PostgreSQL/AzurePostgresExtensions.cs#L163-L169

mitchdenny commented 1 week ago

I wonder if we should augment the connection string so that we can do this automatically for folks.

davidfowl commented 1 week ago

I think there’s definitely more work to do here. I’m not sure what that work is yet though. The only thing that makes this seamless is a client integration. I think we will end up there.

eerhardt commented 1 week ago

@tg-msft has an idea on how we can make an abstraction of the Azure SDK's TokenCredential - see ClientModel: Add cloud-agnostics OAuth credential to System.ClientModel (Azure/azure-sdk-for-net#42852).

You would still need to change our app code, but it would be much simpler:

Npgsql would take a dependency on this "cloud-agnostic OAuth credential" type
Your app references Azure.Identity (chances are you already are if you are doing Azure stuff)
You write one line of code to set the npgsql connection's "TokenCredential" to new DefaultAzureCredential().

cc @annelo-msft

davidfowl commented 1 week ago

I still think we need to make a client integration for azure postgres and redis. Now that we have this first class managed identity support, it's clear this is a rough edge that every customer will need to figure out. What makes it harder with aspire is that local dev will work, and deployment will fail. On top of that, if you do change the application to support managed identity, then running as a container will fail.

The client integration should seamlessly handle the shift from local dev to deployed in each situation.

aaronpowell commented 4 days ago

See also https://devblogs.microsoft.com/dotnet/using-postgre-sql-with-dotnet-and-entra-id/

I recognise that blog 😉.

Enabling managed identity on Azure Postgres is a little tricky as you have to initially provision the resource with a username/password and then run a SQL script to configure the managed identity access on the database (which then would allow you to delete the default admin account). This isn't something that would be easy to do in Aspire as it'd require running some custom SQL scripts via Bicep post deployment. We have all that in https://github.com/microsoft/azure-openai-service-proxy/ if you need a reference.

davidfowl commented 4 days ago

This isn't something that would be easy to do in Aspire as it'd require running some custom SQL scripts via Bicep post deployment.

Deployment script 😄

eerhardt commented 4 days ago

Enabling managed identity on Azure Postgres is a little tricky as you have to initially provision the resource with a username/password and then run a SQL script to configure the managed identity access on the database (which then would allow you to delete the default admin account).

The Aspire.Hosting.Azure.PostgreSQL library has been updated to enable Entra ID by default in .NET Aspire 9. It is much easier to do with the latest Azure bits. See

https://github.com/dotnet/aspire/blob/ba4cc8b9736719f1589146e09a75198dfd6dfcdf/playground/cdk/CdkSample.AppHost/Program.cs#L51-L52

Results in bicep:

https://github.com/dotnet/aspire/blob/ba4cc8b9736719f1589146e09a75198dfd6dfcdf/playground/cdk/CdkSample.AppHost/pgsql2.module.bicep#L13-L16

and https://github.com/dotnet/aspire/blob/ba4cc8b9736719f1589146e09a75198dfd6dfcdf/playground/cdk/CdkSample.AppHost/pgsql2.module.bicep#L54-L65

aaronpowell commented 3 days ago

This isn't something that would be easy to do in Aspire as it'd require running some custom SQL scripts via Bicep post deployment.

Deployment script 😄

Yes, that's what's needed to be done - https://github.com/microsoft/azure-openai-service-proxy/blob/main/infra/db-seed.bicep

Enabling managed identity on Azure Postgres is a little tricky as you have to initially provision the resource with a username/password and then run a SQL script to configure the managed identity access on the database (which then would allow you to delete the default admin account).

The Aspire.Hosting.Azure.PostgreSQL library has been updated to enable Entra ID by default in .NET Aspire 9. It is much easier to do with the latest Azure bits.

Does that configure the permissions on the databases that are deployed as well? We found we had to do this https://github.com/microsoft/azure-openai-service-proxy/blob/main/database/setup.sql to support it when building out the AI Proxy to configure the permissions correctly, otherwise while Entra was enabled it wasn't actually the right permissions.

eerhardt commented 2 days ago

Does that configure the permissions on the databases that are deployed as well?

If you create the database with Aspire, yes. At least it works to query and insert to the database in the apps that I have made with it.

We found we had to do this https://github.com/microsoft/azure-openai-service-proxy/blob/main/database/setup.sql to support it

That script is creating the database on line 4. With Aspire, the database is created in the same bicep as the server.

dotnet / aspire