SQL Row-Level Security: A Practical Tutorial

What and Why

Row-level security (RLS) lets the database itself decide which rows a user can see or modify. Instead of relying only on WHERE tenant_id = ? in application code, you attach policies to a table and PostgreSQL applies them transparently to every query. This is invaluable for multi-tenant SaaS, regulated data, and any system where forgetting a filter could leak information across customers.

RLS turns a class of “I forgot the tenant filter” bugs into impossible operations. It also lets you safely expose the database directly to tools like PostgREST or BI users.

Mental Model

Think of RLS as an invisible WHERE clause that the database appends to every query against a protected table. The policy is a boolean expression evaluated per row. If the expression returns true, the row is visible; otherwise it is filtered out.

There are two clauses in a policy. USING controls which existing rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK controls which new or modified rows are allowed by INSERT and UPDATE. They are often the same expression, but separating them lets you express subtle rules such as “you can read all your rows but only insert active ones.”

Policies typically reference session-level context, set by the application through SET LOCAL or a custom GUC such as app.tenant_id.

Hands-on Example

CREATE TABLE invoices (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  amount      numeric,
  created_at  timestamptz DEFAULT now()
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- In application code, per request:
SET LOCAL app.tenant_id = '8d3c...';
SELECT * FROM invoices;   -- only this tenant's rows

Application query:
  SELECT * FROM invoices WHERE amount > 100;

Effective query after RLS:
  SELECT * FROM invoices
   WHERE amount > 100
     AND tenant_id = current_setting('app.tenant_id')::uuid;

 row | tenant_id | amount     visible?
 ----+-----------+--------    --------
  1  | T_A       |  120        yes (tenant=T_A)
  2  | T_B       |  500        NO
  3  | T_A       |   50        no (amount filter)

The application never has to remember the tenant filter. The database enforces it for every statement, including ad-hoc ones.

Common Pitfalls

The biggest pitfall is forgetting that table owners and superusers bypass RLS by default. If your app connects as the table owner, policies are silently ignored. Either connect as a non-owner role or use ALTER TABLE ... FORCE ROW LEVEL SECURITY.

Another mistake is using SET (session-wide) instead of SET LOCAL (transaction-scoped). With connection pooling, a leftover SET can leak tenant context into the next request, which is exactly the bug RLS was supposed to prevent.

Policies that call expensive functions can degrade performance, because the predicate runs per row. Keep policy expressions simple and indexable.

Finally, WITH CHECK is easy to forget. Without it, a user could update a row to belong to another tenant, then read it back. Always set both clauses unless you have a specific reason not to.

Practical Tips

Use a dedicated database role for application traffic, distinct from migration or admin roles. Grant only the privileges that role needs, and rely on RLS for row-level filtering.

Set tenant context at the start of every transaction in a middleware layer. Validate the value before injecting it, and treat it as untrusted user input even though it comes from your own session.

Add an index on the columns referenced by your policies (often tenant_id). Otherwise the planner may still scan the whole table before applying the predicate.

Test policies with a test role using SET ROLE in your migration suite. This catches “this works as superuser but fails in production” bugs early.

Wrap-up

RLS moves authorization logic into the database, where it can be enforced consistently and audited centrally. Enable it on sensitive tables, write clear USING and WITH CHECK policies, drive them from session context set transactionally, and verify behavior with a non-owner role. Done well, RLS removes an entire category of multi-tenant data leak bugs.