Connecting to Azure Postgresql with Managed Identities #397
Description
Hi Hangfire Postgresql Team,
I had the task to connect Hangfire to a Azure Postgresql database.
In Azure we have the concept of managed identities which allows identities to connect without a password. This is handled by requesting a token from azure for a specific resource.
I managed to get this to work with hangfire after some research and I was thinking that this might be a nice addition to this project.
What I see problematic is that the solution dpends on some azure specific nugets, so maybe it's not worth to invest into a new repository, but maybe have it here as a wiki page so that people how need it can easily find this information.
The solution is basically this:
`
class AzureManagedIdentityNpgsqlConnectionFactory(string connectionString, PostgreSqlStorageOptions options, [CanBeNull] Action? connectionSetup = null) : NpgsqlInstanceConnectionFactoryBase(options)
{
private readonly NpgsqlDataSource _dataSource = AzureNpgsqlDataSource.CreateAzureManagedIdentityEnabledSource(connectionString);
[CanBeNull] private readonly Action<NpgsqlConnection>? _connectionSetup = connectionSetup;
public override NpgsqlConnection GetOrCreateConnection()
{
var connection = _dataSource.CreateConnection();
_connectionSetup?.Invoke(connection);
return connection;
}
}
static class AzureNpgsqlDataSource
{
//Kudos https://mattparker.dev/blog/azure-managed-identity-postgres-aspnetcore
public static NpgsqlDataSource CreateAzureManagedIdentityEnabledSource(string connectionString)
{
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
if (dataSourceBuilder.ConnectionStringBuilder.Password is null) // lets us supply a password locally, bypassing this expiry handling
{
var credentials = new DefaultAzureCredential();
dataSourceBuilder.UsePeriodicPasswordProvider(
async (_, ct) =>
{
// This specific context must be used, to get a token to access the postgres database
var token = await credentials.GetTokenAsync(new TokenRequestContext(["https://ossrdbms-aad.database.windows.net/.default"]), ct);
return token.Token;
},
TimeSpan.FromHours(4), // successRefreshInterval - gets a new token every 23 hours
TimeSpan.FromSeconds(10) // failureRefreshInterval - retries after 10 seconds if a token retrieval fails
);
}
return dataSourceBuilder.Build()!;
}
}
context.Services.AddHangfire(config =>
{
var options = new PostgreSqlStorageOptions
{
PrepareSchemaIfNecessary = true, //migrate database everytime
};
config.UsePostgreSqlStorage(c => c.UseConnectionFactory(new AzureManagedIdentityNpgsqlConnectionFactory(configuration.GetProviderDependentConnectionString(), options)), options);
});
`