ENGINEERING

← All posts · June 4, 2026 · By Brian Vitko

Shipping Postgres Row-Level Security in a multi-tenant .NET app (and the footguns we hit)

We just flipped Row-Level Security live in production on RFF - a multi-tenant Windows fleet management SaaS running on ASP.NET Core + EF Core + Postgres. Here’s the full story: why we did it, how the implementation works, and the three footguns that would have been silent production outages if we hadn’t caught them first.

Silent production outages are a bit of a genre for us. A deploy once wiped our Data Protection key ring and broke every user’s MFA without a single error in the logs - so when I say “would have been silent,” I mean the kind we’ve actually shipped and learned from.

The problem: EF query filters aren’t enough

Multi-tenant apps in EF Core typically use a global query filter - something like:

modelBuilder.Entity<Target>()
    .HasQueryFilter(t => t.TenantId == _currentTenantId);

This works. We had it. But it has a soft underbelly: it’s enforced in the ORM layer, not the database. If you accidentally call .IgnoreQueryFilters() on the wrong query, write a raw SQL string, use a stored procedure, or make a mistake during a refactor, the filter evaporates silently. The database doesn’t know it should have been there.

For a pre-revenue side project this is fine. For a tool that runs arbitrary PowerShell on Windows machines on behalf of paying customers, we wanted the database to be an independent enforcement point that doesn’t care what the ORM does.

Postgres RLS is exactly that. Once CREATE POLICY is in place and FORCE ROW LEVEL SECURITY is set on a table, the database enforces tenant isolation unconditionally - the application layer is layered defense, not the last line.

The shape of the implementation

Our approach has four pieces:

  1. A per-request GUC (SET LOCAL app.current_tenant = {tenantId}) set inside a transaction for every HTTP request, and per-invocation for SignalR hub methods
  2. Explicit scopes in AppDbContext - EnterTenantScopeAsync and EnterGodModeScopeAsync - that set the GUC and own the transaction lifetime
  3. A tripwire interceptor (TenantGucTripwireInterceptor) that throws loud on any query that touches a tenant-owned table without an active GUC scope
  4. rls.sql - the actual ENABLE/FORCE/CREATE POLICY DDL, applied manually out-of-chain from the normal deploy pipeline

Let’s go through each.

The middleware: SET LOCAL per request

