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
- It is off until you enable it. Policies have no effect until you run
ALTER TABLE ... ENABLE ROW LEVEL SECURITY, as theCREATE POLICYdocumentation points out. - Enabled with no policy means no rows. According to the row security documentation, if RLS is enabled and no policy applies, a default-deny policy is used and nothing is visible or modifiable.
USINGfilters existing rows,WITH CHECKvalidates new ones.USINGdecides which rows a query can see, update or delete.WITH CHECKdecides which rows anINSERTorUPDATEmay write. If you give onlyUSING, it is used for both.- Policies combine. Permissive policies, the default, are combined with
OR. Restrictive policies are combined withAND. One overly broad permissive policy therefore opens the table, whatever the others say.
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:
- Create the policy before you enable RLS, in one transaction. Enabling first switches the table to default-deny and the application sees empty results until the policy exists.
- The
nullif(..., '')is deliberate. In our experience a setting that was set earlier in the session can come back as an empty string rather than NULL after the transaction ends, and''::bigintraises an error. Withnullif, a missing tenant simply matches nothing. FORCE ROW LEVEL SECURITYcovers the owner. The documentation says table owners normally bypass RLS. If your application connects as the role that owns the tables, which is very common, the policy does nothing withoutFORCE.
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.
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
- Superusers and
BYPASSRLSroles always bypass it. Keep both away from the application role. - Table owners bypass it unless the table has
FORCE ROW LEVEL SECURITY. - Views run as their owner by default. The
CREATE VIEWdocumentation says the policies of the view owner are applied to the base tables. If a superuser or the table owner created the view, the view returns every tenant's rows. Since PostgreSQL 15 you can switch a view to the caller's permissions and policies:ALTER VIEW open_invoices SET (security_invoker = true); - Whole-table operations such as
TRUNCATEandREFERENCESare not subject to row security. - Constraint checks on unique keys and foreign keys always bypass RLS. The documentation warns that this can leak information: an insert that fails with a duplicate key tells the user that a row with that value exists in another tenant. Avoid globally unique business keys that reveal something, or include
tenant_idin them.
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
- The policy is part of every query. Index tables with
tenant_idas the leading column, and check withEXPLAINthat the tenant condition appears as an index condition, not as a filter after a scan of many tenants. - Keep policy expressions simple. A policy with a subquery, for example a membership lookup in another table, is evaluated as part of every query that touches the table. A plain equality against a setting is cheap; a join is not. If you need membership logic, resolve the tenant once in the application or a function and store it in the setting.
- Watch query plans for non-leakproof functions. The documentation says policy conditions are applied before user-supplied conditions, except for functions and operators marked
LEAKPROOF. In our experience this can change the order of filters and occasionally the plan, so compare plans with and without RLS for your heaviest queries. - Skewed tenants need separate testing. On a shared table where one tenant owns a large share of the rows, the best plan for the big tenant may differ from the best plan for small ones. Test both, with production-sized data.
- Backups must see all rows.
pg_dumpsetsrow_securitytooffby default and fails with an error if the role cannot bypass RLS, which is what you want: a silently partial backup would be worse. Run backups as a role withBYPASSRLSor as the owner. - Logical replication needs privileges too. The PostgreSQL 15 release notes state that only superusers, roles with
BYPASSRLSand table owners can replicate into tables with row-level security policies. Plan the subscription owner before a migration, not during it. - Rolling RLS out on a live system. Enable it table by table, in short transactions with
lock_timeout, and deploy the application change that sets the tenant before you enable the policy. Monitor for queries that suddenly return zero rows; that is the default-deny telling you some code path does not set the tenant.
Key takeaways
- RLS only applies after
ENABLE ROW LEVEL SECURITY, and with no matching policy nobody sees anything. - Use
FORCE ROW LEVEL SECURITYif the application connects as the table owner. - Set the tenant per transaction with
set_config(..., true), especially behind PgBouncer, and make a missing tenant match no rows. - Superusers,
BYPASSRLSroles, owner-run views,TRUNCATEand constraint checks all bypass policies. - Index on
tenant_idfirst, keep policies simple and compare plans with RLS on. - Run backups as a role that bypasses RLS;
pg_dumpfails rather than dumping partial data.