Postgres RLS without the foot-guns

Row-level security moves authorization out of application code and into the database itself, so a policy is enforced no matter which code path - API route, background job, ad-hoc admin query - touches the table. That's the appeal. It's also exactly why a misconfigured policy is more dangerous than a missing `if` statement: it fails silently across your entire surface area at once.

Mistake one: forgetting RLS is deny-by-default, but only once enabled

Enabling RLS on a table with zero policies doesn't lock it down - by default, with no policies, nobody (other than the table owner) can read or write a single row. That's correct and safe. The dangerous moment is the gap between `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` and writing your first policy, during a migration, where a table can briefly go fully inaccessible in production if you're not deploying both together.

alter table tickets enable row level security;

create policy "users read own tickets"
  on tickets for select
  using (auth.uid() = user_id);

Mistake two: policies that don't account for joins

A policy on `tickets` checking `user_id = auth.uid()` does nothing to protect a `ticket_comments` table joined to it. RLS is per-table, not per-query-graph. Every table that contains or references sensitive data needs its own policy - there's no inheritance through foreign keys, and assuming there is is the single most common RLS mistake we've seen.

Mistake three: using `auth.uid()` (or its equivalent) inside a function marked `SECURITY DEFINER` without realizing it changes context

Functions marked `SECURITY DEFINER` execute with the privileges of the function's owner, not the calling user - which is sometimes exactly what you want (a controlled escape hatch) and sometimes a way to accidentally bypass every RLS policy you just wrote. If you must use `SECURITY DEFINER`, audit every line inside it as if RLS doesn't exist, because for that function, it doesn't.

Mistake four: assuming RLS protects against bad indexes too

RLS filters rows after the planner has already decided how to scan the table. If your policy predicate isn't indexed, Postgres may need to evaluate it against every row before filtering, which turns a fast query into a slow one at scale - a performance bug that looks like a security feature is to blame, when really it's a missing index on the column your policy checks.

The four-point checklist we run before shipping any new policy

• Does every table touching this data have its own explicit policy, not just the "main" table?
• Have we tested the policy as an authenticated non-owner user, not just as the database superuser in a migration script?
• Is the column referenced in the policy predicate indexed?
• Does any `SECURITY DEFINER` function in the call path get audited separately, with RLS assumed absent?

Written by Appesto Engineering.