Row-level security for real multi-tenant isolation

Most multi-tenant SaaS products implement isolation in the application layer: a tenant_id column on every table, and an ORM filter that adds WHERE tenant_id = ? to every query. It works — until someone writes a raw SQL query, forgets the filter on a join, or hits a code path where the filter wasn’t applied. Then suddenly Tenant A is reading Tenant B’s data, and you find out in an incident report.

We took a different approach: enforce isolation at the database, not the app.

PostgreSQL Row-Level Security

PostgreSQL has built-in row-level security policies. You declare a policy on a table that says “rows are only visible if tenant_id = current_setting('app.client_id')”, and from that moment on the database itself enforces it. The app sets the session variable on connection, and any query — ORM or raw — is filtered.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (client_id = current_setting('app.client_id')::uuid);

That’s it. Forget the filter in your application code? Database returns zero rows. Try to write to another tenant’s row? Insertion fails. The escape hatch is a SET ROLE to a privileged role — used only by background jobs that have legitimate cross-tenant needs.

Wiring it into EF Core

The tricky part is making sure every connection has app.client_id set before any query runs. We did this with a custom DbConnectionInterceptor that runs on connection open:

public class ClientRlsConnectionInterceptor : DbConnectionInterceptor
{
    public override async ValueTask<InterceptionResult> ConnectionOpenedAsync(
        DbConnection connection,
        ConnectionEndEventData eventData,
        CancellationToken cancellationToken = default)
    {
        var clientId = _currentClient.ClientId;
        if (clientId is not null)
        {
            await using var cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT set_config('app.client_id', @id, false)";
            cmd.Parameters.Add(new NpgsqlParameter("id", clientId.ToString()));
            await cmd.ExecuteNonQueryAsync(cancellationToken);
        }
        return await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }
}

The current client is resolved from the JWT in the request pipeline and flows through DI as a scoped service. By the time EF actually opens a connection (lazy, often inside the first query), the interceptor sets the session variable.

What broke when we shipped it

A few things we didn’t anticipate:

1. Migrations needed a privileged role. EF migrations run schema DDL, which RLS blocks if you don’t explicitly grant BYPASSRLS. We use a separate tenavora_migrator role for the migrate container.

2. Connection pooling matters. Npgsql pools connections, and the session variable persists across reuses. We had to make sure the interceptor runs on every connection acquisition, not just the first.

3. Health checks. The /health/ready endpoint runs db.CanConnectAsync, which opens a connection with no client context — RLS happily returns empty results. We special-cased it to a BYPASSRLS connection.

The result: even if someone writes raw SQL and forgets a WHERE clause, the database refuses to leak. That’s worth the upfront complexity.

When NOT to use RLS

If your isolation needs are simple and you have a small team that can discipline themselves with consistent ORM usage, app-layer filtering is fine. RLS is the right call when:

• You have raw SQL anywhere (analytics queries, reporting)
• You need defense-in-depth for compliance audits
• You have multiple engineers and the chance of someone forgetting a filter is non-zero

For us, all three were true. The piece-of-mind has been worth the investment.