Azure PostgreSQL, Entra ID Authentication and .NET
Tags: dotnet, security, azure
Published: 2024-06-03 00:34:02

---
title: Azure PostgreSQL, Entra ID Authentication and .NET
published: true
date: 2024-06-03 00:08:47 UTC
tags: dotnet,security,azure
canonical_url: https://www.aaron-powell.com/posts/2024-06-03-azure-postgresql-and-entra-id-dotnet/
---

I’m currently working on a project in which we are using [Entra ID](https://learn.microsoft.com/azure/postgresql/single-server/how-to-configure-sign-in-azure-ad-authentication?WT.mc_id=dotnet-139180-aapowell) 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](https://www.npgsql.org/) 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 [<svg height="22px" viewbox="0 0 24 24" width="22px" xmlns="http://www.w3.org/2000/svg"><path d="M0 0h24v24H0z" fill="none"></path><path d="M3.9 12c0-1.71 1.39-3.1 3.1-3.1h4V7H7c-2.76 0-5 2.24-5 5s2.24 5 5 5h4v-1.9H7c-1.71 0-3.1-1.39-3.1-3.1zM8 13h8v-2H8v2zm9-6h-4v1.9h4c1.71 0 3.1 1.39 3.1 3.1s-1.39 3.1-3.1 3.1h-4V17h4c2.76 0 5-2.24 5-5s-2.24-5-5-5z"></path></svg>](#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 [<svg height="22px" viewbox="0 0 24 24" width="22px" xmlns="http://www.w3.org/2000/svg"><path d="M0 0h24v24H0z" fill="none"></path><path d="M3.9 12c0-1.71 1.39-3.1 3.1-3.1h4V7H7c-2.76 0-5 2.24-5 5s2.24 5 5 5h4v-1.9H7c-1.71 0-3.1-1.39-3.1-3.1zM8 13h8v-2H8v2zm9-6h-4v1.9h4c1.71 0 3.1 1.39 3.1 3.1s-1.39 3.1-3.1 3.1h-4V17h4c2.76 0 5-2.24 5-5s-2.24-5-5-5z"></path></svg>](#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:

```bash
az account get-access-token --resource-type oss-rdbms

```

Which returns something like this:

```json
{
  "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](https://www.nuget.org/packages/Azure.Identity/).

## Using Azure.Identity [<svg height="22px" viewbox="0 0 24 24" width="22px" xmlns="http://www.w3.org/2000/svg"><path d="M0 0h24v24H0z" fill="none"></path><path d="M3.9 12c0-1.71 1.39-3.1 3.1-3.1h4V7H7c-2.76 0-5 2.24-5 5s2.24 5 5 5h4v-1.9H7c-1.71 0-3.1-1.39-3.1-3.1zM8 13h8v-2H8v2zm9-6h-4v1.9h4c1.71 0 3.1 1.39 3.1 3.1s-1.39 3.1-3.1 3.1h-4V17h4c2.76 0 5-2.24 5-5s-2.24-5-5-5z"></path></svg>](#using-azureidentity)

`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`](https://learn.microsoft.com/dotnet/api/azure.identity.defaultazurecredential?view=azure-dotnet&WT.mc_id=dotnet-139180-aapowell) 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](https://learn.microsoft.com/dotnet/api/azure.identity.defaultazurecredential?view=azure-dotnet&WT.mc_id=dotnet-139180-aapowell) to see all the sources).

To use `DefaultAzureCredential` you need to install the `Azure.Identity` NuGet package:

```bash
dotnet add package Azure.Identity

```

Then you can use it in your code like this:

```csharp
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 `https://ossrdbms-aad.database.windows.net/.default`, 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 [<svg height="22px" viewbox="0 0 24 24" width="22px" xmlns="http://www.w3.org/2000/svg"><path d="M0 0h24v24H0z" fill="none"></path><path d="M3.9 12c0-1.71 1.39-3.1 3.1-3.1h4V7H7c-2.76 0-5 2.24-5 5s2.24 5 5 5h4v-1.9H7c-1.71 0-3.1-1.39-3.1-3.1zM8 13h8v-2H8v2zm9-6h-4v1.9h4c1.71 0 3.1 1.39 3.1 3.1s-1.39 3.1-3.1 3.1h-4V17h4c2.76 0 5-2.24 5-5s-2.24-5-5-5z"></path></svg>](#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.

```csharp
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 [<svg height="22px" viewbox="0 0 24 24" width="22px" xmlns="http://www.w3.org/2000/svg"><path d="M0 0h24v24H0z" fill="none"></path><path d="M3.9 12c0-1.71 1.39-3.1 3.1-3.1h4V7H7c-2.76 0-5 2.24-5 5s2.24 5 5 5h4v-1.9H7c-1.71 0-3.1-1.39-3.1-3.1zM8 13h8v-2H8v2zm9-6h-4v1.9h4c1.71 0 3.1 1.39 3.1 3.1s-1.39 3.1-3.1 3.1h-4V17h4c2.76 0 5-2.24 5-5s-2.24-5-5-5z"></path></svg>](#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:

```csharp
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 `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 [<svg height="22px" viewbox="0 0 24 24" width="22px" xmlns="http://www.w3.org/2000/svg"><path d="M0 0h24v24H0z" fill="none"></path><path d="M3.9 12c0-1.71 1.39-3.1 3.1-3.1h4V7H7c-2.76 0-5 2.24-5 5s2.24 5 5 5h4v-1.9H7c-1.71 0-3.1-1.39-3.1-3.1zM8 13h8v-2H8v2zm9-6h-4v1.9h4c1.71 0 3.1 1.39 3.1 3.1s-1.39 3.1-3.1 3.1h-4V17h4c2.76 0 5-2.24 5-5s-2.24-5-5-5z"></path></svg>](#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.