Read the row-level security article

Postgres row-level security lab

By · PostgreSQL 18 · .NET 10 · EF Core 10

Run a PostgreSQL 18 database with 1,000,000 invoices in Docker and a .NET 10 console app against it. The app walks through twelve scenarios that prove what the row-level security policy catches, where the tenant setting goes missing, and what the policy costs.

1. Download the files

Download and extract the ZIP, then open the postgres-row-level-security folder inside. You need the .NET 10 SDK and Docker with Compose v2.

Download all files (.zip)

Includes all 8 files below. You can also preview or download each file individually.

  • Program.cs

    Setup command and the ten scenarios

    Download
    Preview contents of Program.cs
    using System.Diagnostics;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;
using TenantRls;

const string Host = "Host=127.0.0.1;Port=5433;Database=tenants;";
const string Admin = Host + "Username=postgres;Password=postgres";
const string Owner = Host + "Username=app_owner;Password=owner";
const string App = Host + "Username=app_user;Password=app";

var tenant1 = Guid.Parse("00000000-0000-0000-0000-000000000001");
var tenant2 = Guid.Parse("00000000-0000-0000-0000-000000000002");

if (args is ["setup"])
{
    var sql = await File.ReadAllTextAsync(Path.Combine(AppContext.BaseDirectory, "setup.sql"));
    await using var admin = new NpgsqlConnection(Admin);
    await admin.OpenAsync();
    var sw = Stopwatch.StartNew();
    await new NpgsqlCommand(sql, admin) { CommandTimeout = 600 }.ExecuteNonQueryAsync();
    await new NpgsqlCommand("VACUUM ANALYZE invoices", admin) { CommandTimeout = 600 }.ExecuteNonQueryAsync();
    var rows = await new NpgsqlCommand("SELECT count(*) FROM invoices", admin).ExecuteScalarAsync();
    Console.WriteLine($"setup done: {rows:N0} invoices in {sw.Elapsed.TotalSeconds:F1}s");
    return;
}

Section("1. Two layers: the EF query filter and the policy");
await using (var scope = Request(App, tenant1, interceptor: true))
{
    var db = scope.Db;
    var newest = await db.Invoices.OrderByDescending(i => i.CreatedAt).Take(3).ToListAsync();
    Console.WriteLine($"filtered read as tenant 1: {newest.Count} rows, tenants seen: {Distinct(newest)}");

    var leaked = await db.Invoices.IgnoreQueryFilters().CountAsync();
    Console.WriteLine($"IgnoreQueryFilters() as app_user: {leaked:N0} rows (table has 1,000,000)");
}

Section("2. Who bypasses the policy");
await Exec(Admin, "ALTER TABLE invoices NO FORCE ROW LEVEL SECURITY");
await using (var scope = Request(Owner, tenant1, interceptor: true))
    Console.WriteLine($"app_owner, ENABLE only: {await scope.Db.Invoices.IgnoreQueryFilters().CountAsync():N0} rows");
await Exec(Admin, "ALTER TABLE invoices FORCE ROW LEVEL SECURITY");
await using (var scope = Request(Owner, tenant1, interceptor: true))
    Console.WriteLine($"app_owner, FORCE:       {await scope.Db.Invoices.IgnoreQueryFilters().CountAsync():N0} rows");
await using (var scope = Request(Admin, tenant1, interceptor: true))
    Console.WriteLine($"postgres (superuser):   {await scope.Db.Invoices.IgnoreQueryFilters().CountAsync():N0} rows");

Section("3. Writes: attach a stub for another tenant's invoice");
var foreignId = (Guid)(await Scalar(Admin, $"SELECT id FROM invoices WHERE tenant_id = '{tenant2}' LIMIT 1"))!;
await using (var scope = Request(App, tenant1, interceptor: true))
{
    var db = scope.Db;
    var stub = new Invoice { Id = foreignId, TenantId = tenant1 };
    db.Attach(stub);
    stub.Status = "Paid";
    try
    {
        await db.SaveChangesAsync();
        Console.WriteLine("tracked UPDATE succeeded (it should not)");
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Console.WriteLine($"tracked UPDATE: DbUpdateConcurrencyException: {FirstSentence(ex.Message)}");
    }

    var rows = await db.Invoices.IgnoreQueryFilters()
        .Where(i => i.Id == foreignId)
        .ExecuteUpdateAsync(s => s.SetProperty(i => i.Status, "Paid"));
    Console.WriteLine($"ExecuteUpdate by id only: {rows} rows affected");
}
Console.WriteLine($"stored status of that invoice: {await Scalar(Admin, $"SELECT status FROM invoices WHERE id = '{foreignId}'")}");

