Postgres RLS tenant isolation: enforce it and prove it with tests

TL;DR — Use a dedicated application role without BYPASSRLS, enable and force RLS on every tenant-scoped table, and write policies against a session variable such as app.current_tenant_id. The most common failure is testing as the table owner or a superuser, which silently bypasses policies unless you FORCE ROW LEVEL SECURITY and use a non-owner app role. Reading time: ~5 min

Goal

When you finish, your Postgres database will only return and mutate rows for the tenant ID set in the current session, and you will have repeatable SQL tests that prove cross-tenant SELECT, INSERT, UPDATE, and DELETE are blocked.

Prerequisites

• PostgreSQL 14+; check with:

psql --version

• A role that can create roles, schemas, tables, and policies in the target database.
• psql access to the target database; verify with:

psql "$DATABASE_URL" -c "select current_user, current_database();"

• A tenant key format decided up front. This article uses uuid in a tenant_id column.
• Two test tenant IDs to validate isolation:

11111111-1111-1111-1111-111111111111
22222222-2222-2222-2222-222222222222

• Your application must be able to run one SQL statement per transaction or request to set tenant context. This article uses:

select set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);

Steps

Step 1: Create a non-owner application role

⚠️ If your app currently connects as the table owner, changing roles can break writes until grants and policies are in place. Do this in a maintenance window if the app is live.

create role app_user login password 'replace-with-long-random-password' nosuperuser nocreatedb nocreaterole noinherit nobypassrls;

If this succeeds, psql returns CREATE ROLE.

Step 2: Create a tenant-scoped table owned by a separate role

If you already have tables, skip creation and apply the same pattern to each tenant-scoped table.

create table public.projects (
  id bigserial primary key,
  tenant_id uuid not null,
  name text not null,
  created_at timestamptz not null default now()
);

create index projects_tenant_id_idx on public.projects (tenant_id);

If this succeeds, psql returns CREATE TABLE and CREATE INDEX.

Step 3: Enable and force row-level security

FORCE ROW LEVEL SECURITY matters because table owners otherwise bypass RLS.

alter table public.projects enable row level security;
alter table public.projects force row level security;

If this succeeds, psql returns ALTER TABLE twice.

Step 4: Grant only the minimum table privileges to the app role

Do not grant ownership. Do not use a superuser. Do not grant to PUBLIC.

revoke all on public.projects from public;
grant select, insert, update, delete on public.projects to app_user;
grant usage, select on sequence public.projects_id_seq to app_user;

If this succeeds, psql returns REVOKE and GRANT lines with no errors.

Step 5: Create RLS policies tied to a session variable

This pattern fails closed when the variable is missing because current_setting(..., true) returns NULL, and comparisons against NULL do not pass.

