Postgres row-level security for multi-tenant SaaS: the patterns that scale past 500 tenants

The page in your on-call rotation was labelled "P1 — customer data leak." That is the kind of alert you hope never resolves to something real. At 2:14 AM on a Wednesday, it did.

A mid-market B2B customer had triggered a CSV export of their pipeline data — several thousand rows, nothing unusual. Their customer success manager happened to glance at the file before forwarding it. Fourteen rows did not match any account, contact, or deal she recognized. She cross-referenced the record IDs against their CRM. None of them existed in their tenant. She opened a support ticket at 1:58 AM with the subject line: "URGENT: seeing data that isn't ours."

By 2:14 AM the pager went off. By 2:22 AM a senior engineer had found the endpoint. It was a debug route, added six weeks earlier to help the support team reproduce a specific export bug. The handler called a helper function that assembled the SQL query — same helper used everywhere else in the codebase — but someone had omitted the WHERE tenant_id = $1 clause on the secondary join. Every other query in that module had it. This one did not.

The export had run for three customers that night. All three got a mix of rows. The mix was small — fourteen, nine, and three rows respectively — but the damage was done. Three customers had seen each other's data. Legal was on a call by 4:30 AM. The GDPR notification clock started at 6:00 AM when the DPO confirmed the exposure was reportable.

The post-mortem conclusion: the application had been relying on the ORM to enforce tenant isolation, and the ORM was not a security boundary. It was a convenience layer. One missing .where() call, one forgotten argument, one debug shortcut — and the isolation was gone. The system had no second line of defence.

This is not a hypothetical. Variations of this incident appear in breach disclosures regularly. The pattern is always the same: a developer adds a route or a job or a reporting query, is thinking about functionality rather than tenancy, and omits the filter. The ORM does not stop them. The test suite does not catch it because the test data was all scoped to a single tenant anyway. And the bug sits in production until a customer notices.

Postgres row-level security exists to be that second line of defence. It moves the tenant filter from application code — where it can be forgotten — into the database, where it cannot be bypassed by accident. Done right, it means a query that forgets the application-level filter still returns zero rows for the wrong tenant, not fourteen. Done wrong, it creates a false sense of safety while adding overhead with no benefit.

This post covers how to do it right: when RLS is the correct tool, how it actually evaluates predicates, the patterns that work at scale, the ORM integration that does not collapse under connection pooling, and the failure modes that catch engineers who assume they understood it.

What's actually happening

The application-level tenancy trap

Every multi-tenant backend engineer has heard or written the sentence: "We always filter by tenant_id in the ORM." It sounds like a guarantee. It is not.

The ORM is a code layer. It has no enforcement mechanism. When a developer writes a repository method, a data loader, a background job, or a one-off migration script, the ORM does not validate that tenant context is present. It executes what it is given. If the developer forgets to call .where({ tenantId: ctx.tenantId }), the ORM issues the query without it. The database executes the query without it. Rows come back from every tenant that matches the rest of the predicate.

The reasons tenancy filters get omitted fall into predictable categories:

Debug and admin endpoints. Routes added by support engineers or senior developers who trust themselves not to abuse them. The trust is warranted. The problem is what happens when the endpoint survives beyond its intended lifetime and is called in a different context.

Background jobs. A scheduled job that sends daily digests has no HTTP context. The developer who writes it may not add tenant scoping because the job is "meant to run across all tenants." Three months later a different developer copies the query into a new job that is not meant to run across all tenants, and the copy-paste brings the unscoped query with it.

Report and analytics queries. Complex SQL written by a data engineer who is thinking about aggregation logic, not access control. These queries are often reviewed for correctness but not for tenant isolation.

ORM escape hatches. Raw SQL fragments, $queryRaw calls, execute() wrappers. Every ORM has them, and they all bypass whatever tenant-scoping middleware the developer added to the ORM layer.

The fundamental problem is that application-level tenancy filtering is voluntary. The developer opts in on every query they write. A database-level policy is involuntary. The developer cannot opt out accidentally.

Connection pooling and tenant context

Before covering RLS specifically, it is worth understanding why tenant context in the database is non-trivial.

When your application connects to Postgres, it does so through a connection. That connection has a session. Postgres lets you set configuration variables on that session with SET variable = value. These variables persist for the lifetime of the session. In a simple model, you open a connection, call SET app.tenant_id = 'acme-corp', and every query on that connection runs with that variable available via current_setting('app.tenant_id').

This works fine when each application instance has a dedicated connection. It breaks under connection pooling.

PgBouncer, the most widely deployed Postgres connection pooler, operates in three modes:

• Session mode: A client gets a server connection for its entire session. Variables set with SET persist. Roughly equivalent to no pooling.
• Transaction mode: A client gets a server connection for the duration of a transaction. After COMMIT or ROLLBACK, the connection is returned to the pool and may be given to a different client. Any SET variables from the previous client's transaction persist on the connection. The next client inherits them.
• Statement mode: One statement per connection lease. Generally too restrictive for multi-statement workflows.

Transaction mode is the default operating mode for most high-throughput deployments. It is where the session variable trap bites engineers who did not read the pgbouncer documentation carefully enough.

If your application runs SET app.tenant_id = 'acme-corp' at connection time, and the connection later gets reused by a request scoped to 'beta-co' before that request issues its own SET, then 'beta-co''s queries run with app.tenant_id = 'acme-corp'. That is a cross-tenant data leak caused by the pooler doing its job correctly.

The correct approach under transaction pooling is SET LOCAL. SET LOCAL applies the variable for the current transaction only. When the transaction ends, the variable reverts to its previous value. This makes it safe to use with pgbouncer in transaction mode: the connection is returned to the pool with the variable reset.

BEGIN;
SET LOCAL app.tenant_id = 'acme-corp';
SELECT * FROM deals;
COMMIT;
-- app.tenant_id is reset after COMMIT

This distinction — SET (session-scoped) vs SET LOCAL (transaction-scoped) — is the single most common source of bugs in RLS deployments. The Postgres documentation on SET LOCAL is explicit about this: the effect of SET LOCAL lasts only until the end of the current transaction.¹

There is a subtlety worth highlighting: what happens if your application code issues a bare SET LOCAL outside of an explicit transaction? Postgres auto-wraps single statements in implicit transactions. SET LOCAL in an implicit transaction takes effect for that statement and then immediately reverts, because the implicit transaction commits immediately after. For RLS to work correctly, the SET LOCAL and the subsequent data queries must all be within the same explicit BEGIN ... COMMIT block. If your ORM or connection library does not begin transactions explicitly, SET LOCAL is effectively a no-op — the variable is set and unset before the first data query runs.

