How to Implement Multi-Tenancy with Postgres Row-Level Security (2026)

This guide is for engineers building a pooled multi-tenant SaaS on Postgres who want the database, not every hand-written WHERE clause, to stop one tenant reading another tenant's rows. By the end you will have a shared-schema design keyed on tenant_id, an application role that cannot bypass row-level security (RLS), policies driven by a per-transaction setting, a connection pattern that is safe behind PgBouncer in transaction mode, indexes that keep RLS fast, and a CI test that fails if isolation breaks. Every SQL output below is real: I ran it against PostgreSQL 16.8 locally, and the behaviour matches the current PostgreSQL 18 row security documentation (18.6 is the current minor per the versioning page; 19 is at RC 1). Prerequisites: a Postgres you control with superuser access for setup, and psql. If you are still choosing between database-per-tenant, schema-per-tenant and shared tables, read the companion explainer on multi-tenant SaaS architecture first.

Step 1: Design tables around tenant_id

Every tenant-owned table gets a non-null tenant_id, and it leads the primary key. That gives you a tenant-prefixed index for free, and it lets foreign keys carry the tenant too.

-- Run as a superuser. Two roles: one owns objects, one is used by the app.
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOSUPERUSER NOBYPASSRLS;
CREATE DATABASE saas OWNER app_owner;
\c saas
SET ROLE app_owner;

CREATE SCHEMA app AUTHORIZATION app_owner;

CREATE TABLE app.tenants (
  id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL
);

CREATE TABLE app.projects (
  tenant_id uuid NOT NULL REFERENCES app.tenants (id),
  id        bigint GENERATED ALWAYS AS IDENTITY,
  name      text NOT NULL,
  PRIMARY KEY (tenant_id, id)            -- tenant_id first
);

CREATE TABLE app.tasks (
  tenant_id  uuid NOT NULL,
  id         bigint GENERATED ALWAYS AS IDENTITY,
  project_id bigint NOT NULL,
  title      text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  -- composite FK: a task can only point at a project in the SAME tenant
  FOREIGN KEY (tenant_id, project_id) REFERENCES app.projects (tenant_id, id)
);

The composite foreign key is not decoration. The Postgres docs state that "referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security". With a plain project_id foreign key, a tenant could attach a task to another tenant's project ID, and the success or failure of that insert tells them which IDs exist. With (tenant_id, project_id) the check fails, as it did when I tried it:

ERROR:  insert or update on table "tasks" violates foreign key constraint "tasks_tenant_id_project_id_fkey"
DETAIL:  Key is not present in table "projects".

Step 2: Create a helper that reads the tenant setting safely

The policy compares each row's tenant_id with a custom setting the application sets per transaction. AWS's prescriptive guidance for SaaS on Postgres recommends this runtime-variable approach over one database role per tenant, and uses current_setting('app.current_tenant')::UUID in its example. That bare cast has a trap, so wrap it:

-- Still as app_owner
CREATE FUNCTION app.current_tenant_id() RETURNS uuid
LANGUAGE sql STABLE
AS $$ SELECT nullif(current_setting('app.tenant_id', true), '')::uuid $$;

Two details matter. The second argument true is missing_ok; per the admin functions docs, without it current_setting throws an error when the setting has never been defined. And once a session has set the value even once, it does not go back to NULL after the transaction ends: it becomes an empty string. I checked:

 is_null | raw
---------+-----
 f       | ''

A bare ''::uuid then fails with invalid input syntax for type uuid: "", which surfaces as random 500s on pooled connections. nullif(..., '') turns it into NULL, and tenant_id = NULL matches nothing, so the failure mode is "no rows", not "error" and not "all rows".

Step 3: Enable and force RLS, then write the policies

-- As app_owner (only the owner can enable RLS and add policies)
ALTER TABLE app.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.projects FORCE  ROW LEVEL SECURITY;   -- owner is subject too

CREATE POLICY tenant_isolation ON app.projects
  USING      (tenant_id = (SELECT app.current_tenant_id()))   -- rows you can see/target
  WITH CHECK (tenant_id = (SELECT app.current_tenant_id()));  -- rows you can write

ALTER TABLE app.tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.tasks FORCE  ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON app.tasks
  USING      (tenant_id = (SELECT app.current_tenant_id()))
  WITH CHECK (tenant_id = (SELECT app.current_tenant_id()));

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.projects, app.tasks TO app_user;
GRANT SELECT ON app.tenants TO app_user;
RESET ROLE;

Why each piece is there, straight from the docs:

• Default deny. With RLS enabled and no policy, "no rows are visible or can be modified". Enabling first and adding policies second is safe.
• Who bypasses. Superusers and roles with BYPASSRLS always bypass. Table owners bypass too, unless you FORCE ROW LEVEL SECURITY. That is why the app connects as app_user, which owns nothing, and why I force it anyway: migrations and admin scripts often run as the owner.
• USING vs WITH CHECK. USING filters what is visible; WITH CHECK validates new rows. If you omit WITH CHECK, the USING expression is reused, but writing both makes intent obvious in review.

