Add sqlproof.contrib.supabase.audit module for schema-level RLS audits

[alialavia]

Summary

Add a sqlproof.contrib.supabase.audit module exposing canned helpers for the one-shot, introspection-style RLS audits that recurrent Supabase RLS articles call out as table stakes — "is RLS enabled on every public table", "does every policy specify a TO clause", "is every SECURITY DEFINER function locking down search_path", etc. Today these are all writable with raw db.scalar queries against pg_class / pg_policies / pg_proc, but tedious enough that users skip them in practice.

Motivation

While drafting the inbox sample's RLS coverage (docs/superpowers/specs/2026-06-03-inbox-sample-design.md), several common RLS bug classes turned out to be schema-shape audits rather than data-shape invariants:

• Table created without ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
• RLS enabled but zero policies defined (table locked out).
• Policy missing TO authenticated / TO anon clause.
• SECURITY DEFINER function without SET search_path = ''.

Each is a one-line pg_catalog query. None is a property-shaped invariant (so they don't belong in @given tests), but all are real bug classes Supabase shops repeatedly ship in production. They belong in a one-shot CI assertion suite — and the lack of canned helpers means audits get skipped.

Proposed API

from sqlproof.contrib.supabase.audit import (
    assert_rls_enabled,
    assert_policy_targets_role,
    assert_security_definer_search_path_locked,
    tables_without_rls,
    policies_without_role_clause,
)

# Per-object assertions
def test_tickets_has_rls(db):
    assert_rls_enabled(db, "public.tickets")

# Schema-wide audits
def test_all_public_tables_have_rls(db):
    assert tables_without_rls(db, schema="public") == set()

Two assertion shapes: per-object (assert_X(db, name)) and schema-wide (X_without_Y(db, schema=...) returning a set). Both are thin wrappers over pg_catalog.

Scope

• Implement: assert_rls_enabled, assert_policy_targets_role, assert_security_definer_search_path_locked, tables_without_rls, policies_without_role_clause, public_tables_readable_by_anon.
• Tests against examples/supabase_rls/schema.sql (assertions should all pass) and against a synthesized minimal schema with each bug class (assertions should each fail).
• Docs: extend guides/supabase.md (or the new guides/supabase-rls-bug-classes.md planned in the inbox spec) with an "Audit my schema" section.

Out of scope

• Performance audits (per-row auth.uid(), missing indexes on policy columns) — these are real bugs but a distinct category (perf, not security).
• Anything requiring per-query introspection (EXPLAIN, etc.).

Related

• Inbox sample design spec — see "Gap 1" in the RLS bug-classes analysis: docs/superpowers/specs/2026-06-03-inbox-sample-design.md