Joins & relationships

A common worry with row-level security is whether it survives joins and relationships — multi-table queries, nested subqueries, and policies that depend on other tables. With sqlite-rls the answer is yes, and the reason is the same one that makes it drop-in: every protected table is a view, so every reference to it — in your query or inside another table's policy — goes through that table's filter. Filtering composes at every level.

You don't configure anything for this. It falls out of the design.

Joins in your queries just work

Because each table in a join is its filtered view, an N-way join is scoped at every table simultaneously:

SELECT t.title
FROM tasks t
JOIN projects p ON p.id = t.project_id
JOIN orgs o     ON o.id = p.org_id
JOIN memberships m ON m.org_id = o.id;

The current user sees a task here only if they can see the task and its project and its org and the membership — each restriction applied by that table's own policy. The same holds for LEFT JOIN (both sides are filtered), aggregates over joins, correlated subqueries, self-joins, and recursive CTEs. This is the big advantage over injecting a WHERE clause per query: there's nothing to inject at each level, and nothing to forget.

Policies can reference other tables

The powerful part: a policy expression can query another protected table, and that reference goes through the other table's policy too. This lets you express "visible because of a relationship" cleanly.

Say a user belongs to orgs via a memberships table, and projects belong to orgs. "You can see a project if it's in an org you belong to" is just:

// memberships: you see your own membership rows
{ "table": "memberships", "select": "user_id = rls_ctx('user_id')" }

// orgs: visible if you're a member (references memberships)
{ "table": "orgs",
  "select": "id IN (SELECT org_id FROM memberships WHERE user_id = rls_ctx('user_id'))" }

// projects: visible if their org is visible (references orgs → memberships)
{ "table": "projects", "select": "org_id IN (SELECT id FROM orgs)" }

Reading projects transparently walks orgs, which walks memberships — each hop filtered by its own policy. You can chain this as deep as your schema goes (tasks → projects → orgs → memberships works the same way).

Write checks compose too

The same applies to WITH CHECK on writes. "You may only add a project to an org you belong to":

{ "table": "projects",
  "select": "org_id IN (SELECT id FROM orgs)",
  "insert": "NEW.org_id IN (SELECT id FROM orgs)" }

An insert whose org_id isn't a visible org is rejected — the check walks the relationship the same way a read does.

Two things to know

Reference the view, not the base table

In a policy that depends on another protected table, use its public name (memberships), not the internal _rls_memberships. The public name routes through that table's policy (what you almost always want). Referencing the base table directly would read it unfiltered — occasionally intentional, but usually a mistake. (Only trusted setup code writes policies, so this is a footgun, not an attacker avenue.)

Truly circular policies error cleanly

A policy whose expression references its own view — e.g. a folders policy that selects from folders — is genuinely circular. SQLite detects this and raises view … is circularly defined at query time. It does not hang or leak; it just fails, so you'll notice immediately. To model hierarchies, filter on a stored column (like owner) and do the tree walk in your query with a recursive CTE — which, as shown above, stays correctly scoped:

WITH RECURSIVE tree(id, parent_id) AS (
  SELECT id, parent_id FROM folders WHERE parent_id IS NULL
  UNION ALL
  SELECT f.id, f.parent_id FROM folders f JOIN tree t ON f.parent_id = t.id
)
SELECT * FROM tree;   -- every folders read filtered by the folders policy

Performance

Deeply nested policies mean the planner expands nested views/subqueries. Two things keep this fast: rls_ctx() is deterministic (so col = rls_ctx('x') still uses an index on col), and the columns you join and filter on (org_id, project_id, foreign keys) should be indexed just as you'd index them without RLS. With the right indexes, a policy chain is planned like the equivalent hand-written join.

---

Every relationship-scoped query in this page is covered by test/joins.ts in the repo. Next: the security model, or the policy reference.