Introduction
In my last article, I demonstrated how an Azure Postgres instance can be integrated with Azure AD. I demonstrated how it allows to create read-only roles that can be managed with Azure AD groups and how it provides a way to implement a least-privilege pattern on the database layer.
In this post, I am going to demonstrate how you can authenticate programmatically against an AAD integrated Postgres instance using Entity Framework Core and the corresponding npgsql provider.
For that purpose we are going to leverage each of the following identity types together with Azure App Service and restrict them with read-only access.
- System assigned managed identity
- User assigned managed identity
- Service principal
System assigned and user assigned managed identities only differ in their lifetime. A system assigned identities lifetime is bound to the resource it got enabled for. When you delete the resource, you are deleting the identity. A user assigned managed identity on the other hand has its own lifetime. However both managed identities provide the benefit of not having to care about the credentials itself, this is opposed to a service principal where we have to take care of the password.
High-level Architecture
I may state the obvious here, but based on your requirement you'd use only one of this identity types in production 😎. So without further ceremony let's get to it 🐱🏍
How Postgres AAD authentication works
In the traditional way we would create a Postgres role and password and then connect to the desired database with a connection string in the form below. Please note that the connection string carries the password.
{
"ConnectionStrings": {
"psql-azureblue-demo": "Server=psql-azureblue-demo.postgres.database.azure.com;Database=demodb;Port=5432;User Id=someDbUser@psql-azureblue-demo;Password=Abracadabra!;Ssl Mode=Require;"
}
}Then we would create a domain specific database context by deriving from DbContext, register the npgsql provider and finally retrieve the connection string from appsettings.json like so:
public class BloggingContext : DbContext
{
private IConfiguration config;
public BloggingContext(IConfiguration config)
{
this.config = config;
}
public DbSet<Post> Posts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(this.config.GetConnectionString("psql-azureblue-demo");
}
}For obvious reasons it's bad practice to check in credentials into a source control system. So instead we'd use Secret Manager to store a connection string for local development and than override that in production with a different one as documented here. Or even better - use a key vault reference as I have documented here.
"Okay, this is all good and well, but why are you telling me this?" - you may ask 😴
Because when dealing with an AAD integrated Postgres instance, you would send a valid access token in the password field.
The reason for this design decision was that Microsoft created the Postgres Azure AD integration with tool-compatibility in mind, so that the Azure AD authentication even works with common Postgres tools building on top of the libpq C-library (for example like psql.exe) which are not natively Azure AD aware.
The following diagram perfectly highlights the steps involved.
So to authenticate you'd have to grab an access token by issuing az account get-access-token --resource-type oss-rdbms --query accessToken and than pass the returned token in the password field. I usually semi-automate this steps with a function in my local PowerShell profile similar to this.
function psql-azureblue-demo
{
param([string]$db, [string]$user)
$env:PGUSER=$user
$env:PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)
$env:PGHOST="psql-azureblue-demo.postgres.database.azure.com"
$env:PGDATABASE=$db
}This allows for easy and quick access to the database by issuing psql-azureblue-demo -db demodb -user psql-demodb-ro@psql-azureblue-demo, followed by a plain psql and you are ready to go!
Okay great, but how do we finally programmatically authenticate? By making use of a callback named ProvidePasswordCallback (documented here), that will get executed when new database connections are opened to request a new token in code. It take a delegate in the form of public delegate string ProvidePasswordCallback(string host, int port, string database, string username);
System Assigned & User Assigned Managed Identity
Requesting an access token with the new Azure.Identity client library is a snap!
Especially the DefaultAzureCredential() type makes a developers life easy in that respect. It will attempt to authenticate via a couple of mechanisms, where its order is as depicted:
So to request a token via system system assigned managed ID you would simply issue the following:
var tokenRequestContext = new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" })
var credential = new DefaultAzureCredential();
var accessToken = credential.GetToken(tokenRequestContext);And when you'd like to use a specific user assigned managed identity simply pass the client id via an DefaultAzureCredentialOptions instance like so
var tokenRequestContext = new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" })
var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions()
{
ManagedIdentityClientId = "<client_id>"
});
var accessToken = await credential.GetToken(tokenRequestContext);If you need more control over what is happening you might want to use ManagedIdentityCredential() instead.
Service Principal
Using a service principal instead is easy as well, just go with ClientSecretCredential()
var tokenRequestContext = new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" })
var credential = new ClientSecretCredential(tenantId, appId, clientSecret);
var accessToken = credential.GetToken(tokenRequestContext);Make sure your managed identity or service principal is a member of the Azure AD group that corresponds to the Postgres role. More details can be found here.
Putting it all together
You can either create an anoymous function with a lambda like so...
public class BloggingContext : DbContext
{
// ...
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(this.config.GetConnectionString("psql-azureblue-demo"), o =>
{
o.ProvidePasswordCallback((h, p, d, u) => {
var tokenCredential = new DefaultAzureCredential();
var accessToken = tokenCredential.GetToken(new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" }));
return accessToken.Token;
});
});
}
}... or introduce a helper class for enhanced testability like so...
public interface ITokenHelper
{
string RequestToken(string host, int port, string db, string username);
}
public class TokenHelper : ITokenHelper
{
private static readonly TokenRequestContext tokenRequestContext = new TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" });
private readonly TokenCredential tokenCredential;
public TokenHelper(TokenCredential tokenCredential)
{
this.tokenCredential = tokenCredential;
}
public string RequestToken(string host, int port, string db, string username)
{
var accessToken = this.tokenCredential.GetToken(tokenRequestContext, CancellationToken.None);
return accessToken.Token;
}
}And than later on inject into your database context.
public class BloggingContext : DbContext
{
private readonly IConfiguration config;
private readonly ITokenHelper tokenHelper;
public BloggingContext(IConfiguration config, ITokenHelper tokenHelper)
{
this.config = config;
this.tokenHelper = tokenHelper;
}
// ...
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(this.config.GetConnectionString("psql-azureblue-demo"), o =>
{
o.ProvidePasswordCallback(this.tokenHelper.RequestToken);
});
}
}Don't forget to register the helper with your DI-Container of choice! Based on your requirements you than would use the helper as describe in the following tests
public class TokenHelperTests
{
[Fact]
public void ShouldRequestTokenViaSystemManagedIdentity()
{
// Arrange
var tokenHelper = new TokenHelper(new DefaultAzureCredential());
// Act
var token = tokenHelper.RequestToken(host: default, port: default, db: default, username: default);
// Assert
Assert.NotEmpty(token);
}
[Fact]
public void ShouldRequestTokenViaUserManagedIdentity()
{
// Arrange
var tokenHelper = new TokenHelper(new DefaultAzureCredential(new DefaultAzureCredentialOptions()
{
ManagedIdentityClientId = "<your user managed identity>"
}));
// Act
var token = tokenHelper.RequestToken(host: default, port: default, db: default, username: default);
// Assert
Assert.NotEmpty(token);
}
[Fact]
public void ShouldRequestTokenViaServicePrincipal()
{
// Arrange
var tenantId = "<your tenant id>";
var clientId = "<your client id>";
var clientSecret = "<your client secret>";
var tokenHelper = new TokenHelper(new ClientSecretCredential(tenantId, clientId, clientSecret));
// Act
var token = tokenHelper.RequestToken(host: default, port: default, db: default, username: default);
// Assert
Assert.NotEmpty(token);
}
}Final words
Unfortunately we can't use the asynchronous counterparts of GetToken() in our simple authentication helper, as this would result in a signature change of the method. This would be incompatible with the delegate (public string M(string, int, string, string) versus public Task M(string, int, string, string)).
So that's it for today. I hope you have found some usefull information. In case of any questions or comments leave a comment!
Happy coding, Matthias 😎
Further reading
