A
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.
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:
But when connecting using Entra ID, it looks like this:
Notice how there is no
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:
Which returns something like this:
If you extract the
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
To use
Then you can use it in your code like this:
The important part here is that we're providing a specific scope to the
One thing to note is that the token that is returned by
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
On the
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:
Here we're getting the connection string and creating a
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.
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
Continue reading...
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.
What makes connecting different
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:
Server=myServerAddress;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;But when connecting using Entra ID, it looks like this:
Server=server-name.postgres.database.azure.com;Database=postgres;Port=5432;Username=<Entra ID>;Ssl Mode=Require;Notice how there is no
Password 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.Generating a token
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:
az account get-access-token --resource-type oss-rdbmsWhich returns something like this:
Code:
{
"accessToken": "<nope!>",
"expiresOn": "2024-05-31 17:52:59.000000",
"expires_on": 1717141979,
"subscription": "<nope!>",
"tenant": "<nope!>",
"tokenType": "Bearer"
}If you extract the
accessToken from the JSON you can then plug that into the connection string for PostgreSQL in the Password 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
Azure.Identity NuGet package.Using Azure.Identity
Azure.Identity is a library that provides a way to authenticate with Azure services using the Azure SDK, and it contains a class called DefaultAzureCredential 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
DefaultAzureCredential you need to install the Azure.Identity NuGet package:dotnet add package Azure.IdentityThen you can use it in your code like this:
Code:
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
TokenRequestContext of [URL]https://ossrdbms-aad.database.windows.net/.default[/URL], which grants access to the Azure PostgreSQL Flexible Server. It's what is being done with the az account get-access-token call and the --resource-type oss-rdbms argument. With this in C# though, we're able to get the token and then use that to connect to the database.Handling Token Expiry
One thing to note is that the token that is returned by
DefaultAzureCredential 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 NpgsqlDataSourceBuilder, 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
DefaultAzureCredential to get the token, and then use that token to connect to the database.
Code:
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
dataSourceBuilder we call the UsePeriodicPasswordProvider method, passing in a callback function that will get the token, and then two TimeSpan 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.Connecting it all up
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:
Code:
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 codeHere we're getting the connection string and creating a
NpgsqlConnectionStringBuilder 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 UsePeriodicPasswordProvider 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.
Conclusion
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
NpgsqlDataSourceBuilder and then recreate it when the token expired. But thanks to the UsePeriodicPasswordProvider method, it's actually quite easy to manage the token retrieval and expiry, and it's all handled for you.Continue reading...