Execute SQL statement after connection established in EF Core with Npgsql

I'd like to execute a specific SQL command each time after the connection to the database was successful using Entity Framework Core 9 with the Npgsql Entity Framework Core Provider.

More specifically, I'd like to make sure that each subsequent SQL command is executed as a specific user (who may be different from the login user):

SET ROLE other_user;

What's the best approach to achieve this?

NpgsqlDataSourceBuilder.UsePhysicalConnectionInitializer() does exactly this:

Documentation

UsePhysicalConnectionInitializer(Action<NpgsqlConnection>?, Func<NpgsqlConnection, Task>?)

Register a connection initializer, which allows executing arbitrary commands when a physical database connection is first opened.

public NpgsqlDataSourceBuilder UsePhysicalConnectionInitializer(Action<NpgsqlConnection>? connectionInitializer, Func<NpgsqlConnection, Task>? connectionInitializerAsync)

Parameters

connectionInitializer Action<NpgsqlConnection>

A synchronous connection initialization lambda, which will be called from Open() when a new physical connection is opened.

connectionInitializerAsync Func<NpgsqlConnection, Task>

An asynchronous connection initialization lambda, which will be called from OpenAsync(CancellationToken) when a new physical connection is opened.

Returns

NpgsqlDataSourceBuilder

The same builder instance so that multiple calls can be chained.

Remarks

If an initializer is registered, both sync and async versions must be provided. If you do not use sync APIs in your code, simply throw NotSupportedException, which would also catch accidental cases of sync opening.

Source: Npgsql documentation

Example

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);
    var builder = optionsBuilder
        .UseNpgsql(DataManager.EfConnectionString, x =>
        {
            x.ConfigureDataSource(dataSourceBuilder =>
            {

                if (DataManager.IsEffectiveUserActive)
                {
                    dataSourceBuilder.UsePhysicalConnectionInitializer(
                        (conn) =>
                        {
                            using (var command = new NpgsqlCommand($"SET SESSION ROLE {DataManager.EffectiveUser}", conn))
                            {
                                _logger.LogDebug("Setting role to {0}", DataManager.EffectiveUser);
                                command.ExecuteNonQuery();
                            }
                        },
                        async (aconn) => throw new NotSupportedException());
                }
            });
        });
}