This is another reason the transaction wrapper pattern described in the implementation section is non-negotiable. You cannot inject SET LOCAL as a standalone statement before a series of queries and expect it to persist. Transactions are not optional when using RLS with SET LOCAL.

One more connection pooler interaction to be aware of: some connection pools (HikariCP in Java, for example) implement connection validation by executing a test query against newly acquired connections. If that validation query runs before your SET LOCAL, it runs without the tenant context. This is fine as long as the validation query does not touch tenant-scoped tables. If it does — for example, if someone configured a validation query that selects from your application tables — the validation query will either return no rows (silent failure) or raise an error depending on your policy configuration. Use a neutral validation query like SELECT 1 or SELECT NOW().

Three classical isolation strategies

Before discussing where RLS fits, it is worth placing it in the context of the three standard multi-tenant database architectures:

Shared schema, shared tables, tenant column. All tenants share the same database, the same schema, and the same tables. Every row has a tenant_id column. Application code (and optionally RLS) filters by that column. This is the most common architecture for SaaS applications because it minimizes operational complexity — one schema to migrate, one set of indexes to maintain. The risk is that isolation is entirely software-enforced. RLS is most directly applicable here.

Schema-per-tenant. Each tenant gets their own Postgres schema (a namespace within a single database). Tables have the same structure across schemas, but the data is physically separated. Application code switches the search_path to the tenant's schema on each connection. This provides stronger logical isolation at the cost of operational complexity: migrations must run against every schema, and Postgres has practical limits on the number of schemas (not a hard ceiling, but performance degrades at high schema counts). RLS is less relevant here because the schema boundary itself provides isolation, though it can still be useful for admin queries that span schemas.

Database-per-tenant. Each tenant gets a separate Postgres database or cluster. Maximum isolation, maximum operational complexity. Used where compliance requirements (HIPAA, FedRAMP) demand physical separation or where tenants have contractual requirements for dedicated infrastructure. RLS adds no value for per-database tenancy.

For shared-schema architecture — which is where the vast majority of B2B SaaS products operate — RLS is the appropriate database-level enforcement mechanism.

It is also worth noting that RLS and schema-per-tenant are not mutually exclusive. Some teams use schema-per-tenant as their primary isolation boundary but enable RLS within each schema as an additional layer for admin tooling that needs to query across schemas using search_path manipulation. This is a valid combination, though operationally complex. The most common real-world use case is pure shared-schema with RLS, and that is the architecture this post focuses on.

How Postgres evaluates RLS predicates internally

Understanding how Postgres evaluates RLS predicates — not just what the syntax means, but how the planner handles them — is important for reasoning about performance and for debugging unexpected behaviour.

When you enable RLS on a table and define a policy, Postgres incorporates the policy predicate into the query plan as if it were an additional WHERE clause that the user cannot see or override. The predicate is "silently" appended by the parser before the planner sees the full query. This is why RLS is sometimes described as "invisible to the application": the application sends SELECT * FROM deals and the planner processes SELECT * FROM deals WHERE tenant_id = current_setting('app.tenant_id')::uuid.

The planner can then use this predicate in the same ways it uses any predicate: it can push it down into index scans, use it for partition pruning, and combine it with other predicates via and/or logic. The key implication for performance: if your tenant_id column has an index, the planner will use it, and the RLS policy does not prevent that optimization.

However, the predicate contains a function call — current_setting('app.tenant_id') — and Postgres evaluates function calls differently depending on their volatility classification. current_setting() is classified as STABLE (its result does not change within a single statement execution but may change between statements). This means the planner treats the result as a constant for the duration of a single query, which is the correct and safe behaviour. It will not re-call current_setting() for every row during a sequential scan.

Where things get more complex is with subqueries. In a query like:

SELECT * FROM deals
WHERE stage IN (
  SELECT stage FROM deal_stages WHERE tenant_id = current_setting('app.tenant_id')::uuid
);

Postgres evaluates the subquery as a separate query context, which means the RLS policy on deal_stages is evaluated independently of the outer query's policy. If both tables have RLS enabled, both predicates are evaluated. In complex nested queries with multiple correlated subqueries, the policy predicate may be evaluated multiple times. This is the source of the planning overhead described in the case studies section.

PERMISSIVE vs RESTRICTIVE policies are another aspect of policy evaluation worth understanding:

• PERMISSIVE policies (the default) are combined with OR. If a table has multiple PERMISSIVE policies, a row is visible if it passes any one of them.
• RESTRICTIVE policies are combined with AND. A row must pass all RESTRICTIVE policies to be visible, in addition to at least one PERMISSIVE policy.

For the common single-tenant-isolation use case, one PERMISSIVE policy per table is sufficient. You only need RESTRICTIVE policies when you are implementing layered access control — for example, tenants can see their own rows AND only rows in an active state, where the second condition is enforced by a separate RESTRICTIVE policy.

How RLS evaluates predicates

Postgres row-level security, introduced in Postgres 9.5 (2016), allows table owners to define policies that restrict which rows can be seen or modified by which roles.²

A policy has two parts:

• USING clause: Controls which rows are visible for SELECT, UPDATE, and DELETE. Before any query against the table, Postgres evaluates the USING predicate and restricts the result set to rows where the predicate returns true.
• WITH CHECK clause: Controls which rows can be inserted or updated. Before an INSERT or UPDATE, Postgres evaluates the WITH CHECK predicate against the new row values. If the predicate returns false, the operation is rejected with a permission error.

If you specify only a USING clause, it is implicitly used for WITH CHECK as well. But this default is dangerous: a USING clause written to filter existing rows by tenant may not correctly validate that a new row's tenant_id matches the current session's tenant. Always specify WITH CHECK explicitly on INSERT/UPDATE policies.

RLS is enabled per table, not globally:

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

ENABLE ROW LEVEL SECURITY activates RLS for non-owner roles. FORCE ROW LEVEL SECURITY also applies it to the table owner. You almost always want both — without FORCE, the table owner (often the role your migration tooling uses) bypasses all policies.

Once RLS is enabled, a table has no policies by default, which means no rows are accessible to non-owner roles. You then create explicit policies to allow access:

CREATE POLICY tenant_isolation ON deals
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Postgres evaluates this predicate as part of the query plan. It is not evaluated in application code, it cannot be bypassed by ORM raw-query escape hatches, and it applies equally to SELECT, INSERT, UPDATE, and DELETE unless you scope the policy to specific commands.

The current_setting('app.tenant_id') call reads the session variable set by SET LOCAL app.tenant_id = '...' in your application's database middleware. This is the bridge between the application context and the database policy.

