Issues with Aspire, PostgreSQL and Azure Postgres Flexible server

[kyurkchyan]

Issue 1 - Ef Core and managed identity integration

When you read the documentation of the EF Core integration of Azure Flexible PostgreSQL and managed identities you find a snipped like this

builder.AddNpgsqlDbContext<YourDbContext>(
    "postgresdb", 
    configureDataSourceBuilder: (dataSourceBuilder) =>
{
    if (!string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
    {
        return;
    }

    dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
    {
        var credentials = new DefaultAzureCredential();
        var token = await credentials.GetTokenAsync(
            new TokenRequestContext([
                "https://ossrdbms-aad.database.windows.net/.default"
            ]), ct);

        return token.Token;
    },
    TimeSpan.FromHours(24),
    TimeSpan.FromSeconds(10));
});

AddNpgsqlDbContext doesn't not have a parameter named configureDataSourceBuilder.

The other parameters that it does have do not have neither the ConnectionStringBuilder nor UsePeriodicPasswordProvider. Those are available on the NpgsqlDataSourceBuilder class which is not exposed through the AddNpgsqlDbContext
Thus, I am not sure how to configure PostgreSQL with Ef Core and default azure credentials.

I believe this snipped was copy-pasted from the non-ef core postgresql setup from here

builder.AddNpgsqlDataSource(
    "postgresdb", 
    configureDataSourceBuilder: (dataSourceBuilder) =>
{
    if (!string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
    {
        return;
    }

    dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
    {
        var credentials = new DefaultAzureCredential();
        var token = await credentials.GetTokenAsync(
            new TokenRequestContext([
                "https://ossrdbms-aad.database.windows.net/.default"
            ]), ct);

        return token.Token;
    },
    TimeSpan.FromHours(24),
    TimeSpan.FromSeconds(10));
});

This code does compile already. Whether it works or no, I don't know as I have ef-core setup.

So, is there a way I could re-use the created by the AddNpgsqlDataSource to create the db context?

Issue 2 - how to configure azure postgres flexible server with local development environment

I have the following setup in my app host

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
                                                                        .RunAsContainer(configure =>
                                                                        {
                                                                            configure
                                                                                .WithDataBindMount("./.local_data", isReadOnly:false)
                                                                                .WithPgAdmin();
                                                                        });

var db = postgres.AddDatabase("MyDb");

Most of the time this is exactly what you need

1. Run a postgres container when working locally
2. Use deployed azure postgres flexible server when deployed

However, there are cases where you want to run the local project against the real deployed postgres server. For instance, I can't figure out how to setup the managed identity and I need to run against flexible server from debug console to iterate and understand what's going on. There are plenty of other valid reasons why you would want something like this.

Here's the issue, when I remove RunAsContainer as soon as I start the app I see this

This issue is easy to fix, I simply need to add Azure section to the configurations.

Once I do that, instead of the app using the deployed version of the postgres database, it starts deploying a new version.

I've tried manually setting ConnectionStrings:MyDb in configuration and point it to the real thing, but It still deploys a new instance of the server.

What am I doing wrong? Is this intended behavior?

[kyurkchyan]

Issue 1 updates

I've tried doing something like this

hostBuilder.AddNpgsqlDataSource("BibleLinesDb",
                                        configureDataSourceBuilder:dataSourceBuilder =>
                                        {
                                            Console.WriteLine("Configuring data source builder");
                                            if (!string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
                                            {
                                                Console.WriteLine("Password is already set, returning");
                                                return;
                                            }

                                            Console.WriteLine("Setting up periodic password provider");
                                            dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
                                                                                          {
                                                                                              Console.WriteLine("Getting token");
                                                                                              var credentials = DefaultAzureCredentialFactory.Create(hostBuilder.Configuration);
                                                                                              var token = await credentials.GetTokenAsync(new TokenRequestContext([
                                                                                                                                              "https://ossrdbms-aad.database.windows.net/.default"
                                                                                                                                          ]),
                                                                                                                                          ct);

                                                                                              Console.WriteLine($"Returning token {token.Token}");
                                                                                              return token.Token;
                                                                                          },
                                                                                          TimeSpan.FromHours(24),
                                                                                          TimeSpan.FromSeconds(10));
                                        });

        hostBuilder.Services.AddDbContextPool<BibleLinesDbContext>((serviceProvider, dbContextOptionsBuilder) =>
        {
            var dataSource = serviceProvider.GetRequiredService<NpgsqlDataSource>();

            dbContextOptionsBuilder.UseNpgsql(dataSource,
                                              builder =>
                                              {
                                                  builder.EnableRetryOnFailure();
                                              });
        });

