Azure PostgreSQL, Entra ID Authentication and .NET

[Guest aaronpowell]

I'm currently working on a project in which we are using Entra ID rather than a traditional Postgre username and password. This is a great way to secure your database and ensure that only the right people have access to it.

 

Note: For the purpose of this article, I'm going to use Entra ID to refer to a user identity, as well as a managed identity such as a service principal, as the approach is the same in this context here.

 

The above linked documentation covers how you would setup the Azure resource with Entra ID as the authentication mode, so I won't go over that here (also, you can configure that when you initial create the database, or using a Bicep script), instead I want to look at how we use that in a .NET application, because when you're connecting using Entra ID you don't have a password to use, or at least not in the traditional sense.

 

For this, I'm going to use the Npgql library, which is the most popular PostgreSQL driver for .NET. It's a great library and has a lot of features, and integrates nicely with Entity Framework Core and .NET Aspire.

 

[HEADING=1]What makes connecting different[/HEADING]

 

Before we look at the how of connecting, we need to understand why this is a little different to using a username/password approach. When working with a PostgreSQL database that uses a username/password, you would have a connection string that looks like this:

 

 

 

[iCODE]Server=myServerAddress;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;[/iCODE]

 

 

 

But when connecting using Entra ID, it looks like this:

 

 

 

[iCODE]Server=server-name.postgres.database.azure.com;Database=postgres;Port=5432;Username=<Entra ID>;Ssl Mode=Require;[/iCODE]

 

 

 

Notice how there is no [iCODE]Password[/iCODE] field in the connection string. This is because when you connect using Entra ID, you don't have a password to use. Instead, you need to use a token that is generated by Entra.

 

[HEADING=1]Generating a token[/HEADING]

 

When you connect to the database using Entra ID, you need to request an access token from Entra that you can use to authenticate. You can see this in action using the Azure CLI:

 

 

 

[iCODE]az account get-access-token --resource-type oss-rdbms[/iCODE]

 

 

 

Which returns something like this:

 

 

 

{
 "accessToken": "<nope!>",
 "expiresOn": "2024-05-31 17:52:59.000000",
 "expires_on": 1717141979,
 "subscription": "<nope!>",
 "tenant": "<nope!>",
 "tokenType": "Bearer"
}

 

 

 

If you extract the [iCODE]accessToken[/iCODE] from the JSON you can then plug that into the connection string for PostgreSQL in the [iCODE]Password[/iCODE] argument and you're good to go.

 

But it's not really practical to be running the Azure CLI every time you want to connect to the database, especially since this token is only short lived (you can see the expiry date in the JSON above). Instead, we're going to want to do this in .NET, and for that we'll use the [iCODE]Azure.Identity[/iCODE] NuGet package.

 

[HEADING=1]Using Azure.Identity[/HEADING]

 

[iCODE]Azure.Identity[/iCODE] is a library that provides a way to authenticate with Azure services using the Azure SDK, and it contains a class called [iCODE]DefaultAzureCredential[/iCODE] that can be used to authenticate. This class is actually a roll-up of a number of different authentication sources, such as Managed Identity, as well as the Azure CLI, Visual Studio, and a bunch of other sources (check out the docs to see all the sources).

 

To use [iCODE]DefaultAzureCredential[/iCODE] you need to install the [iCODE]Azure.Identity[/iCODE] NuGet package:

 

 

 

[iCODE]dotnet add package Azure.Identity[/iCODE]

 

 

 

Then you can use it in your code like this:

 

 

 

using Azure.Identity;

var credential = new DefaultAzureCredential();
var ctx = new TokenRequestContext(["https://ossrdbms-aad.database.windows.net/.default"]);
var tokenResponse = await credential.GetTokenAsync(ctx);
Console.WriteLine(tokenResponse.Token);

 

 

 

