Skip to content
Aurabase Logo
aurabase

Row-Level Security in production

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

How-to guide12 min readLevel advancedRevised on Apr 15, 2026

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 canonical database roles are aura_anon, aura_authenticated and aura_service_role. On managed clusters, group aliases anon, authenticated, and service_role also map to them (note that BYPASSRLS stays strictly on aura_service_role alone).
  • RLS is on by default for tables created through Aurabase. Any table created with /raw, a migration, Studio, or the table API automatically receives ENABLE and FORCE ROW LEVEL SECURITY (unless DISABLE ROW LEVEL SECURITY is explicitly executed in the same DDL batch): public/anon queries return [] until you add a policy. Tables created over a direct PostgreSQL connection (psql, Prisma, Drizzle) never pass through that hook, so they get no RLS and, instead, no access for the public keys: aura_anon and aura_authenticated hold no privilege on them until you grant it. To expose such a table to the SDK, enable RLS first, then grant and add a policy: ALTER TABLE posts ENABLE ROW LEVEL SECURITY; GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO aura_authenticated; CREATE POLICY .... Never grant to aura_anon or aura_authenticated on a table whose RLS is off: every holder of the public key, or every signed-up user, would read (and, with write grants, modify) all its rows.
  • 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.

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);
Tip

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

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);

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

pg_prove — one assertion per policy#

Test your policies — as a real user, in CI

Policies are code, so test them. aura db raw ... --as authenticated --user <uuid> runs the SQL inside PostgreSQL with auth.uid() and auth.role() set for that user (add --dry-run to roll writes back), so the answer is exactly what your app will see:

tests/rls.shbash
set -euo pipefail
ALICE=$(aura auth users create --email alice@example.com --password 'Passw0rd!long1' --json | jq -r .id)
BOB=$(aura auth users create --email bob@example.com --password 'Passw0rd!long1' --json | jq -r .id)

aura db raw "insert into posts(author_id, title) values ('$ALICE', 'hello')"
# Bob must not see Alice's post
test "$(aura db raw "select count(*) as n from posts" --as authenticated --user "$BOB" --json | jq -r '.[0].n')" = "0"
# Alice must see it
test "$(aura db raw "select count(*) as n from posts" --as authenticated --user "$ALICE" --json | jq -r '.[0].n')" = "1"
# A visitor sees nothing
test "$(aura db raw "select count(*) as n from posts" --as anon --json | jq -r '.[0].n')" = "0"

Run it in CI after aura db push. The RLS simulator of Studio (Database › a table › RLS simulator) runs the same check inside a rolled-back PostgreSQL transaction and says so next to the result; pgTAP and pg_prove need a direct PostgreSQL connection, which not every deployment publishes.

When the policy doesn't do what you want#

  • Empty result? An anonymous or signed-in read of a table with RLS and no matching SELECT policy returns [] with a meta.hint that names the role and the policy to add. aura db raw "select * from posts" --as anon shows what a visitor sees.
  • RLS disabled? aura db table list marks tables with [RLS], and the Advisor warns about tables with RLS disabled and tables with RLS but no policy. Turning RLS off (aura db table rls posts off) makes the table readable by anyone holding the public key.
  • Views. A view reads with the privileges of the reader (security_invoker = true is applied to every view you create), so the policies of its tables apply.
  • 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

To go further#