Multi-Tenancy Data Isolation

Use Case: Multi-Tenancy Data Isolation

The everyday problem: Your SaaS application serves many customers (tenants) — Acme Corp, Globex, Initech — from one deployment. Each tenant's data must be completely isolated: Acme must never see Globex's records, not through a bug, a forgotten WHERE clause, or a crafted request. Get this wrong and you have the worst possible SaaS incident — one customer seeing another's data, a breach of trust and very likely of contract and law. Beyond isolation, tenants differ in size (one has 10 users, another 10,000), may need data residency in specific regions, and must be onboarded and offboarded cleanly. The challenge is serving many tenants from shared infrastructure while guaranteeing their data is isolated, with the isolation enforced so robustly that a single missed filter cannot leak data across tenants.

Four core isolation models plus the resolution mechanism that ties them together. The central tension throughout is isolation strength versus cost/operational efficiency — stronger isolation costs more per tenant; cheaper sharing risks leaks.

---

Way 1: Shared Database, Shared Schema (discriminator column)

Best used for: The most cost-efficient model — many small-to-medium tenants on one database and schema, distinguished by a TenantId column on every tenant-owned table, with isolation enforced by a global query filter.

The concept: Every tenant-scoped table has a TenantId column. All tenants' rows live in the same tables, separated only by that column. A global query filter automatically appends WHERE TenantId = @currentTenant to every query, so the application code never sees other tenants' rows — and crucially, cannot forget to filter, because the filter is automatic.

// Every tenant-owned entity carries the tenant discriminator
public class Order
{
    public int Id { get; set; }
    public Guid TenantId { get; set; } // which tenant owns this row
    public decimal Total { get; set; }
}

// A scoped service resolves the current tenant from the request (see Way 5)
public interface ITenantContext { Guid TenantId { get; } }

// GLOBAL QUERY FILTER — automatically scopes EVERY query to the current tenant
public class AppDbContext(DbContextOptions options, ITenantContext tenant) : DbContext(options)
{
    private readonly Guid _tenantId = tenant.TenantId;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Applied to every query for Order — impossible to forget
        modelBuilder.Entity<Order>().HasQueryFilter(o => o.TenantId == _tenantId);
        // Repeat for every tenant-owned entity (or automate via reflection over ITenantOwned)
    }

    // Stamp TenantId automatically on insert so code can't set it wrong or omit it
    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        foreach (var entry in ChangeTracker.Entries<Order>().Where(e => e.State == EntityState.Added))
            entry.Entity.TenantId = _tenantId;
        return await base.SaveChangesAsync(ct);
    }
}

// Index TenantId first on every tenant table so queries stay fast:
// CREATE INDEX IX_Orders_Tenant ON Orders (TenantId, ...);

Why it works: The global query filter makes tenant isolation automatic and centralised — every query is scoped to the current tenant without any developer remembering to add the condition, which removes the single biggest risk (a forgotten filter leaking data). Stamping TenantId on insert in SaveChanges prevents code from writing rows under the wrong tenant. One database and schema means the lowest infrastructure cost and simplest operations.

Benchmark note: Shared-everything is the most cost-efficient model — one database serves thousands of tenants, onboarding a tenant is just a new TenantId, and maintenance (migrations, backups) is done once. It is the right default for SaaS with many small tenants. But it has the weakest isolation and the highest blast radius for mistakes: a single query that bypasses the filter (raw SQL, IgnoreQueryFilters, a join to an unfiltered entity, a new table where someone forgot the filter) leaks data across tenants — the catastrophic SaaS failure. Defences: apply filters via reflection over a marker interface so no entity is missed, forbid raw SQL on tenant tables without explicit scoping, test isolation aggressively (automated tests that assert tenant A cannot read tenant B), and always index TenantId first or queries scan across all tenants' data. The noisy-neighbour problem also applies — one huge tenant's load affects all others sharing the database. Strong process and testing discipline make this model safe; carelessness makes it dangerous.

---

Way 2: Shared Database, Separate Schemas

Best used for: A middle ground — each tenant gets their own schema (set of tables) within one shared database, giving stronger logical separation than a discriminator column while still sharing the database server.

