I'm currently working on the API for a multi-tenant application, built with .NET 6 and using the Entity Framework. The project uses a PostGRES database.

All tenants are in the same database but records have a tenant_id field and I want to implement Row Level Security, to compartmentalise each tenant/keep their data secure and separate.

What would be the most appropriate approach for implementing RLS for multitenancy? Is it more common to provision a user for each tenant or to use the current_setting('app.current_tenant')::UUID value for the RLS policy?

How would either of these approaches be implemented in the Entity Framework? E.g. how would you get it to switch user every time a query is run or how would you set the app.current_tenant before queries? If you take the second approach, is there a risk of accidentally persisting this tenant ID across queries and potentially leaking data?

CodePudding user response：

Use Global Query Filters,

Global query filters are LINQ query predicates applied to Entity Types in the metadata model (usually in OnModelCreating). A query predicate is a boolean expression typically passed to the LINQ Where query operator. EF Core applies such filters automatically to any LINQ queries involving those Entity Types. EF Core also applies them to Entity Types, referenced indirectly through use of Include or navigation property. Some common applications of this feature are:

Soft delete - An Entity Type defines an IsDeleted property.

Multi-tenancy - An Entity Type defines a TenantId property.

eg

modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);

Although using a separate database per tenant is often a better approach. At least plan to support having more than one tenant database, with some mapping of tenants to databases.