Security & RBAC
Row-Level Security native Postgres, predefined roles, multi-tenant RBAC, audit logs. Your rules live in the database — testable, versioned, impossible to circumvent on the client side.
Security lives in the base#
Aurabase uses PostgreSQL's native Row-Level Security (RLS). This means that your access rules are written in SQL, stored in the database, applied to each request — including those made by the SDK, PostgREST, or direct psql.
On a MongoDB project, there is no RLS. Isolation is ensured by a reserved field _owner_id: any reading of a non-privileged role is filtered on the sub of the JWT, and any writing stamps this field. Three consequences to be aware of before debugging an empty list: a document created with a service_role key has no _owner_id and therefore becomes invisible to all authenticated users ([] response in HTTP 200, without error); a anon caller without a JWT receives an 403 on a non-public collection; and _owner_id, reserved, can never be modified by the client. A collection is opened for reading to all by the explicit opt-in public_read.
Circumventing a policy is impossible on the client side. The gateway validates each JWT before opening a Postgres connection with the appropriate role. The claims (auth.uid(), auth.role(), auth.tenant()) are injected via request.jwt.claims — cannot be modified from an SQL query.
Tables created through Aurabase (Studio, table API, migrations, or /raw) get ENABLE and FORCE ROW LEVEL SECURITY automatically by default (unless explicitly disabled in DDL). Always verify RLS on tables created over a direct psql connection with alter table ... enable row level security before adding policies.
JWT → claims → policy → line#
Each query follows the same path. The gateway decodes the JWT, verifies its signature (ES256 with the project-derived secret by default; RS256 with a JWKS only when an RSA key is configured), extracts the claims, opens a Postgres connection with SET LOCAL role + SET LOCAL request.jwt.claims. All policies that reference auth.*() use these claims.
Client → Gateway verify JWT (ES256 via JWKS, project key)
↓ extract { sub, role, tenant, exp }
Postgres SET LOCAL role = authenticated
SET LOCAL request.jwt.claims = {...}
↓
SELECT ... FROM posts WHERE ...
↓ (RLS injecte la policy USING)
SELECT ... FROM posts WHERE author_id = auth.uid()
Three Postgres roles, clear hierarchy#
| Role | Claims | Description |
|---|---|---|
anon | No user_id | Requests made with the anon key and no signed user JWT |
authenticated | auth.uid() is the user | A signed-in user with a valid JWT, from any Aurabase session |
service_role | Bypasses RLS | Backend key that passes every policy. Server only. |
Five essential patterns#
Policies are attached to a table + an operation (SELECT/INSERT/UPDATE/DELETE) + a role. Multiple policies on the same pair are combined in OR. Use using to filter existing rows, with check to validate new values.
alter table posts enable row level security;
-- Reading: author only
create policy "posts_own_read"
on posts for select
to aura_authenticated
using (auth.uid() = author_id);
Test your policies with pg_prove. One rls_posts.sql file per table, executed in CI on each migration. See the RLS guide.
Beyond anon/authenticated#
The application roles (admin, editor, viewer, billing, etc.) live in the roles array of the user's app_metadata, which only the server can write (the admin API PUT /admin/users/{id} with a service_role key, or the access-token hook). Each user can have N roles. They travel in the token under app_metadata.roles, and auth.has_role('admin') reads them in your policies. user_metadata never grants a role: users can edit it themselves. Do not confuse them with the token's role claim (anon or authenticated: the Postgres tier, read by auth.role()) nor with the user's role field (a free label shown in the Studio, such as user or editor): neither of these grants an application role.
-- Admin sees everything
create policy "posts_admin_read"
on posts for select
to aura_authenticated
using (auth.has_role('admin'));
-- Billing sees sensitive columns in dedicated view
create view posts_billing as
select id, author_id, revenue_cents from posts
where auth.has_role('billing');
Complete trace, SIEM export#
Each security event is logged in project_{uuid}_auth.auth_audit_log: signin, signout, password_change, mfa_enroll, role_granted, service_role_access, failed_auth. 30-day retention on Pro, 2 years on Enterprise.
Automatic export to your SIEM (Splunk, Datadog, Elastic) via webhook or Kafka stream. See /docs/compliance for detailed event schemas.