Section("4. Writes: insert a row for another tenant");
await using (var scope = Request(App, tenant1, interceptor: true))
{
    var db = scope.Db;
    db.Invoices.Add(new Invoice
    {
        Id = Guid.NewGuid(), TenantId = tenant2, Number = "INV-999999",
        Amount = 10m, Status = "Open", CreatedAt = DateTimeOffset.UtcNow
    });
    try
    {
        await db.SaveChangesAsync();
        Console.WriteLine("INSERT succeeded (it should not)");
    }
    catch (DbUpdateException ex) when (ex.InnerException is PostgresException pg)
    {
        Console.WriteLine($"INSERT: {pg.SqlState} {pg.MessageText}");
    }
}

Section("5. No tenant resolved");
await using (var scope = Request(App, tenantId: null, interceptor: true))
{
    var db = scope.Db;
    Console.WriteLine($"current_setting in the session: '{await db.Database.SqlQueryRaw<string>("SELECT coalesce(current_setting('app.tenant_id', true), '<null>') AS \"Value\"").SingleAsync()}'");
    Console.WriteLine($"filtered read: {await db.Invoices.CountAsync()} rows, IgnoreQueryFilters(): {await db.Invoices.IgnoreQueryFilters().CountAsync()} rows");
}

Section("6. SET at the start of the request, then let EF open and close connections");
await using (var scope = Request(App, tenant1, interceptor: false))
{
    var db = scope.Db;
    await db.Database.ExecuteSqlRawAsync($"SET app.tenant_id = '{tenant1}'");
    Console.WriteLine($"SET, then a query on a pooled connection: {await db.Invoices.IgnoreQueryFilters().CountAsync():N0} rows");

    await db.Database.OpenConnectionAsync();
    await db.Database.ExecuteSqlRawAsync($"SET app.tenant_id = '{tenant1}'");
    Console.WriteLine($"SET on a connection EF keeps open:       {await db.Invoices.IgnoreQueryFilters().CountAsync():N0} rows");
    await db.Database.CloseConnectionAsync();
}

Section("7. What the next request sees on the same physical connection");
const string OnePhysical = ";Maximum Pool Size=1";
const string NoReset = ";Maximum Pool Size=1;No Reset On Close=true";
foreach (var (label, cs) in new[] { ("default (reset on close)", App + OnePhysical), ("No Reset On Close=true", App + NoReset) })
{
    await using (var first = Request(cs, tenant1, interceptor: false))
    {
        await first.Db.Database.OpenConnectionAsync();
        await first.Db.Database.ExecuteSqlRawAsync($"SET app.tenant_id = '{tenant1}'");
        await first.Db.Invoices.IgnoreQueryFilters().CountAsync();
        await first.Db.Database.CloseConnectionAsync();
    }
    await using (var second = Request(cs, tenantId: null, interceptor: false))
    {
        var seen = await second.Db.Database.SqlQueryRaw<string>("SELECT coalesce(current_setting('app.tenant_id', true), '<null>') AS \"Value\"").SingleAsync();
        var rows = await second.Db.Invoices.IgnoreQueryFilters().CountAsync();
        Console.WriteLine($"{label,-26} next request, no SET: setting = '{seen}', {rows:N0} rows visible");
    }
    NpgsqlConnection.ClearPool(new NpgsqlConnection(cs));
}
await using (var second = Request(App + NoReset, tenant2, interceptor: true))
{
    var seen = await second.Db.Database.SqlQueryRaw<string>("SELECT current_setting('app.tenant_id', true) AS \"Value\"").SingleAsync();
    Console.WriteLine($"{"with the interceptor",-26} next request as tenant 2: setting = '{seen}', {await second.Db.Invoices.IgnoreQueryFilters().CountAsync():N0} rows visible");
}
NpgsqlConnection.ClearPool(new NpgsqlConnection(App + NoReset));

Section("8. Plans: the policy is a predicate like any other");
await Explain(App, tenant1, """
    SELECT id, number, amount
    FROM invoices
    ORDER BY created_at DESC
    LIMIT 20
    """);