Here is the behaviour as app_user, with no tenant set, then inside a transaction for one tenant (the seed data has 2,000 rows for one tenant and 198,000 for another):

== no tenant set
 count
-------
     0

== SET LOCAL in a transaction
 count
-------
  2000

== cross-tenant insert
ERROR:  new row violates row-level security policy for table "projects"

An UPDATE that tries to move a row to another tenant fails the same way, because the new row fails WITH CHECK. As a superuser the count is 200,000, and as app_owner it is 0, which confirms FORCE is doing its job.

Step 4: Set the tenant per transaction in application code

Set the tenant with set_config('app.tenant_id', $1, true) or SET LOCAL app.tenant_id = '...' inside an explicit transaction. The third argument true means the value "will only apply during the current transaction". set_config is the better choice from application code because it takes a bind parameter, while SET does not accept placeholders and tempts people into string formatting.

Here it is with psycopg 3.3.6 and psycopg-pool 3.3.3. Derive tenant_id from the verified session or token on the server, never from a request header the client controls.

# pip install "psycopg[binary]==3.3.6" "psycopg-pool==3.3.3"
from uuid import UUID
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    "host=127.0.0.1 port=5432 dbname=saas user=app_user",
    min_size=1, max_size=10,
    kwargs={"autocommit": True},   # transactions are opened explicitly below
    open=True,
)

def list_projects(tenant_id: UUID, name: str):
    with pool.connection() as conn:
        with conn.transaction():   # BEGIN ... COMMIT (or ROLLBACK on error)
            # is_local=true: dies at COMMIT/ROLLBACK, never leaks to the next request
            conn.execute("SELECT set_config('app.tenant_id', %s, true)", (str(tenant_id),))
            return conn.execute(
                "SELECT tenant_id, id, name FROM app.projects WHERE name = %s",
                (name,),
            ).fetchall()

acme = UUID("11111111-1111-1111-1111-111111111111")
print(list_projects(acme, "p100"))   # acme's own row
print(list_projects(acme, "p101"))   # belongs to the other tenant
pool.close()

Real output against the test database:

[(UUID('11111111-1111-1111-1111-111111111111'), 100, 'p100')]
[]

Do not use SET LOCAL outside a transaction block. Postgres does not error, it just warns and ignores it (SET docs), and if your driver hides warnings you get zero rows and a confused afternoon:

WARNING:  SET LOCAL can only be used in transaction blocks

Step 5: Make it safe behind PgBouncer transaction pooling

Transaction pooling is where most RLS setups leak. In pool_mode = transaction a server connection goes back to the pool when the transaction ends, and PgBouncer's config docs note that server_reset_query (default DISCARD ALL) is not run in that mode. The features table lists session SET/RESET as "Never" compatible with transaction pooling. So a plain SET app.tenant_id stays on that server connection for whichever client gets it next. I reproduced the leak on one connection: after a session-level SET, two later transactions that never mentioned the tenant both returned the other tenant's 198,000 rows.

The fix is the pattern from Step 4: only transaction-scoped settings, every query inside an explicit transaction. A minimal PgBouncer config for this:

; /etc/pgbouncer/pgbouncer.ini  (PgBouncer 1.26.1)
[databases]
saas = host=10.0.0.5 port=5432 dbname=saas

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 2000
; protocol-level prepared statements work in transaction mode when non-zero
max_prepared_statements = 200

Run the latest release. PgBouncer 1.26.1 shipped on 14 October 2026 with three security fixes, including a SCRAM authentication bug that allowed impersonating other SCRAM users under specific conditions. Also note that max_prepared_statements covers protocol-level prepared statements only; SQL-level PREPARE is still "Never" in transaction mode.

Step 6: Index for RLS and keep the policy cheap

The policy is added to every query on the table, so it has to be index-friendly. Two rules.

First, make tenant_id the leading column of the indexes your queries use, as in CREATE INDEX projects_tenant_name_idx ON app.projects (tenant_id, name);. Second, wrap the function call in a scalar subquery, (SELECT app.current_tenant_id()). The planner then evaluates it once as an InitPlan and uses the result as an index condition, rather than calling it per row. Supabase's RLS performance guide recommends the same wrapping for auth.uid() and warns it only applies when the value does not depend on the row. The plan from my test database:

 Index Scan using projects_tenant_name_idx on projects (actual rows=1 loops=1)
   Index Cond: ((tenant_id = $0) AND (name = 'p100'::text))
   InitPlan 1 (returns $0)
     ->  Result (actual rows=1 loops=1)

Before that index existed the same query was a Seq Scan with the tenant check as a filter. Also add an explicit WHERE tenant_id = ... in hot queries when it helps the planner; RLS remains the security boundary, the extra filter is just a hint. Keep policies to comparisons on the current row. The docs warn that policies which sub-select other tables can leak under READ COMMITTED races and call the current-row case "the simplest and best-performing".

