Skip to content

Row-Level Security (RLS)

Database-side enforcement of the permissions model. The second wall of defense after requirePermission() in src/lib/api.ts — and the only thing that stops a caller who bypasses the API layer from mutating data they shouldn't.

Why RLS matters here

The API layer (src/lib/api.ts) already checks permissions before every write. That is the first line of defense but it is application-side and therefore bypassable by anyone holding a valid authenticated Supabase token — a leaked anon-key session, a misbehaving integration, or a staff member whose JWT is reused outside the approved UI. RLS pushes the same check into Postgres so the database will reject the write even if the app layer is skipped.

The design intent is called out explicitly in the initial migration header:

The application layer (src/lib/api.ts + PermissionGate in the UI) already checks these permissions before firing a mutation. That is the first wall of defense but it is application-side and therefore bypassable by anyone holding a valid authenticated supabase-js token (e.g. a leaked anon-key session, a misbehaving integration, or a staff user whose JWT is reused outside the approved UI). RLS pushes the same check into the database — Postgres will reject the write even if the app layer is bypassed. — supabase/migrations/20260415200000_permission_aware_rls.sql:12-20

The auth_user_has_permission() helper

The workhorse. One SQL function every policy calls. Defined originally in supabase/migrations/20260415200000_permission_aware_rls.sql:72 and redefined without the role-name bypass in supabase/migrations/20260416020000_remove_permission_bypass.sql:40:

CREATE OR REPLACE FUNCTION public.auth_user_has_permission(perm_name text)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
STABLE
SET search_path = public
AS $$
DECLARE
  uid uuid := auth.uid();
BEGIN
  IF uid IS NULL THEN
    RETURN false;
  END IF;

  -- Explicit user-level deny wins over any role grant.
  IF EXISTS (
    SELECT 1 FROM public."UserPermission" up
    JOIN public."Permission" p ON p.id = up."permissionId"
    WHERE up."userId" = uid::text AND p.name = perm_name AND up.allowed = false
  ) THEN
    RETURN false;
  END IF;

  -- Explicit user-level grant.
  IF EXISTS (
    SELECT 1 FROM public."UserPermission" up
    JOIN public."Permission" p ON p.id = up."permissionId"
    WHERE up."userId" = uid::text AND p.name = perm_name AND up.allowed = true
  ) THEN
    RETURN true;
  END IF;

  -- Role-based grant via RolePermission.
  RETURN EXISTS (
    SELECT 1 FROM public."UserRole" ur
    JOIN public."RolePermission" rp ON rp."roleId" = ur."roleId"
    JOIN public."Permission" p ON p.id = rp."permissionId"
    WHERE ur."userId" = uid::text AND p.name = perm_name
  );
END;
$$;

Properties worth remembering:

  • SECURITY DEFINER — runs with the definer's privileges so it can read the permission tables even under restrictive policies.
  • STABLE — safe to call multiple times inside a single statement; Postgres can cache.
  • Explicit user-level deny wins over any role grant (step 2 above).
  • No role-name shortcut — CEO/GM/IT_ADMIN work because supabase/migrations/20260416020000_remove_permission_bypass.sql inserts explicit RolePermission rows for them, not because the function short-circuits.
  • userId columns are TEXT, auth.uid() returns uuid — the cast is uid::text. Match it when you write new policies.

Companion helper

public.is_staff_user() (from supabase/migrations/20260401170000_rls_staff_only_writes.sql) returns true when the caller has any staff role. Permission-aware policies AND-in both helpers via the RESTRICTIVE mechanic below.

How policies combine

  • PERMISSIVE policies combine with OR — any one of them granting access is enough.
  • RESTRICTIVE policies combine with AND against the overall result — each one must also pass.
  • service_role bypasses RLS entirely, so Supabase Edge Functions, CLI migrations, and cron jobs are unaffected.

The codebase uses the following layering:

  1. A base permissive staff-only policy (from rls_staff_only_writes / is_staff_user()).
  2. One or more RESTRICTIVE policies that require specific permissions.

Net effect: is_staff_user() AND auth_user_has_permission(...) must both be true for the write to land.

Standard policy shape

Every sensitive table gets one SELECT / INSERT / UPDATE / DELETE family. The template from supabase/migrations/20260415200000_permission_aware_rls.sql:37-63:

DROP POLICY IF EXISTS foo_perm_insert ON public."Foo";
CREATE POLICY foo_perm_insert ON public."Foo"
  AS RESTRICTIVE
  FOR INSERT
  TO authenticated
  WITH CHECK (public.auth_user_has_permission('foo.create'));