await Explain(App, tenant1, $"""
    SELECT id, number, amount
    FROM invoices
    WHERE tenant_id = '{tenant1}'
    ORDER BY created_at DESC
    LIMIT 20
    """);

Section("9. Plans: a predicate the planner cannot push below the policy");
await Explain(App, tenant1, """
    SELECT id, number, amount
    FROM invoices
    WHERE number LIKE 'INV-00000%'
    """);
await Exec(Admin, "ALTER TABLE invoices DISABLE ROW LEVEL SECURITY");
Console.WriteLine("-- same query with row-level security disabled --");
await Explain(App, tenant1, $"""
    SELECT id, number, amount
    FROM invoices
    WHERE number LIKE 'INV-00000%' AND tenant_id = '{tenant1}'
    """);
await Exec(Admin, "ALTER TABLE invoices ENABLE ROW LEVEL SECURITY");

Section("10. A composite index does not rescue LIKE, a leakproof operator does");
Console.WriteLine(await Scalar(Admin, """
    SELECT string_agg(p.proname || ' leakproof=' || p.proleakproof, ', ' ORDER BY p.proname)
    FROM pg_proc p WHERE p.proname IN ('textlike', 'starts_with')
    """));
await Exec(Admin, "CREATE INDEX ix_invoices_tenant_number ON invoices (tenant_id, number text_pattern_ops)");
await Exec(Admin, "ANALYZE invoices");
Console.WriteLine("-- LIKE again, now with an index on (tenant_id, number text_pattern_ops) --");
await Explain(App, tenant1, """
    SELECT id, number, amount
    FROM invoices
    WHERE number LIKE 'INV-00000%'
    """);
Console.WriteLine("-- the same prefix through the leakproof ^@ operator --");
await Explain(App, tenant1, """
    SELECT id, number, amount
    FROM invoices
    WHERE number ^@ 'INV-00000'
    """);
await using (var scope = Request(App, tenant1, interceptor: true))
{
    var starts = scope.Db.Invoices.Where(i => i.Number.StartsWith("INV-00000"));
    Console.WriteLine("-- what EF Core generates for StartsWith --");
    Console.WriteLine(starts.ToQueryString().Split('\n').Last().Trim());
    Console.WriteLine($"rows: {await starts.CountAsync()}");
}
await Exec(Admin, "DROP INDEX ix_invoices_tenant_number");

Section("11. What FORCE costs the owner during maintenance");
// pg_dump runs SET row_security = off and then COPY ... TO stdout. Both steps, as the owner:
foreach (var (label, sql) in new[]
{
    ("SET row_security = off (pg_dump default)", "SET row_security = off; SELECT count(*) FROM invoices"),
    ("migration backfill, no tenant set", "UPDATE invoices SET status = status WHERE number = 'INV-000001'"),
    ("COPY FROM (Npgsql binary import)", "COPY invoices FROM STDIN")
})
{
    try
    {
        await using var connection = new NpgsqlConnection(Owner);
        await connection.OpenAsync();
        var affected = await new NpgsqlCommand(sql, connection).ExecuteNonQueryAsync();
        Console.WriteLine($"{label,-41}: {affected} rows");
    }
    catch (PostgresException ex)
    {
        Console.WriteLine($"{label,-41}: {ex.SqlState} {ex.MessageText}");
    }
}
await using (var connection = new NpgsqlConnection(Owner))
{
    // What pg_dump --enable-row-security does: the policy stays on, so the dump succeeds and is empty.
    await connection.OpenAsync();
    var lines = 0;
    await using (var reader = await connection.BeginTextExportAsync("COPY invoices TO STDOUT"))
        while (await reader.ReadLineAsync() is not null) lines++;
    Console.WriteLine($"{"COPY TO with the policy on (dump)",-41}: {lines} rows exported of 1,000,000");
}

Section("12. Cost of set_config on every open");
var someId = (Guid)(await Scalar(Admin, $"SELECT id FROM invoices WHERE tenant_id = '{tenant1}' LIMIT 1"))!;
foreach (var withInterceptor in new[] { true, false, true, false })
{
    var sw = Stopwatch.StartNew();
    const int n = 500;
    for (var i = 0; i < n; i++)
    {
        await using var scope = Request(App, tenant1, withInterceptor);
        await scope.Db.Invoices.IgnoreQueryFilters().SingleOrDefaultAsync(x => x.Id == someId);
    }
    Console.WriteLine($"{n} requests, interceptor {(withInterceptor ? "on " : "off")}: {sw.Elapsed.TotalMilliseconds / n:F3} ms per request");
}

