Row level security

Your frontend talks to Postgres directly through the REST API, storage and realtime. RLS policies are the security boundary.

How a request becomes a database role

Caller	Postgres role	`auth.uid()`
publishable key, no user	`anon`	NULL
publishable key + signed-in user	`authenticated`	the user’s id
server with a secret key	`service_role` (bypasses RLS)	NULL
SQL editor, migrations	the project owner role (not subject to RLS)	NULL

Helpers available in policies (all STABLE):

Function	Returns
`auth.uid()`	the user id as `uuid`, NULL for anon
`auth.role()`	`anon`, `authenticated` or `service_role`
`auth.jwt()`	all token claims as `jsonb`
`auth.claim('name')`	one top-level claim as text
`auth.aal()`	the session’s assurance level, `aal1` or `aal2` (MFA)
`auth.tenant_id()`, `auth.tenant_role()`, `auth.has_tenant_role('admin')`	active tenant, only when enabled

Grants and policies work together

Postgres first checks the privilege, then RLS filters rows. Write both:

grant select, insert, update, delete on public.orders to authenticated;

create policy orders_select on public.orders for select to authenticated
  using (user_id = (select auth.uid()));
create policy orders_insert on public.orders for insert to authenticated
  with check (user_id = (select auth.uid()));
create policy orders_update on public.orders for update to authenticated
  using (user_id = (select auth.uid()))
  with check (user_id = (select auth.uid()));   -- stops moving a row to another user
create policy orders_delete on public.orders for delete to authenticated
  using (user_id = (select auth.uid()));

create index orders_user_id_idx on public.orders (user_id);

USING filters the rows a command can see (SELECT, and the target rows of UPDATE and DELETE).
WITH CHECK validates the rows a command writes (INSERT, and the new version in UPDATE).
Permissive policies for the same command and role are OR-ed. Restrictive policies are AND-ed on top and grant nothing on their own.

Patterns

Owner only

The example above. In the dashboard Database → Policies editor, choose Owner only and a uuid owner column.

Membership

create policy members_read_projects on public.projects for select to authenticated
  using (exists (select 1 from public.project_members m
                  where m.project_id = projects.id and m.user_id = (select auth.uid())));
create index on public.project_members (user_id, project_id);

Roles from the token

Add a role to the access token with the claims hook and read it in policies instead of querying on every row:

create policy admins_manage_products on public.products for all to authenticated
  using ((select auth.claim('app_role')) = 'admin')
  with check ((select auth.claim('app_role')) = 'admin');

A claim changes at the next token refresh (at most the access-token lifetime, 10 minutes by default).

Require MFA for sensitive writes

create policy "payouts need MFA" on public.payouts as restrictive for insert to authenticated
  with check ((select auth.aal()) = 'aal2');

Restrictive guards for invariants

Use permissive policies to say who may do what, and restrictive policies for rules that must always hold, such as tenant isolation.

Write fast policies

Wrap helpers in a sub-select: (select auth.uid()) is evaluated once per statement instead of once per row.
Index the owner column and the columns used in membership checks.

Storage and realtime use the same mechanism

Storage access is decided by policies on storage.objects.
Realtime database changes are checked against the table’s SELECT policy as the subscriber. Private channels use policies on realtime.channel_access.

Security advisor

The dashboard Advisors page and potalab base lint check the same rules, for example tables without RLS, SECURITY DEFINER functions without search_path, functions executable by anon, unwrapped auth.uid(), unindexed owner columns, views that bypass RLS and tenant tables missing a restrictive guard.

Common mistakes

Symptom	Cause
`42501 permission denied for table`	no `GRANT` to the role
empty result, no error	grant present but no policy matches
insert works, `.insert().select()` fails	no SELECT policy matching the new row
slow list queries	unwrapped `auth.uid()` or an unindexed owner column
a view shows every row	views run as their owner: use `with (security_invoker = true)`