Row-level security makes Postgres check, per row, whether the current session is allowed to see or change it. You write a policy once, and Postgres applies it to every query against that table.

The invoices table

create table invoices (
  id        int primary key,
  tenant_id int not null,
  amount    numeric not null
);

insert into invoices values
  (1, 1, 120), (2, 1, 340), (3, 1, 90), (4, 1, 500),
  (5, 2, 210), (6, 2, 75);

Four rows belong to tenant 1 and two to tenant 2. Everything below is the same statement, select count(*) from invoices, run again after one change.

After Count
No RLS 6
RLS on, no policies 0
One SELECT policy for tenant 1 4
The same query as the table owner 6
force row level security 4
A second policy added for tenant 2 6

Run it in a scratch database while you read.

Policies

A policy is a boolean expression against the columns of one row. Postgres evaluates it for every row a statement touches, and drops the rows where it comes back false.

alter table invoices enable row level security;

create policy tenant_read on invoices
  for select
  using (tenant_id = current_setting('app.tenant_id')::int);

Deny by default

Between the alter table and the create policy above, the count is 0.

With no policies, nothing evaluates true. No error and no warning, and every read against that table returns an empty set. Enable RLS on a live table before the policies exist and the application keeps running while returning nothing.

The session variable

current_setting('app.tenant_id') reads a runtime parameter off the current connection. You set it the same way you set any other:

set local app.tenant_id = '1';
select count(*) from invoices;  -- 4

set local scopes it to the transaction, which is what you want with a connection pool. Change it to '2' and the same query returns 2 without touching the policy.

I'd do this by hand once before reaching for auth.uid(), which hides this step.

Owner bypass

Connect as the role that owns invoices and run the same query. You get 6.

Table owners are exempt from RLS unless you force it. Superusers and any role with BYPASSRLS are exempt too, and force doesn't apply to them.

alter table invoices force row level security;

Now the owner gets 4. That's the usual cause of "it works in psql but not in the app": one of the two connections owns the table. \dt in psql lists the owner of each one.

Permissive and restrictive

Add a second policy and the count goes up.

create policy tenant_two_read on invoices
  for select
  using (tenant_id = 2);

select count(*) from invoices;  -- 6, with app.tenant_id still '1'

Policies are permissive by default, and permissive policies for the same command combine with OR. A row is visible if any one of them allows it. Adding a policy can only widen access, never narrow it, so the mistake fails open. Two ways to narrow:

create policy tenant_only on invoices
  as restrictive
  for select
  using (tenant_id = current_setting('app.tenant_id')::int);

Restrictive policies AND with everything else. The other way is to put the AND inside a single permissive policy and keep one policy per command.

USING and WITH CHECK

USING filters rows that already exist — what a SELECT returns, and which rows an UPDATE or DELETE is allowed to touch. WITH CHECK validates row values on the way in, for INSERT and for the post-update state of an UPDATE.

Omit WITH CHECK and Postgres uses the USING expression for the check as well, which is the safe default. Writing one explicitly can be weaker than that default:

create policy tenant_write on invoices
  for insert
  with check (true);

insert into invoices values (7, 2, 999);  -- succeeds, session is tenant 1

A read policy tells you nothing about what that role can write.

Policies attach per command — for select, for insert, for update, for delete, or for all. A table with a SELECT policy and nothing else denies every write.

Supabase

auth.uid() is the same mechanism as current_setting above. Supabase puts the JWT claims on the connection and the helper reads the subject out of them.

The keys map onto rows in the count table above. The anon and authenticated roles don't own your tables, so policies apply to them. The service role key connects as service_role, which carries BYPASSRLS, so it returns 6 and keeps returning 6. force row level security reaches the table owner and not that role.

Anything running under that key sees every row, including the server action that grabbed it because a policy was in the way.