PostgreSQL row-level security: a practical guide with policies and pitfalls

Short answer: row-level security (RLS) lets PostgreSQL filter rows per user inside the database. You turn it on per table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY and then add policies with CREATE POLICY. Without a matching policy, a restricted role sees no rows. It is not enforced for superusers or, by default, for the table owner, which is the most common way it silently fails.

How do you enable it?

CREATE TABLE invoices (
  id         bigserial PRIMARY KEY,
  tenant_id  uuid    NOT NULL,
  number     text    NOT NULL,
  total      numeric NOT NULL
);

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

ENABLE turns the feature on. FORCE makes it apply to the table owner as well. Without FORCE, the role that created the table, often the same one your application uses, skips every policy. This single line is the difference between a working boundary and a false sense of safety.

How do you write a policy?

A policy is a condition. USING filters rows that can be read, updated or deleted. WITH CHECK validates rows being inserted or updated.

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

The application sets the tenant for the current transaction, then runs its queries.

BEGIN;
SELECT set_config('app.tenant_id', '7c0a...-uuid', true);  -- true = local to this transaction
SELECT * FROM invoices;   -- only this tenant's rows
COMMIT;

Use the transaction-local form. A session-level setting leaks to the next request when connections are pooled, and a leaked tenant ID is a data breach.

What roles bypass it?

• Superusers always bypass RLS.
• Roles with the `BYPASSRLS` attribute bypass it.
• The table owner bypasses it unless you use FORCE.

So run the application as a dedicated role that owns nothing, has no BYPASSRLS, and has only the SELECT, INSERT, UPDATE, DELETE grants it needs. Migrations run as a different role. This role design is the heart of the setup, and it is what I describe in row-level security is not your only tenant boundary.

How do you keep it fast?

The policy condition is added to every query on the table, so it must be indexable. Put tenant_id first in your indexes.

CREATE INDEX ON invoices (tenant_id, number);

Two performance traps are common. First, calling a function in the policy that the planner cannot treat as stable. Wrap current_setting in a sub-select, (SELECT current_setting('app.tenant_id')::uuid), so it is evaluated once per query and not once per row. Second, policies that join other tables. Check them with EXPLAIN (ANALYZE, BUFFERS) under the application role, not as a superuser, because a superuser plan does not include the policy.

What about views and functions?

Views run with the privileges of the view owner by default, which can bypass RLS on the underlying tables. In PostgreSQL 15 and later, create views with WITH (security_invoker = true) so they use the caller's rights. SECURITY DEFINER functions also run as their owner, so treat them as a way around your policies and use them sparingly.

How do you test it?

-- as the application role, with the tenant set to A
SET ROLE app_runtime;
SELECT set_config('app.tenant_id', 'A-uuid', false);
SELECT count(*) FROM invoices;                    -- expect only A's rows
INSERT INTO invoices (tenant_id, number, total)
VALUES ('B-uuid', 'X-1', 10);                     -- expect: new row violates row-level security policy

Write tests for four cases: tenant A reads A, tenant A cannot read B, tenant A cannot insert as B, and a request with no tenant set returns nothing or errors. Run them in CI against a real PostgreSQL instance.

Is RLS enough on its own?

No. It protects against missing WHERE clauses in application code, which is valuable. It does not replace authorisation inside a tenant, validation, or checks on who may do what to which record. Treat it as a net underneath your application checks, not a replacement.