SQL API

Every capability of the extension is a SQL function, which is why it works from any driver. All functions are per-connection.

Setup (privileged — before rls_guard)

rls_setup(config_json) → text

The whole setup in one idempotent, correctly-ordered call — prefer this over the individual steps below. Protects every table in tables, sets the resolver, seals with seal, and arms the guard (unless guard: false). Applied atomically: if any table fails, the whole thing rolls back and the connection stays unguarded. Config:

{
  "tables": [ /* policy objects, each parsed like rls_protect's argument */ ],
  "adminRole": "admin",     // optional: default admin-bypass for every policy
  "ownerClaim": "user_id",  // optional: claim the `owner` shorthand compares (default "user_id")
  "resolver": "SELECT ...", // optional: rls_config('resolver', ...)
  "seal": "<token>",        // optional: rls_seal(...)
  "guard": true             // optional: arm the guard last (default true)
}

Returns e.g. configured 3 table(s), resolver, sealed, guarded. Safe to call again on reconnect (protects are idempotent; resolver/seal/guard reapplied).

rls_protect(policy_json) → text

Compiles a policy for one table: renames the table to _rls_<table>, creates the filtered view and the INSTEAD OF triggers, and records the definition in _rls_meta. Returns 'protected', or 'unchanged' if the policy and columns are identical to last time (idempotent). May append a warning if it finds existing objects that reference the base table directly. Refused on a guarded connection.

rls_unprotect(table) → text

Drops the view and triggers and renames the base table back to its original name, turning it into a plain table again. Refused on a guarded connection.

rls_config('resolver', sql) → int

Sets the SQL used by rls_auth(). The statement's ?1…?n are bound from rls_auth's arguments; the first result row's column names become the claim keys and its values the claim values. The resolver runs with the extension's privileges, so it may read base tables. Refused on a guarded connection.

Per query

rls_set_context(json) → int

Binds a JSON object of claims for subsequent queries; returns the number of claims. Pass SQL NULL to clear. A non-object or malformed JSON is rejected and leaves the connection with no context bound (fail closed).

rls_auth(args…) → int

Runs the configured resolver with the given arguments and binds its first row as the claims; returns the claim count. If the resolver returns no row, it raises an authentication error and leaves the context unbound.

rls_clear_context() → int

Unbinds all claims. Protected queries then fail closed.

rls_ctx(key) → value

Reads a single claim, for use inside policy expressions. Returns the claim's value, or NULL if the key is absent (so comparisons fail closed). Raises if no context is bound. Marked deterministic so the planner can use indexes on policy columns.

rls_last_rowid() → int | null

The rowid assigned by the most recent INSERT through a policy view on this connection. Use this instead of last_insert_rowid(), which SQLite resets after INSTEAD OF triggers (see caveats).

Privilege control

rls_seal(token) → int

Sets the connection's re-elevation token. Can be called once; calling again with the same token is a no-op, with a different token is an error (so it can't be taken over). Without a seal, rls_unguard needs no token — fine for trusted single-process tests, unsafe in production.

rls_guard() → int

Drops privileges: snapshots the sanctioned view/triggers from _rls_meta, expires statements compiled before this point, and arms the authorizer. After this the connection can only serve scoped queries.

rls_unguard([token]) → int

Re-elevates the connection to privileged mode. If the connection was sealed, the correct token is required. Also drops the lock (below).

rls_lock() → int

Enters untrusted-SQL mode. While locked, the authorizer denies every identity/privilege-mutating rls_ function (rls_set_context, rls_auth, rls_guard, rls_unguard, …) and all writes (INSERT/UPDATE/DELETE). Reads still flow through the policy views, so RLS keeps scoping them. This is what makes it safe to run client-supplied SQL under a fixed identity: bind the caller's context, rls_lock(), run the untrusted SELECT, then unlock. The query cannot become someone else, cannot write, and (via the guard) cannot reach base tables or run DDL — it can only read what the policies allow.

rls_unlock([token]) → int

Leaves untrusted-SQL mode. Token required if the connection is sealed — so the untrusted SQL itself (which lacks the token) can never unlock, while the app can. Always unlock in a finally.

-- run one untrusted statement as user 42, RLS-enforced, read-only:
SELECT rls_set_context('{"user_id":42,"role":"user"}');
SELECT rls_lock();
SELECT * FROM documents JOIN authors USING (author_id);  -- scoped; can't impersonate/write
SELECT rls_unlock('<seal-token>');

rls_version() → text

Returns the extension version string.

Reserved names

The extension owns everything prefixed _rls_: the base tables (_rls_<table>), the triggers (_rls_<table>_ins/upd/del), and the _rls_meta bookkeeping table. Don't create your own objects with that prefix or reference the base tables directly from application SQL — go through the views.