Row-level security

Row-level security (RLS) is a Postgres feature: each table has policies that decide which rows a signed-in user can read or change. In this kit the policies, not the app code, are what keep one customer's data away from another's. A query from the browser, a server component or the phone app runs as the signed-in user, and Postgres filters it.

What is in place

• RLS is on for all 13 tables in public. schema-conditions.test.sql raises an error if any table in public has it off, so a new table without it fails the tests.
• Grants are narrow. Each table starts with revoke all from anon, authenticated and service_role, then grants back only what is needed. schemas/00-privileges.sql (part of the baseline migration) also strips the TRUNCATE, REFERENCES and TRIGGER privileges Supabase grants by default (TRUNCATE ignores RLS), and privileges.test.sql checks they stay gone.
• Users cannot move rows between accounts. Where users may update a table, the grant lists the editable columns only, never id or account_id.
• Billing tables are read-only for users. Signed-in users can SELECT their subscriptions, orders and billing customer rows. Only service_role can write them, which happens in the payment webhook after the provider's signature is checked. Nobody can give themselves a paid plan through the API.
• Two-step sign-in is enforced in the database. Restrictive policies require a completed second step from users who set one up (see Two-step sign-in and passkeys).

Roles and permissions

Team members have a role. The seed (17-roles-seed.sql) creates two:

Roles have a rank (hierarchy_level; a lower number is more senior). The team's primary owner can act on any member. Anyone else needs members.manage and can only act on members ranked below them, and nobody can act on the primary owner (can_action_account_member). You can add roles and change these permissions in your own migration.

Helper functions

Policies call these functions from public. Use them in your own policies rather than writing the joins again.

They are SECURITY DEFINER and granted to authenticated on purpose, because the policies call them as the signed-in user. Do not move them to another schema or revoke execute: every policy that uses them would fail.

A typical policy for your own table:

create policy "projects_read" on public.projects for select
  to authenticated using (
    account_id = (select auth.uid())
    or public.has_role_on_account(account_id)
  );

The first line covers the user's personal account, the second any team they belong to.

Files

The storage bucket account_image (profile pictures and team logos) has its own policies in 16-storage.sql: each file is named after the account id, members of that account can read it, and only the owner of a personal account or a team member with settings.manage can upload, replace or delete it. See File uploads.

What RLS does not cover

• The admin client. getSupabaseServerAdminClient() uses the secret key and skips RLS. The kit uses it in server code for the payment and database webhooks, invitations, billing, one-time codes, account deletion and the admin panel. Code that uses it has to check permissions itself, so use it only when the normal client cannot do the job.
• Public file URLs. The account_image bucket is public, so anyone with a file's URL can load it. Do not store private files there; create a private bucket with its own policies.
• Other schemas. The RLS check in schema-conditions.test.sql covers the public schema only.

How it is tested

13 of the 23 pgTAP test files sign in as one user and try to read or change another account's data, and assert that it fails. See Database tests.