The View Is the Wall — RLS & Policy

A solution architect asked me last week how I was scoping data access across 2,500 locations and a handful of personas. Good question — and a common one in flight maintenance programs, where a single platform ends up serving stations, technicians, and program managers spread across a large network, all of whom should see their own slice of the data and nothing else.

The answer is Postgres row-level security and policy — RLS is the mechanism, but the policy is where the actual business rule lives. It's less exotic than people expect once you stop thinking of it as a feature and start thinking of it as a wall.

Here's the shape of it.

Where trust actually starts

Before any of the schema design matters, it's worth being precise about where identity comes from, because it's not where people assume.

The front end never sets who a user is. It can't be trusted to — anything a client sends can be edited before it's sent, so if the database took its word for who's asking, the whole model would be worthless. A user authenticates against an Auth Service and gets a token back. The front end carries that token forward, but it doesn't derive identity from anything the user typed or clicked.

The back end API endpoint is where identity actually gets asserted into the database. It's already validated the token by the time it opens a query, so it has a trustworthy, server-derived user ID — and that's the only thing that ever gets set as the session variable Postgres checks:

select set_config('app.current_user_id', '123456', true);
select case_id, location_id, persona_id, status, created_at
from app.case_headers;

That's two steps, always in that order: set the identity, then query. Everything downstream — the views, the policies, the schema boundaries — assumes that variable is trustworthy, because nothing upstream of the back end ever had the chance to fake it.

One detail worth calling out on that first line: the true at the end scopes the setting to the current transaction, not the whole connection. That's a deliberate choice, not a default. A connection-scoped setting works fine today, since there's no connection pooler reusing the same physical connection across different users. But making it transaction-scoped now removes a hidden assumption — that one connection always means one user — instead of relying on it staying true forever. The cost of doing it this way is close to zero: one word, and re-issuing the line at the start of every transaction instead of once per connection. The cost of not doing it, if a pooler ever gets added later without anyone remembering this detail, is a session variable leaking from one user's transaction into another's. That's the kind of bug that doesn't announce itself — it just quietly returns the wrong data to someone, and the first anyone hears about it is a phone call, not an error log.

The ring, and who gets to touch it

The location list itself — all 2,500 stations across the network — is just reference data. One table, no security logic attached to it directly:

create table locations (
  location_id    bigint primary key,
  location_code  text not null unique,
  location_name  text,
  region         text
);

What determines who sees what isn't the table — it's a join table sitting between the user and the location:

create table auth_user (
  user_id      bigint primary key,
  email        text not null unique,
  full_name    text,
  persona_code text not null,     -- e.g. 'technician', 'program_manager'
  is_active    boolean not null default true
);

create table user_locations (
  user_id      bigint not null references auth_user(user_id),
  location_id  bigint not null references locations(location_id),
  primary key (user_id, location_id)
);

auth_user is deliberately thin — it identifies the person and their persona, nothing else. Email is what ties the row back to whatever identity provider sits in front of the app; persona_code is what the policies key off of downstream.

Worth being explicit about what's not in this table: no password, no hash, nothing to validate a login against. Authentication is somebody else's job — an identity provider, SSO, whatever sits in front of this. auth_user only answers "given this already-authenticated person, what are they scoped to see." Access, not identity verification. Conflating the two is how you end up with a credentials table you didn't mean to build and now have to secure like one.

Same pattern for personas, if a user needs to be scoped to more than one — a technician versus a program manager, say. The policy on the actual transactional table does the enforcing:

create policy location_scope on case_headers
  using (
    exists (
      select 1 from user_locations ul
      where ul.user_id = current_setting('app.current_user_id')::bigint
        and ul.location_id = case_headers.location_id
    )
  );

Postgres runs this check on every query, automatically, regardless of what client issued it. That's the part people underestimate — it doesn't matter if the request came from the app, a BI tool, or someone poking around with a SQL client. The database enforces the boundary itself. Not the application layer, not a convention everyone has to remember to follow in every code path.

Why the database, and not the API

The alternative to all of this is filtering in the API layer — every endpoint adds a where-clause built from whatever the request claims about the caller. It works, right up until it doesn't, and the way it fails is quiet.

Every new endpoint has to remember to apply the filter. Every report someone writes against the database directly — an ad hoc query, a BI dashboard, an export job — has to reimplement it from scratch, because none of that traffic goes through your API. Every service added later has to get the logic right the same way the first one did, and every refactor is a chance to drop it without anyone noticing, because a missing filter doesn't throw an error. It just returns rows it shouldn't.

That's the real cost: filtering in application code depends on every person, on every team, remembering to do the right thing, every time, forever. Data access rules become tribal knowledge scattered across a codebase instead of a rule enforced in one place.

RLS collapses that dependency to zero. The rule lives on the table, once. It doesn't matter how many services get built against this database, how many teams touch it, or what client issues the query — Postgres enforces the boundary at the engine level, before a row ever leaves the database. You're not trusting every caller to filter correctly. You're not trusting anyone to remember anything. The wall is structural, not procedural.

There's a more personal reason to want it at this layer too: when it fails, you want it to fail closed, not open. With RLS, if no policy matches, the default is zero rows — not an error, not a partial result, just nothing. The worst-case failure mode is a user sees less than they should and files a ticket. Compare that to filtering built in application code, where the worst-case failure mode is a missing clause silently returning everyone's data to everyone, and the first anyone hears about it is a call from the business at 2am asking why a technician in one region just saw case data from a station across the country. One of those failure modes is an inconvenience. The other is an incident report.

