RLS PERMISSIVE Policies OR Together, Admin Policy Wins

Conclusion

PostgreSQL RLS combines PERMISSIVE policies with OR. If even one all-schools policy exists for a privileged role, any other policy that scopes to a single tenant stops mattering as a firing condition. On an operation meant to target one specific school, a same-school check silently let another school’s rows through.

The failure mode

The system has an operations-wide role (system_admin) that spans every school tenant, and a tenant-scoped role (school_admin) confined to one school. The school admin hub (CRUD for departments, grades, and classes) performs operations scoped to one school, with a guard that, before writing a parent reference (e.g. attaching a grade under a department), runs a RLS-gated SELECT to confirm the referenced parent actually exists “in my own school.”

That same-school check was written correctly as code. But when the actor executing it was system_admin, the check misjudged another school’s rows as “existing in my own school,” letting operations through that attached or re-parented a child record under a department or grade belonging to a different tenant. Nothing went wrong when the same operation ran as school_admin — the boundary only disappeared when the actor was system_admin.

Cause

RLS policies come in two kinds, PERMISSIVE and RESTRICTIVE, and the default is PERMISSIVE. When multiple PERMISSIVE policies exist on the same table for the same command, PostgreSQL combines them with OR: if any single USING clause evaluates to true, the row is visible or writable.

This database had two RLS policies on the same table:

• tenant_isolation: school_id = NULLIF(current_setting('app.current_school_id', true), '')::uuid — the definition of the tenant boundary itself, letting through only rows belonging to the caller’s own school.
• system_admin_full_access: current_setting('app.current_user_role', true) = 'system_admin' — a policy meant for operations-side screens, such as a school list, that need system_admin to see every school.

Both are PERMISSIVE, so they’re OR’d. When an actor opens a transaction as system_admin, app.current_user_role = 'system_admin' always evaluates true, so system_admin_full_access fires — making every school’s rows visible regardless of app.current_school_id. No matter how correctly tenant_isolation scopes to one school, that scoping can’t take effect once its OR partner is a policy that passes everything unconditionally.

The school admin hub’s same-school check (existsInSchool, implemented in apps/web/lib/school-admin/hub-actions.ts) judges “same school or different school” from the result of this RLS-gated SELECT. Inside a transaction running as system_admin, the SELECT itself returns another school’s row, so the check’s implementation stays correct while its verdict becomes wrong.

A test makes the contrast concrete against a real PostgreSQL connection (packages/db/__tests__/rls/tenant-scoped-context.test.ts):

// Without downgrade (raw system_admin), another school's department is visible — the old over-privileged path
const rows = await withTenantContext(
  db,
  { userId: fx.sysAdmin, role: "system_admin", schoolId: fx.schoolA },
  (tx) => tx.select().from(departments).where(eq(departments.id, deptB)),
  APP,
);
expect(rows).toHaveLength(1); // system_admin_full_access fires for every school

Running as system_admin for schoolA and selecting school B’s department (deptB) directly by id returns one row. Even though the intent was to act within schoolA, system_admin_full_access fires unconditionally, so the other school’s row becomes visible — reproduced exactly.

The fix

Adding a school_id condition to the system_admin policy was rejected: system_admin also has legitimate paths — a school list screen, for one — that genuinely need to span every school, and restricting the policy itself would break those. What was actually needed was for the same system_admin to see a different scope depending on the kind of operation being performed.

So a pure function was added that temporarily downgrades the role to school_admin within a transaction, but only for operations scoped to one specific school:

export function tenantScopedContext(ctx: TenantContext): TenantContext {
  if (ctx.role === "system_admin" && ctx.schoolId != null && ctx.schoolId !== "") {
    return { ...ctx, role: "school_admin" };
  }
  return ctx;
}

A system_admin with no schoolId is not downgraded — downgrading it would leave no school for tenant_isolation’s predicate (school_id = app.current_school_id) to match, making every row invisible. Genuinely cross-school paths pass through this transform untouched, preserving their previous behavior.

A tenantScoped option was added to the caller-side withSession, and the school admin hub’s shared post-processing (finish) now calls it with tenantScoped: true.

const data = await withSession(build, { tenantScoped: true });

With the downgrade applied, the same contrast test returns zero rows:

const rows = await withTenantContext(
  db,
  tenantScopedContext({ userId: fx.sysAdmin, role: "system_admin", schoolId: fx.schoolA }),
  (tx) => tx.select().from(departments).where(eq(departments.id, deptB)),
  APP,
);
expect(rows).toHaveLength(0); // system_admin_full_access no longer fires; only tenant_isolation remains

Downgrading makes system_admin_full_access’s predicate false, leaving only tenant_isolation — so the other school’s row becomes invisible. The same holds for UPDATE: an attempt to overwrite another school’s department name runs against zero matching rows and leaves the target row unchanged (the test actually attempts to write a literal “hijacked” string and confirms it never lands).

There’s no effect on the audit log. The downgrade only changes app.current_user_role; app.current_user_id stays the actor’s own uid throughout, so the policy that records “who performed this action” continues to hold.

Making it not recur

What this incident shows is that “we’re safe because the app checks the same-school condition” isn’t enough on its own — that check reads its answer from a SELECT that has its own boundary, defined by RLS, as a separate layer. Even correct application logic produces a wrong result once the data boundary feeding that logic has already broken down.

The fix taken here wasn’t to make the policy itself stricter, but to make “whose view is this transaction running as” an explicit, caller-side decision. tenantScoped is opt-in, so existing cross-school paths are unaffected. Every time a new operation scoped to a specific school is added, whether to pass tenantScoped: true to withSession has to be judged case by case — by design, this still depends on the caller remembering to think about it.

The same risk applies to any other table carrying a PERMISSIVE policy for a privileged role. What matters isn’t the correctness of any single policy in isolation, but what the OR of every PERMISSIVE policy on that same table and command actually resolves to.