Row-level security

Enforce access beside the data

Row-level security defines PostgreSQL-style policy DDL that FFDB compiles into protected SQLite enforcement.

Policies keep authorization at the backend boundary instead of relying on every client to remember filters.

Use it before exposing a table to end-user queries and whenever roles or claim semantics change.

Requirements for Row-level security

• Prerequisite — A migrated table with a stable primary key.
• Prerequisite — Documented subject, role, and claim rules for select and mutation paths.
• Required value — Policy name, table, command, role set, USING expression, and WITH CHECK expression where required.
• Required value — Test identities representing allowed, denied, anonymous, and changed-scope cases.

Policy DDL

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

CREATE POLICY documents_read ON documents
  FOR SELECT TO authenticated
  USING (owner_id = auth.uid());

CREATE POLICY documents_write ON documents
  FOR INSERT TO authenticated
  WITH CHECK (owner_id = auth.uid());

Combination and enforcement

For the current command and role, permissive policies combine with OR and restrictive policies combine with AND. RLS with no applicable permissive policy denies access.

• SELECT and DELETE use USING.
• INSERT uses WITH CHECK.
• UPDATE evaluates both the old row and new row.
• FOR ALL participates in every command.
• FORCE removes the trusted developer bypass.

Row-level security workflow

• 1. Write the narrowest policy expression using verified auth functions.
• 2. Apply policy DDL through a migration.
• 3. Test select, insert, update, and delete as multiple users.
• 4. Inspect compiled policy metadata.
• 5. Repeat tests after schema or claim changes.

Verify row-level security

Authorized rows remain usable while disallowed rows are filtered or rejected consistently across reads, writes, storage, and sync.

Troubleshoot row-level security

• A policy form is unsupported — FFDB fails closed; rewrite it to the documented subset.
• A client adds an owner filter to compensate — fix the server policy and keep client filters about product behavior only.

Continue from Row-level security

• Review JWT claims.
• Run query and sync isolation tests.