
Most bugs can be apologized for. Showing one customer's data to another customer is not one of them. In a multi-tenant system, tenant isolation is the property that has to hold on the worst day, written by the newest developer, through the code path nobody reviewed.
In a multi-tenant platform I am building, each service keeps its own PostgreSQL database, and tenants share tables, separated by a tenant_id column. Isolation is enforced twice: once in EF Core, with global query filters, and once in PostgreSQL, with row-level security. This note explains why one guard is not enough, how the second one is wired, and the connection-pooling details that decide whether it actually works.
Shared tables, on purpose
There are three common layouts. A database per tenant gives the strongest separation and per-tenant backups, but every schema change becomes a loop over hundreds of databases, and connection pools multiply. A schema per tenant is a middle ground with most of the same operational cost. Shared tables with a tenant column are the cheapest to run and to migrate, and the riskiest, because every query must remember the filter.
For a platform with many small tenants, shared tables win on operations, as long as the risk is handled by design rather than by discipline. That is what the two guards are for.
Resolve the tenant once, and fail closed
Everything below depends on knowing the current tenant, so its resolution is deliberately strict. The tenant comes from the authenticated caller's token, never from a default and never from a header alone:
- An unauthenticated request has no tenant and is refused.
- The token must carry exactly one tenant claim. None, or two (even with the same value), is a refusal.
- If the client also sends a tenant selector header, it must match the claim exactly.
- The tenant directory must report the tenant as active. A suspended tenant gets a clear error, not an empty page.
Each refusal is a ProblemDetails response with a stable error code. The admitted tenant becomes the current tenant for the rest of the request, through a scoped ICurrentTenant service. Endpoints that genuinely need no tenant, such as health checks, opt out explicitly with an attribute, so forgetting is safe and opting out is visible in review.
The first guard: EF Core global query filters
A global query filter is a predicate EF Core adds to every LINQ query for an entity type. The important detail is that a filter which reads a property of the DbContext is evaluated per context instance: the model is built once and cached, but the tenant value is a parameter read from the context that runs the query.
public interface ITenantScoped { string? TenantId { get; } } // also read by the interceptor below
public sealed class AppDbContext(DbContextOptions<AppDbContext> options, ICurrentTenant tenant)
: DbContext(options), ITenantScoped
{
public string? TenantId => tenant.Id; // read for every query, per context
public DbSet<Employee> Employees => Set<Employee>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// EF Core 10: a named filter, so other filters (soft delete) can sit beside it
modelBuilder.Entity<Employee>()
.HasQueryFilter("Tenant", e => e.TenantId == TenantId);
// EF Core 8 and 9 allow one unnamed filter per entity type:
// modelBuilder.Entity<Employee>().HasQueryFilter(e => e.TenantId == TenantId);
}
}
In practice you do not write one filter per entity by hand. The platform applies it by convention to every entity that implements an ITenantOwned marker interface, so a new table cannot be added without a filter. A save interceptor completes the picture: a new tenant-owned row without a tenant gets the current one, and a row that belongs to another tenant, or a write with no current tenant at all, is refused before any SQL is sent. When the tenant is unknown, the filter compares with null and matches nothing, so the default is an empty result, not everything.
This guard covers the normal path very well. It does not cover everything:
IgnoreQueryFilters()turns it off, sometimes for a good reason and sometimes by copy and paste. Named filters in EF Core 10 help, because you can ignore only the soft-delete filter and keep the tenant one.- Raw SQL that does not go through an entity set, such as
Database.SqlQuery<T>orExecuteSql, gets no filter. - Anything that is not EF Core gets no filter: a Dapper query, a reporting tool, a data fix run by hand in psql.
The second guard: row-level security
PostgreSQL's row-level security (RLS) attaches policies to a table, and the database adds them to every query on that table, whoever wrote the query. The policy needs to know the current tenant, so the application puts it in a custom session setting, app.tenant_id, and the policy reads it:
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
ALTER TABLE employees FORCE ROW LEVEL SECURITY; -- the service connects as the owner
CREATE POLICY tenant_isolation ON employees
USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), ''))
WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), ''));
Each line is there for a reason:
- FORCE. A table's owner is normally exempt from its policies. Services usually connect as the role that owns their tables, because that role runs the migrations, so without
FORCE ROW LEVEL SECURITYthe policy would apply to everyone except the application. - No superuser. Superusers and roles with
BYPASSRLSskip every policy, forced or not. The service role must have neither attribute, and a startup check verifies that. - USING and WITH CHECK.
USINGfilters the rows a statement can see, update or delete.WITH CHECKrejects inserts and updates that would produce a row for another tenant; PostgreSQL answers with an error, not a silent skip. - current_setting(..., true) and NULLIF. The second argument returns null instead of an error when the setting does not exist. A setting that was set earlier in the session and then reset can come back as an empty string, which
NULLIFturns into null. Null equals nothing, so a connection without a tenant sees no rows and can write none.
One limit is worth knowing before it surprises you. Unique and foreign key checks bypass RLS, so a unique index on email alone tells tenant A that tenant B already has that address. Unique constraints in tenant-owned tables should include tenant_id.
Putting the tenant on the connection
The policy is only as correct as the value of app.tenant_id on the connection that runs the query, and connections are pooled. A connection returns to the pool with whatever session state it had; Npgsql resets that state on reuse by default, but the No Reset On Close option, sometimes enabled for performance, turns the reset off. Correctness must not depend on either setting, so the platform overwrites the value every time EF Core opens a connection, using an empty value when there is no tenant:
public sealed class TenantSessionInterceptor : DbConnectionInterceptor
{
public override async Task ConnectionOpenedAsync(DbConnection connection,
ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
{
await using var command = SetTenant(connection, eventData.Context);
await command.ExecuteNonQueryAsync(cancellationToken);
}
public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
{
using var command = SetTenant(connection, eventData.Context);
command.ExecuteNonQuery();
}
private static DbCommand SetTenant(DbConnection connection, DbContext? context)
{
var command = connection.CreateCommand();
command.CommandText = "SELECT set_config('app.tenant_id', @tenant, false)";
var tenant = command.CreateParameter();
tenant.ParameterName = "tenant";
tenant.Value = (context as ITenantScoped)?.TenantId ?? ""; // empty matches no row
command.Parameters.Add(tenant);
return command;
}
}
// registration: options.UseNpgsql(dataSource).AddInterceptors(new TenantSessionInterceptor());
The interceptor keeps no state of its own; it reads the tenant from the context that opened the connection, so one instance can serve every context. set_config is used instead of SET because SET cannot take a bind parameter, and building it by string concatenation is an injection risk.
The third argument of set_config is the subtle part. With true, the setting lasts only until the end of the current transaction, like SET LOCAL. That is the safest scope, but EF Core runs most queries without an explicit transaction, so each one would need to open a transaction just to set the tenant. With false, the setting lasts for the session, which matches how EF Core holds a connection for the duration of a query or a unit of work. Two consequences follow. The value must be set before a transaction begins, because a setting changed inside a transaction is undone if that transaction rolls back. And the rule "overwrite on every open" is not optional, because the session outlives the request.
Two pooling setups break session-scoped state entirely. Npgsql's multiplexing mode sends commands from different callers over shared connections, so there is no stable session to configure; the platform refuses such connections outright. And PgBouncer in transaction pooling mode hands a server connection to a different client after each transaction, so a session-level setting can leak to another client. Behind such a pooler, use set_config(..., true) inside an explicit transaction for every unit of work.
Migrations and the owner role
The policies belong in migrations, next to the tables they protect. In EF Core that means migrationBuilder.Sql(...) calls, wrapped in a small extension method so every tenant-owned table gets exactly the same statements, and a matching method for Down.
FORCE has a side effect on data migrations. DDL such as adding a column is unaffected, but an UPDATE that backfills values runs under the policy, sees no rows when no tenant is set, and reports success after changing nothing. Backfills should either loop over tenants, setting app.tenant_id for each, or run as a separate maintenance role with BYPASSRLS that the application never uses.
Because a forgotten policy fails silently, the platform has a check, run at startup and in tests, that reads the catalog and confirms the tables are actually protected:
SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = current_schema()
AND c.relkind IN ('r', 'p')
AND EXISTS (SELECT 1 FROM pg_attribute a
WHERE a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped)
AND NOT (c.relrowsecurity AND c.relforcerowsecurity);
Any row returned is a table with a tenant column that is not protected, and the check fails. A similar query against pg_roles confirms that the connected role is neither a superuser nor exempt from RLS.
Testing isolation with Testcontainers
Isolation is a property of the database and the driver together, so it has to be tested against a real PostgreSQL. With Testcontainers the suite starts one PostgreSQL 16 container, and each test gets its own database. One detail matters more than any other: the container's default postgres user is a superuser, and a superuser bypasses every policy. Tests that connect as that user prove nothing about RLS. The fixture creates a separate login role, makes it the owner of each test database, and connects as that role, exactly like the service.
The tests then attack the second guard directly: IgnoreQueryFilters() still sees only the current tenant's rows; raw SQL sees only the current tenant's rows; a context without a tenant sees nothing and cannot insert; an insert for another tenant fails with an insufficient-privilege error; an update aimed at another tenant's rows changes zero rows. The test I value most forces connection reuse:
[Fact]
public async Task Pooled_connection_does_not_leak_the_previous_tenant()
{
var pooled = new NpgsqlConnectionStringBuilder(_db.OwnerConnectionString)
{
NoResetOnClose = true, // keep session state between uses
MaxPoolSize = 1, // so the next context gets the same physical connection
}.ConnectionString;
const string rawTenants = "SELECT tenant_id AS \"Value\" FROM employees";
await using (var acme = _db.NewContext(pooled, tenant: "acme"))
Assert.Equal(new[] { "acme" }, await acme.Database.SqlQueryRaw<string>(rawTenants).ToListAsync());
await using (var none = _db.NewContext(pooled, tenant: null))
Assert.Empty(await none.Database.SqlQueryRaw<string>(rawTenants).ToListAsync());
}
If the interceptor skipped the call when there is no tenant, instead of writing an empty value, the second context would inherit acme from the pooled session and the test would fail.
What RLS does not protect
The two guards make one class of mistake hard: a query that forgets the tenant. They do not help if the tenant is resolved wrongly in the first place, which is why resolution fails closed. They do not cover caches: a Redis key without the tenant in it (think of the caches from the first note in this series) leaks just as well as a query. And they do not cover logs and exports. Isolation is a property of the whole system; the database is simply the last place where it can still be enforced.
If you are designing a multi-tenant service or reviewing one before customers arrive, that is the kind of backend work I take on. The next note looks at work that happens outside requests: background jobs, from hosted services to Hangfire to a lease queue in PostgreSQL.
This note is also available in Persian: read the Persian version.
