Row-Level Security

Your project is a PostgreSQL database exposed to the public internet. Row-Level Security (RLS) is what decides, row by row, who is allowed to read and write it, so your tables stay safe even though the REST, storage, and realtime APIs face the world.

How it works

Every request to the data API runs inside your database as a database role, carrying the caller's identity. A policy is a SQL rule attached to a table that says which rows that role may see or change. Postgres applies your policies automatically on every select, insert, update, and delete; there is no application layer to bypass and nothing to remember to call.

RLS is opt-in per table but deny-by-default once enabled. Turn it on, and until you add a policy no one (except the service role, below) can touch the table through the API:

alter table public.profiles enable row level security;
-- With RLS on and no policy yet, every API read returns zero rows and
-- every write is rejected: "new row violates row-level security policy".

The three roles

Which role a request runs as is decided by the key or token it presents. There are three, and your policies are written in terms of them:

Inside a policy you read the caller's identity through four helper functions, available in every project:

Write policies against auth.*, not to <role>

This is the one thing that trips up people bringing policies from another platform. Postgres lets you target a policy at a grantee role with a to <role> clause. Do not use it with the names anon, authenticated, or service_role. Those names exist only as claims inside the token; the physical database roles behind them are private, per-project identifiers, so a policy written…

-- ✗ Does NOT work on Kethosbase: role "authenticated" does not exist here,
-- so this policy is created against a role that never runs, and never matches.
create policy "p" on public.notes for select
  to authenticated
  using ( true );

…silently applies to no one. Put the identity check in the using / with check expression instead, where the auth.* helpers do the work:

-- ✓ Works. No "to" clause: the policy is evaluated for every request, and
-- auth.role() distinguishes who is calling.
create policy "notes readable when signed in" on public.notes for select
  using ( auth.role() = 'authenticated' );

Recipes

Each user owns their rows

The most common shape. Store the owner's id in a column and compare it to auth.uid(). Default the column so a client can never write someone else's id:

create table public.notes (
  id       bigint generated always as identity primary key,
  owner    uuid not null default auth.uid(),
  body     text not null,
  created  timestamptz not null default now()
);
alter table public.notes enable row level security;

create policy "read own"   on public.notes for select
  using ( owner = auth.uid() );

create policy "insert own" on public.notes for insert
  with check ( owner = auth.uid() );

create policy "update own" on public.notes for update
  using      ( owner = auth.uid() )
  with check ( owner = auth.uid() );

create policy "delete own" on public.notes for delete
  using ( owner = auth.uid() );

Public read, owner-only write

A blog, say. Anyone may read, only the author may change:

create policy "posts are public"      on public.posts for select
  using ( true );

create policy "authors write posts"   on public.posts for insert
  with check ( author = auth.uid() );

create policy "authors edit own"      on public.posts for update
  using ( author = auth.uid() ) with check ( author = auth.uid() );

Team / multi-tenant scoping

When rows belong to an organization rather than a person, join to a membership table (or read a claim you put in the token). A membership check keeps the rule in one place:

create policy "members see their org rows" on public.invoices for select
  using (
    org_id in (
      select org_id from public.memberships
      where user_id = auth.uid()
    )
  );

If you mint your own claims (for example an org_id baked into the token), read them straight from auth.jwt() to skip the join:

using ( org_id = (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid )

Server-only tables

To make a table reachable only from your backend, enable RLS and write no policy at all. The anon and authenticated roles are denied everything; the secret key (service_role) bypasses RLS and still has full access.

Storage follows the same rules

File access is governed by RLS on storage.objects, exactly like your own tables; each upload records the uploader in owner_sub, and you gate access with owner_sub = auth.uid(). See Storage → Access control for the bucket recipes.

Testing your policies

The service role bypasses RLS, so a query that works from your backend proves nothing about what a user can see. Test as the actual role:

• In the dashboard. The Table Editor can impersonate a role or a specific user (the view as selector) and re-run the grid under their policies, so you see precisely the rows that user would.
• Over Direct SQL. Simulate a request by switching role and publishing claims inside a transaction:

  begin;
  select set_config('request.jwt.claims',
    '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}', true);
  set local role authenticated;   -- use the role your app connects as
  select * from public.notes;     -- only that user's rows come back
  rollback;

• From the API. Call with the publishable key (anon) and with a signed-in user's token, and confirm each sees only what it should.