Policy JSON

A policy object (given to rls_setup's tables or to rls_protect) can be written in the Postgres-style form (recommended) or the legacy per-command form. Both compile to the same view + triggers.

Postgres-style form (recommended)

Mirrors CREATE POLICY … USING (…) WITH CHECK (…), with bare column names:

{
  "table": "documents",
  // USING — who may see / update / delete a row. Bare columns; `true` = all rows.
  "using": "owner_id = rls_ctx('user_id')",
  // WITH CHECK — what a written (INSERT/UPDATE) row may be. Defaults to `using`.
  "check": "owner_id = rls_ctx('user_id')",
  // which commands this policy grants (default all); others are denied.
  "for": "all",                      // or ["select","insert"], "select", …
  // optional per-table overrides of the rls_setup defaults:
  "adminRole": "admin",              // this role bypasses the policy
  "ownerClaim": "user_id"
}

owner shorthand

For the ubiquitous "you own it" case, one field writes the whole thing:

{ "table": "documents", "owner": "owner_id" }
// ≡ using/check = `"owner_id" = rls_ctx('user_id')`, for all commands

Bare columns work because the write triggers evaluate the expression against the NEW (check) or OLD (using) row — SQLite's name resolution binds them, including correlated subqueries, exactly like Postgres. Explicit NEW./OLD. still work if you prefer them.

Legacy per-command form

The original form, one expression per command, is still fully supported:

{
  // required — the table to protect
  "table": "documents",

  // required — read policy (the view's WHERE). Bare column names.
  // A string expression, or `true` for "all rows visible".
  "select": "owner_id = rls_ctx('user_id')",

  // insert policy (WITH CHECK over NEW.*).
  // Omit or `false` = deny all inserts. `true` = allow. String = check expr.
  "insert": "NEW.owner_id = rls_ctx('user_id')",

  // update policy. Omit/`false` = deny, `true` = allow.
  // String = WITH CHECK over NEW.*.
  // Object = { "using": <OLD.* filter>, "check": <NEW.* check> }.
  "update": {
    "using": "OLD.owner_id = rls_ctx('user_id')",
    "check": "NEW.owner_id = rls_ctx('user_id')"
  },

  // delete policy (USING over OLD.*). Omit/`false` = deny, `true` = allow.
  "delete": "OLD.owner_id = rls_ctx('user_id')"
}

Field summary

Field	Required	Values	Column prefix
table	yes	string	—
select	yes	expression | true	bare (owner_id)
insert	no (default deny)	expression | true | false	NEW.
update	no (default deny)	expression | {using,check} | true | false	OLD. / NEW.
delete	no (default deny)	expression | true | false	OLD.

Rules and semantics

• Default deny for writes. A table given only a select policy is read-only to guarded connections. Grant writes explicitly.
• NULL is a violation. A check expression that evaluates to NULL (not just false) is treated as failed — the row is rejected. This is the same "fail closed" behavior as Postgres.
• UPDATE and DELETE only reach visible rows. Because the triggers fire on the view, an UPDATE/DELETE can only affect rows the select policy exposes. update.using and delete are additional restrictions on top of that.
• Bare vs. OLD./NEW. select reads the table directly (bare names). The write expressions run in triggers and must qualify columns with NEW. (the incoming/resulting row) or OLD. (the existing row). A missing prefix is rejected at rls_protect time.
• Expressions are trusted SQL. They are inserted into the generated view/triggers verbatim, so only privileged setup code should build policies. The table name is safely quoted; the expressions are yours.
• Claims via rls_ctx. Any expression may call rls_ctx('key'). Nested JSON claims come back as JSON text — use json_each / json_extract.

Requirements on the table

• An explicit PRIMARY KEY (the write triggers match rows by it; the implicit rowid isn't visible through a view).
• Re-run rls_protect after a migration changes the table's columns — the view's column list is regenerated from the current schema.

See Writing policies for worked examples and caveats for the edge cases.