Row-Level Security in production
Three RLS patterns covering 95% of use cases: ownership, multi-tenant, hierarchical org. With pg_prove and debug tests.
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().
- Role names. The canonical database roles are
aura_anon,aura_authenticatedandaura_service_role. On managed clusters, group aliasesanon,authenticated, andservice_rolealso map to them (note thatBYPASSRLSstays strictly onaura_service_rolealone). - RLS is on by default for tables created through Aurabase. Any table created with
/raw, a migration, Studio, or the table API automatically receivesENABLEandFORCE ROW LEVEL SECURITY(unlessDISABLE ROW LEVEL SECURITYis 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_anonandaura_authenticatedhold 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 toaura_anonoraura_authenticatedon 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()isNULLfor the public key and for aservice_rolekey; it returns an id only for a signed-in user. Useto aura_authenticatedto 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.
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);
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).
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().
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:
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
SELECTpolicy returns[]with ameta.hintthat names the role and the policy to add.aura db raw "select * from posts" --as anonshows what a visitor sees. - RLS disabled?
aura db table listmarks 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 = trueis 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