Postgres Row-Level Security for Multi-Tenant Node.js Apps: Roles, Pooled Connections, and Blind Spots — Field Notes

Headline: PostgreSQL Row-Level Security (RLS) lets the database itself reject cross-tenant reads and writes, so one forgotten WHERE tenant_id = $1 in a Node.js query returns zero rows instead of another customer's data. RLS only holds when the app connects as a non-owner role and sets the tenant with set_config('app.tenant_id', $1, true) inside every transaction, because a session-level setting leaks across pooled connections.

In a shared-schema multi-tenant application, every table carries a tenant_id column and every query is supposed to filter on it. One forgotten filter in a new endpoint, a report or an ORM relation load is a data leak, and code review does not reliably catch a missing line. At Devya we move tenant isolation into PostgreSQL so the database refuses cross-tenant rows even when application code is wrong. These are our field notes on the policy, the role setup, the pooling pattern and the blind spots, for PostgreSQL 15 and later with node-postgres, Drizzle ORM or Prisma.

Key takeaways

PostgreSQL Row-Level Security appends a policy predicate to every query on a table, so a session can only read and write rows whose tenant_id matches the tenant set for the current transaction.

Table owners, superusers and roles with the BYPASSRLS attribute skip RLS, so the Node.js application must connect as a separate role that owns no tables, and FORCE ROW LEVEL SECURITY covers the owner too.

Set the tenant with SELECT set_config('app.tenant_id', $1, true) inside a transaction; the true argument scopes the value to that transaction, so the next request on the same pooled connection cannot inherit it.

Views, SECURITY DEFINER functions, unique and foreign-key constraint checks, and TRUNCATE are the main RLS blind spots, and each needs its own fix.

RLS enforces the boundary between tenants, not permissions inside a tenant; rules such as which members may delete invoices stay in application code.

What does PostgreSQL Row-Level Security actually enforce?

PostgreSQL Row-Level Security is a per-table feature, available since PostgreSQL 9.5, that filters which rows a database role can read and write using policies attached to the table. Running ALTER TABLE invoices ENABLE ROW LEVEL SECURITY with no policy makes the table default-deny: non-owner roles see zero rows and every write fails.

A policy then defines what is allowed. The USING expression filters existing rows for SELECT, UPDATE and DELETE. The WITH CHECK expression validates new row values for INSERT and UPDATE. Our standard policy, tenant_isolation, applies FOR ALL to the app_user role and uses the same expression in both clauses: tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid.

With both clauses in place, an INSERT that writes another tenant's tenant_id fails with the error "new row violates row-level security policy for table invoices", and an UPDATE cannot move a row into another tenant.

The nullif wrapper handles a real failure mode. current_setting('app.tenant_id', true) returns NULL when the setting was never defined on the connection, but after an earlier transaction on that connection has set it, it can read back as an empty string. Casting an empty string to uuid raises "invalid input syntax for type uuid". With nullif, a missing tenant becomes NULL, the comparison is never true, and the query returns zero rows. Both outcomes fail closed; zero rows is easier to reason about.

Should I use RLS, schema-per-tenant, or app-level filters?

Row-Level Security fits shared-schema multi-tenancy, where tenants share tables and a tenant_id column separates them. It does not replace physical isolation when a customer contract requires a dedicated database.

App-level WHERE tenant_id filters: every query in the codebase enforces isolation, a missing filter leaks other tenants' rows, and operational cost is the lowest.

Shared schema with RLS: PostgreSQL policies enforce isolation, a missing filter returns only the current tenant's rows, and the cost is one schema plus role and policy setup.

Schema per tenant: the search_path on each connection enforces isolation, safety depends on the search_path being correct, and every migration runs once per schema.

Database per tenant: separate connection strings enforce isolation, a query cannot reach another tenant, and provisioning, pooling and backups multiply per tenant.

We keep explicit tenant_id filters in application queries anyway. RLS is the backstop that turns a forgotten filter into an empty result instead of a breach.

Which database role should my Node.js app connect as?

The Node.js application should connect as a dedicated role that owns no tables and lacks the BYPASSRLS attribute, because PostgreSQL skips RLS policies for table owners, superusers and BYPASSRLS roles. This is how RLS most often ends up silently disabled: the app reuses the migration role, that role owns every table, and every policy is ignored without an error.

Our setup uses three roles. app_owner owns the schema and is used only by migrations. app_user is a NOBYPASSRLS login role granted SELECT, INSERT, UPDATE and DELETE on the tables, plus ALTER DEFAULT PRIVILEGES so tables created by future migrations get the same grants. A third role with BYPASSRLS serves cross-tenant jobs such as billing rollups through its own connection string, so cross-tenant access is a deliberate choice that shows up in code review.

FORCE ROW LEVEL SECURITY makes policies apply to the table owner as well, which protects against a misconfigured connection string. Nothing makes them apply to superusers or BYPASSRLS roles.

