Postgres Row-Level Security: A Multi-Tenant Playbook

by

What Postgres Row-Level Security Actually Does

Row-level security (RLS) lets Postgres filter and check rows per query, per role, based on a policy you define in SQL. Instead of adding WHERE tenant_id = :current_tenant to every query in every service, you write the rule once and the database enforces it — on your API, your background jobs, and the ad-hoc psql session someone opens at 2am.

📖 Read the full guide: Postgres Row-Level Security: Policies That Actually Work

▶ Watch the video walkthrough: Row-Level Security in 5 Real Scenarios
https://www.youtube.com/watch?v=Gz3X6bkzIHc

Postgres Row-Level Security: A Multi-Tenant Playbook

That last part is the whole point. RLS isn't a performance feature or a convenience layer. It's a last line of defense for multi-tenant data, and it only works if you understand the handful of primitives that control it — not just the policy text, but what turns the policy on in the first place.

Enabling RLS: The Step Everyone Forgets

Two separate statements matter here, and skipping either one silently defeats the whole setup.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

ENABLE ROW LEVEL SECURITY turns policies on for everyone except the table owner. If your application connects as the table owner (a common default), RLS does nothing — every policy you write is quietly bypassed. FORCE ROW LEVEL SECURITY closes that gap by applying policies to the owner too, unless the owner has the BYPASSRLS attribute.

This is the single most common reason RLS "doesn't work" in testing: someone writes a correct policy, tests it as the owner role, sees all the rows, and assumes the policy is broken. It isn't. Ownership was never in scope.

CREATE POLICY: A Working Example

Here's a minimal, real-world policy for a tenant_id column, which is the standard shape for postgres RLS multi-tenant setups:

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);

Two clauses, two different jobs:

  • USING filters which existing rows are visible for SELECT, UPDATE, and DELETE.
  • WITH CHECK validates rows being written — on INSERT and the new values of UPDATE. Without it, a session could read only its own tenant's rows but still insert or update rows belonging to someone else.

Leaving off WITH CHECK is the second most common RLS bug. The policy looks complete, reads are correctly scoped, and nobody notices the write-side hole until a cross-tenant row shows up in an audit.

Setting the Tenant Context

The policy above depends on a session-level GUC (app.tenant_id) being set correctly before any query runs:

SET app.tenant_id = '3fa85f64-5717-4562-b3fc-2c963f66afa6';

This is where connection poolers cause real damage. If you use PgBouncer in transaction mode (the common setup for high-concurrency apps), a SET at the session level can leak across transactions if it isn't reset, or silently fail to apply if the pooler doesn't preserve session state the way you expect. Use SET LOCAL inside an explicit transaction, and confirm — don't assume — that your pooler mode actually scopes it correctly per request. This is the third classic failure mode, and it's the hardest one to catch in testing because it only shows up under concurrent load.

Restrictive vs Permissive Policies

By default, CREATE POLICY creates a permissive policy. If a table has multiple permissive policies, Postgres combines them with OR — access is granted if any policy matches.

Restrictive policies work the opposite way: they're combined with AND, narrowing access further regardless of what permissive policies allow.

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

CREATE POLICY block_archived ON invoices
  AS RESTRICTIVE
  USING (archived = false);

Use restrictive policies sparingly, for hard constraints that should never be overridden by another policy — things like "never expose archived rows via this role," layered on top of your normal tenant isolation. Mixing too many permissive policies on one table makes the effective access rule hard to reason about; restrictive policies are the clearer tool when you need an unconditional gate.

BYPASSRLS: Handle With Care

Roles with the BYPASSRLS attribute skip RLS entirely, regardless of FORCE:

ALTER ROLE migration_runner BYPASSRLS;

This is appropriate for a small number of trusted roles — migration tooling, admin scripts, backup jobs — that legitimately need unrestricted access. It is not appropriate for your application's main connection role. If that role has BYPASSRLS, every policy you've written is decorative. Audit this periodically; it's easy for a role to pick up the attribute during a debugging session and never lose it.

Does RLS Hurt Performance?

Postgres RLS performance is generally fine if the policy expression is indexable. The planner treats the USING clause similarly to an added WHERE predicate, so:

CREATE INDEX idx_invoices_tenant_id ON invoices (tenant_id);

…keeps queries fast even with RLS enabled, because the policy condition can use the index just like a hand-written filter would. Where performance actually degrades is when the policy calls a non-inlinable function, does a subquery against another table on every row check, or references current_setting inside a complex expression that defeats index usage. Keep policy expressions simple and directly comparable to an indexed column, and check EXPLAIN ANALYZE on a representative query after adding a policy — don't assume it's free.

A Practical Rollout Checklist

  • ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY on every tenant-scoped table
  • Confirm your application role does not have BYPASSRLS
  • Every policy has both USING and WITH CHECK, unless you have a specific reason to omit one
  • app.tenant_id (or your equivalent GUC) is set with SET LOCAL inside a transaction, verified under your actual pooler configuration — not just in a direct psql session
  • Indexes exist on every column referenced in a policy's USING clause
  • A test that connects as the application role, not a superuser, and asserts that querying without a tenant context set returns zero rows, not an error and not everything

That last test matters more than it sounds like it should. A misconfigured GUC should fail closed — return nothing — not fail open and hand back every tenant's data. If your policy relies on current_setting('app.tenant_id', true) with the "missing is OK" flag, double-check what happens when the setting really is missing.

The Short Version

RLS moves tenant isolation from a thousand call sites into one place the database enforces on every path. The policy text — USING, WITH CHECK, restrictive vs. permissive — is genuinely the easy half. The half that decides whether any of it runs at all is ownership, BYPASSRLS, FORCE, and where exactly you set that GUC in your connection lifecycle. Get those three right and RLS does what it promises. Get any one of them wrong and you have policies that look correct in code review and do nothing in production.

If you're reviewing an existing schema for gaps like these, running the ownership and BYPASSRLS checks as part of your normal migration review — the way tools like MyDBA can flag as part of a routine schema audit — catches this class of bug before it ships rather than after an incident.

Leave a Comment