Skip to content
md ~/writing/ef-core-cok-kiracili-global-query-filter
All posts

· 7 min read

Preventing Data Leaks in a Multi-Tenant App with EF Core Global Query Filters

Preventing Data Leaks in a Multi-Tenant App with EF Core Global Query Filters

When several businesses use the same application and the same database, the system is called multi-tenant. The most expensive bug in such a system is one business seeing another business's data. In this post I explain how I ruled out that bug in Tamirhane, a repair shop management app I am building, with EF Core global query filters, and how I tested it against a real PostgreSQL database.

The problem: one table, many shops

In Tamirhane every phone repair business is a shop. The customers, devices, work orders and spare parts of all shops live in the same tables, and every row has a shop_id column that says who it belongs to. This keeps setup and maintenance simple, with one condition: every query must only see the rows of the signed-in employee's shop.

I saw how easily that condition breaks in my own project. When I first built the data layer there was no filter, and while testing end to end I created a second shop. Registering a customer in the new shop, the app told me "this phone number is already registered". It was, but as a customer of a different shop. A small warning on the surface, but it proved that one shop could query another shop's customer list.

The obvious fix and why it is not enough

The obvious fix is to add the condition to every query by hand:

db.Customers.FirstOrDefaultAsync(c => c.Phone == phone && c.ShopId == shopId, ct);

That works, but it makes security depend on every developer remembering it in every query. The project has dozens of queries and every new screen adds more. A single forgotten ShopId condition leaves the whole system open. And this kind of bug slips through tests easily, because during development you usually work with a single shop.

I wanted the opposite: the filter should be the default, and turning it off should take a deliberate decision.

The solution: EF Core global query filters

EF Core lets you define a global query filter on an entity type. The filter is added automatically to every LINQ query on that type, including relationships loaded with Include.

public sealed class TamirhaneDbContext(DbContextOptions<TamirhaneDbContext> options, ICurrentUser currentUser)
    : DbContext(options), IUnitOfWork
{
    // Without a session this is Guid.Empty and the filter matches nothing.
    private Guid CurrentShopId => currentUser.ShopId;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(TamirhaneDbContext).Assembly);

        modelBuilder.Entity<WorkOrder>().HasQueryFilter(o => o.ShopId == CurrentShopId);
        modelBuilder.Entity<Customer>().HasQueryFilter(c => c.ShopId == CurrentShopId);
        modelBuilder.Entity<Device>().HasQueryFilter(d => d.ShopId == CurrentShopId);
        modelBuilder.Entity<Part>().HasQueryFilter(p => p.ShopId == CurrentShopId);
    }
}

There is an important detail here: the filter does not use a constant, it points to a property of the DbContext (CurrentShopId). Even though the model is built once and shared by all requests, EF Core reads this value from the current DbContext instance on every query and sends it to SQL as a parameter. In ASP.NET Core every request gets its own DbContext, so every request sees its own shop.

The shop id comes from the ICurrentUser interface, which means from the session. A value from a form or a request is never trusted, so a user cannot switch to another shop by changing a number in the browser.

The repository queries no longer know about shops at all:

public Task<Customer?> FindByPhoneAsync(PhoneNumber phone, CancellationToken ct) =>
    db.Customers.FirstOrDefaultAsync(c => c.Phone == phone, ct);

The generated SQL, however, always contains WHERE shop_id = @shopId.

Fail closed

Without a session, ShopId is Guid.Empty and matches no record. So if something goes wrong, the app falls to the "show nothing" side, not the "show everything" side. In security this is called fail closed.

A column the domain does not have: shadow properties

The stock classes in the domain had no ShopId; a stock record belongs to a part and a branch. Instead of changing the domain just for the filter, I used EF Core shadow properties. The column exists in the database but not in the class:

b.Property<Guid>("ShopId");

modelBuilder.Entity<StockLevel>()
    .HasQueryFilter(l => EF.Property<Guid>(l, "ShopId") == CurrentShopId);

Reading is not enough: protecting writes

The filter protects reads, but writes are a separate concern. A bug could create a new record with the wrong shop id. I catch that inside SaveChanges, right before anything reaches the database:

