Row-Level Security, and the Bug That Fails by Disappearing

Clearspar Heli is a concept build — a prototype day-of-operations platform for a heli-ski/heli-charter operation, not a real company, not a certified safety system, and every aircraft and guest in it is sample data. But one of the design decisions underneath it is real enough to be worth writing up on its own: how the app keeps one operator's data from ever touching another's, and a bug class that decision kept producing during the build.

The premise of the app is that multiple operators could share one deployment. Each one needs guaranteed isolation — an ops manager at one organization should never be able to see another organization's manifests, guest medical flags, aircraft positions, or duty logs, full stop, with no code path that can accidentally leak across the boundary. That's not a “remember to filter by org_id” kind of requirement. It's the kind of requirement where you want the database itself refusing to hand back rows that don't belong to you, regardless of what the application code above it does or forgets to do.

So isolation is enforced with Postgres Row-Level Security, not application-level filtering. RLS checks a per-request session variable — a Postgres GUC, app.current_org_id — that gets set from the authenticated user's real organization at the start of each request. The query itself doesn't need a WHERE org_id = ... clause sprinkled everywhere; the database enforces the boundary structurally. That's the appeal of RLS over app-level filtering: you can't forget to add the filter to a new endpoint, because the filter isn't optional and isn't yours to skip.

Here's the catch, and it showed up more than once during the build. RLS needs that session variable set before it can do its job. A few background code paths — a position-tracking poller, an escalation-checking engine — queried the database without ever setting it first. Postgres RLS defaults to zero rows when the variable isn't set, rather than showing everything or throwing an error. Which is the correct default. It means the failure mode is “shows nothing” instead of “leaks another organization's data” — exactly the direction you want a security default to fail in.

But zero rows and no error also means: nothing crashes, nothing logs an exception. The poller just quietly stops writing aircraft positions. The escalation engine just quietly stops checking anything. From the outside, this doesn't look like a security bug — it looks like a missing feature, or a flaky integration, or “huh, why hasn't this table updated.” In this case it surfaced as live aircraft positions never getting recorded, caught only by noticing the absence of data that should have been there, not by any error.

That's the actual lesson from this one, and it's a little uncomfortable: a secure-by-default system can still fail in a way that's invisible, and “fails safe” and “fails invisible” can look identical from where you're standing. RLS did exactly what it was supposed to do — it protected the tenant boundary — and the cost of that protection was a silent, complete outage of a code path that nobody would think to blame on RLS. Fixing each instance meant making sure that session variable actually gets set on every code path that touches the database, not just the ones reached through a normal web request.

Nothing about this makes RLS the wrong call — a database-enforced tenant boundary is still a better bet than trusting every future endpoint to remember an application-level filter. It just means the audit doesn't stop at “does the isolation hold.” It has to include “does every code path that touches this data actually participate in the isolation mechanism in the first place,” because the failure mode for forgetting isn't an error message. It's silence.