an application serving more than one customer out of one database ends up with a tenant_id column on most of its tables and a matching where tenant_id = ... in most of its queries. the rule is easy to remember, which is exactly the problem: remembering is the only thing enforcing it. one query written by hand, one join added to a report, one background job that runs without a request behind it, and the filter is gone. nothing errors. the query returns more rows than it should and looks like it worked.
postgres can hold that predicate instead. row level security attaches a boolean expression to a table, and the planner adds it to every statement that touches the table, no matter who wrote the sql. the forgotten filter then returns nothing rather than everything.
the short version
two statements. row security is switched on for the table, which makes it deny-all, and a policy grants back a subset of rows as a predicate.
alter table documents enable row level security;
create policy tenant_isolation on documents
using (tenant_id = current_setting('app.current_tenant', true)::uuid);after that, select * from documents returns only the rows matching the session’s tenant, and so does a join that passes through documents. the application sets one value per request instead of a predicate per query.
enabling row security
with row security on and no policies present, the table is empty for everyone except the owner. that default means a half-finished rollout fails closed.
the owner is the catch. table owners, superusers, and roles with the bypassrls attribute skip policies entirely, so an application connecting as the role that owns its tables sees no effect at all. this is the usual reason a first attempt appears to do nothing. the fix is a separate non-owning role for the application, plus a backstop on the table:
alter table documents force row level security;force closes the owner hole. superuser and bypassrls stay open by design, for backup and maintenance roles.
using and with check
a policy names a table, a command, the roles it applies to, and up to two expressions.
create policy tenant_isolation on documents
for all
to application
using (tenant_id = current_setting('app.current_tenant', true)::uuid)
with check (tenant_id = current_setting('app.current_tenant', true)::uuid);using filters rows that already exist. it decides what select returns and which rows an update or delete is allowed to touch. rows that fail it are not errors, they are invisible, so an update that would have matched them reports zero rows affected.
with check validates rows being written: the new row on insert, the resulting row on update. a row that fails it raises new row violates row-level security policy. the asymmetry is deliberate - a write landing outside the tenant is a bug worth surfacing, while a read outside the tenant is just an empty result.
policies are per-command, and a command with no matching policy is denied. a table carrying only a for select policy is readable and not writable, which is a compact way to define a reporting role.
combining policies
policies on the same command combine. the default type, permissive, combines with or, so each one only widens access. a restrictive policy combines with and and is applied on top of that result, so it can only narrow - and cannot grant anything alone, since there has to be a permissive policy to narrow.
that split is how “tenant isolation always, plus whatever the feature needs” is expressed.
create policy tenant_isolation on documents
as restrictive
for all
to application
using (tenant_id = current_setting('app.current_tenant', true)::uuid)
with check (tenant_id = current_setting('app.current_tenant', true)::uuid);
create policy own_documents on documents
for all
to application
using (owner_id = current_setting('app.current_user', true)::uuid);
create policy shared_documents on documents
for select
to application
using (id in (select document_id from document_shares
where user_id = current_setting('app.current_user', true)::uuid));the two permissive policies are or’d, so a user sees what they own and what was shared with them. the restrictive policy is and’d over both, so neither path can cross a tenant boundary. a third access path later means one more permissive policy, and the isolation guarantee holds without being restated.
passing the tenant through
the predicate needs a value from the session. any setting with a dot in its name can be set at runtime, and current_setting reads it back; its second argument, missing_ok, returns null instead of raising when the setting was never set, which turns “no tenant” into “no rows”.
a plain set lasts for the whole session, which is wrong on a pooled connection - the value outlives the request and the next request inherits it. set_config with its third argument true scopes the value to the current transaction and resets it on commit or rollback.
in go with pgx, that is a helper owning the transaction the value is pinned to.
func withTenant(ctx context.Context, pool *pgxpool.Pool, tenant string, fn func(pgx.Tx) error) error {
tx, err := pool.Begin(ctx)
if err != nil {
return err
}
defer tx.Rollback(ctx)
if _, err := tx.Exec(ctx, "select set_config('app.current_tenant', $1, true)", tenant); err != nil {
return fmt.Errorf("set tenant: %w", err)
}
if err := fn(tx); err != nil {
return err
}
return tx.Commit(ctx)
}the tenant is bound as a parameter, not concatenated. building that statement out of strings reintroduces sql injection at the exact point that was supposed to become trustworthy.
// wrong - the tenant string lands in the sql text
tx.Exec(ctx, "set local app.current_tenant = '"+tenant+"'")
// better - the tenant is a bound parameter
tx.Exec(ctx, "select set_config('app.current_tenant', $1, true)", tenant)set and set local take no parameters, which is why set_config is the form used here.
the alternative is a database role per tenant, with policies written against current_user. it makes the isolation visible in \du and costs a role plus grants per tenant, so it fits tens of tenants and becomes a migration problem at thousands.
policies and the planner
policy predicates are planned as ordinary quals, so an index on tenant_id is used exactly as it would be for a hand-written filter. current_setting is stable, meaning it is evaluated once per statement and its result can serve as an index scan key. the direction of the cast decides whether that happens: tenant_id = current_setting(...)::uuid keeps the index usable, tenant_id::text = current_setting(...) does not.
the one real difference from a hand-written predicate is ordering. postgres evaluates policy quals before user-supplied quals whose functions are not marked leakproof, so a user function can never observe a row the policy hides. the visible effect is a plan where a selective user predicate cannot be pushed below the policy check; explain (analyze, verbose) shows the policy qual in the filter list.
testing policies
policies are code. the test that catches regressions is the negative one - assume the application role, set one tenant, write a row belonging to another, and assert the error.
begin;
set role application;
select set_config('app.current_tenant', '00000000-0000-0000-0000-000000000001', true);
insert into documents (tenant_id, title) values
('00000000-0000-0000-0000-000000000002', 'leaked'); -- expects an error
rollback;running the suite as a superuser is the mistake to avoid. every assertion passes, because every policy was bypassed.
what to watch out for
the owner bypasses everything until forced. an application connecting as the role that owns its tables has policies that exist, look correct, and are never evaluated. connect as a non-owning role and add force row level security.
foreign keys and unique constraints are checked outside the policy. integrity checks and unique index probes run with system privileges and see invisible rows, so a key collision with another tenant’s row confirms that row exists. where that inference matters, the constraint needs the tenant column: unique (tenant_id, slug).
views run as their owner by default. a view over a protected table applies its owner’s policies, not the caller’s, which reopens everything it touches. postgres 15 added security_invoker = true on views; without it a view is a bypass.
partitions carry their own policies. a policy on the partitioned table covers rows reached through the parent, and a policy on a partition applies when that partition is queried directly. attaching a new partition without checking both is a gap no query on the parent will show.
pg_dump needs the right role. dumping a protected table as a non-owning role either fails or, with --enable-row-security, succeeds and produces a partial dump that looks complete. backups belong to a role that bypasses rls.
policies do not replace grants. row security narrows what a role can reach, it does not grant access. the role still needs its table privileges, and a policy on a table it cannot read changes nothing.
references
[1] postgresql documentation. “row security policies.”
postgresql.org/docs/current/ddl-rowsecurity
[2] postgresql documentation. “create policy.”
postgresql.org/docs/current/sql-createpolicy
[3] postgresql documentation. “system administration functions.”
postgresql.org/docs/current/functions-admin
[4] postgresql documentation. “create view.”
postgresql.org/docs/current/sql-createview
[5] postgresql documentation. “function volatility categories.”
postgresql.org/docs/current/xfunc-volatility
[6] jackc. “pgx - postgresql driver and toolkit for go.”
github.com/jackc/pgx