create policy projects_select_tenant on public.projects
  for select
  using (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

create policy projects_insert_tenant on public.projects
  for insert
  with check (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

create policy projects_update_tenant on public.projects
  for update
  using (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
  with check (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

create policy projects_delete_tenant on public.projects
  for delete
  using (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

If this succeeds, psql returns CREATE POLICY four times.

Step 6: Seed two tenants of data as an admin role

Insert test rows before switching to the app role.

insert into public.projects (tenant_id, name) values
  ('11111111-1111-1111-1111-111111111111', 'tenant-a-project-1'),
  ('11111111-1111-1111-1111-111111111111', 'tenant-a-project-2'),
  ('22222222-2222-2222-2222-222222222222', 'tenant-b-project-1');

If this succeeds, psql returns INSERT 0 3.

Step 7: Test reads as the application role with tenant A set

Use SET ROLE inside an admin session, or connect directly as app_user.

set role app_user;
select set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
select id, tenant_id, name from public.projects order by id;

You should see exactly the two tenant-a-* rows and no tenant B rows.

Step 8: Test that cross-tenant writes fail

First try an insert with the wrong tenant ID, then an update that attempts to move a row to another tenant.

set role app_user;
select set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);

insert into public.projects (tenant_id, name)
values ('22222222-2222-2222-2222-222222222222', 'should-fail');

update public.projects
set tenant_id = '22222222-2222-2222-2222-222222222222'
where name = 'tenant-a-project-1';

You should see errors shaped like:

ERROR:  new row violates row-level security policy for table "projects"
ERROR:  new row violates row-level security policy for table "projects"

Step 9: Test that missing tenant context fails closed

Do not set app.current_tenant_id for this test.

reset role;
set role app_user;
reset app.current_tenant_id;
select count(*) from public.projects;
insert into public.projects (tenant_id, name)
values ('11111111-1111-1111-1111-111111111111', 'should-also-fail');

You should see count = 0 for the SELECT, and the INSERT should fail with violates row-level security policy.

Step 10: Add a reusable test script for CI or pre-deploy checks

Save this as rls_test.sql and run it in a disposable database or transaction.

begin;
set role app_user;

select set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
select case when (select count(*) from public.projects where tenant_id = '11111111-1111-1111-1111-111111111111') >= 1 then 1 else pg_sleep(0) end;
select case when (select count(*) from public.projects where tenant_id = '22222222-2222-2222-2222-222222222222') = 0 then 1 else 1/0 end;

savepoint s1;
insert into public.projects (tenant_id, name) values ('22222222-2222-2222-2222-222222222222', 'blocked');
rollback to s1;

savepoint s2;
update public.projects set tenant_id = '22222222-2222-2222-2222-222222222222' where tenant_id = '11111111-1111-1111-1111-111111111111';
rollback to s2;

rollback;

Run it with:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f rls_test.sql

If this succeeds, psql exits 0. If a policy leaks data or permits a forbidden write, psql exits non-zero.

Verify it works

Run these exact checks.

set role app_user;
select set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
select array_agg(name order by name) from public.projects;

Expected result shape:

               array_agg
----------------------------------------
 {tenant-a-project-1,tenant-a-project-2}
(1 row)

Then switch tenants:

set role app_user;
select set_config('app.current_tenant_id', '22222222-2222-2222-2222-222222222222', true);
select array_agg(name order by name) from public.projects;

Expected result shape:

       array_agg
------------------------
 {tenant-b-project-1}
(1 row)

Finally, prove writes are blocked across tenants:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
set role app_user;
select set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
insert into public.projects (tenant_id, name) values ('22222222-2222-2222-2222-222222222222', 'x');
SQL
printf 'exit_code=%s\n' "$?"

Expected output shape:

ERROR:  new row violates row-level security policy for table "projects"
exit_code=3

Common pitfalls

Testing as the table owner or superuser

Mistake: running checks as the table owner, database owner, or superuser. Symptom: queries return all rows even though policies exist. Fix: connect as app_user or run SET ROLE app_user; and keep ALTER TABLE ... FORCE ROW LEVEL SECURITY; in place.

Forgetting FORCE ROW LEVEL SECURITY

Mistake: only ENABLE ROW LEVEL SECURITY is set. Symptom: owner sessions still bypass RLS, so manual testing looks fine for app users but admin jobs accidentally read everything. Fix:

alter table public.projects force row level security;

Using a role with BYPASSRLS

Mistake: the application role was granted BYPASSRLS directly or inherited it from another role. Symptom: all tenant rows are visible regardless of current_setting. Fix:

alter role app_user nobypassrls noinherit;

Not setting tenant context at transaction start

Mistake: pooled connections reuse old session state or have no tenant set. Symptom: one request sees another tenant's rows, or sees zero rows intermittently. Fix: at the start of every transaction, run:

select set_config('app.current_tenant_id', '<tenant-uuid>', true);

Use transaction pooling carefully; if your pooler resets session state, set it after BEGIN.

Policy exists, but table lacks tenant_id index

Mistake: RLS works but every query scans the whole table. Symptom: tenant-scoped endpoints get slow as data grows. Fix:

create index if not exists projects_tenant_id_idx on public.projects (tenant_id);

Forgetting WITH CHECK on INSERT or UPDATE

Mistake: only a USING clause is defined. Symptom: app can insert or change rows to another tenant even if later reads hide them. Fix: define both clauses for UPDATE, and WITH CHECK for INSERT, exactly as shown in Step 5.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.