Row-Level Security for Multi-Tenant PostgreSQL

Application-side scoping stops the ordinary case: a handler that fetches by identifier alone. It does nothing for the reporting job that connects with its own client, the migration someone runs by hand during an incident, or the analytics service that was given read access “just for dashboards”. Row-level security moves the tenant predicate into PostgreSQL itself, where it applies to every statement on the connection regardless of which code wrote it.

This guide sets up policy-based isolation end to end: role separation, per-table policies, transaction-scoped tenant configuration, and the tests that prove the isolation actually holds. It is part of the Access Control & IDOR Prevention guide within Vulnerability Patterns & Web Mitigation Strategies, and pairs with object-scoped queries in the API layer — the database policy is a backstop, not a replacement.

Prerequisites

  • PostgreSQL 12 or newer with permission to create roles and alter table ownership
  • A tenant column on every tenant-scoped table, indexed and non-nullable
  • A verified session that yields the tenant identifier server-side
  • A connection pool you can configure to run a statement at transaction start
  • A staging database with data for at least two tenants

Expected Outcomes

  • Policies enabled and forced on every tenant-scoped table
  • The application connecting as a non-owner role to which policies apply
  • The tenant parameter set per transaction from the session, never from request input
  • A test that runs a deliberately unscoped query and gets zero rows back

Step 1: Separate the Application Role From the Owner

The most common reason row-level security silently does nothing is that the application connects as the table owner, and owners bypass policies unless the table is explicitly forced. Fix the roles before writing a single policy.

-- Migration role: owns the schema, runs DDL, exempt from policies by design.
CREATE ROLE app_migrator LOGIN PASSWORD :'migrator_pw';

-- Application role: owns nothing, subject to every policy.
CREATE ROLE app_runtime LOGIN PASSWORD :'runtime_pw';
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;

-- Never grant BYPASSRLS, and never let the runtime role own a table.
Two Roles, Two Very Different Powers The migration role owns the schema, runs data definition statements and is exempt from policies because it must be. The runtime role owns nothing, holds only data manipulation grants, and is fully subject to policies. A single role doing both jobs is the reason most row-level security deployments enforce nothing. app_migrator owns the schema and every table runs migrations from the pipeline only credential lives in the deploy secret store Exempt from policies unless a table is explicitly forced — which is why we force them. app_runtime owns nothing at all holds data manipulation grants only used by the application and every job Fully subject to policies, so a raw query still cannot cross a tenant boundary.

Step 2: Enable, Force, and Write the Policy

Enable row-level security per table, force it so ownership is not an escape hatch, and express the predicate once.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_read ON invoices
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE POLICY tenant_write 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);

USING filters rows the statement may see; WITH CHECK constrains rows it may write. Omitting the second one leaves an insert that stamps another tenant’s identifier perfectly legal — the row simply becomes invisible to its creator, which is a data-integrity incident waiting to be discovered months later.

The second argument to current_setting matters too: passing true makes a missing parameter return NULL rather than raising. A NULL comparison yields no rows, so an unset tenant fails closed. Verify that behaviour rather than assuming it, because failing open here would be catastrophic.

-- Fail-closed check: with nothing set, the table must appear empty.
RESET app.tenant_id;
SELECT count(*) FROM invoices;   -- expect 0, not an error and not the whole table

Apply the same three statements to every tenant-scoped table. Generate them from the schema rather than writing them by hand, and add a test that fails when a table with a tenant column has no policy — new tables are the usual gap.

Step 3: Set the Tenant Per Transaction

The policy reads a configuration parameter. Setting it correctly — from the verified session, scoped to a transaction — is where pooled connections punish carelessness.

// Every request runs inside a transaction that sets the tenant first.
export async function withTenant<T>(subject: Subject, fn: (tx: Tx) => Promise<T>) {
  return db.$transaction(async (tx) => {
    // SET LOCAL is discarded at commit/rollback, so the pooled connection
    // never carries this tenant into the next request.
    await tx.$executeRawUnsafe(
      `SET LOCAL app.tenant_id = '${assertUuid(subject.tenantId)}'`,
    );
    return fn(tx);
  });
}

Two rules keep this safe. Use SET LOCAL inside an explicit transaction — a bare SET persists for the session, and in a pool the next borrower inherits it, which reintroduces exactly the cross-tenant read the policy exists to prevent. And validate the value as a UUID before interpolating it: the tenant comes from your own session store, but a parameter that reaches a SET statement unvalidated is an injection point one refactor away from being user-controlled.

