A field guide to Row Level Security in Postgres

RLS turns "trust the client" into "trust the database."

Row Level Security policies live next to your data, not your app code — which means they hold even if a request skips your API entirely. Forget to check user_id in one endpoint, add a new internal script that queries the database directly, or let an intern run a one-off migration — none of it matters if the database itself refuses to hand back rows the caller isn't allowed to see. That's the whole pitch: the guarantee moves from "every code path remembers to check" to "the database checks, always."

Start with a single rule

Most tables need just one honest question answered: who is allowed to see this row? Write that as a policy before you write anything cleverer.

alter table documents enable row level security;

create policy "owners can read their documents"
on documents
for select
using (auth.uid() = owner_id);

That's it — that's a working policy. Until you write at least one USING clause for a command, Postgres defaults to denying everything once RLS is turned on for a table, which is exactly the fail-safe behavior you want. A table with RLS enabled and zero policies isn't broken, it's locked, and that's the correct default while you figure out the rest.

USING vs. WITH CHECK — the distinction that trips people up

USING governs which existing rows a query is allowed to see or touch. WITH CHECK governs which new or modified rows are allowed to be written. For SELECT, only USING applies. For INSERT, only WITH CHECK applies, because there's no existing row to filter. For UPDATE and DELETE, both apply: USING decides which rows you can reach to update, WITH CHECK decides whether the result of the update is still something you're allowed to have written.

create policy "owners can update their documents"
on documents
for update
using (auth.uid() = owner_id)
with check (auth.uid() = owner_id);

Without the WITH CHECK clause here, a user could technically update a row they own and reassign owner_id to someone else — USING only checked the row on the way in, not the row on the way out. This is the single most common RLS bug: people write USING and assume it covers writes too.

One policy per operation, not one giant policy

It's tempting to write a single FOR ALL policy and move on, but splitting policies by command (SELECT, INSERT, UPDATE, DELETE) makes the rules easier to reason about and easier to loosen selectively later — for example, letting anyone insert a support ticket but only the assigned agent update it.

create policy "anyone can file a ticket"
on tickets for insert
with check (auth.uid() = submitted_by);

create policy "agents can update assigned tickets"
on tickets for update
using (auth.uid() = assigned_agent_id);

Multi-tenant tables: filter on the tenant, not just the user

If you're building anything multi-tenant, the policy usually needs to check organization membership, not just direct ownership:

create policy "members can read their org's rows"
on projects
for select
using (
  org_id in (
    select org_id from memberships where user_id = auth.uid()
  )
);

Watch performance here — a subquery like this runs per row unless Postgres can optimize it, so index memberships(user_id, org_id) and check the query plan with explain analyze once you have realistic data volumes. RLS policies are just SQL predicates injected into your queries, so they get exactly as slow as any other unindexed filter would.

Service roles bypass RLS — by design

Your backend's service-role or superuser connection typically bypasses RLS entirely, which is intentional: your server needs to run admin tasks, migrations, and background jobs without fighting its own policies. The tradeoff is that RLS only protects you against callers using the restricted connection — usually your public API or a client SDK talking directly to Postgres. If your application server itself has a bug and uses the service role for a user-facing query, RLS won't save you. Treat RLS as a second layer behind your application logic, not a replacement for checking permissions in your API.

Test policies as each role, not just as an admin

The easiest way to end up with a false sense of security is testing everything while connected as a superuser, where RLS is invisible. Test by actually assuming the roles you've written policies for:

set role authenticated;
set request.jwt.claims.sub = 'test-user-uuid';
select * from documents; -- should only return that user's rows
reset role;

Run this for each policy, for each role, including the negative case — log in as user A and confirm you genuinely cannot see user B's rows, not just that the "happy path" query returns the right thing.

The one-line takeaway

Enable RLS on every table that holds anything sensitive, write USING and WITH CHECK as separate, deliberate decisions rather than one merged assumption, index whatever your policies filter on, and test by impersonating the role — not by trusting that the policy reads correctly on paper.