DROP POLICY IF EXISTS foo_perm_update ON public."Foo";
CREATE POLICY foo_perm_update ON public."Foo"
  AS RESTRICTIVE
  FOR UPDATE
  TO authenticated
  USING      (public.auth_user_has_permission('foo.edit'))
  WITH CHECK (public.auth_user_has_permission('foo.edit'));

DROP POLICY IF EXISTS foo_perm_delete ON public."Foo";
CREATE POLICY foo_perm_delete ON public."Foo"
  AS RESTRICTIVE
  FOR DELETE
  TO authenticated
  USING (public.auth_user_has_permission('foo.delete'));

Policy naming conventions

  • <table_lowercase>_perm_<action> for permission-aware policies — e.g. booking_perm_insert, customer_perm_update, supplier_perm_delete.
  • <table_snake_case>_<action> for coarser ones — e.g. bank_statement_import_select, bank_statement_line_write.
  • Always DROP POLICY IF EXISTS before CREATE POLICY so the migration is re-runnable (idempotent).

Grant routine privileges too

RLS on its own doesn't grant the authenticated role the ability to touch the table — you still need explicit GRANT SELECT, INSERT, UPDATE, DELETE ON public."Foo" TO authenticated; (and to service_role for Edge Functions). Every new-table migration should include those grants. See supabase/migrations/20260418150000_bank_reconciliation.sql:119-123 for the pattern.

Where policies live

  • Core permission-aware policies — supabase/migrations/20260415200000_permission_aware_rls.sql (Booking, Customer, Agent, Supplier, Payment, JournalEntry, JournalLine, FinanceConfig).
  • Groups & inventory follow-ups — supabase/migrations/20260416040000_permission_aware_rls_groups_inventory.sql.
  • Permission tables (meta — RLS on Permission, RolePermission, UserPermission, UserRole) — supabase/migrations/20260417000000_permission_aware_rls_permission_tables.sql.
  • Finance tighten — supabase/migrations/20260418113627_finance_rls_tighten.sql, supabase/migrations/20260413100000_finance_rls_update_delete.sql.
  • New tables add their own policies in the same migration — e.g. bank reconciliation (20260418150000_bank_reconciliation.sql), TDS (20260418150000_tds_scaffolding.sql), group pricing / invoices (20260418000000_group_pricing_and_invoices.sql), period lock trigger (20260418150000_journal_period_lock_trigger.sql).

Find them all with:

supabase/migrations/*_rls_*.sql

plus the inline RLS blocks in feature migrations.

Common pitfalls

Forgetting RLS on a new table

ALTER TABLE public."Foo" ENABLE ROW LEVEL SECURITY; is easy to miss. Without it, every authenticated user can read and write the table — RLS disabled means no filtering at all. Check pg_tables or the Supabase dashboard after a migration, and include RLS + policies + grants in the same file.

Using role names in policies instead of permissions

WHERE ur.role IN ('CEO','GM') is exactly the anti-pattern the permission bypass removal closed. If you need CEO-only access to something, grant CEO a dedicated permission and check auth_user_has_permission('x.y') instead.

Casting userId

UserRole.userId / UserPermission.userId are TEXT; auth.uid() is uuid. Match them with ::text or your policy will silently fail to find any grants.

Permissive vs restrictive

Two PERMISSIVE policies OR together — a permissive auth_user_has_permission() check alongside an existing permissive staff-only one is effectively "any staff OR anyone with the permission", which is broader than you want. New permission-aware policies must be RESTRICTIVE.

Granting the permission in code but not in RolePermission

A new requirePermission('foo.create') without a matching Permission + RolePermission seed migration means every user (including super-admins, since the bypass was removed) is denied. Always pair the code change with a seed migration.

Troubleshooting

  • permission denied for table Foo — RLS policy is blocking AND the base GRANT is missing too. Check both.
  • new row violates row-level security policy — the INSERT/UPDATE WITH CHECK clause returned false. Usually a missing RolePermission row.
  • Works for CEO but not for an accountant — confirm the permission is seeded in the relevant role bundle (see docs/PERMISSIONS.md §5 and the seed migrations 20260415220000_role_default_grants.sql, 20260416010000_seed_missing_permissions.sql).
  • Drift test failing — src/test/permissions.matrix.test.ts is telling you code references a permission not in PERMISSIONS.md or vice versa. Fix in the same PR.