Why the Setting Must Be Transaction-Scoped Two timelines over one pooled connection. With a session-scoped setting, request one sets tenant A, and request two — which forgets to set anything — inherits tenant A and reads its data. With a transaction-scoped setting, the value is discarded at commit, so request two starts with no tenant and the policy returns zero rows. Session-scoped setting on a pooled connection request 1 · SET app.tenant_id = A · reads A request 2 · sets nothing · still reads A Transaction-scoped setting on the same connection request 1 · SET LOCAL = A · reads A · commit request 2 · nothing set · reads zero rows Failing closed is the point: a request that forgets to set a tenant sees nothing at all which surfaces as an obvious empty-page bug rather than as a silent cross-tenant read

Verification

Prove the isolation from the database side, where no application code can flatter the result.

Four Checks, Run as the Application Role Four database-side checks. A normal count returns only the configured tenant. An explicitly cross-tenant predicate still returns nothing. An insert stamping another tenant is refused by the write check. And a schema query lists any table that has a tenant column but no enforced policy. count with a tenant configured — rows for that tenant only the ordinary case, and the one that proves the policy is active at all an explicitly cross-tenant predicate — zero rows you cannot opt out of the policy by writing a broader query an insert stamping another tenant — refused this is the write check; without it, rows vanish instead of being rejected schema query for unenforced tables — empty the check that catches the table someone added last week 
-- As app_runtime, with tenant A configured.
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT count(*) FROM invoices;                       -- rows for A only
SELECT count(*) FROM invoices WHERE tenant_id <> current_setting('app.tenant_id')::uuid;
-- Expect 0: even an explicitly cross-tenant predicate returns nothing.

-- Attempt to write another tenant's row.
INSERT INTO invoices (id, tenant_id, total) VALUES (gen_random_uuid(),
  '22222222-2222-2222-2222-222222222222', 100);
-- Expect: new row violates row-level security policy

Add the same three checks to the automated suite, plus a schema-level assertion that no tenant-scoped table is missing a policy:

SELECT c.relname
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id'
WHERE c.relkind = 'r' AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity);
-- Expect zero rows; any table listed here has a tenant column and no enforcement.

Troubleshooting

Symptom Likely cause Fix
Policies appear to do nothing The application connects as the table owner Move the application to a non-owner role and add FORCE ROW LEVEL SECURITY
Occasional rows from another tenant SET used instead of SET LOCAL, leaking through the pool Set the parameter inside an explicit transaction and confirm the pool is transaction-scoped
Every query returns zero rows after deployment The tenant parameter is never set on that code path Route all access through the transaction wrapper; treat the empty result as the intended fail-closed signal
Inserts succeed but rows vanish Policy has USING without WITH CHECK Add a WITH CHECK clause so writes cannot stamp another tenant
Query latency regressed after enabling Plan changed because the tenant column is missing from the index Add the tenant column as the leading column of the composite indexes hot queries use
Background job sees nothing Job connects with the runtime role but sets no tenant Give jobs an explicit per-tenant loop, or a dedicated role with an audited exemption

Common Implementation Mistakes

Frequently Asked Questions

Does row-level security replace application-side scoping?

No — it backstops it. Application scoping gives better errors, expresses rules a single predicate cannot (shared documents, delegated access, support impersonation), and protects data that never touches the database. The policy catches what application code misses: raw queries, dashboards, ad-hoc migrations and jobs. With both in place, a forgotten predicate in a handler is a bug that returns an empty page rather than a breach that returns another customer’s invoices.

What is the performance cost?

The policy expression is folded into the plan as an extra predicate, so the cost is roughly the tenant filter you should already be applying — provided the tenant column is indexed and leads the composite indexes your hot queries use. The regression to watch for is a plan change rather than the predicate itself: a policy can prevent an index-only scan when the tenant column is not part of the index. Capture plans for your top queries before and after enabling, and adjust indexes accordingly.

How do connection pools interact with the tenant setting?

Use SET LOCAL inside an explicit transaction. The value is then discarded at commit or rollback, so the next borrower of the pooled connection starts clean. A plain SET persists for the session, which in a pool means request two can inherit request one’s tenant — precisely the cross-tenant read the policy was installed to prevent. With a transaction-mode pooler, make the parameter the first statement of every transaction, and add an integration test that runs two requests for different tenants over a pool of size one.