RLS Policy Standard

Data isolation in MSK has two layers.

Layer	What	Default
Application — Eloquent global scopes	Injects the ownership condition into every query	Always on
Database — PostgreSQL RLS	A backstop that holds even when the app slips	Opt-in

RLS is not the only defense. Application scopes are the first line; RLS re-enforces the same boundary physically underneath. That means turning RLS on or off must not change what you can see — if it does, one of the two is wrong.

The key idea: an upper admin only holds a prefix

A row's ownership is the composite key (saas_product_id, tenant_id). But the requester's context is a prefix of that key.

Requester	Context	As a composite key
Tenant admin	(5, 12)	the complete key
SaaS admin	(5, ∅)	prefix only
Platform admin	(∅, ∅)	empty prefix

Written as a path it is /saas5/tenant12/row. A SaaS admin holds /saas5/ and must see everything below it.

So exact matching breaks

-- ⛔ Requires the context to be present
saas_ctx > 0 AND tenant_ctx > 0
AND saas_product_id = saas_ctx AND tenant_id = tenant_ctx

A SaaS admin has no tenant context, so the second condition rejects them. The result is an admin page that silently shows zero rows. This exact shape once blocked a project from attaching a resource to its SaaS panel.

-- ✅ The standard — each axis is restricted only when its context is set
(saas_ctx = 0 OR saas_product_id = saas_ctx)
AND (tenant_ctx = 0 OR tenant_id = tenant_ctx)

Two kinds of "empty"

In the standard predicate, 0 (unset) and the reset sentinel -1 behave differently. That distinction is the heart of the design.

State	Session variable	Result
During a request, no context (upper admin)	NULL / '' / 0	that axis is not restricted
Outside a request (after reset)	-1	col = -1 → no rows

It separates "an admin who legitimately sees widely" from "no request is in flight". That is why the reset writes a non-matching sentinel rather than an empty value.

How to use it

Applying a policy

use App\Core\Base\Tenant\Rls\{RlsPolicy, RlsAxis};

// in a migration
RlsPolicy::apply('my_table', [RlsAxis::Saas, RlsAxis::Tenant]);

// to undo
RlsPolicy::drop('my_table');

Pass only the axes whose columns actually exist on the table. Passing a missing axis fails loudly before the policy is created — otherwise you end up with a table that has FORCE ROW LEVEL SECURITY enabled and no policy, which blocks everything.

Writing session context

use App\Core\Base\Tenant\Rls\RlsContext;

app(RlsContext::class)->write([
    'app.current_tenant_id' => $tenant->id,
]);

SET x = ? fails silently

PostgreSQL's SET is a utility statement and does not accept prepared-statement parameters.

DB::statement('SET app.current_tenant_id = ?', [$id]);  // SQLSTATE[42601]

If the caller swallows the exception you end up believing the session variable is set when nothing was written. RLS is not applied at all, and there is no failure signal. Always go through RlsContext::write().

Only two axes: SaaS and Tenant

The organization hierarchy (Organization / Workspace / Group) is deliberately not an RLS axis. Organizations form a self-referencing tree, so expressing "my org and everything under it" in a policy predicate requires a recursive CTE evaluated per row, which defeats indexes.

Isolation below the tenant is handled by application scopes and permission gates. RLS exists to have the database guarantee the tenant boundary — not to move the whole permission hierarchy into SQL.

Four prerequisites before enabling

All four must hold. Miss one and enabling RLS is either meaningless or breaks operations.

#	Prerequisite	Why
a	Non-superuser connection role	A superuser bypasses even FORCE RLS — enabling it enforces nothing, which is worse than knowing it is off
b	Policies applied	A flag without policies protects nothing
c	Context injection works	The session variables must actually be set
d	Reset guaranteed	If a value crosses the request boundary, the next request queries as the previous tenant

In particular the policy's target role must match the role you actually connect as. FORCE ROW LEVEL SECURITY applies to the table owner too, so a mismatch leaves no matching policy and blocks everything.

Diagnostics

php artisan rls:doctor

Reports the flag, the connection role and whether it is a superuser, the policy inventory, each policy's predicate shape (standard vs context-required), whether context injection actually works, and reset symmetry — then judges the four prerequisites.

The static check needs no database, so it fits in CI.

bash ops/scripts/audit-rls-posture.sh --strict

The other half

Any model carrying tenant/saas columns must use BelongsToTenant / BelongsToSaasProduct. RLS is only a backstop; isolation has to hold at the application layer even where RLS is off.