// ---- helpers ----

static void Section(string title) => Console.WriteLine($"\n== {title}");

static string Distinct(IEnumerable<Invoice> invoices) =>
    string.Join(", ", invoices.Select(i => i.TenantId.ToString()[^2..]).Distinct());

static string FirstSentence(string message) => message.Split(". ")[0] + ".";

static async Task Exec(string cs, string sql)
{
    await using var connection = new NpgsqlConnection(cs);
    await connection.OpenAsync();
    await new NpgsqlCommand(sql, connection).ExecuteNonQueryAsync();
}

static async Task<object?> Scalar(string cs, string sql)
{
    await using var connection = new NpgsqlConnection(cs);
    await connection.OpenAsync();
    return await new NpgsqlCommand(sql, connection).ExecuteScalarAsync();
}

static async Task Explain(string cs, Guid tenantId, string sql)
{
    await using var connection = new NpgsqlConnection(cs);
    await connection.OpenAsync();
    await using (var set = new NpgsqlCommand("SELECT set_config('app.tenant_id', @t, false)", connection))
    {
        set.Parameters.AddWithValue("t", tenantId.ToString());
        await set.ExecuteNonQueryAsync();
    }
    // warm cache, then explain
    await new NpgsqlCommand(sql, connection).ExecuteNonQueryAsync();
    await using var explain = new NpgsqlCommand("EXPLAIN (ANALYZE, COSTS OFF) " + sql, connection);
    await using var reader = await explain.ExecuteReaderAsync();
    Console.WriteLine(sql.Replace("\n", "\n").Trim());
    Console.WriteLine("---");
    while (await reader.ReadAsync())
        Console.WriteLine(reader.GetString(0));
    Console.WriteLine();
}

static RequestScope Request(string cs, Guid? tenantId, bool interceptor)
{
    var services = new ServiceCollection();
    services.AddScoped<TenantContext>();
    services.AddScoped<TenantConnectionInterceptor>();
    services.AddDbContext<AppDbContext>((sp, options) =>
    {
        options.UseNpgsql(cs);
        if (interceptor)
            options.AddInterceptors(sp.GetRequiredService<TenantConnectionInterceptor>());
    });
    var provider = services.BuildServiceProvider();
    var scope = provider.CreateAsyncScope();
    scope.ServiceProvider.GetRequiredService<TenantContext>().TenantId = tenantId;
    return new RequestScope(provider, scope);
}

sealed class RequestScope(ServiceProvider provider, AsyncServiceScope scope) : IAsyncDisposable
{
    public AppDbContext Db { get; } = scope.ServiceProvider.GetRequiredService<AppDbContext>();

    public async ValueTask DisposeAsync()
    {
        await scope.DisposeAsync();
        await provider.DisposeAsync();
    }
}

  • Db.cs

    EF Core model, query filter, and connection interceptor

    Download
    Preview contents of Db.cs
    using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace TenantRls;

// One instance per request scope. Null means nothing resolved a tenant.
public sealed class TenantContext
{
    public Guid? TenantId { get; set; }
}

public sealed class Tenant
{
    public Guid Id { get; set; }
    public string Name { get; set; } = "";
}

public sealed class Invoice
{
    public Guid Id { get; set; }
    public Guid TenantId { get; set; }
    public string Number { get; set; } = "";
    public decimal Amount { get; set; }
    public string Status { get; set; } = "";
    public DateTimeOffset CreatedAt { get; set; }
}

public sealed class AppDbContext(DbContextOptions<AppDbContext> options, TenantContext tenant)
    : DbContext(options)
{
    public DbSet<Tenant> Tenants => Set<Tenant>();
    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Tenant>(b =>
        {
            b.ToTable("tenants");
            b.Property(t => t.Id).HasColumnName("id");
            b.Property(t => t.Name).HasColumnName("name");
        });

        modelBuilder.Entity<Invoice>(b =>
        {
            b.ToTable("invoices");
            b.Property(i => i.Id).HasColumnName("id");
            b.Property(i => i.TenantId).HasColumnName("tenant_id");
            b.Property(i => i.Number).HasColumnName("number");
            b.Property(i => i.Amount).HasColumnName("amount");
            b.Property(i => i.Status).HasColumnName("status");
            b.Property(i => i.CreatedAt).HasColumnName("created_at");

            // The application layer. Row-level security is the database layer underneath it.
            b.HasQueryFilter(i => i.TenantId == tenant.TenantId);
        });
    }
}