One important implementation detail: current_setting() returns text. If your tenant_id column is UUID, you must cast: current_setting('app.tenant_id')::uuid. If the variable is not set and the query is issued without it, current_setting('app.tenant_id') raises an error by default. Use current_setting('app.tenant_id', true) (the second argument true means "return null instead of error if not set") only if you have a reason to allow unscoped queries — which for tenancy enforcement, you generally do not.

The pattern

Variation by scale

The right RLS architecture depends significantly on your tenant count and traffic distribution.

Five to fifty tenants. At this scale, operational simplicity dominates. All tenants are probably similar in size. You likely have direct relationships with each tenant's technical team. A single RLS policy on each table, enforced via SET LOCAL, is the entire solution. Connection pool overhead is minimal. A BYPASSRLS role for migrations runs behind a deploy script. Debugging is easy because you can impersonate any tenant manually.

Fifty to five hundred tenants. This is where operational complexity starts to matter. You may have a handful of "whale" tenants with 100x the row count of the average tenant. Queries that hit the full table — reports, exports, aggregations — show very different latency across tenants. You need to monitor the RLS policy evaluation overhead, ensure composite indexes include tenant_id as the leading column, and handle the fact that your connection pool now has to serve many concurrent tenant contexts simultaneously.

Five hundred to fifty thousand tenants. This is the scale at which the shared-schema model with RLS shows its limits. The IN clause approach — where a single query fetches data for multiple tenants — becomes a planning problem. The current_setting call in the policy gets evaluated once per row in the scan, not once per query. Long-running reports must be scoped carefully or offloaded to a read replica with a separate connection context. At this scale you also need to think carefully about the hot-tenant problem.

The hot-tenant problem

A hot tenant is a single tenant whose query volume, row count, or connection count is disproportionately large relative to the tenant population. In a shared schema, a hot tenant's queries compete with all other tenants for the same Postgres resources.

RLS does not help with the hot-tenant problem directly, but it interacts with it. If your RLS policy uses (tenant_id = current_setting('app.tenant_id')::uuid) and your composite index on (tenant_id, created_at) is selective, the hot tenant's queries still use the index efficiently. The problem is that a hot tenant running a full-table scan — say, an unindexed reporting query — will lock autovacuum, bloat dead tuple counts, and cause lock contention that affects all other tenants.

Solutions to the hot-tenant problem at the database level typically involve one of:

• Rate-limiting the hot tenant's connection count via pg_hba.conf or a separate pool with a lower max_connections cap.
• Moving the hot tenant to a dedicated read replica for reporting queries.
• Schema-per-tenant migration for that specific tenant (operational cost, but sometimes the right call for contractual reasons).

None of these require abandoning RLS. They layer on top of it.

Policy versioning and schema evolution

As your product evolves, you will need to modify RLS policies. Unlike a WHERE clause in application code, a policy change is a DDL operation that requires a CREATE OR REPLACE POLICY or DROP POLICY + CREATE POLICY. This has two practical implications.

First, policy changes must be included in your migration pipeline. Your migration tooling needs to track policy definitions alongside table definitions. Drizzle Kit does not currently auto-generate RLS policy migrations from schema definitions (as of Drizzle 0.30); teams typically manage policy DDL in hand-written migration files alongside auto-generated table migrations. Alembic in Python supports manual DDL statements inline with auto-generated migrations. Whatever your tooling, the policy definition must be version-controlled alongside the table definition it belongs to.

Second, changing a policy while traffic is running requires care. CREATE OR REPLACE POLICY takes an AccessShareLock on the table, not a full AccessExclusiveLock, but the policy replacement is not instantaneous. During the brief window where the old policy is being replaced by the new one, queries may see the old behaviour. For most policy changes this is acceptable — for security-critical changes (e.g., tightening a policy that was previously too permissive), consider running the change during a low-traffic maintenance window.

The operational recommendation is to treat policy DDL with the same care as column or index DDL: include it in a migration, test it in staging against a production-representative dataset, and deploy it during a scheduled maintenance window for high-sensitivity changes.

Background jobs

Background jobs are one of the trickiest cases for RLS because they may have no tenant context at all, or they may need to process data across all tenants sequentially.

Jobs scoped to a single tenant (e.g., generating a nightly digest for a specific tenant based on a scheduled trigger) should set the tenant context exactly as an HTTP request would:

BEGIN;
SET LOCAL app.tenant_id = '<tenant-uuid>';
-- job query here
COMMIT;

Jobs that iterate across all tenants (e.g., computing monthly invoice amounts for every tenant) need to either use the BYPASSRLS role or loop through each tenant and issue SET LOCAL per transaction. The BYPASSRLS approach is operationally simpler but requires careful access control on the role. The loop approach is safer because the policy remains active.

Jobs that aggregate across tenants (e.g., computing platform-wide statistics for internal dashboards) should use the BYPASSRLS role explicitly, and those queries should never touch the customer-facing application path.

Cross-tenant admin queries

Your support tooling, customer success dashboards, and internal admin panels need to read data across tenants. This is a legitimate use case. The mistake is satisfying it by granting BYPASSRLS to the application role (covered in the anti-patterns section). The correct approach is a dedicated admin role with BYPASSRLS, used only by tooling that requires it, never by the customer-facing application.

Migrations

Schema migrations present a specific challenge: ALTER TABLE, CREATE INDEX, UPDATE statements that touch every row across all tenants must bypass RLS. If RLS is active and the migration user's role does not have BYPASSRLS, the migration will fail or silently operate on zero rows depending on whether the policy blocks writes.

The standard approach is a dedicated migration role with BYPASSRLS:

CREATE ROLE migrator BYPASSRLS;
GRANT migrator TO deploy_user;

Your migration tooling (Flyway, Liquibase, Drizzle Kit, Alembic, db-migrate) should connect as this role. The customer-facing application role should never have BYPASSRLS.

ORM friction

Every major ORM has a path to injecting SET LOCAL per transaction, but none of them make it automatic. The integration requires explicit middleware.

Prisma does not have built-in RLS support as of version 5.x. The recommended approach is to use $transaction with an $executeRaw to set the variable:

await prisma.$transaction([
  prisma.$executeRaw`SET LOCAL app.tenant_id = ${tenantId}`,
  prisma.deal.findMany({ where: { /* no tenantId needed here */ } }),
]);

This is verbose and easy to forget. Teams building on Prisma typically wrap it in a middleware or a repository abstraction.

Drizzle ORM provides transaction support with a callback API that makes it straightforward to inject the SET LOCAL at the start of every transaction. We cover this in detail in the next section.