Essentially, I use none ef core variant of setting up the postgres and then manually setup Db Context Pool and use the DataSource that the first step generated.

After doing so, and running against azure postgres SQL server locally, I managed to connect to the database through the generated token. However, when I deploy this to Azure I started getting the following error

Npgsql.NpgsqlException (0x80004005): No password has been provided but the backend requires one (in cleartext)

After looking for this error in issues I've found a very similar topic.

Now I am not sure why the token generation works locally but does not work when deployed to azure container apps.

Issue 2 updates

I think I know why the issue number 2 happen. Instead of using default aspire deployment I used azd infra synth and overridden the names of the resources slightly. I like to use company-region-environment-resource style naming, e.g. bl-dev-eu-postgres. And that's what I modified in my bicep file.

It seems when you run locally, the automatic provisioning kicks in and since it generates a bicep file with the default setup and doesn't know about my own overrides, it won't find the postgres DB that it expects and will create a new one.

I wish there was a way to manually override the names of the resources that aspire creates in such a way that it would be the same both inside the custom bicep files and the ones that aspire generates automatically. Is there any way this problem can be solved?

[davidfowl]

cc @IEvangelist @eerhardt

[kyurkchyan]

Thanks @davidfowl . I actually created another issue #2329 in aspire-docs, that wouldn't go so technical and would simply explain the gap between docs and reality. And this one was originally created in the Aspire repo to go deeper technical. But I guess we can merge them together into this one.

I will close the other one as duplicate.

[davidfowl]

There are some updates to the docs that explain how azure resources work that might answer some of the questions you have https://learn.microsoft.com/en-us/dotnet/aspire/azure/integrations-overview?tabs=dotnet-cli

[kyurkchyan]

@davidfowl I didn't have a chance yet to view the docs you've shared. I will definitely dig into them.

Today I decided to delete the manually overriden infra, delete the deployments and deploy everything from scratch using the default generated bicep files without manual overrides. It worked! I am not sure what I was doing wrong. Perhaps it was a stale state of deployment, perhaps it was something wrong in the deployment script.

I realized, for the project that I am building with Aspire, being a personal project, can live with the default generated random resource names, considering it will make my life much easier, result in less maintenance work when I need to re-generate infra.

For anybody that'll face similar issue here's the db context configuration that worked for me

• AppHost Program.cs

IResourceBuilder<AzurePostgresFlexibleServerResource> postgres = builder.AddAzurePostgresFlexibleServer("postgres")
                                                                        .RunAsContainer(configure =>
                                                                        {
                                                                            configure
                                                                                .WithDataBindMount("./.local_data", isReadOnly:false)
                                                                                .WithPgAdmin();
                                                                        });

IResourceBuilder<AzurePostgresFlexibleServerDatabaseResource> bibleLinesDb = postgres.AddDatabase("BibleLinesDb");

IResourceBuilder<ProjectResource> migrations = builder.AddProject<BibleLines_Backend_MigrationService>("migrations")
                                                      .WithReference(bibleLinesDb)
                                                      .WaitFor(bibleLinesDb);

• Client project can use the following DI extension