Roles matter here too, and they should be deliberately unequal — but deliberately unequal doesn't mean routing more people to the admin account. Internal staff who legitimately need broader visibility — a program manager overseeing multiple stations, someone in corporate reporting — still log in through the application like everyone else. They just get a broader user_locations set, or a persona with wider scope, than a single-station technician. That's still RLS doing the work; it's just a wider policy match, not a bypass.

BYPASSRLS is reserved for something narrower and rarer: the handful of actual database roles used for pipeline jobs, migrations, or hands-on data correction — not a shortcut for staff whose job happens to require seeing more of the business. That list should be short enough to name from memory and short enough to review in a sentence, not a growing set of just-in-case grants. Every role added to it is a role that skips the policy layer entirely, so it gets treated the same way you'd treat production database credentials — tightly held, reviewed periodically, and never handed out by default. The moment "I need broader access" turns into "give them the admin login," the whole model breaks, because now you're back to trusting people to only use that power responsibly instead of trusting the policy to define what they're allowed to see. Broader access should still mean a broader policy, scoped and auditable — never a different door entirely.

Two schemas that never touch, and one that sits between them

Underneath app, there are two source schemas, and it's worth being precise about how they relate, because it's not the strict pipeline it might look like at first.

doc_parser_output and ingest are adjacent, related domains — but structurally isolated from each other. Nothing in doc_parser_output references ingest, and nothing in ingest reaches back into doc_parser_output. ingest isn't simply "what doc_parser_output becomes after processing" — it's broader than that. It holds validated, parsed results, but also independent datasets that never touched a parser at all. So the relationship isn't upstream-to-downstream in a single line; it's two separate domains that happen to overlap in subject matter.

The only place they're allowed to meet is app. Views in app can source from either schema independently, or join across both — that's the only place a relationship between the two domains gets expressed at all. Nothing about that is arbitrary. doc_parser_output is closer to raw — the direct output of parsing and extraction, not yet fully trusted. ingest is the modeled, validated layer. Keeping them apart protects ingest's guarantees from doc_parser_output's lack of them, and it means every cross-domain relationship in the system is enumerable in one place: the views in app.

It also leaves a door open without walking through it yet. Because the two schemas are kept separate rather than merged on the way in, it stays possible to compare what the parser originally extracted against what's since been validated in ingest — useful for catching drift, especially once late-arriving facts are in play. That's not a feature that exists today. It's a capability the separation happens to preserve, in case it's ever needed.

app remains the only schema an application role ever touches. RLS policies — or the view's own where-clause — enforce location and persona scope uniformly on the way out, whether a given view is sourcing from one schema or joining both. Nobody's granted more than the layer they actually operate in.

Hiding a schema behind a view

The part that surprised me most working through this: how do you let a user query data without letting them anywhere near the raw tables it came from?

Turns out the answer is a view, and a default Postgres behavior that most people never think about. Say the transactional data is maintenance case records, fed by an ingest pipeline you don't want the application layer anywhere near:

create view app.case_headers as
select case_id, location_id, persona_id, status, created_at
from ingest.case_headers_raw;

Revoke everything on the ingest schema from the user role. Grant only USAGE on app and SELECT on its views. The user queries app.case_headers and gets data back — but they've never touched ingest.case_headers_raw directly, and they can't.

Why it works: a Postgres view runs with the privileges of whoever created it, not whoever's querying it — unless you explicitly flip security_invoker to true, which is a newer option most people don't know exists and definitely shouldn't set here. Leave it at the default. The view owner's grants carry the query through to the underlying table. The user gets the wall; the view is the door in it.

Check it isn't accidentally flipped:

select table_schema, table_name, security_invoker
from information_schema.views
where table_schema = 'app';

If it comes back NO, or the row's just not there, you're fine.

One thing worth knowing before you go looking for bugs that aren't there: your SQL client will still list the underlying tables from other schemas in its object tree. That's catalog browsing, not data access — it doesn't require SELECT. The real test isn't what shows up in the UI, it's what happens when you actually run the query. Table denies. View returns data. That's the pattern working exactly as designed.

Two things I didn't need yet

Two questions came up while building this that both had the same answer, even though they're about completely different parts of the stack.

The first was PgBouncer — connection pooling. There's a real interaction between pooling and the session-variable pattern this whole design depends on: pool in transaction mode, and a session-level variable can leak across clients between transactions, because the underlying connection gets handed off mid-flight. Scoping the variable to the transaction instead of the session, like the earlier example does, is what makes that safe if pooling ever gets added.

The second was materialized views, as a way to avoid paying the cost of joining across ingest and doc_parser_output at query time. A materialized view would trade that live-join cost for a flat read — but it also introduces staleness, a refresh strategy that has to be scheduled and monitored, and RLS that no longer composes for free. A plain view re-evaluates the policy on every query using whatever the current session's identity is. A materialized view is a static snapshot underneath — RLS has to be deliberately reapplied on it rather than inherited automatically, which undercuts the whole point of the wall being structural instead of something someone has to remember.

Neither of these is a wrong idea. They're both the right idea at the wrong time. Connection limits aren't the current bottleneck, so pooling isn't solving a real problem yet — it's just added machinery with its own failure modes. The same is true of the join cost a materialized view would precompute: real optimizations, both of them, for a problem that hasn't shown up yet. The discipline here isn't avoiding these tools. It's building the plain, correct version first, and reaching for the more complex one only once there's evidence — not a hunch — that the simple version is the actual constraint.

Solve the problem you have. Skip the one you might.

Clarity through the chaos.