Security recipes

Each recipe names the threat it stops, the rules (access policy and/or SQL) and how to test it from both sides. Replace the table and column names with yours. Test every recipe with Simulate access before you ship before relying on it.

#Public read-only catalogue

Threat: visitors or users changing prices. Allows: anyone reads products; nobody but your team writes.

ALLOW IF ctx.resource.name == "products" AND ctx.action == "SELECT"
DENY  IF ctx.resource.name == "products" AND ctx.action IN ["INSERT", "UPDATE", "DELETE"] AND ctx.actor.lane == "product"

Test: with the anon key, select returns rows and update products set price = 0 is refused with 403.

#Users read and change only their own rows

Threat: one user reading or editing another's data by guessing ids.

alter table notes enable row level security;
alter table notes force row level security;
create policy "own notes" on notes
  for all using (owner_id = auth.uid()) with check (owner_id = auth.uid());

Test: as user A, select * from notes returns only A's notes; update notes set body = 'x' where id = <B's note> changes 0 rows. Signed out, the request is refused.

#Users create rows they own — and can't give them away

Threat: a user inserting a row with someone else's owner_id, or moving their row to another user.

alter table notes alter column owner_id set default auth.uid();
create policy "insert as yourself" on notes
  for insert with check (owner_id = auth.uid());
create policy "keep the owner" on notes
  for update using (owner_id = auth.uid()) with check (owner_id = auth.uid());

with check runs on the new row, so an update that sets owner_id to another user fails. Test: insert with owner_id set to another user's id — refused.

#Team-level data

Threat: members of one team reading another team's projects.

create table team_members (team_id uuid, user_id uuid, primary key (team_id, user_id));
create table team_docs (id bigint generated always as identity primary key, team_id uuid not null, body text);

create or replace function is_team_member(t uuid) returns boolean
language sql stable security definer set search_path = public as $
  select exists (select 1 from team_members where team_id = t and user_id = auth.uid())
$;

alter table team_docs enable row level security;
alter table team_docs force row level security;
create policy "team members" on team_docs
  for all using (is_team_member(team_id)) with check (is_team_member(team_id));

The security definer helper reads team_members without its own row security getting in the way, and still sees the caller through auth.uid(). Test: a user in team A gets 0 rows for team B's documents.

#Admin-only operations

Threat: ordinary users deleting or refunding. Give admins the app role ADMIN on the Users page.

DENY IF ctx.resource.name == "refunds" AND ctx.action IN ["INSERT", "UPDATE", "DELETE"] AND NOT ("ADMIN" IN ctx.actor.roles) AND ctx.actor.lane == "product"

#Public form submissions

Threat: visitors reading other submissions, or flooding the table. Allows: anyone inserts; nobody but your team reads.

ALLOW IF ctx.resource.name == "contact_requests" AND ctx.action == "INSERT"

Rate limits apply per IP and per project (API keys, origins and security); add CAPTCHA on sign-up if you take sign-ups too. Test: insert with the anon key works; select returns nothing.

#Read-only access for a server or partner

Create a custom API key with the viewer role and an expiry under Project Settings › API keys (API keys). Unlike the service role key it does not bypass row-level security: in SQL it acts as anon. Test: run an insert with the key in Simulate access before you ship or against staging — it must be refused.

#No cross-tenant access (SaaS apps)

Threat: a customer of your app seeing another customer's data. Put the tenant in the token with a JWT hook (Authentication settings reference) and compare every row against it.

create policy "own tenant" on invoices
  for all using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
  with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

Never take the tenant id from the request body. Test: run the same query as users of two tenants and compare row counts.

#Only some columns

Row-level security works on rows, not columns. Expose the safe columns through a view and allow the view instead of the table:

create view public_profiles with (security_invoker = true) as
  select id, display_name, avatar_url from profiles;   -- no email, no phone

security_invoker makes the view run with the caller's row security.

#Private files per user

Threat: users reading or overwriting each other's uploads. Store files as <user id>/<file> in a private bucket.

ALLOW IF ctx.resource.name == "documents" AND ctx.storage.in_own_folder == True AND ctx.action IN ["READ", "LIST", "WRITE", "DELETE"]

The bucket's Policies tab has this as a preset (rules there are named [bucket:documents] …). Share a file with someone else through a signed link that expires (Storage). Test: user A downloading B/file.pdf gets 403.

#Private realtime channels

Threat: anyone joining a channel and reading its messages. Allow subscribing and publishing per channel:

# Everyone signed in hears announcements; only staff send them
ALLOW IF ctx.resource.name == "announcements" AND ctx.action == "SUBSCRIBE" AND ctx.actor.is_anonymous == False
ALLOW IF ctx.resource.name == "announcements" AND ctx.action == "PUBLISH" AND "STAFF" IN ctx.actor.roles

# Each user's private channel: user:<their id> (and user:<their id>:anything)
ALLOW IF ctx.realtime.in_own_channel == True

For messages even Sovaria can't read, turn on end-to-end encryption for the channel (Realtime).