Row level security

Putting authorization in the database so no client path can skip it.

Why it belongs in the database

A policy enforced by PostgreSQL applies to the Data API, a direct psql session and your application alike. Application-level checks only cover the code paths you remembered to write.

Enabling is a decision

Turning on row level security for a table denies everything until a policy allows it. That is the correct default, and it will break reads immediately if you enable it without a policy in place.

A tenant policy

Policies are per command. Read access and write access are usually different questions, so give them different policies.

sql

alter table documents enable row level security;

create policy documentsTenantRead
  on documents for select
  using (tenant_id = current_setting('app.tenant_id')::uuid);

create policy documentsTenantWrite
  on documents for insert
  with check (tenant_id = current_setting('app.tenant_id')::uuid);

Test as the application role

A policy that looks right as a superuser can be wrong for the role your application connects with. Switch roles and run the exact queries your code issues.

sql

set role applicationRole;
set app.tenant_id = '00000000-0000-0000-0000-000000000001';

select count(*) from documents;

reset role;