What is Postgres row-level security, and where does it fit among multi-tenant layouts?
Row-level security (RLS) lets Postgres attach a filter to a table, so every SELECT, INSERT, UPDATE and DELETE sees only the rows a policy allows. In a SaaS database, that policy usually compares a row's tenant_id with the tenant of the current session. Tenant isolation then stops being a convention that every query must remember (WHERE tenant_id = ?) and becomes a rule the database enforces. A forgotten predicate in a new endpoint returns no rows instead of another customer's data.
RLS applies to only one of the three common layouts:
| Layout | Isolation boundary | Fits | Main cost |
|---|---|---|---|
Shared schema + tenant_id |
RLS policies | Many small-to-medium tenants in one monolith | Weakest blast-radius separation |
| Schema-per-tenant | search_path and grants |
Tens to hundreds of tenants who need some custom tables | Migrations run once per schema, and catalog bloat grows with tenant count |
| Database-per-tenant | Separate database or cluster | Few large or regulated tenants | Operational overhead and harder cross-tenant reporting |
RLS controls which rows a session can read and write, and nothing else. It does not address:
- Noisy neighbours. One tenant's heavy query still competes with everyone else for CPU and I/O.
- Per-tenant backup and point-in-time restore. You cannot roll back one tenant's rows on their own.
- Data residency. Every tenant's data lives in the same region.
- Side channels. Unique-constraint errors, shared sequence values and query timing can reveal that another tenant's rows exist.
Choose a separate schema or database when a tenant needs its own restore point, region, resource limits or contractual physical separation.
How does row-level security work in Postgres?
When a query touches a table that has RLS enabled, Postgres looks up the policies that apply to the current role and command, then adds their expressions to the query as extra conditions before it plans and runs the query. The application sends SELECT * FROM invoices, and Postgres executes it as if it read SELECT * FROM invoices WHERE <policy expression>. If several permissive policies apply, Postgres combines them with OR. Restrictive policies (CREATE POLICY ... AS RESTRICTIVE) are combined with AND on top of that. The policy expression usually reads a per-session or per-transaction setting that the application sets for each request. The rest of this section shows each of those pieces.
Take a sample invoices table with a tenant_id uuid column. Running ALTER TABLE invoices ENABLE ROW LEVEL SECURITY flips it to default-deny. With no policies defined, ordinary roles see zero rows and every write fails. Only the table owner, superusers and roles with the BYPASSRLS attribute are exempt. The owner exemption matters because many apps connect as the role that owns the schema. ALTER TABLE ... FORCE ROW LEVEL SECURITY removes that exemption for the owner. Section 3 covers role design in detail.
Policies then grant access back. CREATE POLICY takes two kinds of expression:
USINGfilters existing rows. Postgres appends it toSELECT,UPDATEandDELETE, so rows that fail it are invisible and cannot be modified. Postgres does not raise an error for them.WITH CHECKvalidates new row versions fromINSERTandUPDATE. A failing row raises an error. Without it, a user could insert rows for another tenant or move a row into another tenant by updatingtenant_id. If you omitWITH CHECKon a policy covering updates, Postgres reuses theUSINGexpression as the check.
The tenant predicate for both is tenant_id = current_setting('app.tenant_id', true)::uuid. The second argument, missing_ok, makes current_setting return NULL when the setting is undefined. Without it, the call raises an error. Returning NULL fails closed. tenant_id = NULL evaluates to NULL rather than true, and a policy treats anything other than true as false. A connection that never set a tenant therefore sees nothing and writes nothing. One caveat: after a custom setting has been defined in a session, resetting it can leave an empty string instead of NULL, and the ::uuid cast then raises an error. That still blocks access, but it surfaces as a failed query rather than an empty result.
-- Sample multi-tenant table
CREATE TABLE invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id uuid NOT NULL,
amount numeric NOT NULL
);
-- Default-deny: with no policies, non-owner roles without BYPASSRLS
-- see zero rows and every write fails
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- Optional: also apply policies to the table owner (see Section 3)
-- ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
-- Grant access back, scoped to the current tenant.
-- USING filters rows for SELECT, UPDATE and DELETE.
-- WITH CHECK validates new row versions from INSERT and UPDATE.
-- missing_ok = true returns NULL if app.tenant_id is unset, so
-- tenant_id = NULL is never true and the policy fails closed.
CREATE POLICY tenant_isolation ON invoices
FOR ALL
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- Per-request usage from the application (as a non-owner role):
BEGIN;
-- SET LOCAL scopes the value to this transaction only
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT * FROM invoices; -- only this tenant's rows
-- Rejected by WITH CHECK: new row belongs to another tenant
INSERT INTO invoices (tenant_id, amount)
VALUES ('22222222-2222-2222-2222-222222222222', 100);
ROLLBACK;
-- In a session where app.tenant_id was never defined, this returns
-- zero rows instead of raising an error or leaking data
SELECT count(*) FROM invoices;
Enabling row-level security on an invoices table, adding a tenant policy with USING and WITH CHECK built on current_setting('app.tenant_id', true), and showing that a missing tenant setting returns no rows rather than leaking data.
Why do most RLS rollouts fail at roles, session context and connection pooling?
Most RLS failures come from who runs the query, not from the policies. Superusers and roles with BYPASSRLS skip policies entirely. Table owners skip them too unless the table has FORCE ROW LEVEL SECURITY. Keep two roles: a migration role that owns the schema, and an application role that owns nothing and has only the grants it needs. Then check the indirect paths that run with owner privileges. SECURITY DEFINER functions run as their owner. Views check RLS against the view owner by default, not the caller. Trigger functions declared SECURITY DEFINER do the same. Any of these owned by the migration role can expose every tenant's rows.
Session context causes the next problem. A plain SET app.tenant_id stays on the server connection after the request ends. The pool hands that connection to the next request, which runs with the previous tenant's ID. Transaction-mode poolers make this worse because they share server connections between clients at every transaction boundary. Scope the value to a transaction instead. Middleware opens a transaction, calls SET LOCAL or set_config('app.tenant_id', $1, true), runs every query for the request on that connection and transaction, and then commits. The value is cleared at commit or rollback.
request ─▶ middleware ─▶ pool checkout ─▶ BEGIN
set_config(..., true)
queries ─▶ RLS policy reads setting
COMMIT (setting discarded)
pool return ◀───┘
This pattern breaks when code steps outside that transaction. Watch for ORMs that check out a different pooled connection for some queries, helpers that run in autocommit mode, and code that commits partway through a request. Queries that run after such a commit have no tenant context.
import express from "express";
import { Pool, PoolClient } from "pg";
// Connect as the low-privilege app role, never the migration/owner role.
// Policy assumed: USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
const pool = new Pool({ connectionString: process.env.APP_DATABASE_URL });
export async function withTenant<T>(
tenantId: string,
fn: (db: PoolClient) => Promise<T>,
): Promise<T> {
const client = await pool.connect();
try {
await client.query("BEGIN");
// is_local = true: the setting is discarded at COMMIT/ROLLBACK,
// so the next request that checks out this connection never sees it.
await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
// Every query for the request must go through this client, not the pool.
const result = await fn(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK");
throw err;
} finally {
client.release();
}
}
const app = express();
app.get("/invoices", async (req, res, next) => {
try {
// res.locals.tenantId is set earlier by auth middleware from a verified token.
const rows = await withTenant(res.locals.tenantId, async (db) =>
(await db.query("SELECT id, total FROM invoices ORDER BY id")).rows,
);
res.json(rows);
} catch (err) { next(err); }
});
A node-postgres helper that runs a request's queries on one pooled connection inside a transaction, with tenant context set by set_config(..., true) so it is cleared at commit or rollback and cannot leak to the next request.
How do you write RLS policies that perform well and cover every table?
Treat every policy expression as a WHERE clause that runs on every query. The simplest effective form compares tenant_id to a session value, such as tenant_id = (SELECT current_setting('app.tenant_id')::uuid). Wrapping the call in a scalar subquery makes the planner evaluate it once per statement and use the result as a constant. A STABLE function that returns the setting has a similar effect. In both cases the planner can match the predicate to an index instead of re-evaluating the expression per row.
Make tenant_id the leading column of most composite indexes, for example (tenant_id, created_at). Then every tenant-scoped query can use a single index range. Run EXPLAIN as the application role, not as a superuser or table owner, because those roles bypass RLS. Confirm that the tenant filter appears as an index condition and not as a filter applied after a scan.
Two patterns often prevent the planner from pushing filters down to the index:
- Non-leakproof functions in user predicates. Postgres evaluates RLS quals before these functions, so the function cannot drive index selection.
- Policies that join to a membership table. An
EXISTSsubquery against memberships is harder to optimize than a direct column comparison.
Coverage problems are mostly a schema-design issue:
- Join and child tables need their own
tenant_idand their own policy. Do not rely on the parent row to restrict access. - Global reference tables, such as currencies or plans, should either have no RLS and read-only grants, or a policy that allows reads by everyone and denies writes.
- Foreign keys should be composite, like
(tenant_id, parent_id). This makes it impossible for a row to reference another tenant's record. - Unique constraints should include
tenant_id. Otherwise, a duplicate-key error can reveal that a value exists in another tenant's data.
-- Parent: (tenant_id, id) is unique so children can use composite FKs
CREATE TABLE projects (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL,
name text NOT NULL,
UNIQUE (tenant_id, id),
UNIQUE (tenant_id, name) -- scoped per tenant; errors can't reveal other tenants' names
);
-- Child table carries its own tenant_id; the FK cannot point across tenants
CREATE TABLE tasks (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL,
project_id uuid NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);
-- tenant_id leads, so tenant-scoped queries use one index range
CREATE INDEX tasks_tenant_created_idx ON tasks (tenant_id, created_at);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
-- Scalar subquery: evaluated once per statement, then used as a constant
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = (SELECT current_setting('app.tenant_id')::uuid))
WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id')::uuid));
CREATE POLICY tenant_isolation ON tasks
USING (tenant_id = (SELECT current_setting('app.tenant_id')::uuid))
WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id')::uuid));
-- Verify as the application role (assumed to exist with grants);
-- owners and superusers bypass RLS and would show a misleading plan
SET ROLE app_user;
SET app.tenant_id = '6f1c2a9e-3b7d-4e2a-9c51-0d8b4f7a2e10';
EXPLAIN SELECT * FROM tasks WHERE created_at > now() - interval '7 days';
-- Look for an Index Cond on tenant_id (via the InitPlan value),
-- not a Seq Scan with tenant_id in a Filter line
RESET ROLE;
A two-table schema with tenant-leading indexes, composite foreign keys, tenant-scoped unique constraints, policies that wrap current_setting in a scalar subquery, and an EXPLAIN run as the application role to confirm the tenant filter becomes an index condition.
How should admin tools and background jobs handle cross-tenant access?
Cross-tenant access has two legitimate designs. In both, it should be a deliberate path that you can trace, never a side effect of using a powerful connection.
| Approach | How it works | Strengths | Risks |
|---|---|---|---|
Dedicated BYPASSRLS role |
A separate login role with the BYPASSRLS attribute, used only by migrations, analytics exports and break-glass support |
Simple, with no policy changes and no per-row overhead | Every query sees every tenant, and one wrong connection string leaks everything |
| Explicit admin policies | Extra policies granted TO support_role, scoped by conditions such as a read-only USING clause |
Access is visible in policy definitions and can be limited to specific tables or operations | More policies to maintain and test |
Use BYPASSRLS for schema migrations and bulk jobs that must touch all rows. Use admin policies for support tools, where staff usually need read access to one tenant at a time.
Background workers are not a special case. Each job carries its tenant_id in the payload. The worker opens a transaction, runs SET LOCAL app.tenant_id the same way the web middleware does, and then runs the job. A job that has no tenant fails instead of falling back to a privileged role.
To audit bypass paths, list roles with rolbypassrls or rolsuper set in pg_roles. Also check which roles own tables, because owners skip RLS unless you set FORCE ROW LEVEL SECURITY. Enable statement logging for those roles with ALTER ROLE ... SET log_statement.
Expect some confusion during debugging. An engineer connected as an owner or superuser sees all rows, so a bug report that says "the row is missing" may look wrong when it isn't. To reproduce what the app sees, run SET ROLE to the application role and set the tenant context first.
-- 1. Bulk path: a separate login role that skips RLS, used only by migrations
CREATE ROLE migrator LOGIN BYPASSRLS;
ALTER ROLE migrator SET log_statement = 'all'; -- log every statement it runs
-- 2. Support path: an explicit read-only policy, limited to one tenant at a time
CREATE ROLE support_role NOLOGIN;
GRANT SELECT ON invoices TO support_role;
CREATE POLICY support_read_one_tenant ON invoices
FOR SELECT TO support_role
USING (tenant_id = current_setting('app.support_tenant_id', true)::uuid);
-- 3. Audit: which roles bypass RLS outright?
SELECT rolname, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolbypassrls;
-- ...and which RLS tables do their owners still read unfiltered?
SELECT c.relname, pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
WHERE c.relkind = 'r'
AND c.relrowsecurity
AND NOT c.relforcerowsecurity;
-- 4. Debugging: reproduce what the app sees instead of what a superuser sees
BEGIN;
SET LOCAL ROLE app_user;
SET LOCAL app.tenant_id = '6f1c2b9e-0000-4000-8000-000000000001';
SELECT id, status FROM invoices WHERE id = 1042; -- empty here means RLS filtered it
ROLLBACK;
Shows a logged BYPASSRLS role for migrations and a single-tenant read-only policy for support staff. It also includes queries that audit bypass paths and a transaction that switches to the app role and tenant to reproduce what the app actually sees.
How do you test tenant isolation and enforce RLS in CI?
When debugging by hand, you switch to the application role and set the tenant setting before you query. Your automated tests should take the same approach.
Isolation tests
Run the test suite as the application role. Do not use the table owner, a superuser, or any role with BYPASSRLS, because those roles skip the policies and every check would pass. Seed two tenants, set the session to tenant A, and assert four things:
- Selecting tenant B's rows by primary key returns nothing.
- Updating or deleting them affects zero rows. Postgres filters these rows out silently, so check the affected-row count. Do not wait for an error.
- Inserting a row with tenant B's
tenant_idfails with a row-level security violation. - Updating one of tenant A's rows to change its
tenant_idto B also fails.
Add one more test with no tenant set. Reads should return no rows or raise an error, depending on how your policy handles a missing or empty setting. Writes should be rejected. A returned row means the policy has a gap.
Catalog check in CI
Behavioral tests only cover the tables you remember to test. A catalog query covers the rest. Join pg_class to pg_namespace and pg_attribute to find every ordinary or partitioned table that has a tenant_id column. Then fail the build if any of them has relrowsecurity false, relforcerowsecurity false, or no matching row in pg_policies. Run it after migrations so it sees the schema that will ship.
Why this pays off
None of these failures produce an error. A table without RLS returns every tenant's data. A missing FORCE lets owner-role connections bypass the policies. A reviewer reading a migration can miss either one. The catalog check catches both on every build, which is much cheaper than finding the leak during an incident.
What are the alternatives to RLS for tenant isolation?
RLS is one way to enforce tenant boundaries in a shared schema. The main alternatives trade enforcement location, operational cost and isolation strength differently:
- Application-level filtering only. Every query carries
WHERE tenant_id = ?, often through an ORM default scope, a repository base class or a query-builder wrapper. It needs no database changes and is easy to debug, because what you read in code is what runs. The weakness is that enforcement depends on every code path using the wrapper. Raw SQL, reporting queries, ad-hoc scripts and new services can bypass it without any error. - Schema-per-tenant. Each tenant gets its own schema, and the application sets
search_pathper request. Isolation comes from grants and naming rather than per-row predicates, and per-tenant customisation is possible. The cost is running migrations once per schema and a catalog that grows with tenant count. A wrongsearch_pathstill points a request at the wrong tenant, so the session-context problems described above do not disappear. - Database-per-tenant. Each tenant gets its own database or cluster. This gives the strongest separation, per-tenant backup and restore, independent resource limits and region placement. It costs the most to operate, needs connection routing per tenant, and makes cross-tenant reporting a separate data pipeline.
- Tenant-scoped views or functions. The application role is granted access only to views or
SECURITY DEFINERfunctions that apply the tenant filter, with no direct table grants. This works, but you end up maintaining a parallel API layer. As noted earlier, views and definer functions run with their owner's privileges, so they need the same care as RLS bypass paths.
These are not mutually exclusive. A common combination keeps application-level tenant_id filters for clarity and query planning, adds RLS as the enforcement backstop, and moves a few large or regulated tenants to their own databases.
How can RLS save engineering time?
RLS saves time mainly by shrinking what humans have to check, not by making queries faster to write.
- Code review. Without RLS, every query that touches a tenant table is a potential leak, so reviewers must verify the tenant predicate on each one. With RLS and the CI catalog check, review focuses on a small set of places: policy definitions, the request middleware that sets context, roles with
BYPASSRLS, andSECURITY DEFINERfunctions and views. - New features and services. A new endpoint, report or internal tool that connects as the application role inherits isolation automatically. Engineers do not need to rediscover the filtering convention for each new code path.
- Testing. The isolation tests and catalog check described above are written once and cover every table with a
tenant_idcolumn, including tables added later. Without RLS, isolation tests have to exercise each query path separately. - Incident response. When a suspected cross-tenant leak is reported, the question "could any query have returned another tenant's rows?" narrows to the bypass paths and the context-setting code rather than the whole codebase.
The time savings come with upfront costs: the transaction-per-request refactor, ORM adjustments, and slower debugging when engineers forget that their own connection bypasses policies. Those costs are covered in the next section.
As an illustrative estimate, with assumed inputs rather than measured data: assume a team merges 40 pull requests a month that add or change tenant-scoped queries, and a reviewer spends an assumed 5 extra minutes per PR tracing tenant predicates. That is about 200 minutes, or a little over 3 hours, of review time a month. If RLS lets reviewers skip that check for most of those PRs, most of those hours return to the team. Separately, assume the RLS rollout itself takes an assumed 2 to 4 engineer-weeks. On these assumed numbers, review savings alone take a long time to repay the rollout. The stronger case is that RLS reduces the chance of a cross-tenant leak, which is far more expensive than review time. Substitute your own team's numbers before relying on this estimate.
When should you adopt or avoid RLS, and how do you roll it out incrementally?
Rolling RLS into an existing app
Retrofit in stages so each step can be reverted on its own:
- Add
tenant_idand backfill it. Make it nullable, backfill in batches from the owning parent row, then addNOT NULLand a foreign key. Policies on a half-populated column hide rows silently. - Enable policies one table at a time, permissively at first. The policy should match the tenant when the session setting is present and allow everything when it is absent. Have the app log every request that reaches the database without tenant context. Tighten each table once that log goes quiet. The existing
WHERE tenant_id = …filters stay in place during this phase. - Switch the app to the non-owner role and force RLS. Do this only after strict policies cover every table the catalog check lists.
Cost against benefit
| Cost | Benefit | |
|---|---|---|
| Request handling | Transaction-per-request refactor so context is scoped with SET LOCAL |
A missing filter no longer leaks data |
| ORM | Fights hooks, lazy loads, and raw-query escape hatches | Fewer hand-written tenant predicates |
| Operations | Harder debugging; plans must be reviewed with policies applied | Smaller review surface per query |
RLS is a second layer of defence. It does not replace application authorization. If a tenant_id comes from an unvalidated header, or a confused-deputy bug sets the wrong tenant, Postgres will enforce that wrong value correctly.
When to avoid it
Skip RLS in these cases:
- Residency rules or contracts require per-tenant restore. Separate databases fit that better.
- A few very large tenants dominate the data and need their own capacity.
- The team cannot commit to transaction-scoped context across every code path.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgres row level security multi tenant, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.