The concept: One database server, but each tenant has a dedicated schema (e.g. acme.Orders, globex.Orders). Tenants' tables are physically separate within the shared database, and the application selects the right schema based on the current tenant. Isolation is at the schema level rather than the row level.

// Resolve and switch to the tenant's schema per request/connection
public class TenantSchemaInterceptor(ITenantContext tenant) : DbConnectionInterceptor
{
    // Set the schema search path for this connection to the tenant's schema
    public override async ValueTask<InterceptionResult> ConnectionOpenedAsync(
        DbConnection connection, ConnectionEndEventData eventData, CancellationToken ct = default)
    {
        using var cmd = connection.CreateCommand();
        // PostgreSQL: scope all unqualified table references to the tenant's schema
        cmd.CommandText = $"SET search_path TO \"{tenant.SchemaName}\"";
        await cmd.ExecuteNonQueryAsync(ct);
        return InterceptionResult.Suppress();
    }
}

// Or configure the schema per tenant in the model (when known at context build time):
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>().ToTable("Orders", _tenant.SchemaName);
}

// Onboarding a tenant = create their schema and tables (run migrations into the new schema)

Why it works: Separate schemas give genuine structural separation — a query against the tenant's schema simply cannot return another tenant's rows because the other tenant's data is in different tables entirely, not merely different rows of the same table. This is stronger isolation than a discriminator column (no shared rows, so no filter to forget) while still sharing one database server's cost and connection.

Benchmark note: Separate schemas sit between shared-schema and separate-databases on the isolation/cost spectrum: stronger isolation than a discriminator column (the "forgotten filter" leak is far harder when data is in physically separate tables), while still cheaper than a database per tenant (one server, one connection pool). The costs rise, though: migrations multiply — a schema change must be applied to every tenant's schema, so onboarding and updating get more complex as tenant count grows, and hundreds of schemas become operationally heavy. It also does not solve noisy-neighbour (shared server resources) or data residency. This model fits a moderate number of tenants needing stronger isolation than shared-schema offers, but it scales worse operationally than shared-schema and is less common than the two ends of the spectrum (Ways 1 and 3). Many teams skip it, going straight from shared-schema to database-per-tenant when isolation needs grow.

---

Way 3: Database per Tenant

Best used for: Strong isolation requirements — enterprise tenants, regulated data, data-residency needs, or large tenants needing dedicated resources — where each tenant gets their own database.

The concept: Each tenant has a completely separate database (on shared or dedicated servers). The application resolves the current tenant and connects to their database. Isolation is maximal — there is no shared data store at all, so cross-tenant leakage through a query is essentially impossible, and each tenant can be sized, located, backed up, and restored independently.

// Resolve the tenant's connection string and build their DbContext per request
public class TenantConnectionResolver(ITenantContext tenant, ITenantStore store)
{
    public async Task<string> GetConnectionStringAsync(CancellationToken ct)
    {
        // Look up THIS tenant's dedicated database connection (cached)
        var info = await store.GetTenantAsync(tenant.TenantId, ct);
        return info.ConnectionString; // points at the tenant's own database
    }
}

// Configure DbContext to use the resolved per-tenant connection
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    var resolver = sp.GetRequiredService<TenantConnectionResolver>();
    var connStr  = resolver.GetConnectionStringAsync(CancellationToken.None).GetAwaiter().GetResult();
    options.UseNpgsql(connStr); // each request targets the current tenant's database
});

// Onboarding = provision a new database and run migrations into it.
// Offboarding = back up and drop that one database — clean and complete.
// Per-tenant backup/restore, scaling, and regional placement all become possible.

Why it works: Complete physical separation means the strongest possible isolation — no global filter to forget, no shared tables, no discriminator to get wrong; a tenant's database simply contains only their data. It also unlocks per-tenant operations impossible in shared models: restore one tenant to a point in time without affecting others, place a tenant's database in a specific region for data residency, give a large tenant dedicated resources, and offboard a tenant by dropping exactly one database.

