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?

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?

• entity-framework-core
• npgsql

• Sounds like an XY problem. Can you explain why you want this? Something to do with multi-tenancy? – Gert Arnold Commented Mar 7 at 15:03
• It's for allowing manual testers to impersonate other users without actually logging in as them. The authentication and authorization is implemented completely in the database. – belidzs Commented Mar 7 at 15:08
• 1 Register new DbConnectionInterceptor, then you will be able to control connections. – Svyatoslav Danyliv Commented Mar 7 at 22:56
• @SvyatoslavDanyliv this is the answer I've been looking for, thanks. If you add it as such, I'll accept it – belidzs Commented Mar 9 at 8:49
• Looks like NpgsqlDataSourceBuilder.UsePhysicalConnectionInitializer() does this at the physical level, so it only runs after the physical connection is opened, unlike the DbConnectionInterceptor (which hooks onto the logical connection layer) – belidzs Commented Mar 9 at 9:41

1 Answer 1

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());
                }
            });
        });
}