private void StampShop()
{
    foreach (var entry in ChangeTracker.Entries().Where(e => e.State == EntityState.Added))
    {
        // Stock records get their shop from the session.
        if (entry.Entity is StockLevel or StockMovement)
        {
            entry.Property("ShopId").CurrentValue = CurrentShopId;
            continue;
        }

        var shopId = entry.Entity switch
        {
            WorkOrder o => o.ShopId,
            Customer c => c.ShopId,
            Device d => d.ShopId,
            Part p => p.ShopId,
            _ => CurrentShopId,
        };
        if (shopId != CurrentShopId)
            throw new InvalidOperationException($"{entry.Entity.GetType().Name} was about to be written to another shop.");
    }
}

Stock records with the shadow column get their shop here. For records that carry their own shop id, the save is refused if the value does not match the session.

A deliberate exception: the tracking code

Customers check the status of their device without signing in, using the tracking code they were given. There is no session and no shop in that query, so I turn the filter off on purpose:

// The tracking code cannot be guessed, so the filter is turned off on purpose.
public Task<WorkOrder?> GetByTrackingCodeAsync(string trackingCode, CancellationToken ct) =>
    Full.IgnoreQueryFilters().FirstOrDefaultAsync(o => o.TrackingCode == trackingCode, ct);

This is safe because the tracking code is not a sequence number: it is 12 characters generated with a cryptographic random generator from a 32-character alphabet. That is about 10¹⁸ possibilities, so it cannot be guessed. On top of that, the tracking page will show the customer's name masked ("Ay** Ka**"). IgnoreQueryFilters appears in exactly one place in the project, so a code review knows what to search for.

Where the filter cannot see: raw SQL

Global query filters are added to LINQ queries. SQL you write directly, with Database.SqlQuery or ExecuteSql, does not see them. Tamirhane has a counter per shop and year for work order numbers (2026-0001), and it is incremented with a single SQL statement so that two work orders opened at the same moment can never get the same number. In that statement I pass the shop id explicitly:

INSERT INTO work_order_sequences (shop_id, year, value) VALUES (@shopId, @year, 1)
ON CONFLICT (shop_id, year) DO UPDATE SET value = work_order_sequences.value + 1
RETURNING value

The rule is simple: wherever raw SQL is written, shop_id is written by hand and checked specifically in code review.

Testing against a real database

Testing a rule like this with a mocked repository proves little, because the real work happens in the SQL that EF Core generates. So I run these tests on a real PostgreSQL with Testcontainers. When the tests start, a temporary database is created in Docker and the migrations are applied; when they finish, the container is removed.

The test I wrote as the acceptance criterion: a work order is saved for shop B, then shop A asks for that work order by its id.

[Fact]
public async Task Shop_cannot_open_another_shops_work_order_by_id()
{
    var order = await ReceiveAsync(_shopB, "0532 111 11 11");

    using var a = db.AsShop(_shopA);
    Assert.Null(await a.Orders.GetAsync(order.Id, default));

    using var b = db.AsShop(_shopB);
    Assert.NotNull(await b.Orders.GetAsync(order.Id, default));
}

For shop A the record simply does not exist, and the use case returns a "not found" error. In the web layer this will become a 404, so not even the existence of the record will leak. Four more tests cover the rest: another shop's customer cannot be found by phone (the bug I started with), nothing comes back without a session while the tracking code lookup still works, stock records get their shop from the session and stay hidden from other shops, and an attempt to write a record for another shop is refused.

To make sure the tests really work, I temporarily removed the filters and ran them again: 3 of the 5 tests turned red. With the filters back, all of them passed. So if someone breaks the filter by accident later, the very first test run will show it.

Summary

  • Instead of writing the tenant condition into every query by hand, I made it the default with EF Core global query filters; turning it off takes IgnoreQueryFilters, which appears in exactly one place in this project.
  • The shop id always comes from the session; without a session the system shows no data.
  • Writes are checked too, inside SaveChanges; for columns the domain does not have, I used shadow properties.
  • Raw SQL does not see the filter, so shop_id is written there by hand.
  • I tested all of it against a real PostgreSQL with Testcontainers and confirmed that the tests really fail when the filter is broken.

Tamirhane's source code is on GitHub: github.com/MrcDprm/tamirhane-repair-tracker. The project is still in development; once it is done, it will be here with a full project post.