// Runs on every connection open, so the setting is in place before EF sends the first command,
// no matter which pooled physical connection EF got.
public sealed class TenantConnectionInterceptor(TenantContext tenant) : DbConnectionInterceptor
{
    private const string Sql = "SELECT set_config('app.tenant_id', @tenant, false)";

    public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
    {
        using var command = Build(connection);
        command.ExecuteNonQuery();
    }

    public override async Task ConnectionOpenedAsync(
        DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
    {
        await using var command = Build(connection);
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    private DbCommand Build(DbConnection connection)
    {
        var command = connection.CreateCommand();
        command.CommandText = Sql;
        var parameter = command.CreateParameter();
        parameter.ParameterName = "tenant";
        parameter.Value = tenant.TenantId?.ToString() ?? "";
        command.Parameters.Add(parameter);
        return command;
    }
}

  • setup.sql

    Roles, tables, 1,000,000 seed rows, and the policy

    Download
    Preview contents of setup.sql
    -- Run as the postgres superuser. Creates two roles, the schema, 1,000,000 seed rows, and the policy.
-- app_owner: runs migrations and owns the tables. app_user: what the application connects as.

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_owner') THEN
    CREATE ROLE app_owner LOGIN PASSWORD 'owner';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_user') THEN
    CREATE ROLE app_user LOGIN PASSWORD 'app';
  END IF;
END $$;

GRANT USAGE, CREATE ON SCHEMA public TO app_owner;
GRANT USAGE ON SCHEMA public TO app_user;

DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS tenants;

CREATE TABLE tenants (
  id   uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE invoices (
  id         uuid PRIMARY KEY,
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  number     text NOT NULL,
  amount     numeric(12, 2) NOT NULL,
  status     text NOT NULL,
  created_at timestamptz NOT NULL
);

ALTER TABLE tenants OWNER TO app_owner;
ALTER TABLE invoices OWNER TO app_owner;

GRANT SELECT ON tenants TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

-- 50 tenants with readable ids, 20,000 invoices each.
INSERT INTO tenants (id, name)
SELECT ('00000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, 'Tenant ' || g
FROM generate_series(1, 50) AS g;

INSERT INTO invoices (id, tenant_id, number, amount, status, created_at)
SELECT gen_random_uuid(),
       t.id,
       'INV-' || lpad(i::text, 6, '0'),
       round((random() * 1000)::numeric, 2),
       (ARRAY['Open', 'Paid', 'Overdue'])[1 + floor(random() * 3)::int],
       now() - (random() * interval '365 days')
FROM tenants AS t
CROSS JOIN generate_series(1, 20000) AS i;

CREATE INDEX ix_invoices_tenant_created ON invoices (tenant_id, created_at DESC);
CREATE INDEX ix_invoices_number ON invoices (number text_pattern_ops);

-- The policy. NULLIF turns an unset or reset setting into NULL, and NULL matches nothing.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

  • TenantRls.csproj

    .NET 10 console project and package references

    Download
    Preview contents of TenantRls.csproj
    <Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="10.0.12" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.3" />
  </ItemGroup>

  <ItemGroup>
    <None Update="setup.sql" CopyToOutputDirectory="PreserveNewest" />
  </ItemGroup>

</Project>

  • global.json

    Pins the .NET SDK version

    Download
    Preview contents of global.json
    { "sdk": { "version": "10.0.301" } }

  • docker-compose.yml

    PostgreSQL 18 container on port 5433

    Download
    Preview contents of docker-compose.yml
    services:
  postgres:
    image: postgres:18
    ports:
      - "127.0.0.1:5433:5432"
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: tenants
    healthcheck:
      test: [CMD-SHELL, pg_isready -U postgres -d tenants]
      interval: 2s
      timeout: 5s
      retries: 30

  • README.md

    Setup, commands, and scenario summaries

    Download
    Preview contents of README.md
    # Postgres row-level security with EF Core

A PostgreSQL 18 row-level security policy on an `invoices` table with 1,000,000 rows, and a .NET 10 console app that runs twelve scenarios against it. The scenarios show what the policy catches, where the tenant setting gets lost on pooled connections, and what the policy costs in query plans, maintenance work, and per-request time.

## Prerequisites

The .NET 10 SDK (`global.json` pins 10.0.301) and Docker with Compose v2.

## Run it

```sh
docker compose up -d
dotnet run -- setup
dotnet run
```

`setup` connects as the `postgres` superuser and creates the `app_owner` and `app_user` roles, the `tenants` and `invoices` tables, 50 tenants with 20,000 invoices each, and the `tenant_isolation` policy. It takes a few seconds. `dotnet run` then runs the ten scenarios and prints what you see in `output.txt`. Row counts and plan shapes should match, timings will not.

The container listens on `127.0.0.1:5433`, so it does not conflict with a PostgreSQL instance on the default port.

## Scenarios

1. Two layers: the EF query filter and the policy. `IgnoreQueryFilters()` as `app_user` still sees only the current tenant's 20,000 rows.
2. Who bypasses the policy. The table owner sees every row until `FORCE ROW LEVEL SECURITY` is on, and the superuser always does.
3. Writes: attach a stub for another tenant's invoice. The tracked `UPDATE` and an `ExecuteUpdate` by id both affect zero rows.
4. Writes: insert a row for another tenant. The `INSERT` fails with `42501` because of the `WITH CHECK` clause.
5. No tenant resolved. An empty setting matches nothing, so every query returns zero rows.
6. `SET` at the start of the request, then let EF open and close connections. The setting only survives on a connection EF keeps open.
7. What the next request sees on the same physical connection. The default connection reset clears the setting, `No Reset On Close=true` leaks the previous tenant, and the interceptor overwrites it on every open.
8. Plans: the policy is a predicate like any other. The policy uses the `(tenant_id, created_at)` index with the same buffer count as an explicit `WHERE tenant_id = ...`.
9. Plans: a predicate the planner cannot push below the policy. `LIKE` is not leakproof, so it runs after the policy scan over 20,000 rows instead of combining two indexes.
10. A composite index does not rescue `LIKE`, a leakproof operator does. Adding `(tenant_id, number text_pattern_ops)` changes nothing, while the same prefix through `^@` puts both columns in the `Index Cond`. EF Core translates `StartsWith` to `LIKE`.
11. What `FORCE` costs the owner during maintenance. `pg_dump`'s default fails, a dump with the policy on exports zero rows, a backfill reports zero rows changed, and `COPY FROM` is rejected.
12. Cost of `set_config` on every open. The interceptor adds about half a millisecond per request in `output.txt`.

## Notes

The role names and passwords in `setup.sql` and `Program.cs` are demo values. The superuser connection runs `setup`, and a few scenarios use it to toggle the policy or to read a row for comparison. Everything that stands in for application code connects as `app_user`.

  • output.txt

    Output from a full run of the ten scenarios

    Download
    Preview contents of output.txt
    
== 1. Two layers: the EF query filter and the policy
filtered read as tenant 1: 3 rows, tenants seen: 01
IgnoreQueryFilters() as app_user: 20,000 rows (table has 1,000,000)

== 2. Who bypasses the policy
app_owner, ENABLE only: 1,000,000 rows
app_owner, FORCE:       20,000 rows
postgres (superuser):   1,000,000 rows

== 3. Writes: attach a stub for another tenant's invoice
tracked UPDATE: DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded.
ExecuteUpdate by id only: 0 rows affected
stored status of that invoice: Paid

== 4. Writes: insert a row for another tenant
INSERT: 42501 new row violates row-level security policy for table "invoices"

== 5. No tenant resolved
current_setting in the session: ''
filtered read: 0 rows, IgnoreQueryFilters(): 0 rows

== 6. SET at the start of the request, then let EF open and close connections
SET, then a query on a pooled connection: 0 rows
SET on a connection EF keeps open:       20,000 rows

== 7. What the next request sees on the same physical connection
default (reset on close)   next request, no SET: setting = '', 0 rows visible
No Reset On Close=true     next request, no SET: setting = '00000000-0000-0000-0000-000000000001', 20,000 rows visible
with the interceptor       next request as tenant 2: setting = '00000000-0000-0000-0000-000000000002', 20,000 rows visible

== 8. Plans: the policy is a predicate like any other
SELECT id, number, amount
FROM invoices
ORDER BY created_at DESC
LIMIT 20
---
Limit (actual time=0.008..0.016 rows=20.00 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using ix_invoices_tenant_created on invoices (actual time=0.008..0.015 rows=20.00 loops=1)
        Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid)
        Index Searches: 1
        Buffers: shared hit=23
Planning Time: 0.042 ms
Execution Time: 0.029 ms

SELECT id, number, amount
FROM invoices
WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
ORDER BY created_at DESC
LIMIT 20
---
Limit (actual time=0.008..0.016 rows=20.00 loops=1)
  Buffers: shared hit=23
  ->  Result (actual time=0.008..0.014 rows=20.00 loops=1)
        One-Time Filter: ((NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid = '00000000-0000-0000-0000-000000000001'::uuid)
        Buffers: shared hit=23
        ->  Index Scan using ix_invoices_tenant_created on invoices (actual time=0.006..0.011 rows=20.00 loops=1)
              Index Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid)
              Index Searches: 1
              Buffers: shared hit=23
Planning Time: 0.038 ms
Execution Time: 0.024 ms


== 9. Plans: a predicate the planner cannot push below the policy
SELECT id, number, amount
FROM invoices
WHERE number LIKE 'INV-00000%'
---
Bitmap Heap Scan on invoices (actual time=1.668..5.757 rows=9.00 loops=1)
  Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid)
  Filter: (number ~~ 'INV-00000%'::text)
  Rows Removed by Filter: 19991
  Heap Blocks: exact=11647
  Buffers: shared hit=11748
  ->  Bitmap Index Scan on ix_invoices_tenant_created (actual time=0.859..0.860 rows=20000.00 loops=1)
        Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid)
        Index Searches: 1
        Buffers: shared hit=101
Planning Time: 0.062 ms
Execution Time: 5.768 ms

-- same query with row-level security disabled --
SELECT id, number, amount
FROM invoices
WHERE number LIKE 'INV-00000%' AND tenant_id = '00000000-0000-0000-0000-000000000001'
---
Bitmap Heap Scan on invoices (actual time=0.646..0.648 rows=9.00 loops=1)
  Recheck Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid)
  Filter: (number ~~ 'INV-00000%'::text)
  Heap Blocks: exact=5
  Buffers: shared hit=109
  ->  BitmapAnd (actual time=0.641..0.641 rows=0.00 loops=1)
        Buffers: shared hit=104
        ->  Bitmap Index Scan on ix_invoices_number (actual time=0.008..0.008 rows=450.00 loops=1)
              Index Cond: ((number ~>=~ 'INV-00000'::text) AND (number ~<~ 'INV-00001'::text))
              Index Searches: 1
              Buffers: shared hit=3
        ->  Bitmap Index Scan on ix_invoices_tenant_created (actual time=0.632..0.632 rows=20000.00 loops=1)
              Index Cond: (tenant_id = '00000000-0000-0000-0000-000000000001'::uuid)
              Index Searches: 1
              Buffers: shared hit=101
Planning Time: 0.049 ms
Execution Time: 0.657 ms


== 10. A composite index does not rescue LIKE, a leakproof operator does
starts_with leakproof=true, textlike leakproof=false
-- LIKE again, now with an index on (tenant_id, number text_pattern_ops) --
SELECT id, number, amount
FROM invoices
WHERE number LIKE 'INV-00000%'
---
Bitmap Heap Scan on invoices (actual time=1.482..5.490 rows=9.00 loops=1)
  Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid)
  Filter: (number ~~ 'INV-00000%'::text)
  Rows Removed by Filter: 19991
  Heap Blocks: exact=11647
  Buffers: shared hit=11748
  ->  Bitmap Index Scan on ix_invoices_tenant_created (actual time=0.654..0.654 rows=20000.00 loops=1)
        Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid)
        Index Searches: 1
        Buffers: shared hit=101
