Aurabase Logo
aurabasedocs
docs›Guides›Row-Level Security

Row-Level Security in production

Three RLS patterns covering 95% of use cases: ownership, multi-tenant, hierarchical org. With pg_prove and debug tests.

12 min read·Level advanced·Revised on Apr 15, 2026
This English text was generated automatically from the French original and has not been reviewed yet.
#
Reminder

What is RLS

Row-Level Security is a native PostgreSQL feature that filters returned (or accepted) rows on the fly, by evaluating an SQL expression that may depend on the current role and JWT claims. Aurabase injects user claims via request.jwt.claims and exposes helpers auth.uid(), auth.role(), auth.tenant(), auth.has_role().

#
Pattern 1

Ownership — each user their own lines

Use cases: profiles, notes, posts where each line belongs to a unique user via user_id or author_id.

migrations/posts_rls.sqlsql
alter table posts enable row level security;

create policy "posts_own_read" on posts
  for select to authenticated
  using (auth.uid() = author_id);

create policy "posts_own_write" on posts
  for all to authenticated
  using (auth.uid() = author_id)
  with check (auth.uid() = author_id);
Astuce

Combine for all with using + with check to cover SELECT/UPDATE/DELETE in one policy. The INSERT only uses with check.

#
Pattern 2

Multi-tenant — scope by organization

Use case: B2B SaaS where each user belongs to an organization, and sees all the data of their org. The JWT contains tenant, the auth.tenant() helper returns it.

migrations/tenant_rls.sqlsql
create policy "orders_tenant_scope" on orders
  for all to authenticated
  using (tenant_id = auth.tenant())
  with check (tenant_id = auth.tenant());

-- Indispensable index for performance
create index idx_orders_tenant on orders(tenant_id);
#
Pattern 3

Hierarchical org — permissions by role

Use case: SaaS where the roles in an org (admin, member, viewer) have different permissions. We combine auth.tenant() with auth.has_role().

migrations/org_rls.sqlsql
create policy "invoices_read" on invoices
  for select to authenticated
  using (
    tenant_id = auth.tenant()
    and (auth.has_role('admin') or auth.has_role('billing'))
  );

create policy "invoices_write" on invoices
  for all to authenticated
  using (tenant_id = auth.tenant() and auth.has_role('admin'))
  with check (tenant_id = auth.tenant());
#
Testing

pg_prove — one assertion per policy

Policies are code. We test them. pgTAP + pg_prove run in CI on each migration.

tests/rls_posts.sqlsql
begin;
select plan(4);

-- seed
select tests.create_supabase_user(''alice@ex.com'');
select tests.create_supabase_user(''bob@ex.com'');

-- Alice creates a post
select tests.authenticate_as('alice@ex.com');
insert into posts(author_id, title) values (auth.uid(), 'hello');

-- Bob doesn't see her
select tests.authenticate_as('bob@ex.com');
select is_empty('select * from posts', 'Bob ne voit pas les posts d\'Alice');

select * from finish();
rollback;
#
Debug

When the policy doesn't do what you want

  • RLS disabled? Check with select relrowsecurity from pg_class where relname = 'posts'
  • Policies which add up: multiple policies on the same table + operation + role combine in OR. Restrict with AND in a single policy if necessary
  • Missing GRANT permissions: even with a policy, the role must have grant select on posts to authenticated
  • Claims not injected: from Postgres, select current_setting('request.jwt.claims', true) — null means you are in direct DB, not via the gateway
Last updated · Apr 15, 2026