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.