The important part here is that we're providing a specific scope to the [iCODE]TokenRequestContext[/iCODE] of [iCODE]https://ossrdbms-aad.database.windows.net/.default[/iCODE], which grants access to the Azure PostgreSQL Flexible Server. It's what is being done with the [iCODE]az account get-access-token[/iCODE] call and the [iCODE]--resource-type oss-rdbms[/iCODE] argument. With this in C# though, we're able to get the token and then use that to connect to the database.

 

[HEADING=1]Handling Token Expiry[/HEADING]

 

One thing to note is that the token that is returned by [iCODE]DefaultAzureCredential[/iCODE] is short lived, and will expire after a certain amount of time (24 hours service principal, 4 hours for a user token). This is fine for, say, a console app that is only running for a short period of time, but this becomes a problem if you're using the connection string in something that is long running, like a web app, since the [iCODE]NpgsqlDataSourceBuilder[/iCODE], the type that is used to build the connection string, should be a singleton.

 

Thankfully, the authors of Npgsql have given us an approach to handling token refreshes in the box using a Periodic Password Provider. With this feature, we can provide a callback function to be run that will retrieve the password when a connection is opened, and then cache that password for a certain amount of time. This means that we can use the [iCODE]DefaultAzureCredential[/iCODE] to get the token, and then use that token to connect to the database.

 

 

 

NpgsqlDataSourceBuilder dataSourceBuilder = new(builder.Configuration.GetConnectionString("Database"));

dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
{
   DefaultAzureCredential credential = new();
   TokenRequestContext ctx = new(["https://ossrdbms-aad.database.windows.net/.default"]);
   AccessToken tokenResponse = await credential.GetTokenAsync(ctx, ct);
   return tokenResponse.Token;
}, TimeSpan.FromHours(4), TimeSpan.FromSeconds(10));

 

 

 

On the [iCODE]dataSourceBuilder[/iCODE] we call the [iCODE]UsePeriodicPasswordProvider[/iCODE] method, passing in a callback function that will get the token, and then two [iCODE]TimeSpan[/iCODE] objects that represent the refresh period and the failure refresh period. The refresh period is how often the token will be refreshed, and the failure refresh period is how long to wait before trying to refresh the token again if the token retrieval fails.

 

[HEADING=1]Connecting it all up[/HEADING]

 

Now that we know how we can retrieve a token to act as the password for our connections, let's look at how to connect it all up for a local dev or Azure deployed app:

 

 

 

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
var connStr = builder.Configuration.GetConnectionString("db");
NpgsqlConnectionStringBuilder csb = new(connStr);

if (!string.IsNullOrEmpty(csb.Password))
{
   builder.AddNpgsqlDataSource("db");
}
else
{
   builder.AddNpgsqlDataSource("db", dataSourceBuilder =>
   {
       dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
       {
           DefaultAzureCredential credential = new();
           TokenRequestContext ctx = new(["https://ossrdbms-aad.database.windows.net/.default"]);
           AccessToken tokenResponse = await credential.GetTokenAsync(ctx, ct);
           return tokenResponse.Token;
       }, TimeSpan.FromHours(4), TimeSpan.FromSeconds(10));
   });
}
// and the rest of your app code

 

 

 

Here we're getting the connection string and creating a [iCODE]NpgsqlConnectionStringBuilder[/iCODE] from it so that it gets parsed for us. If the connection string we have has a password, then we can just use that as normal, but if it doesn't have a password, then we can use the [iCODE]UsePeriodicPasswordProvider[/iCODE] method to get the token and use that as the password.

 

This means we can run locally against a database that uses username/password style access (since we don't have Entra ID locally), and then deploy to Azure and use Entra ID without having to change the code.

 

[HEADING=1]Conclusion[/HEADING]

 

When porting an app that uses PostgreSQL to using Managed Identity I was expecting that it would be quite a lot of work to manage the token retrieval and expiry, initially I thought that it'd require doing a bunch of work to discard the singleton for the [iCODE]NpgsqlDataSourceBuilder[/iCODE] and then recreate it when the token expired. But thanks to the [iCODE]UsePeriodicPasswordProvider[/iCODE] method, it's actually quite easy to manage the token retrieval and expiry, and it's all handled for you.

 

Continue reading...