Planning Time: 0.072 ms
Execution Time: 5.502 ms

-- the same prefix through the leakproof ^@ operator --
SELECT id, number, amount
FROM invoices
WHERE number ^@ 'INV-00000'
---
Bitmap Heap Scan on invoices (actual time=0.011..0.013 rows=9.00 loops=1)
  Recheck Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid)
  Filter: (number ^@ 'INV-00000'::text)
  Heap Blocks: exact=5
  Buffers: shared hit=8
  ->  Bitmap Index Scan on ix_invoices_tenant_number (actual time=0.007..0.007 rows=9.00 loops=1)
        Index Cond: ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::uuid) AND (number ~>=~ 'INV-00000'::text) AND (number ~<~ 'INV-00001'::text))
        Index Searches: 1
        Buffers: shared hit=3
Planning Time: 0.065 ms
Execution Time: 0.021 ms

-- what EF Core generates for StartsWith --
WHERE i.tenant_id = @ef_filter__TenantId AND i.number LIKE 'INV-00000%'
rows: 9

== 11. What FORCE costs the owner during maintenance
SET row_security = off (pg_dump default) : 42501 query would be affected by row-level security policy for table "invoices"
migration backfill, no tenant set        : 0 rows
COPY FROM (Npgsql binary import)         : 0A000 COPY FROM not supported with row-level security
COPY TO with the policy on (dump)        : 0 rows exported of 1,000,000

