How it works

The whole design is three moving parts you can hold in your head at once: a view that filters reads, triggers that guard writes, and a per-connection context the policies read from. A fourth part — the authorizer — is the fence that keeps it honest.

1. A protected table becomes a view

When you call rls_protect on documents, the extension:

1. renames the real table to _rls_documents, then
2. creates a view named documents (the original name) that selects from _rls_documents but adds your read policy as a WHERE clause.

CREATE VIEW documents AS
  SELECT id, owner_id, title, body
  FROM _rls_documents
  WHERE (owner_id = rls_ctx('user_id') OR rls_ctx('role') = 'admin');

Because the view takes the original name, every query you already wrote keeps working — it just now goes through the filter. Joins, aggregates, subqueries, SELECT * — all of them read the view, so all of them are scoped. This is the key advantage over injecting a WHERE clause into each query: there is nothing to inject, and nothing to forget.

2. Writes go through INSTEAD OF triggers

Views aren't normally writable, so the extension adds INSTEAD OF INSERT/UPDATE/DELETE triggers on the view. Each trigger:

• checks your write policy (Postgres's WITH CHECK) and aborts if the new row wouldn't be allowed, then
• forwards the write to the real _rls_documents table.

Because an UPDATE or DELETE against the view can only see rows the read policy exposes, a user physically cannot modify a row they can't see — you get that for free.

3. Policies read the current user via rls_ctx()

The policy expressions call rls_ctx('user_id'), rls_ctx('role'), and so on. Those values come from the context you bind for the current query:

SELECT rls_set_context('{"user_id": 42, "role": "user"}');

The context lives in the extension's per-connection memory. Since a connection runs one statement at a time, binding the context immediately before a query gives you exact per-query identity. Set it, run the query, set it again for the next user. (More in The user context.)

rls_ctx is marked deterministic, so the query planner treats owner_id = rls_ctx('user_id') as a constant comparison and can still use an index on owner_id. Row-level security doesn't mean full-table scans.

4. The authorizer keeps everyone honest

The view is only as good as the guarantee that nothing reads _rls_documents directly. That's the job of rls_guard(), which arms SQLite's authorizer — a callback the engine consults for every table access, function call, and schema change. Once guarded, the connection:

• cannot read or write the _rls_* base tables directly (only the extension's own view and triggers may),
• cannot run DDL, ATTACH, load another extension, or change pragmas that would weaken enforcement,
• fails closed if no context is bound — a query on a protected table errors instead of returning everything.

Setup happens before rls_guard(), while the connection is still privileged. After guarding, the connection is only good for serving scoped queries. The security model covers the boundary — and the red-teaming behind it — in detail.

Putting it together

             rls_protect(policy)                 rls_guard()
   table  ─────────────────────────▶  view + triggers  ──────────▶  enforced
 documents      (once, at setup)        documents            authorizer armed
                                            │
                                            ▼
              per query:  rls_set_context({...})  →  SELECT * FROM documents

That's the entire mental model. The next page gets it running.