Row-Level Security in Cinderblock

Every table in the public schema has RLS enabled, with policies that spell out both USING (read/visibility) and WITH CHECK (write) — no implicit defaults. The tests/05_security_definer.sql hardening suite asserts the catalog state stays this way:

• Every public-schema table has rowsecurity = on.
• Every public view is security_invoker = on (a security_definerview bypasses the caller's RLS).
• No policy uses using(true) or with check(true)unless it's scoped to a single non-public role.
• Every app_private.* helper has search_path = '' in proconfig.

Helper functions (all security_definer, search_path = '')

is_workspace_member(_workspace_id uuid)         -> boolean
has_workspace_role(_workspace_id uuid, _min_role workspace_role) -> boolean
workspace_is_writable(_workspace_id uuid)       -> boolean
user_has_mfa(_user_id uuid)                     -> boolean
is_slug_reserved(_slug text)                    -> boolean

Every helper has explicit set search_path = '' — without it, a workspace member who creates a function in their own schema named auth.uid() could hijack identifier resolution. The search-path attack test in tests/05_security_definer.sql simulates this end-to-end and asserts the helper still resolves the real auth.uid().

Policy patterns by table

workspaces

select: deleted_at is null and is_workspace_member(id)
insert: created_by = auth.uid() and deleted_at is null
update: has_workspace_role(id, 'owner') (USING + WITH CHECK)
delete: <no policy — hard-delete via service-role cron only>

workspace_members

select: (user_id = auth.uid() and removed_at is null)
          or has_workspace_role(workspace_id, 'admin')
insert: with check (false)   -- service-role only via invite-accept
update: has_workspace_role(workspace_id, 'admin')
delete: <no policy — soft-delete sets removed_at>

audit_events

select: admin+: all rows
        member: actor_id = auth.uid() only
        guest:  none
insert: only via cb_audit_writer role (separate INSERT-only grant)
update/delete: <no policy, no grant — append-only by design>

tasks

select: is_workspace_member(workspace_id)
insert: has_workspace_role(workspace_id, 'member')
        and created_by = auth.uid()
        and workspace_is_writable(workspace_id)
update: has_workspace_role(workspace_id, 'member')
        and workspace_is_writable(workspace_id)
delete: has_workspace_role(workspace_id, 'admin')

The hostile fixture

tests/01_fixture.sql seeds 5 workspaces × 8 users with a deliberate membership matrix that mixes overlapping memberships across workspaces, role variants, and one outsider. Every test authenticates as a user with no business reading the target row and asserts an empty result.

See the live policy viewer for the deployed catalog state, or the latest test results for the green run.