How do I pass the tenant ID safely through a connection pool?

Run SELECT set_config('app.tenant_id', $1, true) as the first statement of a transaction, and keep the request's queries inside that same transaction. The third argument, is_local = true, scopes the value to the transaction, so PostgreSQL discards it at COMMIT or ROLLBACK.

A session-level setting, either SET app.tenant_id or set_config with false, stays on the physical connection after the pool takes it back, and the next request that borrows that connection runs as the previous tenant.

We use set_config instead of SET LOCAL because SET is a utility command that does not accept bind parameters. SET LOCAL app.tenant_id = $1 is a syntax error, which leaves string interpolation into SQL as the only alternative. set_config is a regular function, so the tenant ID travels as a query parameter.

With node-postgres, our withTenant helper checks out a client from the Pool, runs BEGIN, runs the set_config call with the tenant ID as $1, executes the callback, then runs COMMIT, or ROLLBACK on error, and always releases the client. With Drizzle ORM, the first statement inside db.transaction is tx.execute with the sql template, which sends the tenant ID as a parameter. With Prisma, an interactive prisma.$transaction that starts with tx.$executeRaw running the same set_config call does the job. Because the setting lives inside the transaction, the pattern also works behind PgBouncer in transaction pooling mode.

The tenant ID must come from the verified session or access token on the server, never from a header or body the client controls. In NestJS we resolve it in a guard and carry it to the data layer with AsyncLocalStorage from node:async_hooks, so no repository can skip withTenant. The price is a transaction around each request's queries, with extra BEGIN and COMMIT round trips, and we accept it for a boundary the database enforces.

Where does Row-Level Security silently not apply?

RLS filters queries against the table, and several PostgreSQL features reach table data through another path. We check these on every RLS rollout.

Views run with the view owner's privileges by default, so a view owned by app_owner bypasses the tenant policy. PostgreSQL 15 added WITH (security_invoker = true) for views, which applies the caller's policies instead.

SECURITY DEFINER functions run as their owner, so a helper function owned by app_owner reads every tenant's rows. SECURITY INVOKER is the PostgreSQL default, and every SECURITY DEFINER function gets a manual review.

Unique and foreign-key checks bypass RLS by design to protect data integrity. A global UNIQUE (email) on a tenant-scoped table tells tenant A, through the constraint-violation error, that tenant B already uses that email. Composite constraints such as UNIQUE (tenant_id, email) close that channel.

TRUNCATE ignores RLS because it acts on the whole table, so the app role never gets the TRUNCATE privilege.

Every connection string that uses a BYPASSRLS role is an RLS-free path, so that role stays out of request-handling code entirely.

Does Row-Level Security slow queries down?

RLS costs about what a tenant_id filter costs, which is cheap when indexes lead with tenant_id. We build composite indexes tenant-first, such as (tenant_id, created_at) for a paginated invoice list.

Check hot queries with EXPLAIN while connected as app_user with a tenant set; a plan captured as the owner role does not include the policy at all. PostgreSQL also evaluates the policy predicate before any non-LEAKPROOF function in your own WHERE clause, and that ordering can change which index the planner picks.

How do I test that tenant isolation actually holds?

Test RLS with integration tests that connect as app_user to a real PostgreSQL instance, because a mocked database cannot evaluate policies. Our suite seeds two tenants and asserts four things.

First, with tenant A set, selecting tenant B's invoice by primary key returns zero rows. Second, with tenant A set, inserting a row with tenant B's tenant_id fails with "new row violates row-level security policy". Third, with no tenant set, every tenant-scoped table returns zero rows.

Fourth, a catalog query over pg_class lists every table in the public schema where relrowsecurity or relforcerowsecurity is false. It runs in CI after migrations and catches the likeliest regression: a new table shipped without RLS enabled and forced. Intentionally global tables, such as a plans catalog, sit on an explicit allow-list.

FAQ

Q: Does PostgreSQL RLS work with PgBouncer in transaction pooling mode?

A: Yes, when the tenant is set with set_config('app.tenant_id', $1, true) inside the same transaction as the queries. PostgreSQL discards transaction-scoped settings at COMMIT or ROLLBACK, so the next client on that server connection starts with no tenant.

Q: Why does my RLS policy have no effect?

A: The app is usually connecting as the table owner, a superuser, or a role with BYPASSRLS, all of which skip policies. Connect as a non-owner role and add ALTER TABLE ... FORCE ROW LEVEL SECURITY so the owner is covered too.

Q: Do I still need WHERE tenant_id filters if RLS is enabled?

A: RLS alone stops a missing filter from leaking data. We keep explicit filters because they make queries readable without knowing the policies and keep results correct if a query ever runs under a bypass role.

Q: Should RLS handle permissions inside a tenant, like admin versus member?

A: It can, but we keep in-tenant authorization in application code, where it is easier to test per feature. RLS is most valuable at the tenant boundary, where a single mistake exposes another customer's data.