Benchmark note: Database-per-tenant gives the strongest isolation and the most per-tenant flexibility (residency, independent backup/restore, dedicated scaling, clean offboarding) — the right choice for enterprise, regulated, or large tenants, and often a contractual requirement. The costs are the highest: migrations must run across every tenant database (hundreds or thousands of databases to migrate, requiring robust automation), connection management is more complex (a connection pool per tenant, or careful pooling), per-tenant cost is higher (each database has overhead), and operational tooling must handle fleet-wide operations. It also does not scale to huge numbers of tiny tenants economically (thousands of databases for thousands of trivial tenants is wasteful) — that is shared-schema's domain. A common mature pattern is hybrid/tiered: small tenants on shared-schema (Way 1), large or regulated tenants promoted to their own database (Way 3), chosen per tenant. This balances cost and isolation across a diverse tenant base.

---

Way 4: Row-Level Security (database-enforced isolation)

Best used for: Adding a database-level safety net to the shared-schema model (Way 1) — having the database itself enforce tenant isolation so that even a buggy or malicious query cannot cross tenants, regardless of the application.

The concept: Row-Level Security (RLS), supported by PostgreSQL and SQL Server, lets the database enforce a per-row access policy. You set the current tenant on the database session, and a security policy ensures every query — no matter how it is written — only sees rows matching that tenant. Isolation moves from "the application remembers to filter" to "the database guarantees it".

-- PostgreSQL Row-Level Security — the DATABASE enforces tenant isolation
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: a session can only see/modify rows for its current tenant
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Even a query with NO tenant filter, or raw SQL, or a forgotten WHERE clause,
-- returns ONLY the current tenant's rows — the database enforces it.

// Set the current tenant on the connection each request; the DB does the rest
public class RlsTenantInterceptor(ITenantContext tenant) : DbConnectionInterceptor
{
    public override async ValueTask<InterceptionResult> ConnectionOpenedAsync(
        DbConnection connection, ConnectionEndEventData eventData, CancellationToken ct = default)
    {
        using var cmd = connection.CreateCommand();
        // Set the session variable the RLS policy reads
        cmd.CommandText = "SELECT set_config('app.current_tenant', @tenantId, false)";
        var p = cmd.CreateParameter(); p.ParameterName = "@tenantId";
        p.Value = tenant.TenantId.ToString(); cmd.Parameters.Add(p);
        await cmd.ExecuteNonQueryAsync(ct);
        return InterceptionResult.Suppress();
    }
}
// Now isolation holds even if application-level filters are bypassed — defence in depth.

Why it works: RLS moves enforcement into the database, the lowest and most authoritative layer — the policy applies to every query against the table regardless of how the application wrote it, so a forgotten filter, raw SQL, an injection, or a buggy ORM query cannot return cross-tenant rows. It transforms isolation from an application promise (which a single mistake can break) into a database guarantee, providing genuine defence in depth on top of the shared-schema model.

Benchmark note: RLS is the strongest safeguard for the shared-schema model and the answer to its core weakness — it makes the catastrophic "forgotten filter leaks data" failure essentially impossible because the database enforces isolation beneath the application entirely. Combined with Way 1's application-level filters, it gives defence in depth: the app filters for correctness and performance, the database guarantees isolation as a backstop. The costs: a small performance overhead (the policy predicate is applied to queries — usually minor with proper indexing on tenant_id), some operational complexity (policies to manage, the session variable to set reliably on every connection — easy to get wrong with connection pooling if not set per checkout), and it is database-specific. The session-variable setting must be bulletproof: if a pooled connection is reused without resetting the tenant, you get cross-tenant access — so set it on every connection open/checkout. For shared-schema SaaS handling sensitive data, RLS is strongly worth the complexity as the safety net that makes the cost-efficient model genuinely safe.

---

Way 5: Tenant Resolution (the mechanism underneath all models)

Best used for: Every multi-tenant app regardless of isolation model — reliably identifying which tenant a request belongs to, since every model above depends on correctly resolving the current tenant.

The concept: Before any isolation can work, the application must determine the current tenant from the incoming request — from the subdomain (acme.app.com), a path segment (/acme/...), a header, or a claim in the authenticated user's token. This resolved tenant is then made available (as a scoped service) to whichever isolation model is in use. The most secure source is the authenticated identity, not user-supplied routing.

