Using EFCore interceptors for row-level security

user_283445 0
Viewed 667

I have a multi-tenant PostgreSQL database which uses row-level security to control what a tenant should be able to see.

I'm using EF Core with ASP.NET Core to access this database. To handle tenant access, I'm using a connection interceptor to run the appropriate database commands. For example:

To do this, I've created a connection interceptor.

public override async Task ConnectionOpenedAsync(
    DbConnection connection, 
    ConnectionEndEventData eventData, 
    CancellationToken cancellationToken = default)
{
    using var command = connection.CreateCommand();
    var commandText = string.Join("\r\n",
        "SET SESSION ROLE TO tenant;",
        $"SET SESSION tenant.userId TO '{this.userId}';");

    command.CommandText = commandText;
    await command.ExecuteNonQueryAsync(cancellationToken);
    await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
}

All of this works fine, but I also need to access the database in a non-tenant context. In other words, I don't want the above interceptor to always run.

I'm trying to find the best way to do this. My initial attempt was to use interfaces to define the tenant or 'global' interface

// (MyDbContext implements both IMyTenantDbContext and IMyGlobalDbContext)
services.AddDbContext<IMyTenantDbContext, MyDbContext>((sp, options) =>
{
    var tenantInterceptor = sp.GetRequiredService<SetTenantConnectionInterceptor>();
    options
        .UseNpgsql(connectionString)
        .AddInterceptors(tenantInterceptor);
});
services.AddDbContext<IMyGlobalDbContext, MyDbContext>(options =>
{
     options
         .UseNpgsql(connectionString);
});

However for some reason the DI system uses the first AddDbContext, even if I try to inject a IMyGlobalDbContext meaning the interceptor is still being used.

I'm sure there must be a better way of doing this, so please let me know if I'm doing it all completely wrong.

ef-core-3.1 entity-framework-core c#
2 Answers

I’d suggest making it so userId can be null or a “system” userId that means don’t run the interceptor code, so the same interceptor handles both cases.

user_5540370

I believe I came across the answer to this question earlier today. I can't see your MyDbContext definition so I am making an assumption on how that looks, but I'm thinking it looks something like this.

public class MyDbContext : DbContext, IMyTenantDbContext, IMyGlobalDbContext
{
    public MyDbContext()
    {
    }

    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
}

If so, I believe the problem is in the DbContextOptions<MyDbContext> options parameter injected into the constructor. When you're requesting an IMyTenantDbContext or an IMyGlobalDbContext, you're injecting a DbContext, however, EntityFramework is setting up the DI for DbContextOptions<> and each time you're constructing a DbContext using one of those interfaces, you're getting the same options injected each time. The end result is, the same set of options is passed to any context you create this way.

There is a two-step solution to this problem. Step 1, change it to an inheritance-based architecture instead like this:

public class MyTenantDbContext : MyDbContext
{
    public MyTenantDbContext ()
    {
    }

    public MyTenantDbContext (DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
}

public class MyDbContext : DbContext
{
    public MyDbContext()
    {
    }

    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
}

Essentially, just create a wrapper around your MyDbContext.

This is only 1/2 of the solution though, because we're still using DbContextOptions<MyDbContext> in both classes, so we'll still get the same set of options in each class. Due to a restriction on the options we have for what we can pass to the MyDbContext base class from the MyTenantDbContext class constructor we can't just pass aDbContextOptions<MyTenantDbContext> object. This is explained in detail in this github issue. https://github.com/dotnet/efcore/issues/7533

The fix for that (and step 2 of the solution) is also mentioned in the same github issue. Namely, create a protected constructor on your base class that accepts the non-generic DbContextOptions class. THEN you CAN change the type in the MyTenantDbContext constructor to DbContextOptions<MyTenantDbContext>.

Here is the final code.

public class MyTenantDbContext : MyDbContext
{
    public MyTenantDbContext()
    {
    }

    public MyTenantDbContext(DbContextOptions<MyTenantDbContext> options)
        : base(options)
    {
    }
}

public class MyDbContext : DbContext
{
    public MyDbContext()
    {
    }

    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }

    protected MyDbContext(DbContextOptions options)
        : base(options)
    {
    }
}

When you set up DI for this, set up the context for both of the concrete types and the DbContextOptions will be passed with the correct configuration to the correct context class.

user_11468500
Related