Every /api/* request goes through TenantGucMiddleware:

public async Task Invoke(HttpContext ctx, AppDbContext db, ITenantContext tenant)
{
    if (!_enabled) { await _next(ctx); return; }

    await using var scope = tenant.TenantId is Guid t
        ? await db.EnterTenantScopeAsync(t)
        : await db.EnterGodModeScopeAsync("platform-admin (no tenant claim)");

    await _next(ctx);

    if (ctx.Response.StatusCode < 400)
        await scope.CommitAsync();
}

Simple. Tenant requests get a tenant scope; platform-admin requests (which have no tenantId claim) get god-mode. If the response fails (4xx, 5xx, unhandled exception), the transaction doesn’t commit - partial writes can’t persist.

The EnterTenantScopeAsync path opens a transaction and runs:

await Database.ExecuteSqlRawAsync(
    "SELECT set_config('app.current_tenant', {0}, true)",
    new object[] { tenantId.ToString() });

The true argument to set_config means LOCAL - the GUC resets when the transaction commits. This is the correct choice under Pgbouncer/connection pooling: a session-level SET would outlive the request and potentially leak onto the next request on the reused connection.

Footgun #1: the god-mode sentinel must NOT be empty string

This was the most subtle find, caught by integration tests before it reached production.

Our RLS policy looks like this:

CREATE POLICY tenant_isolation ON "Targets"
    USING (
        current_setting('app.current_tenant', true) = '<sentinel>'
        OR "TenantId"::text = current_setting('app.current_tenant', true)
    );

The obvious design for “god-mode” is: set the GUC to some special non-empty sentinel value when you want to see all rows. Why not empty string?

The answer is in how Postgres handles custom placeholder GUCs. When you SET LOCAL app.current_tenant = 'abc123' and the transaction commits, the GUC doesn’t revert to NULL. It reverts to the placeholder’s reset value, which is '' (empty string).

So on a pooled/reused connection where no scope has been entered yet, the GUC is '' - not NULL, not missing, just an empty string.

If we had designed god-mode as current_setting(...) = '', then any request that hit the database on a reused connection without entering a GUC scope would accidentally see all tenants. Silent fail-OPEN.

By making the sentinel a non-empty string, both '' and NULL fail the policy check and return zero rows - fail-CLOSED. A forgotten scope is visible (empty result) rather than dangerous (all-tenant leak). The cross-tenant test matrix has an explicit case for this - SetLocal_IsPerTransaction_NoSessionLeak - that verifies a committed SET LOCAL resets to empty string and doesn’t survive to the next request.

The constant is defined once in C# and copied literally into rls.sql. We made sure they match.

The tripwire: making the invisible visible

Without enforcement, a forgotten GUC scope in the code returns zero rows from tenant-owned tables. That looks like “no data found” - it might not even surface as a bug immediately. The request succeeds with an empty response. The job list shows empty. You might not notice until a customer complains.

The TenantGucTripwireInterceptor converts this from an invisible fail-to-empty into a loud exception:

public sealed class TenantGucTripwireInterceptor : DbCommandInterceptor
{
    private static void Check(DbCommand command, CommandEventData eventData)
    {
        if (!RlsRuntime.Enabled) return;
        if (eventData.Context is not AppDbContext ctx || ctx.TenantGucActive) return;
        if (eventData.CommandSource == CommandSource.Migrations) return;
        if (command.CommandText.Contains("set_config('app.current_tenant'", ...)) return;

        foreach (var table in RlsRuntime.TenantTables)
        {
            if (command.CommandText.Contains('"' + table + '"', StringComparison.Ordinal))
                throw new InvalidOperationException(
                    $"RLS tripwire: query on \"{table}\" without a GUC scope. " +
                    "Wrap in EnterTenantScopeAsync or EnterGodModeScopeAsync.");
        }
    }
}

It fires on every command execution. If TenantGucActive isn’t set and the SQL names a tenant-owned table (quote-anchored to avoid substring false positives), it throws.

This is a development and staging guard, not a production safety net - by the time Rls:Enabled=true in production, all paths must already be covered. The tripwire’s job is to make coverage gaps loudly visible during the GUC coverage pass, so nothing sneaks through.

Footgun #2: the pre-middleware auth handler

SignalR hub methods bypass TenantGucMiddleware (the middleware wraps HTTP requests, not SignalR frames). That’s handled by entering a scope per hub invocation from the connection’s claims. But we missed a trickier one: the authentication pipeline itself.

Our ApiKeyAuthHandler runs before the middleware stack - it’s in the auth pipeline. It needs to resolve an API key to an agent row to extract the tenantId claim. But at that point, no tenant scope exists yet, and we can’t enter one because the tenant is what we’re trying to find.

public class ApiKeyAuthHandler : AuthenticationHandler<ApiKeyAuthOptions>
{
    protected override async Task<AuthenticateResult> HandleAuthenticateAsync()
    {
        // RLS runs before middleware. Tenant is unknown here.
        // Without god-mode: Agents table returns 0 rows → every API key is "invalid".
        await using var guc = await _db.EnterGodModeScopeAsync(
            "ApiKeyAuthHandler: resolve API key before tenant is known");

        var agent = await _db.Agents.FirstOrDefaultAsync(a => a.ApiKeyHash == keyHash);
        await guc.CommitAsync();

        if (agent == null)
            return AuthenticateResult.Fail("Invalid API key.");

        // Stamp tenantId in claims; middleware picks it up for the rest of the request.
        // ...
    }
}

The tripwire caught this when we first ran the tests with Rls:Enabled=true - every agent poll returned 401 because the key lookup returned zero rows. The fix is correct: enter god-mode, find the agent, extract the tenant, let the middleware scope the rest. The auth handler is the bootstrap step; once it stamps the claim, the rest of the request runs tenant-scoped.

Footgun #3: do NOT put RLS DDL in an EF migration

This was the most important architectural decision: rls.sql is applied manually, out-of-chain, and is explicitly never an EF migration.

Here’s why. Our Program.cs calls await db.Database.MigrateAsync() on every startup. Render auto-deploys whenever code merges to main. That means:

  1. You add a migration containing ENABLE ROW LEVEL SECURITY; FORCE ROW LEVEL SECURITY; CREATE POLICY ...
  2. The migration merges to main
  3. Render deploys; MigrateAsync() runs on startup
  4. The policies are now enforced in the database
  5. But Rls:Enabled is still false in your config - the middleware and background services haven’t wrapped their DB work yet
  6. Every query on a tenant-owned table sees current_setting('app.current_tenant', true) = ''
  7. The policy matches zero rows
  8. All 14 tenant tables fail-to-empty simultaneously. Full outage.

The Rls:Enabled config gate controls whether the application sets the GUC. It doesn’t control whether the database enforces the policy. Both have to go live together, in a coordinated window.

So rls.sql is a standalone file - idempotent, wrapped in a single BEGIN/COMMIT, applied by hand via psql at the moment we were ready:

psql "$DATABASE_URL" < platform/src/RFF.Api/Data/rls.sql

And rls_rollback.sql is its pair - one operation, atomic, no redeploy needed if something goes wrong:

BEGIN;
DO $$ ... DISABLE ROW LEVEL SECURITY on all 14 tables ... $$;
COMMIT;

EF Core owns the schema (it creates the TenantId columns via migrations). It doesn’t own RLS - that’s a security policy that lives out-of-band by design.

The policy itself

The DDL is straightforward. The same pattern, applied idempotently to all 14 tenant-owned tables:

ALTER TABLE "Targets" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Targets" FORCE  ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation ON "Targets";
CREATE POLICY tenant_isolation ON "Targets"
    USING (
        current_setting('app.current_tenant', true) = '<sentinel>'
        OR "TenantId"::text = current_setting('app.current_tenant', true)
    );

FORCE is critical - without it, Postgres skips RLS for the table owner. If your app connects as the owner role (common in development), ENABLE alone does nothing for you. FORCE applies the policy unconditionally.

The USING clause covers reads. For writes (INSERT/UPDATE), Postgres applies USING as the row-visibility filter by default - a tenant-scoped connection cannot see (and therefore cannot update) rows it doesn’t own. A god-mode connection can write to any tenant. No separate WITH CHECK needed for our model.

The rollout: a three-phase gate

We shipped this in stages:

  1. Phase A (code merge, gated off): All the middleware, scopes, tripwire, and hub scoping merged to main with Rls:Enabled=false. The entire apparatus was a no-op in production. rls.sql was not yet applied.

  2. GUC coverage pass: With Rls:Enabled=true locally and the tripwire active, we ran the cross-tenant test matrix and walked every background service, hub, and auth handler until the tripwire had nothing left to complain about. 13 hosted services, 2 SignalR hubs, 1 auth handler.

  3. The flip: Applied rls.sql via psql (one transaction, took ~50ms). Deployed Rls:Enabled=true in the Render environment config. Watched the logs for tripwire exceptions - none.

The cross-tenant test matrix is 8 cases run against a real Postgres instance (Testcontainers) as a non-superuser role. That last part matters: if you run your RLS tests as postgres (superuser), BYPASSRLS is set and your tests pass while proving nothing.

Was it worth it?

Yes, for the reasons that motivated it. The EF query filter still runs - it’s the first line of defense and catches most mistakes at query-build time. RLS is the backstop at the database layer, independent of the ORM, independent of the application, enforced even on direct psql connections with the app role.

The implementation is about 400 lines across 5 files (middleware, scopes, tripwire, the runtime gate, and the SQL). The test matrix is another 200 lines. That’s a small surface area for a guarantee that cross-tenant data leakage is enforced at the database level. It’s one layer of the broader posture we write up here.

The footguns - the empty-string sentinel, the auth-pipeline seam, the migration trap - are the parts worth knowing about. They’re the things that looked right at a glance and would have been production incidents if integration tests hadn’t caught them first.

RFF is a Windows fleet management tool - patch, deploy, audit, remote desktop, all from a browser tab, no VPN. getrff.com