Writing policies

A policy is a small JSON object you pass to rls_protect. It names a table and gives an expression for each operation. This page explains each field and the conventions that keep them correct.

The shape

{
  "table":  "documents",
  "select": "owner_id = rls_ctx('user_id')",
  "insert": "NEW.owner_id = rls_ctx('user_id')",
  "update": { "using": "OLD.owner_id = rls_ctx('user_id')",
              "check": "NEW.owner_id = rls_ctx('user_id')" },
  "delete": "OLD.owner_id = rls_ctx('user_id')"
}

Every expression is ordinary SQL that evaluates to true/false per row. They can call rls_ctx('key') to read the current user's claims, reference columns, use subqueries, json_each, EXISTS, whatever SQLite supports.

Reads: select

select becomes the view's WHERE clause. Use bare column names — it reads the table directly:

"select": "owner_id = rls_ctx('user_id') OR rls_ctx('role') = 'admin'"

select is required. Use true to make every row readable (e.g. a lookup table everyone can read but only admins can change).

Writes: insert, update, delete

These run inside the view's triggers, so they follow SQL's trigger conventions:

Field	Runs on	Column prefix	Meaning
insert	the new row	NEW.	may this row be created? (WITH CHECK)
update.using	the existing row	OLD.	may this row be updated at all?
update.check	the resulting row	NEW.	is the result still allowed?
delete	the existing row	OLD.	may this row be deleted?

Qualify write expressions with OLD./NEW.

select uses bare column names, but insert/update/delete run in triggers where a bare owner_id is not a valid reference. Write NEW.owner_id or OLD.owner_id. A policy that forgets the prefix is rejected at rls_protect time, not silently mis-applied — so you'll find out immediately, but it's the most common first mistake.

Each write field may also be:

• omitted or false → that operation is denied entirely. This is the default: a table you only give a select policy is read-only to guarded connections.
• true → that operation is always allowed (any row).
• a string → a WITH CHECK expression (for update, a check on NEW.*).
• for update, an object { "using": ..., "check": ... } to restrict both which rows may be updated and what they may become.

Worked examples

Per-user ownership

The classic case — users see and manage only their own rows:

{
  "table": "documents",
  "select": "owner_id = rls_ctx('user_id')",
  "insert": "NEW.owner_id = rls_ctx('user_id')",
  "update": { "using": "OLD.owner_id = rls_ctx('user_id')",
              "check": "NEW.owner_id = rls_ctx('user_id')" },
  "delete": "OLD.owner_id = rls_ctx('user_id')"
}

Admins bypass the filter

Add a role check to any expression:

"select": "owner_id = rls_ctx('user_id') OR rls_ctx('role') = 'admin'"

Multi-tenant isolation

Every row carries a tenant_id; a user only ever touches their tenant:

{
  "table": "invoices",
  "select": "tenant_id = rls_ctx('tenant_id')",
  "insert": "NEW.tenant_id = rls_ctx('tenant_id')",
  "update": { "using": "OLD.tenant_id = rls_ctx('tenant_id')",
              "check": "NEW.tenant_id = rls_ctx('tenant_id')" },
  "delete": "OLD.tenant_id = rls_ctx('tenant_id')"
}

Visibility through a relationship

A policy can reference another protected table, and that reference is itself filtered by the other table's policy — so "visible because of a relationship" composes cleanly across tables:

"select": "org_id IN (SELECT id FROM orgs)"

This is powerful enough to deserve its own page — see Joins & relationships.

Membership via a claim array

Claims can be arrays. Say each user carries the teams they belong to and a project is visible to its team:

{
  "table": "projects",
  "select": "EXISTS (SELECT 1 FROM json_each(rls_ctx('teams')) WHERE value = team)"
}

Then bind {"user_id": 1, "teams": ["infra", "ml"]}.

Read-only reference table

Everyone reads, nobody writes (only privileged setup can):

{ "table": "countries", "select": true }

Applying and changing policies

• rls_protect is idempotent: calling it again with the same policy is a no-op. Calling it with a changed policy (or after the table's columns change) recompiles the view and triggers. Run it after migrations.
• Column DEFAULTs still apply on insert — the triggers preserve them.
• To remove a policy and turn the view back into a plain table, use rls_unprotect('documents') (on a privileged, unguarded connection).

See the Policy JSON reference for the exact grammar and the caveats for the sharp edges (type coercion, OLD./NEW. rules, cascade behavior).