Connecting to Azure Postgresql with Managed Identities

[alexmaie]

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<NpgsqlConnection>? 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);

});

`

[azygis]

To be fair, it's perfectly fine to have a new assembly in the same solution which exposes the new factory and perhaps the extension method for convenience (UseAzurePostgreSqlStorage or alike), and only reference the azure-specific libraries in there.

[alexmaie]

that would of course be a good compromise. This would mean that another nuget package needs to be published then. I could create a PR with the source code if you guys think it's acceptable.

[azygis]

Please do. Keep in mind that the release may be quite delayed as I've no idea yet how we should go with the release, publishing and so on 😅 but the changes could absolutely be incorporated already.

[alexmaie]

No worries, I already have this running in the guinea pig project :)). Do you have some guidelines for receiving new contributions? This is my first time doing this, but I know each projects has different rules 😅