EF Core + Azure PostgreSQL Managed Identity documentation?

[felinepc]

Ask a question

Documentations and related discussions exist for EF Core + Azure SQL with Azure Managed Identity.

But I cannot find anything for Azure PostgreSQL, which also supports managed identity, for use with EF Core.

It would obviously be in Microsoft's and everyone's best interest to make use of managed identity whenever possible when everything's hosted in Azure.

Is this scenario supported? If so, can you please kindly provide documentation?

Thanks.

[roji]

@felinepc as far as I know (and as far as I can see from the docs), there's nothing special needed for using Azure Managed Identity with PostgreSQL or Npgsql - you just use the Azure APIs to fetch the access token, and then specify that as the password in the connection string, as usual. It's your responsibility to rotate the the access token frequently enough so it doesn't expire.

Are you seeing anything else that would require specific documentation?

[felinepc]

@roji I was referring to a more robust and managed way, like the way EF Core handles Azure SQL with Managed Identity, which involves:

1. A Microsoft.Azure.Services.AppAuthentication package that provides a AzureServiceTokenProvider that takes care of both token caching and token renewal.

2. The connection string no longer needs to include password or token. Instead we simply had to specify

var connection = (SqlConnection)Database.GetDbConnection();
connection.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

I wonder if something like exists for PostgreSQL.

[felinepc]

Just realized for PostgreSQL we can get token using the same AzureServiceTokenProvider, but with https://ossrdbms-aad.database.windows.net as the endpoint.

Then we can just replace the "password" portion of the connection string with the token.

Final working code for those who need a quick example:

 public void ConfigureServices(IServiceCollection services)
 {
            services.AddSingleton<AzureServiceTokenProvider>();
         
            services.AddDbContext<MyDbContext>((sp, options) =>
            {
                var tokenProvider = sp.GetRequiredService<AzureServiceTokenProvider>();
                var config = sp.GetRequiredService<IConfiguration>();

                var token = tokenProvider.GetAccessTokenAsync("https://ossrdbms-aad.database.windows.net", "your-aad-tenant-id-if-necessary").Result;
                var connectionString = config.GetConnectionString("name");

                connectionString = connectionString.Replace("[Password]", token);
                options.UseNpgsql(connectionString);
            });
}

The only drawback of this method is that the token retrieval call is synchronous. But per David Browne at Microsoft, it should not be a big deal because AzureServiceTokenProvider actually caches the token. So the block should only occur when token is (automatically) renewed due to expiration.

[roji]

@felinepc note that the AccessToken is a property of SqlConnection, so this is a SqlClient feature rather than anything in EF Core. Since for PostgreSQL the managed token is simply passed in as a password, I don't see what Npgsql would add here - you can already assign the managed token to NpgsqlConnection.Password if you want.