Row level security in practice: a hands-on tutorial

PostgreSQL Row Level Security (RLS) is the absolute cheapest, most robust authorization layer you will ever write. It enforces security rules directly inside the database engine, rendering your data safe regardless of bugs in your API routes or ORM queries.

However, RLS is also the easiest feature to get subtly and dangerously wrong. A single recursive policy or missing GRANT statement can either lock your app out entirely or leave tenant data exposed.

Step 1: roles live in their own table

A common anti-pattern is storing a user's role directly on their profiles or users table (e.g., profiles.role = 'admin'). If a user ever gains permission to update their own profile name or avatar, a crafted payload can allow them to escalate their own privileges to 'admin'.

To prevent privilege escalation, isolate user roles inside a dedicated junction table that regular users can read, but never modify:

create type public.app_role as enum ('admin', 'editor', 'user');

create table public.user_roles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  role app_role not null,
  unique (user_id, role)
);

grant select on public.user_roles to authenticated;
alter table public.user_roles enable row level security;

Step 2: a security definer helper

If you write an RLS policy on user_roles that queries user_roles directly to check for admin status, Postgres will enter an infinite recursion loop and crash the query.

The clean solution is creating a SECURITY DEFINER function. This helper executes with the privileges of the function owner (bypassing RLS during the role check), breaking the recursion cycle while remaining completely safe:

create or replace function public.has_role(_user_id uuid, _role app_role)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = _user_id and role = _role
  )
$$;

Adding set search_path = public prevents schema poisoning attacks, and setting the function to STABLE allows Postgres to cache the authorization lookup per query execution for maximum performance.

Step 3: test as a real user

An un-tested RLS policy is a false sense of security. Always write automated tests that impersonate non-admin users before shipping to production.

• Enforce negative assertions — Test that a standard user cannot select or update another tenant’s rows. Positive tests only prove your app works; negative tests prove your security holds.
• Verify table GRANTs — Remember that RLS policies only filter rows (USING / WITH CHECK). You still need explicit SQL GRANT SELECT, INSERT, UPDATE permissions on the table for the authenticated role.
• Audit with SET LOCAL ROLE — Use Postgres transaction blocks (SET LOCAL ROLE authenticated; SET LOCAL "request.jwt.claim.sub" = '...';) inside your test suite to run queries as a real authenticated user.

A security policy you have not actively tried to break is a policy you have not actually written.