public static class DataAccessDependencyInjection
{
    public static IHostApplicationBuilder AddPostgres(this IHostApplicationBuilder hostBuilder)
    {
        hostBuilder.AddNpgsqlDataSource("BibleLinesDb",
                                        configureDataSourceBuilder:dataSourceBuilder =>
                                        {
                                            if (!string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
                                            {
                                                return;
                                            }
                                            
                                            dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
                                                                                          {
                                                                                              var credentials = DefaultAzureCredentialFactory.Create(hostBuilder.Configuration);
                                                                                              var token = await credentials.GetTokenAsync(new TokenRequestContext([
                                                                                                                                              "https://ossrdbms-aad.database.windows.net/.default"
                                                                                                                                          ]),
                                                                                                                                          ct);
                                                                                              
                                                                                              return token.Token;
                                                                                          },
                                                                                          TimeSpan.FromHours(24),
                                                                                          TimeSpan.FromSeconds(10));
                                        });

        hostBuilder.Services.AddDbContextPool<BibleLinesDbContext>((serviceProvider, dbContextOptionsBuilder) =>
        {
            var dataSource = serviceProvider.GetRequiredService<NpgsqlDataSource>();

            dbContextOptionsBuilder.UseNpgsql(dataSource,
                                              builder =>
                                              {
                                                  builder.EnableRetryOnFailure();
                                              });
        });

        return hostBuilder;
    }
}

This implementation has a caveat though. It doesn't use the aspire extensions for configuration that the built-in hostBuilder.AddNpgsqlDbContext<T> does with settings and options builder parameters. But it works. I assume, that extension should be fixed to support manual configuration of the NpgsqlDataSourceBuilder.

As for running the app locally with overriden bicep files, that question probably will be answered once I read the docs and I will update this thread once I do that. I simply want to share positive progress ASAP on the first issue.

[kyurkchyan]

@davidfowl I've read through the azure integration documentation. It was indeed very insightful. It seems I can override many of the aspects of the explicit infrastructure deployments I create such as app insights, postgres, storage and so on. However, I couldn't figure out how I would be able to override the names of the implicitly created infrastructure resources. Specifically, when you deploy the application using azd up here are some of the resources that are created implictly

1. Resource group
2. Container app environment
3. Container registry
4. Managed identity
5. Log analytics workspace

Is there a way to control the properties of these resources through azure provisioning APIs without having to manually override the bicep deployment templates?

It's not a deal-breaker TBH, but it'd be really cool to have such capabilities.

[davidfowl]

You can’t change any of those from C# today. When we move the shared infrastructure into the apphost that will be possible. Today you would need to run “azd infra synth” and manually change names

[kyurkchyan]

Awesome. I am totally good with that :) The good thing is that those won't be re-deployed when using local provisioning, and the local provisioning issue I was facing is resolved by overring the names of postgress and app insights through azure provisioning APIs.

As for this issue in general, I still think we should leave it open for the sake of resolving the discrepancies between the documentation and reality of postgres configuration. Though, the good thing is there's a workaround that I've mentioned earlier.

Thanks a lot @davidfowl , you really helped me out towards the right direction.

[davidfowl]

Maybe also worth reading dotnet/aspire#6484

[kyurkchyan]

Thanks! I saw that one. Though, in my case I deliberately chose to do the configuration inside the ConfigureInfrastructure to be more explicit and not to do switch/case.

[kyurkchyan]

Another update about the workaround that I used.

Unfortunately, when using that workaround EF Core migrations will be broken.

When executing migrations like this

 dotnet ef migrations add Migration --project Infrastructure/Infrastructure.csproj --startup-project Api/Api.csproj --context DbContext --output-dir "Common/DataAccess/Postgres/Migrations"

I get this output

Build started...
Build succeeded.
The exception 'ConnectionString is missing. It should be provided in 'ConnectionStrings:PostgresDb' or under the 'ConnectionString' key in 'Aspire:Npgsql' configuration section.' was thrown while attempting to find 'DbContext' types. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

If I replace my workaround with this

hostBuilder.AddNpgsqlDbContext<DbContext>("PostgresDb");

Migrations will work, but the app won't due to the fact that token is no longer being generated.

For the time being, as a workaround inside my Api/appsettings.Development.json I've added the connection string manually

"ConnectionStrings": {
    "PostgresDb": "Host=postgres.postgres.database.azure.com;Database=PostgresDb"
  }