== 12. Cost of set_config on every open
500 requests, interceptor on : 1.293 ms per request
500 requests, interceptor off: 0.844 ms per request
500 requests, interceptor on : 1.214 ms per request
500 requests, interceptor off: 0.817 ms per request

2. Run it

Open a terminal in that folder and run:

docker compose up -d
dotnet run -- setup
dotnet run

setup connects as the superuser and creates the roles, the tables, the seed data, and the policy. It takes a few seconds. The seed is 50 tenants with 20,000 invoices each, 1,000,000 rows in total.

The second dotnet run prints the twelve scenarios. Compare your output with output.txt. Row counts and plan shapes should match, timings will not. The container listens on 127.0.0.1:5433, so it does not conflict with a PostgreSQL instance on the default port.

3. What each scenario shows

  1. Two layers: the EF query filter and the policy. The query filter returns the current tenant, and IgnoreQueryFilters() as app_user still sees only that tenant's 20,000 rows out of 1,000,000.
  2. Who bypasses the policy. The table owner sees every row until FORCE ROW LEVEL SECURITY is on, and the superuser always does.
  3. Writes: attach a stub for another tenant's invoice. The tracked UPDATE and an ExecuteUpdate by id both affect zero rows, and the stored status does not change.
  4. Writes: insert a row for another tenant. The INSERT fails with error 42501 because the WITH CHECK clause rejects the row.
  5. No tenant resolved. An empty setting matches nothing, so every query returns zero rows.
  6. SET at the start of the request, then let EF open and close connections. The setting is gone by the next query on a pooled connection and only survives on a connection EF keeps open.
  7. What the next request sees on the same physical connection. The default connection reset clears the setting, No Reset On Close=true leaks the previous tenant, and the interceptor overwrites it on every open.
  8. Plans: the policy is a predicate like any other. The policy uses the (tenant_id, created_at) index and reads the same 23 buffers as an explicit WHERE tenant_id clause.
  9. Plans: a predicate the planner cannot push below the policy. LIKE is not leakproof, so it runs after the policy scan over 20,000 rows and reads 11,749 buffers, where the same query without the policy combines two indexes and reads 109.
  10. A composite index does not rescue LIKE, a leakproof operator does. Adding (tenant_id, number text_pattern_ops) leaves the LIKE plan unchanged, while the same prefix through the leakproof ^@ operator puts both columns in the Index Cond and reads 5 heap pages instead of 11,646. EF Core translates StartsWith to LIKE.
  11. What FORCE costs the owner during maintenance. pg_dump's default setting fails for the owner, a dump taken with the policy on exports 0 rows of 1,000,000, a migration backfill reports 0 rows changed, and COPY FROM is rejected with 0A000.
  12. Cost of set_config on every open. The interceptor adds about half a millisecond per request in output.txt.

4. Change something

Point the App connection string at the postgres superuser and run the scenarios again. The stub update goes through, the insert succeeds, and every count comes back as 1,000,000, because a superuser bypasses the policy no matter what the table says.

Remove the AddInterceptors line from the Request helper in Program.cs and rerun. Nothing sets the tenant anymore, so scenario 1 returns zero rows from both reads, and the last line of scenario 7 shows an empty setting and zero rows. The No Reset On Close line still leaks, because that path sets the tenant with a raw SET.

When you are done, docker compose down --volumes removes the container and its data.