Architecture

PostgreSQL Row Level Security: A Practical Guide for Multi-Tenant Apps

Row level security lets the database, not the application, decide which rows a tenant can see. It works well, but owners, superusers, views and connection poolers can quietly switch it off. Here is how to set it up, how to test it and what it costs on large tables.

· 13 min read · AG Data Team

PostgreSQL row level security (RLS) lets you attach a rule to a table, such as "only rows where tenant_id matches the current tenant", and PostgreSQL applies it to every query. For multi-tenant SaaS this moves isolation from "every developer remembers the WHERE tenant_id = ?" to the database itself. One forgotten filter in a report no longer leaks another customer's data.

RLS is solid, but it has exceptions that catch teams out: table owners, superusers, views and session settings in a connection pool. Below: a working tenant policy, the bypass rules, how to audit an existing database and what changes on large tables.

How RLS works in two minutes

A tenant isolation policy

The common pattern for SaaS is one database role for the application and a session setting that carries the current tenant. PostgreSQL accepts any two-part setting name such as app.tenant_id as a custom placeholder variable, and current_setting(name, true) returns NULL instead of an error if it has not been set.

BEGIN;
SET LOCAL lock_timeout = '5s';

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;   -- apply to the table owner too
COMMIT;

Some details matter here:

The application then sets the tenant at the start of every transaction:

BEGIN;
SELECT set_config('app.tenant_id', '42', true);   -- true = this transaction only
SELECT id, total FROM invoices WHERE status = 'open';
COMMIT;

Connection poolers and the tenant setting

This is where RLS setups most often go wrong. With PgBouncer in transaction mode, consecutive transactions from one client can run on different server connections, and one server connection serves many clients. A session-level SET app.tenant_id can therefore stay behind on a connection and apply to the next client's transaction.

Use set_config(..., true) or SET LOCAL, which the documentation describes as lasting only for the current transaction, and set it in every transaction. Make "no tenant set" match no rows, as above, so a missing call fails closed. Our PgBouncer guide covers pool modes in more detail.

The setting is not a password. Any session can set a custom variable such as app.tenant_id. RLS with a session setting protects you against application bugs, like a missing filter, not against an attacker who can run arbitrary SQL as the application role. If that is part of your threat model, use separate database roles per tenant or verify the tenant in a SECURITY DEFINER function.

Who and what bypasses RLS

Also note that INSERT ... ON CONFLICT DO UPDATE checks rows against the table's SELECT policies and raises an error instead of skipping silently. Upserts therefore need a SELECT policy as well as INSERT and UPDATE.

Audit an existing database

-- Which tables have RLS, and is it forced?
SELECT c.oid::regclass AS table_name, c.relrowsecurity, c.relforcerowsecurity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY c.relrowsecurity, 1;

-- Which roles skip RLS entirely?
SELECT rolname FROM pg_roles WHERE rolsuper OR rolbypassrls;

-- What the policies actually say
SELECT tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies ORDER BY tablename;

Look for tenant tables with relrowsecurity = false, tables used by an owner role without relforcerowsecurity, unexpected members of the bypass list and views created by privileged roles. Then test as the real application role: set tenant A, try to read, update and insert rows of tenant B, and make the test part of CI. Our PostgreSQL security hardening checklist covers the role setup around it.

What changes on large databases

Key takeaways

PostgreSQL Security Multi-Tenant Row Level Security
Work with us

Planning tenant isolation in PostgreSQL?

We design and review row level security for multi-tenant PostgreSQL, including roles, poolers, backups and query plans on large tables.

Get in touch More from the blog