SQLAlchemy supports session-level events that can inject SET LOCAL statements at the start of each transaction via the after_begin event.

ActiveRecord (Rails) can use around_action in controllers to wrap requests in a transaction with SET LOCAL. The ActiveRecord connection pool does not use pgbouncer transaction mode by default, so session-scoped SET is technically safe, but SET LOCAL is still the correct approach for production deployments that run behind a pooler.

Here is a complete SQLAlchemy example for the after_begin event pattern, since Python backends represent a significant share of multi-tenant SaaS deployments:

from sqlalchemy import event, text
from sqlalchemy.orm import Session
 
def configure_rls_for_session(session: Session, tenant_id: str) -> None:
    """Attach a tenant context listener to an existing Session."""
 
    @event.listens_for(session, "after_begin")
    def set_tenant_context(session, transaction, connection):
        connection.execute(
            text("SET LOCAL app.tenant_id = :tenant_id"),
            {"tenant_id": str(tenant_id)}
        )
 
# Usage in a FastAPI dependency
from fastapi import Depends, Request
from sqlalchemy.orm import Session
 
def get_tenant_db(request: Request, db: Session = Depends(get_db)):
    tenant_id = request.state.tenant_id  # set by auth middleware
    configure_rls_for_session(db, tenant_id)
    return db

Note that in SQLAlchemy, after_begin fires when the session's transaction begins on the connection. For short-lived sessions within a request lifecycle, this is equivalent to issuing SET LOCAL at the start of every transaction — which is the desired behaviour.

For Go backends using database/sql or pgx, the equivalent pattern is to execute the SET LOCAL statement as the first operation within every BeginTx block:

func withTenantTx(ctx context.Context, db *sql.DB, tenantID string, fn func(*sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
 
    if _, err := tx.ExecContext(ctx, "SET LOCAL app.tenant_id = $1", tenantID); err != nil {
        return fmt.Errorf("setting tenant context: %w", err)
    }
 
    if err := fn(tx); err != nil {
        return err
    }
 
    return tx.Commit()
}

The pattern is consistent across languages: begin a transaction, set the tenant context, do your work, commit. The implementation details vary by ORM and driver, but the structure is identical.

Logical replication and backups

RLS policies are defined on tables, not on data. Logical replication replicates the underlying data including all rows. The subscriber receives all rows regardless of the publisher's RLS policies. If you are replicating to a reporting database, that database should have its own access controls; do not assume the publisher's RLS policies carry over.

Physical backups (pg_basebackup, PITR) include all data for all tenants. Access to the backup file is equivalent to access to all tenant data. This is a physical security concern, not one RLS addresses.

The fix / approach

This section walks through a complete RLS implementation from schema design through ORM integration to test strategy. The examples use Postgres 14+ syntax and Drizzle ORM 0.30+ for the TypeScript layer.

1. Schema design: tenant_id on every row, composite indexes leading with tenant_id

   Start before any RLS policy exists. Every table that stores tenant data must have a tenant_id column. This is a prerequisite, not an afterthought.

   CREATE TABLE deals (
     id          UUID        NOT NULL DEFAULT gen_random_uuid(),
     tenant_id   UUID        NOT NULL REFERENCES tenants(id),
     title       TEXT        NOT NULL,
     amount      NUMERIC(12,2),
     stage       TEXT        NOT NULL DEFAULT 'prospect',
     created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
     updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
     PRIMARY KEY (tenant_id, id)
   );

   A composite primary key (tenant_id, id) serves a dual purpose: it enforces uniqueness within a tenant, and it makes the clustered index include tenant_id as the leading column, which is what you want for most queries. For tables where you need global uniqueness on id independent of tenant (e.g., if IDs are referenced across a federated system), keep id as the primary key and add a composite index:

   CREATE INDEX idx_deals_tenant_created ON deals (tenant_id, created_at DESC);
   CREATE INDEX idx_deals_tenant_stage   ON deals (tenant_id, stage);

   All your most common query patterns — fetch deals for a tenant sorted by date, fetch deals by stage for a tenant — hit the tenant_id prefix and use the index. Without this, RLS still works but the query planner may do a sequential scan filtered by the RLS predicate.

2. Create the application role and the admin role

   The application role is what your backend process authenticates as. It has no special privileges beyond DML on the tables it needs.

   -- Application role (used by your API server)
   CREATE ROLE app_user NOLOGIN;
   GRANT CONNECT ON DATABASE myapp TO app_user;
   GRANT USAGE ON SCHEMA public TO app_user;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
   GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
    
   -- Migration role (used by deploy scripts and Drizzle Kit migrations)
   CREATE ROLE migrator NOLOGIN BYPASSRLS;
   GRANT CONNECT ON DATABASE myapp TO migrator;
   GRANT ALL PRIVILEGES ON DATABASE myapp TO migrator;
    
   -- Admin role (used by internal tooling, support dashboards)
   CREATE ROLE admin_user NOLOGIN BYPASSRLS;
   GRANT CONNECT ON DATABASE myapp TO admin_user;
   GRANT USAGE ON SCHEMA public TO admin_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO admin_user;
    
   -- Real login users inherit from the above
   CREATE ROLE api_server LOGIN PASSWORD '...' IN ROLE app_user;
   CREATE ROLE deploy_runner LOGIN PASSWORD '...' IN ROLE migrator;
   CREATE ROLE support_tool LOGIN PASSWORD '...' IN ROLE admin_user;

   The critical separation: app_user has no BYPASSRLS. migrator and admin_user do. The application server authenticates as api_server (which inherits from app_user). Migrations run as deploy_runner (which inherits from migrator).

3. Enable RLS and create the tenant isolation policy

   With the schema and roles in place, enable RLS on each tenant-scoped table:

   ALTER TABLE deals ENABLE ROW LEVEL SECURITY;
   ALTER TABLE deals FORCE ROW LEVEL SECURITY;
    
   CREATE POLICY tenant_isolation ON deals
     AS PERMISSIVE
     FOR ALL
     TO app_user
     USING (tenant_id = current_setting('app.tenant_id', false)::uuid)
     WITH CHECK (tenant_id = current_setting('app.tenant_id', false)::uuid);

   Notes on this policy:

   • AS PERMISSIVE means the policy is OR-combined with other PERMISSIVE policies on the same table. If you add a second policy later (e.g., for admin users), rows accessible by either policy are visible.
   • FOR ALL covers SELECT, INSERT, UPDATE, DELETE, and TRUNCATE.
   • TO app_user restricts this policy to the app_user role. The migrator and admin_user roles bypass RLS entirely via BYPASSRLS, so they do not need a policy.
   • current_setting('app.tenant_id', false) — the false second argument means: raise an error if the variable is not set. This is intentional. If the application forgot to set app.tenant_id before issuing a query, we want a database-level error, not a silent empty result set.

   A silent empty result set is dangerous: the application may interpret zero rows as "no data" rather than "RLS blocked everything because the tenant context is missing." An explicit error surfaces the bug immediately.

   For existing tables, enable RLS after confirming all columns are indexed correctly. Enabling RLS on a large table with missing indexes will cause every policy check to perform a sequential scan.

4. Set up session variable injection in your connection middleware

   Every database transaction that runs in the context of a tenant request must start with SET LOCAL app.tenant_id = '...'. This must happen before any data query.

   The cleanest approach is a wrapper function that your application uses for every database operation. Here is a Drizzle ORM example in TypeScript:

   import { drizzle } from 'drizzle-orm/node-postgres';
   import { sql } from 'drizzle-orm';
   import { Pool } from 'pg';
    
   const pool = new Pool({ connectionString: process.env.DATABASE_URL });
   const db = drizzle(pool);
    
   /**
    * Execute a function within a tenant-scoped database transaction.
    * Injects SET LOCAL app.tenant_id before any query.
    */
   export async function withTenantTransaction<T>(
     tenantId: string,
     fn: (tx: typeof db) => Promise<T>,
   ): Promise<T> {
     return db.transaction(async (tx) => {
       await tx.execute(sql`SET LOCAL app.tenant_id = ${tenantId}`);
       return fn(tx);
     });
   }

   Usage throughout the application:

   // In a route handler
   const deals = await withTenantTransaction(req.tenant.id, async (tx) => {
     return tx.select().from(dealsTable);
     // No WHERE clause needed — RLS enforces tenant isolation
   });

   Because SET LOCAL is transaction-scoped, this is safe under pgbouncer transaction pooling: the variable is reset when the transaction commits, so the next client that gets the connection starts with a clean state.

   For frameworks that use middleware (Express, Fastify, Hono), you can also attach the transaction wrapper to the request context so it is available anywhere in the request lifecycle without explicit threading:

   // Express middleware
   app.use(async (req, res, next) => {
     if (!req.tenant?.id) return next(); // unauthenticated routes
     req.db = (fn: Parameters<typeof withTenantTransaction>[1]) =>
       withTenantTransaction(req.tenant.id, fn);
     next();
   });
    
   // Route handler
   app.get('/deals', async (req, res) => {
     const deals = await req.db((tx) => tx.select().from(dealsTable));
     res.json(deals);
   });

5. Handle pgbouncer transaction pooling explicitly

   If you are running pgbouncer in transaction mode (the recommended mode for high-throughput applications), you must verify that your SET LOCAL approach is used consistently. Session-scoped SET will not work correctly.

   Verify your pgbouncer configuration:

   [databases]
   myapp = host=postgres port=5432 dbname=myapp
    
   [pgbouncer]
   pool_mode = transaction
   max_client_conn = 1000
   default_pool_size = 25

   In transaction mode, each BEGIN ... COMMIT block gets a server connection. SET LOCAL within that block is safe. Any SET (without LOCAL) outside a transaction is session-scoped and will persist after the transaction ends, which is incorrect behaviour under transaction pooling.

   One operational gotcha: pgbouncer 1.20+ supports server-side reset queries (server_reset_query) that are issued when a connection is returned to the pool. The default server_reset_query = DISCARD ALL clears all session variables, providing a safety net. Do not rely on this as your primary mechanism — always use SET LOCAL — but verify it is configured correctly as a defence-in-depth measure.

   [pgbouncer]
   server_reset_query = DISCARD ALL

   The Postgres DISCARD ALL command resets: session variables, prepared statements, advisory locks, temporary tables, and the sequence cache. It is the correct reset for a pooled connection being returned to the pool.

6. Write RLS-aware tests

   Testing RLS requires deliberately testing the policy boundary. Most test suites only test with data that belongs to a single tenant, which means they test that the application logic works but not that the RLS policy blocks what it should block.

   The canonical RLS test pattern has two assertions:

   1. BYPASSRLS assertion: With the migration/admin role (BYPASSRLS), verify that the row exists in the database.
   2. Policy assertion: With the application role and a different tenant's context, verify that the row is not visible.

   describe('RLS: deals table', () => {
     let adminDb: DrizzleDb;  // BYPASSRLS role
     let appDb: DrizzleDb;    // app_user role
    
     beforeAll(async () => {
       adminDb = drizzle(adminPool);  // migrator role
       appDb = drizzle(appPool);      // app_user role
     });
    
     it('prevents tenant A from reading tenant B rows', async () => {
       const tenantA = 'aaaaaaaa-0000-0000-0000-000000000001';
       const tenantB = 'bbbbbbbb-0000-0000-0000-000000000002';
    
       // Seed: insert a row for tenant B using BYPASSRLS role
       await adminDb.insert(dealsTable).values({
         tenantId: tenantB,
         title: 'Tenant B Deal',
         stage: 'prospect',
       });
    
       // Verify the row exists (BYPASSRLS)
       const adminResult = await adminDb
         .select()
         .from(dealsTable)
         .where(eq(dealsTable.title, 'Tenant B Deal'));
       expect(adminResult).toHaveLength(1);
    
       // Attempt to read as tenant A
       const appResult = await withTenantTransaction(tenantA, (tx) =>
         tx.select().from(dealsTable).where(eq(dealsTable.title, 'Tenant B Deal'))
       );
    
       // RLS should block: zero rows returned, no error
       expect(appResult).toHaveLength(0);
     });
    
     it('prevents tenant A from inserting a row with tenant B id', async () => {
       const tenantA = 'aaaaaaaa-0000-0000-0000-000000000001';
       const tenantB = 'bbbbbbbb-0000-0000-0000-000000000002';
    
       await expect(
         withTenantTransaction(tenantA, (tx) =>
           tx.insert(dealsTable).values({
             tenantId: tenantB,  // Wrong tenant_id — should be blocked by WITH CHECK
             title: 'Spoofed Deal',
             stage: 'prospect',
           })
         )
       ).rejects.toThrow(); // Postgres raises an error on WITH CHECK violation
     });
   });

   Run these tests in your CI pipeline against a real Postgres instance (not a mock or SQLite). RLS is a database feature; mocking the database means you are testing that your mock behaves correctly, which tells you nothing about whether your policy is correct.

7. Audit logging the tenant context

   When an application query runs, you know from the RLS policy which tenant it is scoped to. Make this explicit in your audit log.

   Postgres provides the pg_audit extension (or the built-in log_statement parameter) for logging SQL statements. To associate a tenant ID with each logged statement, include it as an application name or in a custom audit event:

   -- Set both tenant context and an application-level label
   BEGIN;
   SET LOCAL app.tenant_id = 'acme-corp-uuid';
   SET LOCAL application_name = 'api:acme-corp:deals:read';
   -- queries here
   COMMIT;

   The application_name shows up in pg_stat_activity and in the Postgres log lines (if log_line_prefix includes %a). This makes it possible to correlate a slow query report with a specific tenant and operation type.

   For structured audit logging at the application layer, log the tenant ID, user ID, operation, and timestamp for every mutation. The RLS policy guarantees that the database operation was scoped correctly; the application log records the intent.

Anti-patterns

Granting BYPASSRLS to the application role. This is the single most common RLS implementation mistake. If the role your application server uses has BYPASSRLS, all your RLS policies are effectively no-ops. The app still works, the policies still exist, but they enforce nothing. Engineering teams discover this during a security review or, worse, during a breach investigation. The application role must never have BYPASSRLS.

You can verify which roles in your database have BYPASSRLS at any time:

SELECT rolname, rolbypassrls
FROM pg_roles
WHERE rolbypassrls = true
ORDER BY rolname;

Run this query periodically as part of your security posture review. The only roles that should appear are your migration tooling role and your explicit admin/support role. If your application service role appears, you have a misconfiguration.

Using SET instead of SET LOCAL (session-scoped variable, unsafe under transaction pooling). Under pgbouncer transaction mode, a session-scoped SET app.tenant_id = 'acme-corp' persists on the connection after the transaction commits. The next client to acquire that connection gets the variable pre-set to the previous client's tenant. If the next client's query runs before it issues its own SET LOCAL, it reads another tenant's data.

Assuming the ORM injects WHERE clauses (treating RLS as the only isolation layer). The opposite mistake from granting BYPASSRLS is removing all application-level tenant filtering and relying entirely on RLS. RLS is a defence-in-depth layer, not a replacement for application-level scoping. Application-level filtering provides fast feedback (the query does not even go to the database with the wrong tenant context), catches bugs earlier, and makes code intent explicit. Keep both layers.

Forgetting RLS on materialized views. A materialized view is a physical table in Postgres. RLS policies are defined per table. If you create a materialized view from a tenant-scoped table, the materialized view is a new table without RLS enabled. Queries against the view bypass the source table's policies entirely.

Either enable RLS on the materialized view with its own policy, or restrict access to the materialized view to roles that already have BYPASSRLS (e.g., the admin role).

ALTER MATERIALIZED VIEW deals_summary ENABLE ROW LEVEL SECURITY;
ALTER MATERIALIZED VIEW deals_summary FORCE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON deals_summary
  FOR SELECT TO app_user
  USING (tenant_id = current_setting('app.tenant_id', false)::uuid);

Sub-query escape via SECURITY DEFINER functions. A SECURITY DEFINER function runs with the privileges of the function owner, not the calling role. If the function owner has BYPASSRLS, calls to that function from app_user can bypass RLS policies on any table the function touches.

-- Dangerous: if function_owner has BYPASSRLS, this bypasses tenant isolation
CREATE OR REPLACE FUNCTION get_all_deals()
RETURNS SETOF deals
SECURITY DEFINER  -- runs as function_owner
LANGUAGE sql AS $$
  SELECT * FROM deals;  -- no tenant filter, bypasses RLS
$$;

Avoid SECURITY DEFINER on functions that access tenant data. If you need to use SECURITY DEFINER for legitimate purposes (e.g., privilege escalation for a specific, constrained operation), ensure the function explicitly sets the tenant context from the calling user's session and applies its own WHERE clause.

A safer alternative when you need privilege escalation is SECURITY INVOKER (the default) with an explicit parameter for the tenant context:

-- Safer: SECURITY INVOKER (default), caller's context applies, RLS is enforced
CREATE OR REPLACE FUNCTION get_tenant_deals(p_stage TEXT)
RETURNS SETOF deals
LANGUAGE sql AS $$
  SELECT * FROM deals WHERE stage = p_stage;
  -- RLS policy on deals table applies to the calling role's context
$$;

Because the function runs with the caller's privileges (SECURITY INVOKER), the RLS policy on deals applies normally. The function gets the rows filtered by the calling role's app.tenant_id context. This is the correct pattern for utility functions that wrap common queries.

Enabling RLS but forgetting FORCE ROW LEVEL SECURITY on tables where the app role is the table owner. This is particularly subtle in development environments where a single superuser role creates both the tables and runs the application. ENABLE ROW LEVEL SECURITY activates policies for non-owner roles; FORCE ROW LEVEL SECURITY extends this to the table owner. If your application role created the tables (common in development and small deployments), the policies are silently ignored for that role without FORCE.

The symptom: your application appears to enforce RLS correctly in staging (where the application role is distinct from the table owner) but does not enforce it in development (where they are the same role). This discrepancy between environments is a dangerous situation — developers test code in an environment where RLS is ineffective.

Always use FORCE ROW LEVEL SECURITY. It has no performance cost, and it eliminates the owner-exception footgun.

Missing WITH CHECK on INSERT policies. A policy with only a USING clause that is implicitly applied to INSERT does not validate the new row's tenant_id — it checks whether the row would be visible after insertion, not whether the tenant_id in the INSERT statement matches the session's tenant. Always specify explicit WITH CHECK clauses for policies that cover INSERT and UPDATE.

Case studies / numbers

The following examples are composite descriptions based on multi-tenant architectures. Company names are invented. Performance figures are representative observations from specific configurations and should not be treated as benchmarks.

Migration from application-level filtering to RLS

Situation: A B2B analytics platform with approximately 340 tenants, sharing a single Postgres 14 database on AWS RDS db.r6g.4xlarge (16 vCPU, 128 GB RAM). Tables ranged from 2M to 180M rows. The engineering team had been relying on application-level WHERE tenant_id = ? clauses enforced by a Prisma middleware function. A security audit found three endpoints that bypassed the middleware (two reporting endpoints and one webhook handler).

Migration approach: The team ran a three-phase migration over six weeks.

Phase 1 (two weeks): Add composite indexes on (tenant_id, ...) for the 12 most-queried tables. Measure query performance before and after. No RLS enabled yet.

Phase 2 (two weeks): Enable RLS and FORCE on tables in a staging environment mirroring production data. Run the full load test suite. Measure policy evaluation overhead.

Phase 3 (two weeks): Enable RLS in production during a low-traffic maintenance window. Monitor pg_stat_statements for queries with elevated planning or execution time.

Measured overhead: For a representative query on the deals table (~4M rows, indexed on (tenant_id, created_at)), the baseline execution time was 8.3 ms (P50 over 1,000 executions). After enabling RLS, the same query measured at 8.7 ms P50 — an overhead of approximately 0.4 ms, within measurement noise for a query of this duration. The query plan showed the RLS predicate being pushed into the index scan, not evaluated as a post-filter.

The larger cost was not RLS itself but the planning cost on queries with subqueries. On a reporting query that used three correlated subqueries against tenant-scoped tables, plan time increased from 1.2 ms to 3.8 ms because the planner re-evaluated the RLS policy predicate independently for each subquery context. Rewriting those three correlated subqueries as a single JOIN eliminated the duplicate planning cost and brought plan time to 1.5 ms.

Hot-tenant isolation with dedicated read pool

Situation: A document management SaaS with 1,200 tenants. One tenant, a financial services firm, had 22 million documents — roughly 40% of the total table size. The firm ran weekly batch exports that caused autovacuum starvation affecting all other tenants.

Solution: A dedicated read replica configured with a separate pgbouncer pool scoped to the large tenant. The application's tenant resolution middleware checked a configuration map: if the tenant ID matched the large tenant, route database reads to the dedicated pool. Writes still went to the primary. RLS was configured identically on both primary and replica.

This is not a pure-RLS solution — it required application-level routing — but RLS remained the enforcement layer on both pools. The routing logic determined which pool handled the query; RLS determined which rows the query could see.

Result: Autovacuum on the main pool normalized within 24 hours of the routing change. P95 latency for other tenants on write-heavy tables (which were the ones the large tenant was touching on the primary) dropped from 180 ms to 22 ms.

Debugging a missing policy on a new table

Situation: A SaaS team added a new comments table during a sprint. The migration script created the table, added the application role permissions, but did not enable RLS. The missing RLS was caught in code review but only after the feature shipped to staging.

The symptom: a test written with the RLS pattern (BYPASSRLS seed, app_user read attempt from different tenant) passed on all previously-existing tables but was never written for the new comments table because the developer assumed the RLS was inherited.

Root cause: RLS is not inherited. It must be explicitly enabled on each new table. The team added a migration linter rule that flagged any CREATE TABLE that lacked a following ALTER TABLE ... ENABLE ROW LEVEL SECURITY within the same migration file.

Migration linter check (bash):

# Check for tables created without RLS enabled in the same migration
grep -l "CREATE TABLE" migrations/*.sql | while read -r f; do
  tables=$(grep -oP 'CREATE TABLE \K\w+' "$f")
  for table in $tables; do
    if ! grep -q "ALTER TABLE $table ENABLE ROW LEVEL SECURITY" "$f"; then
      echo "WARNING: $f creates table '$table' without enabling RLS"
    fi
  done
done

This is a heuristic, not a complete solution — it will flag false positives on tables that do not store tenant data (e.g., enum tables, configuration tables). But it catches the common case of a developer who forgot.

Cross-region replication and policy consistency

Situation: A SaaS platform with primary database in us-east-1 and a read replica in eu-west-1 for European tenants. The replica used logical replication from the primary. The team had defined RLS policies on the primary; they needed to verify those policies existed on the replica.

Finding: Logical replication replicates data changes (INSERT, UPDATE, DELETE row changes), not DDL (policy definitions). RLS policies defined on the primary were not replicated to the replica. The replica's tables had RLS disabled. European tenant reads were served from an unprotected table.

Fix: Add RLS policy creation to the replica setup runbook. The policies were identical to the primary (same policy SQL), applied during initial replica setup. Going forward, any migration that modifies RLS policies explicitly includes a note to apply the same policy change to all replicas, with a checklist item in the migration review process.

Incremental rollout on a live system

A recurring question from teams adopting RLS on an existing system: can you enable RLS incrementally without a maintenance window? The answer is yes, with careful sequencing.

The recommended approach is to enable RLS in a permissive shadow state before switching application behaviour:

Phase 1: Enable with a passthrough policy. Create a policy that permits all rows — USING (true) — for the application role. This enables RLS enforcement infrastructure without changing query results. Confirm query performance is unchanged.

-- Passthrough policy: RLS is active but allows all rows
ALTER TABLE deals ENABLE ROW LEVEL SECURITY;
ALTER TABLE deals FORCE ROW LEVEL SECURITY;
 
CREATE POLICY rls_passthrough ON deals
  AS PERMISSIVE FOR ALL TO app_user
  USING (true)
  WITH CHECK (true);

At this stage, RLS is enabled but not restricting anything. The planner is now involved in policy evaluation; you can measure any overhead.

Phase 2: Add the tenant isolation policy alongside the passthrough. With two PERMISSIVE policies (OR logic), rows accessible by either are visible. The tenant policy starts enforcing, but the passthrough catches anything the tenant policy would reject. This is a pure monitoring phase — queries that would fail under strict RLS still succeed, but you can log or alert on mismatches.

CREATE POLICY tenant_isolation ON deals
  AS PERMISSIVE FOR ALL TO app_user
  USING (tenant_id = current_setting('app.tenant_id', false)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', false)::uuid);

Phase 3: Remove the passthrough policy. Once you have confirmed via application logs and monitoring that all queries are correctly setting app.tenant_id, drop the passthrough:

DROP POLICY rls_passthrough ON deals;

At this point, only the tenant isolation policy exists. Any query that arrives without app.tenant_id set will receive an error or an empty result set (depending on your current_setting configuration).

This three-phase approach allows you to enable RLS on a production system without downtime and without a flag day where all application code must simultaneously be correct. The transition period can last days or weeks depending on your confidence in the application-side changes.

Limits + caveats

RLS is a powerful enforcement layer, but it has clearly defined limits. Understanding what it does not solve is as important as understanding how to implement it.

Connection-time tenant determination is still application logic. RLS cannot determine who the current user is. It reads a session variable (current_setting('app.tenant_id')) that your application set. If your application authenticates a user but sets the wrong tenant ID — due to a bug in the JWT parsing, a session fixation vulnerability, or a caching error in your tenant resolution logic — RLS enforces the wrong tenant's isolation. It enforces whatever you tell it to enforce. The correctness of what you tell it is still application responsibility.

Key compromise. If an attacker compromises the API server process, they can set app.tenant_id to any tenant and read any data. RLS does not prevent compromise of the application tier. It prevents accidental cross-tenant reads by correct application code. It is a defence-in-depth layer against developer mistakes, not against privilege escalation or process compromise.

Side-channels via error messages. Depending on how you configure current_setting('app.tenant_id', false), a missing tenant context raises a Postgres error. That error message — unrecognized configuration parameter "app.tenant_id" or ERROR: missing_tenant_context — may surface in API responses if your error handling is insufficiently careful. Ensure database errors are sanitized before returning to clients.

Plan-cache leaks between roles. Postgres's prepared statement cache can, in specific configurations, cache query plans that include policy predicates in ways that are not immediately obvious. If you use prepared statements and switch between roles (e.g., the same connection is used for both application queries and admin queries), verify that plan caching behaves correctly. In practice, this is primarily a concern for connection pools that do not enforce role consistency per pool, or for applications that use SET ROLE to switch roles within a session.

Performance on very wide IN clauses. Some RLS policies check against a list of allowed tenant IDs (e.g., for consultant users who span multiple tenants). A policy like USING (tenant_id = ANY(string_to_array(current_setting('app.allowed_tenants'), ',')::uuid[])) performs an array scan per row. For large arrays or high-frequency queries, this adds measurable overhead. Consider a dedicated table for multi-tenant access grants and a policy that JOINs against it, with appropriate indexing.

Debugging blindness in EXPLAIN. When you run EXPLAIN on a query as a role subject to RLS, the output may not show the policy predicate explicitly — it may appear as a filter condition with an opaque label, or the planner may optimize it in ways that obscure what the policy is doing. For debugging RLS policy behaviour, temporarily run EXPLAIN as a BYPASSRLS role and compare the plan, or use SET row_security = off in a development session to observe the unfiltered plan.

RLS does not protect against the table owner. By default, RLS does not apply to the table owner. If your application role is also the table owner (a common setup in small deployments where app_user created the tables), ENABLE ROW LEVEL SECURITY without FORCE ROW LEVEL SECURITY means the owner bypasses all policies. Always use FORCE ROW LEVEL SECURITY on tables where the owner and application role overlap. Better practice: separate the table owner (used only by migrations) from the application role.

RLS does not help with data exfiltration via timing attacks or side channels. An attacker who controls query parameters can infer information about rows they cannot see through timing differences (a query that hits a large hot partition takes longer than one that does not, even if no rows are returned). RLS returns no rows from other tenants, but it cannot prevent inference attacks. This is a fundamental limitation of any filtering-based isolation mechanism and is not unique to RLS. Mitigating inference attacks requires query normalization, rate limiting, and audit logging — all outside the scope of RLS.

The policy evaluation is always in the context of the querying role, not the data owner. RLS predicates are evaluated using the privileges and session state of the role issuing the query. This matters for functions: if a function is called by app_user and it internally queries a table with an RLS policy, the policy is evaluated in app_user's context. If the function is SECURITY DEFINER and runs as the table owner, the policy is evaluated (or bypassed) in the owner's context. This context-switching behaviour is one of the most common sources of confusion in RLS debugging.

To summarize the limits in a single mental model: RLS is a correct, enforced, database-level WHERE clause that is automatically applied to every query against protected tables for roles that do not have BYPASSRLS. It is not a cryptographic barrier, not a network-level isolation mechanism, and not a substitute for correct application authentication logic. It is precisely what a WHERE clause is: a filter. The value of that filter is that it cannot be accidentally omitted by application code.

Sources & further reading

The following sources were used in preparing this post. All links were verified at time of writing. The Postgres documentation links are versioned to Postgres 15; replace 15 with your Postgres major version as needed.

Postgres documentation — primary sources

1. PostgreSQL 15 Documentation: Row Security Policies. The authoritative reference for RLS syntax, policy types (PERMISSIVE vs RESTRICTIVE), USING vs WITH CHECK semantics, BYPASSRLS, and FORCE ROW LEVEL SECURITY. https://www.postgresql.org/docs/15/ddl-rowsecurity.html

2. PostgreSQL 15 Documentation: CREATE POLICY. Full syntax reference for CREATE POLICY, including command-specific policies (FOR SELECT, FOR INSERT, etc.) and the role-scoping TO clause. https://www.postgresql.org/docs/15/sql-createpolicy.html

3. PostgreSQL 15 Documentation: SET. Covers SET LOCAL vs session-scoped SET, transaction scope, and interaction with subtransactions. https://www.postgresql.org/docs/15/sql-set.html

4. PostgreSQL 15 Documentation: current_setting(). Function signature, second argument (missing_ok), and behaviour when the variable is not defined. https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-ADMIN-SET

5. PostgreSQL 9.5 Release Notes. The original release that introduced row-level security. Useful for understanding the design intent and original limitations. https://www.postgresql.org/docs/9.5/release-9-5.html

Connection pooling

6. pgBouncer Documentation: Pool Modes. Covers session, transaction, and statement pooling modes, and explicitly documents the implications of session variables under transaction pooling. https://www.pgbouncer.org/config.html#pool_mode

7. pgBouncer Documentation: server_reset_query. Documents DISCARD ALL as the recommended reset query for transaction pooling. https://www.pgbouncer.org/config.html#server_reset_query

Cloud provider documentation

8. AWS Documentation: Using PostgreSQL Row-Level Security with Amazon RDS. Covers RLS on Aurora PostgreSQL and RDS PostgreSQL, including performance considerations and the interaction with IAM authentication. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Security.html

Practitioner guides

9. Crunchy Data Blog: Row Level Security in PostgreSQL. A thorough practitioner walkthrough by Joe Conway, covering policy evaluation order, RESTRICTIVE vs PERMISSIVE policies, and multi-role setups. https://www.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies

10. Supabase Documentation: Row Level Security. Covers the Supabase implementation of RLS (which is standard Postgres RLS), with examples for common patterns including the auth.uid() equivalent. Useful for understanding how a production platform handles RLS at scale. https://supabase.com/docs/guides/database/postgres/row-level-security

11. Prisma Documentation: Row Level Security with Prisma Client. Covers the Prisma-specific integration patterns for injecting tenant context per transaction, including the $transaction API. https://www.prisma.io/docs/orm/prisma-client/queries/row-level-security

12. Drizzle ORM Documentation: Transactions. Documents the transaction callback API used in the middleware pattern described in this post. https://orm.drizzle.team/docs/transactions

The Crunchy Data blog and Supabase documentation are particularly worth reading alongside the official Postgres docs: they cover implementation patterns that the formal documentation assumes are obvious but frequently trip up practitioners.

For a deeper understanding of the security model, the original Postgres 9.5 RLS commit message and associated discussion on the pgsql-hackers mailing list (archived at https://www.postgresql.org/message-id/flat/20130707160250.GD6429%40chet.sneakemail.com) explain the design tradeoffs that shaped the current behaviour.