Step 7: Test isolation in CI and audit for unprotected tables

Two checks, both cheap. The first runs as app_user and fails loudly if any foreign row is visible:

-- tests/rls_isolation.sql  (run with: psql -v ON_ERROR_STOP=1 -U app_user -d saas -f ...)
BEGIN;
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
DO $$
DECLARE leaked bigint;
BEGIN
  SELECT count(*) INTO leaked FROM app.projects
  WHERE tenant_id <> '11111111-1111-1111-1111-111111111111';
  IF leaked > 0 THEN
    RAISE EXCEPTION 'tenant isolation broken: % foreign rows visible', leaked;
  END IF;
  RAISE NOTICE 'isolation ok';
END $$;
ROLLBACK;

The second catches the new table someone added without RLS. Run it as a superuser in CI after migrations:

SELECT n.nspname AS schema, c.relname AS table_name,
       c.relrowsecurity AS rls_enabled, c.relforcerowsecurity AS rls_forced
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity)
ORDER BY 1, 2;

I added an app.invoices table without policies to check it works:

 schema | table_name | rls_enabled | rls_forced
--------+------------+-------------+------------
 app    | invoices   | f           | f

Fail the build if that query returns rows. This is the same idea as the guardrails in policy as code for AI agents: enforcement you cannot forget to call, plus a test that proves it is on. It matters more now that agents generate migrations, because an LLM will happily write a perfect CREATE TABLE and skip the ENABLE ROW LEVEL SECURITY line.

Finally, keep Postgres patched. RLS has had real bugs: CVE-2024-10976 (fixed in 17.1, 16.5 and siblings) could apply the wrong policy when a plan was reused across roles, and CVE-2025-8713 (fixed in 17.6, 16.10 and siblings) let optimizer statistics expose rows hidden by RLS in partitioned tables.

Troubleshooting

Symptom	Likely cause	Fix
Every query returns 0 rows	Tenant setting not set, or set with SET LOCAL outside a transaction (warning only)	Wrap requests in an explicit transaction and call set_config(..., true) first
invalid input syntax for type uuid: ""	Setting reverted to empty string after a previous transaction	Use nullif(current_setting('app.tenant_id', true), '')::uuid
unrecognized configuration parameter "app.tenant_id"	current_setting called without missing_ok in a fresh session	Pass true as the second argument
Users see all tenants' rows	App connects as table owner, a superuser, or a BYPASSRLS role	Use a non-owner NOBYPASSRLS role and FORCE ROW LEVEL SECURITY
Rows from the wrong tenant appear intermittently	Session-level SET leaking through PgBouncer transaction pooling	Only transaction-scoped settings; never plain SET for tenant context
new row violates row-level security policy	INSERT or UPDATE writes a tenant_id that differs from the current tenant	Set tenant_id from the session (column default app.current_tenant_id())
Queries slow after enabling RLS	Policy function evaluated per row, or no index leading with tenant_id	Wrap in (SELECT ...), add (tenant_id, ...) indexes, check EXPLAIN for InitPlan
View returns other tenants' data	View is owned by a superuser or a non-forced owner, and views check RLS as their owner by default	Create views WITH (security_invoker = true) (PostgreSQL 15+)
pg_dump errors with query would be affected by row-level security policy	Dumping as a role subject to RLS with row_security off	Dump as a superuser or BYPASSRLS role; the error is deliberate

FAQ

Does Postgres row-level security work with PgBouncer?

Yes, in transaction mode, as long as tenant context is set with SET LOCAL or set_config(..., true) inside each transaction. Session-level SET is not compatible with transaction pooling and will leak between clients. Session pooling works with either, but gives up most of the connection savings.

Should I use a database role per tenant instead of a session variable?

Usually not. AWS's guidance prefers the runtime variable because a role per tenant means creating and managing a Postgres user for every customer. Roles per tenant can make sense with a small number of large tenants that connect directly.

Does RLS replace WHERE tenant_id in my queries?

It replaces it as the security control. Keeping an explicit filter in hot queries is still useful as a planner hint and makes intent readable, but correctness should never depend on it.

Is RLS enough for compliance-grade isolation?

It is logical isolation inside one database, so a superuser, a backup, or a Postgres bug can still cross tenants. Customers who need physical separation should get a dedicated database, which is the silo model from the explainer.

What I check first on any RLS setup I inherit is boring: which role the application actually connects as, whether relforcerowsecurity is true on every tenant table, and whether anything in the code calls plain SET on a pooled connection. Those three cover most of the real leaks I would expect to find. Then I add the two CI queries above so the answer stays true after the next migration. The primary references are the PostgreSQL docs on row security policies, CREATE POLICY and SET, plus PgBouncer's configuration and features pages.

Related: Multi-Tenant SaaS Architecture Explained, Policy as Code for AI Agents in 2026 and Isolation Is Not an Abuse Control: Lessons From My Fleet.