// Middleware resolves the tenant once per request and exposes it via a scoped service
public class TenantResolutionMiddleware(RequestDelegate next)
{
    public async Task InvokeAsync(HttpContext context, TenantContext tenantContext, ITenantStore store)
    {
        // MOST SECURE: derive tenant from the authenticated user's claim — not from the URL,
        // which a user could tamper with to attempt cross-tenant access.
        var tenantClaim = context.User.FindFirst("tenant_id")?.Value;

        // Other sources (validate against the user's allowed tenants!):
        //   subdomain: context.Request.Host.Host.Split('.')[0]
        //   header:    context.Request.Headers["X-Tenant-Id"]
        //   path:      context.Request.RouteValues["tenant"]

        if (tenantClaim is null || !Guid.TryParse(tenantClaim, out var tenantId))
        {
            context.Response.StatusCode = StatusCodes.Status401Unauthorized;
            return;
        }

        // Verify the tenant exists and is active
        var tenant = await store.GetTenantAsync(tenantId, context.RequestAborted);
        if (tenant is null || !tenant.IsActive)
        {
            context.Response.StatusCode = StatusCodes.Status403Forbidden;
            return;
        }

        tenantContext.SetTenant(tenantId); // now available to the DbContext / filters / RLS
        await next(context);
    }
}

// Registered as a scoped service so the DbContext and filters can consume it per request.

Why it works: Resolving the tenant once, early, from a trustworthy source (ideally the authenticated identity) and exposing it as a scoped service gives every downstream component — query filters, schema selection, connection resolution, RLS session variables — one authoritative answer to "which tenant is this?". Centralising resolution prevents inconsistency (different parts of the code disagreeing on the tenant) and, by sourcing from the validated identity rather than user-controlled input, prevents tenant-spoofing attacks.

Benchmark note: Tenant resolution is the keystone — every isolation model is only as safe as the correctness of "which tenant is this request?". The critical security point: derive the tenant from the authenticated identity (a validated claim), not from user-controllable input like a header or URL segment alone — otherwise a user could change the value to attempt access to another tenant. If you do use subdomain/path routing for convenience, still validate that the authenticated user is actually permitted that tenant. Resolve once in middleware, expose via a scoped service, and ensure background jobs (which have no HTTP request) explicitly set the tenant context they operate on. A bug here — resolving the wrong tenant, or trusting a spoofable source — undermines every other isolation mechanism, making this small piece of code disproportionately security-critical. Test it specifically: attempt cross-tenant access with tampered routing and assert it is rejected.

---

Choosing the right approach

• Many small tenants, cost-efficiency priority → Shared DB, shared schema + filter (Way 1)
• Stronger logical separation, moderate tenant count → Shared DB, separate schemas (Way 2)
• Enterprise / regulated / large tenants, residency needs → Database per tenant (Way 3)
• Database-enforced safety net for shared schema → Row-Level Security (Way 4)
• Identifying the tenant (always required) → Tenant resolution from identity (Way 5)

The decision spine: the choice is fundamentally isolation strength versus cost, and it is legitimately per-tenant. Shared-schema (Way 1) is cheapest and right for many small tenants, but its isolation rests on application filters that a single mistake can break — so for sensitive data, add Row-Level Security (Way 4) to make the database enforce isolation as a backstop, turning the cost-efficient model into a genuinely safe one. Database-per-tenant (Way 3) gives the strongest isolation and per-tenant flexibility (residency, independent backup, clean offboarding) at the highest operational cost, fitting enterprise and regulated tenants. Separate-schemas (Way 2) is a less-common middle ground. The mature pattern is often tiered: small tenants share, large/regulated tenants get dedicated databases, decided per tenant. Underpinning all of them, tenant resolution (Way 5) must derive the tenant from the authenticated identity, never from spoofable input. The overriding principle: tenant isolation is a security boundary where the failure mode is catastrophic (one customer seeing another's data), so favour defence in depth — automatic filters and database-level enforcement and aggressive isolation testing — because here a single forgotten WHERE clause is not a bug, it is a breach.

Note: this is an informational engineering overview. Multi-tenant isolation is security-critical and may carry legal/compliance obligations (data residency, breach liability). Validate isolation with dedicated cross-tenant security testing and align the model with your contractual and regulatory requirements.