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

src/lib/api.ts runs in the browser. Its requirePermission() calls shape the screen; they stop nobody who holds a valid Supabase token and opens a console. RLS is where the check actually happens (ACC-001).

Two rules the policies now satisfy that they did not in April:

  • Deny by default (ACC-002). No table allows reading or writing to "any signed-in user". Core tables — Booking, Customer, Payment, VisaCase, User and the rest — used to be readable by every signed-in user including self-registered customers, and several were writable by them.
  • is_staff_user() means "staff who may write". Since 20260929140000 it is auth_is_staff() AND NOT auth_is_paused(): a paused account (ACC-070) fails every blanket staff write policy while auth_is_staff() — the read predicate — still answers yes. user_has_permission() likewise answers false to a paused user for any permission whose last segment is not view, export or read. Use auth_is_staff() for a read policy and is_staff_user() or a permission for a write.
  • auth_is_staff() alone is not enough for the staff-wide tables. Since 20260929170000 the read policies on User, UserRole, UserPermission, the ledger tables, Chat*, CommunicationsLog, CommunicationSetting and the WhatsApp tables ask auth_reads_beyond_field() — staff holding at least one permission outside field.* — so a TOUR_LEADER-only login (FLD-001) reads its own rows and nothing else there; its screens take every name from the fld_* SECURITY DEFINER functions. Use it, not auth_is_staff(), for a new blanket staff read of personal or money data.
  • is_staff_user() is not an alternative to a permission. Policies used to combine with OR, so is_staff_user() stood beside the real permission on UserRole, Permission, Role, Booking, Customer and Payment — meaning any staff or agent user could grant themselves CEO. is_staff_user() now excludes AGENT, requires the user to be active, and appears alone only on staff-internal configuration and communications tables, never as an OR arm next to a permission check. A handful of operational tables still gate writes on it rather than a specific permission; that tightening is tracked against ACC-002.

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.

Function grants: the anonymous key is a visitor (ACC-053)

Policies decide which rows a role sees. Function grants decide which functions a role may call at all, and Postgres's own default is generous: without a default-privilege entry, every new function is executable by PUBLIC, which includes the website's anonymous key.

Since 20260928140000_the_anonymous_key_is_a_visitor.sql the default for functions a migration creates is closed: authenticated and service_role may execute a new function, anon and PUBLIC may not. The anonymous key can execute only an allow-list — the website's reads (public_departures, public_groups, resolve_departure_poster), portal_context, and the predicates policies call (auth_*, is_staff_user, is_finance_user, has_role_type, bkg_scope). Those predicates stay callable because a policy on a table the website may read (poster templates, exchange rates) is evaluated as the anonymous key, and a policy that calls a function the caller may not execute fails the whole SELECT.

Trigger functions (RETURNS trigger) are granted to nobody. A trigger fires whatever the grants say; the grant only decides who may call the function by hand, and nobody should.

What this means when you write a migration:

  • A function for a signed-in screen: REVOKE ALL ... FROM PUBLIC, anon; is now redundant but harmless; keep granting authenticated explicitly so the intent is on the page.
  • A function for the website: GRANT EXECUTE ... TO anon; with a comment saying why, and add its name to the allow-list in supabase/tests/the_anonymous_key_is_a_visitor.sql — the test fails otherwise.
  • A trigger function: no grants at all.

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.
  • Portal isolation — supabase/migrations/20260918100000_data_isolation_portals.sql (auth_customer_booking_ids(), auth_agent_booking_ids(), auth_portal_group_invoice_ids(); Payment select customer / select agency). 20260929180000_an_allocation_is_read_by_its_owner.sql gives PaymentAllocation the same three-policy shape (staff by permission, the booking's customer, the owning agency); test supabase/tests/an_allocation_is_read_by_its_owner.sql.
  • Families (PAX-036) — 20261001220000_families_per_departure.sql: TravelFamily and TravelFamilyMember are read by staff with the same rights that read BookingPassenger, and by a partner or customer for the families their own travellers are in; FamilyRelationship by any signed-in user. No write policy on any of the three and no write grant to authenticated: every change is a SECURITY DEFINER function (family_save and the rest) that checks bookings.edit or the partner's ownership. Test supabase/tests/families_per_departure.sql.
  • Transferred bookings are read-only — 20261002090100_a_transfer_keeps_its_records.sql. Not a policy but BEFORE triggers on Booking, BookingPassenger, Payment, PaymentAllocation and PaymentRefund, so they hold for SECURITY DEFINER functions and the service role too: a TRANSFERRED booking, its travellers and its payments' money cannot change (LC-031). The only way past is app.transferred_booking_maintenance = 'on' for one transaction (runbook). BookingPaymentCarry is read-only to staff with bookings.view or finance.view; only transfer_passenger_to_booking() writes it. Test supabase/tests/transfer_keeps_records.sql.
  • A staff role changes only by request — 20261003165000_a_role_changes_with_two_approvals.sql (ACC-077 … ACC-080, ACC-082). UserRole has no write policy and no INSERT/UPDATE/DELETE grant for anon or authenticated (the insert guarded / delete guarded policies are dropped). On top, a BEFORE INSERT OR UPDATE OR DELETE trigger, UserRole_staff_role_lock, refuses any write of a staff role — every role but CUSTOMER, AGENT and TOUR_LEADER — so the lock holds for the service role (edge functions) and for every SECURITY DEFINER function too. The one way in is role_change_admin_decide(): for an approved request it stamps the request with its own transaction id (applyTxid = txid_current()), sets app.role_change_request to the request's id for the transaction, and writes the row; the trigger accepts the write only when both match, the person, role and direction agree, and the request is still approved_by_ceo. A deleted login's rows may be cleared. A direct database session passes with app.role_change_maintenance = 'on' (runbook); role_change_is_api_session() (the role setting is anon / authenticated / service_role, or session_user is authenticator — true inside a definer function as well) makes the switch useless through the API. RoleChangeRequest is read by the people in a request and by roles.request / roles.approve / roles.apply; nobody writes it but the role_change_* functions, and a guard trigger keeps it moving forward only and never deleted. Test supabase/tests/a_role_changes_with_two_approvals.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 leadership-only access to something, create a permission, grant it to those roles, and check auth_user_has_permission('x.y'). A role-name list also rots silently: DIRECTOR appears in several of them and has never existed in the RoleType enum, so those clauses match nothing.

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 the super admin but not for an accountant — confirm the permission is in that role's bundle. SUPER_ADMIN holds everything; every other role holds only its bundle, and apply_role_bundles() deletes grants outside it. See docs/PERMISSIONS.md §5 and supabase/migrations/20260919100200_finance_role_bundles.sql.
  • Works locally but not on a restored database — roles have to be created by a migration too, not just granted. supabase/migrations/20260921110000_dr_seed_roles_and_permission_grants.sql creates a Role row per enum value; a test fails the build if a role or permission exists in no migration.
  • 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.