Postgres Row Level Security: A Practical Guide

Postgres row level security (RLS) lets the database decide which rows each user may read, insert, update or delete. You turn it on per table with alter table ... enable row level security, then write policies: SQL conditions that Postgres adds to every query on that table. Once it is on, a query without a matching policy sees no rows, no matter what the application code asks for.

RLS matters most when clients talk to the database through an API, as with StitchDB, Supabase or PostgREST, because the browser or mobile app holds a key that reaches the database directly. The policy is the only thing between a user and everyone else's rows.

#Postgres row level security: the minimum you need to know

Four facts cover most of RLS:

1. RLS is off by default on new tables in plain Postgres. With it off, every role that has table grants sees every row.
2. With RLS on and no policies, nothing is visible (for roles that do not own the table and do not bypass RLS).
3. Policies are per command: select, insert, update, delete, or all.
4. The table owner and superusers bypass RLS unless you use force row level security. Roles with the BYPASSRLS attribute bypass it too.

That fourth point surprises people. If your migration tool or server connects as the table owner, your policies do not apply to it. That is usually what you want for migrations and back-office jobs, but be aware of it.

#USING and WITH CHECK

A policy has up to two expressions:

• using (...) filters existing rows. It applies to select, update and delete: which rows can I see or touch?
• with check (...) validates new row values. It applies to insert and update: is the row I am writing allowed?

create table public.notes (
  id bigint generated always as identity primary key,
  owner_id uuid not null default auth.uid() references auth.users on delete cascade,
  body text not null,
  created_at timestamptz not null default now()
);

alter table public.notes enable row level security;

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

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

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

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

The update policy needs both. Without with check, a user could update their own note and set owner_id to someone else's id, handing the row away (or worse, writing into another user's space).

auth.uid() is a helper that returns the id of the signed-in user from their access token. It exists in StitchDB and other backends built on the same open-source stack. On plain Postgres you would read a setting your app sets per request, for example current_setting('app.user_id')::uuid.

#Roles: who the policy is for

API backends map each request to a database role. On StitchDB:

Always write to authenticated or to anon explicitly. A policy without to applies to public, meaning every role, which is rarely what you meant.

#Permissive and restrictive policies

By default, policies are permissive: if any permissive policy for the command passes, the row is allowed. Policies are combined with OR.

A restrictive policy must pass in addition to at least one permissive policy. They are combined with AND. Use restrictive policies for rules that must always hold, such as "this table requires multi-factor authentication":

create policy "MFA required" on public.payouts
  as restrictive for all to authenticated
  using ((auth.jwt() ->> 'aal') = 'aal2');

Here auth.jwt() returns the user's token claims, and aal2 means the session was verified with a second factor. See Multi-factor authentication.

A common bug is relying on a single restrictive policy and forgetting the permissive one. With only restrictive policies, nothing passes.

#Patterns you will use

#Public read, owner write

create policy "anyone reads published posts" on public.posts
  for select to anon, authenticated
  using (published);

create policy "authors edit their posts" on public.posts
  for update to authenticated
  using (author_id = auth.uid())
  with check (author_id = auth.uid());

#Team or organization membership

For B2B apps, rows belong to a team. Put the membership check in a function so policies stay short:

create table public.members (
  org_id uuid not null,
  user_id uuid not null references auth.users on delete cascade,
  role text not null default 'member',
  primary key (org_id, user_id)
);

create function public.is_member(o uuid) returns boolean
language sql stable security definer set search_path = ''
as $$
  select exists (
    select 1 from public.members m
    where m.org_id = o and m.user_id = auth.uid()
  )
$$;

create policy "members read projects" on public.projects
  for select to authenticated
  using (public.is_member(org_id));

security definer lets the function read members even though the caller's own policies might hide it, and set search_path = '' prevents search-path tricks. Keep members itself protected with RLS.

StitchDB's Organizations feature installs this for you: tables, invites, and helpers such as auth.is_member(org_id) and auth.has_role('admin', org_id). For the bigger picture, see Multi-Tenant SaaS on Postgres.

#Claims from the token

Use app_metadata claims for authorization, never user_metadata. Users can edit their own user_metadata.

create policy "staff read tickets" on public.tickets
  for select to authenticated
  using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'staff');

#Performance

RLS conditions are added to every query, so they need the same care as where clauses.

• Index the columns policies filter on. owner_id, org_id, user_id. Without an index, every select is a scan.
• Wrap per-request functions in a subquery. using (owner_id = (select auth.uid())) lets Postgres evaluate the function once per statement instead of once per row. On large tables this can make a big difference.
• Prefer exists and simple joins over in (select ...) on large membership tables, and index the membership table on (user_id, org_id).
• Still filter in the client. The policy guarantees safety; an explicit .eq('org_id', id) in the query helps the planner use the right index.

Check plans with explain analyze while running as the target role:

begin;
set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"7c1e...","role":"authenticated"}', true);
explain analyze select * from public.notes;
rollback;

#Views, functions and other leaks

RLS protects tables. Several things can route around it:

• Views run with their owner's rights unless you set security_invoker. Fix with alter view public.my_view set (security_invoker = on); (Postgres 15+).
• Materialized views have no RLS at all. Do not expose them to the API if they contain per-user data.
• security definer functions callable through the API run as their owner. Check every one.
• Realtime subscriptions on StitchDB respect RLS, but a published table with RLS off sends every row to every subscriber.

#Testing your policies

Do not trust policies you have not tested from the user's side. Three approaches:

1. SQL tests. Use the set local role pattern above in a transaction, insert fixtures, assert counts, roll back. pgtap makes this repeatable in CI.
2. Two real accounts. Sign in as user A, create data; sign in as user B, try to read and update it through the API.
3. Tooling. StitchDB's Studio has Database › Policies › Check access: pick a user, a table, a row and an action, and it runs every policy as that user and explains which one lets them in or keeps them out. The Exposure check shows what an anonymous visitor and a random signed-in user can read and write right now. Both run in transactions that are rolled back.

#Common mistakes

• Creating a table with SQL and forgetting enable row level security. On StitchDB, RLS Guard turns RLS on for new tables in public by default in new projects, and Studio flags tables without it.
• An update policy with using but no with check.
• A policy without to, so it applies to every role.
• Trusting user_metadata claims for authorization.
• Shipping the service_role key in a mobile app. It bypasses every policy.
• Exposing a view that bypasses RLS.

#FAQ

#Does RLS slow Postgres down?

It adds a condition to each query. With indexes on the filtered columns and functions wrapped in subqueries, the overhead is usually small.

#Can I use RLS without an API layer?

Yes. Set the user id in a per-transaction setting from your server and reference it in policies. It is a good defense in depth for multi-tenant apps.

#Why does my query return no rows?

RLS is on and no policy allows the request. Add a policy, or use a policy checker to see which condition fails. See Row Level Security.

#Should storage files use RLS too?

On StitchDB, yes: file access is controlled by policies on storage.objects. See Storage policies.

Build an app on Postgres with policies, Auth and APIs ready to go: start free on StitchDB, or read our Postgres security checklist.