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().

Three things to know before writing a policy
  • Role names. The database roles are aura_anon, aura_authenticated and aura_service_role. to authenticated fails with role "authenticated" does not exist.
  • RLS is on by default for tables created through Aurabase. A table created with /raw, a migration or the table API gets enable and force row level security: it returns no row to the public key until you add a policy. A table created through a direct database connection is not covered.
  • The public key has no user. auth.uid() is NULL for the public key and for a service_role key; it returns an id only for a signed-in user. Use to aura_authenticated to restrict a policy to signed-in users.
#
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 aura_authenticated
  using (auth.uid() = author_id);

create policy "posts_own_write" on posts
  for all to aura_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 carries app_metadata.tenant, which the auth.tenant() helper returns (NULL when absent or not a UUID, so the policy denies).

migrations/tenant_rls.sqlsql
create policy "orders_tenant_scope" on orders
  for all to aura_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 aura_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 aura_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 aura_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