Your Tenant Context Outlives the Request

On a project I am working on, one PostgreSQL database stores data for more than one tenant. Here, a tenant is simply one organisation using the application. Every tenant has its own records, and a person working inside Tenant A must never be able to read or change a row belonging to Tenant B.

The obvious way to express that rule is to add a tenant condition to every query:

SELECT id, title, status
FROM documents
WHERE tenant_id = :tenant_id
  AND status = 'ACTIVE';

That condition is useful. It makes the scope of the query clear, and I still prefer to keep it when the tenant is part of the query's identity.

It is also a fragile place to put the entire security boundary. One forgotten condition can turn an otherwise valid query into a cross-tenant read or update. The code will compile. PostgreSQL will execute it. Nothing in the statement itself says that returning every tenant's rows is impossible.

I wanted the database to know the same rule as the application: this transaction belongs to one tenant, and every protected table must enforce that fact.

The pattern I settled on uses a transaction-local PostgreSQL setting to carry trusted tenant context, then Row-Level Security to apply it to every query. The interesting part is not that it can save a few repeated WHERE clauses. The interesting part is what happens when a query is wrong, a transaction rolls back, or a pooled connection is handed to the next request.

The request ends. The connection does not.

Most applications do not open a fresh PostgreSQL connection for every request. They borrow one from a pool, use it, and return it. The same physical connection may serve hundreds of requests over its lifetime.

That difference in lifetime is where the danger begins.

Suppose a request for Tenant A borrows connection 12 and stores the tenant in a session-level setting:

SELECT set_config('app.tenant_id', :tenant_id, false);

The final argument is false, so the value remains for the rest of that database session. The HTTP request can finish while the setting remains attached to the connection.

request for Tenant A             pooled connection 12
        |                                  |
        |  set tenant = A                  |
        |--------------------------------->|
        |                                  |  tenant = A
        X request ends                     |
                                           |  still open

request for Tenant B                       |
        |                                  |
        |  borrows the same connection ----|
        |                                  |  tenant = A  <- stale

A well-written request for Tenant B will overwrite the setting before it runs a query. Security should not depend on every path being well-written forever. A missing initialisation step, an early return or an unexpected code path can leave the second request operating with Tenant A's context.

The problem is even clearer with transaction pooling. PgBouncer describes transaction pooling as assigning a server connection only for the duration of a transaction, then returning it to the pool as soon as that transaction ends. It lists session-level SET and RESET behaviour as incompatible with that mode because the next transaction may use a different database session. See the PgBouncer pooling-mode documentation.

The lesson is simple: request-scoped security state cannot safely live as session-scoped connection state.

Tenant context belongs to the transaction

PostgreSQL gives set_config an is_local argument. When it is true, the setting applies only during the current transaction. PostgreSQL restores the previous value when that transaction commits or rolls back. The behaviour is documented alongside current_setting and set_config.

The safer sequence is:

BEGIN;

SELECT set_config(
    'app.tenant_id',
    :tenant_id,
    true
);

-- Every tenant-scoped query runs here.

COMMIT;

That true is doing the important work. It gives the tenant context the same lifetime as the unit of work it protects.

borrow connection
       |
       v
     BEGIN
       |
       v
install tenant context
       |
       v
run protected queries
       |
       v
 COMMIT or ROLLBACK
       |
       v
tenant context reverts
       |
       v
return connection

This also gives the application a clear rule: no tenant-scoped query runs outside the transaction that installed its context. Setting the value in one transaction and expecting it to survive into another defeats the point. So does setting it on one connection and running the query on another.

SET LOCAL app.tenant_id = '...' has the same transaction-lifetime idea. I prefer set_config in application code because it accepts a value through a normal parameterised query. There is no need to build SQL by interpolating a tenant identifier into the statement.

The tenant must already be trusted

Row-Level Security can enforce the tenant value it receives. It cannot decide whether the caller was entitled to choose that tenant in the first place.

If a request contains this header:

X-Tenant-ID: 7a23...

the application should not copy it directly into PostgreSQL and call the result secure. It first has to authenticate the actor, resolve the tenant from a trusted source, and verify that the actor can operate inside it. Only the result of that decision becomes database context.

This is the same distinction I explored in Your Agent Still Has the Tool You Revoked: a value that describes an identity is not, by itself, proof of current authority. The check has to happen at the boundary where that identity gains an effect.

For this database pattern, I treat the tenant setting as trusted infrastructure state. Controllers and repositories do not set it independently. One transaction wrapper establishes it before any tenant-scoped work begins.

The examples below are deliberately simplified and reconstructed. They show the pattern without reproducing code from the project.

async def run_for_tenant(tenant_id, operation):
    async with database.transaction() as transaction:
        installed = await transaction.scalar(
            """
            SELECT set_config(
                'app.tenant_id',
                :tenant_id,
                true
            )
            """,
            {"tenant_id": str(tenant_id)},
        )

        if installed != str(tenant_id):
            raise TenantContextError("Tenant context was not installed")

        return await operation(transaction)

The read-back may look overly cautious because set_config returns the value it was given. I like the explicit assertion because a missing context otherwise looks very similar to a tenant with no data. Failing closed protects the boundary. Failing loudly tells the operator why the request cannot proceed.

Let PostgreSQL enforce the row boundary

For a concrete example, imagine a generic documents table:

CREATE TABLE documents (
    id          uuid        PRIMARY KEY,
    tenant_id   uuid        NOT NULL,
    title       text        NOT NULL,
    status      text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX documents_tenant_status_idx
    ON documents (tenant_id, status);

The table carries tenant_id because each document belongs to exactly one tenant. The index supports the per-tenant status query shown at the start of the article. Row-Level Security does not remove the need to design indexes around the queries the application actually runs.

The policy can read the transaction-local setting:

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

CREATE POLICY documents_tenant_isolation
ON documents
FOR ALL
TO app_runtime
USING (
    tenant_id = NULLIF(
        current_setting('app.tenant_id', true),
        ''
    )::uuid
)
WITH CHECK (
    tenant_id = NULLIF(
        current_setting('app.tenant_id', true),
        ''
    )::uuid
);

There are two separate checks here.

USING controls which existing rows are available to SELECT, UPDATE and DELETE. If the current transaction belongs to Tenant A, rows for Tenant B do not pass the policy.

WITH CHECK controls the rows created by INSERT or produced by UPDATE. A transaction for Tenant A cannot write a row whose tenant_id says Tenant B.

The second argument to current_setting is true, which means “return NULL when this setting does not exist instead of raising an error”. NULLIF(..., '') also handles an empty value. In either case, the comparison does not evaluate to true, so the row is rejected by the policy.

For a read, missing tenant context produces no protected rows. For a write, the policy rejects the new row. PostgreSQL calls this default-deny behaviour out in its Row-Level Security documentation.

I would normally hide the repeated current_setting expression behind a small, schema-qualified database function once several tables need it. I have left it inline here so the entire security decision is visible.

Policy composition also deserves attention. PostgreSQL policies are permissive by default, and multiple permissive policies are combined with OR. Adding a second broad policy can therefore widen access instead of narrowing it. If a table needs several policies, decide deliberately which ones are permissive or restrictive, then test the combined result under the real runtime role.

ENABLE is not enough

There is an easy way to build the policy correctly and still never exercise it: connect as a role that bypasses RLS.

PostgreSQL superusers and roles with BYPASSRLS always bypass row security. Table owners normally bypass it too. FORCE ROW LEVEL SECURITY makes the owner subject to the policy, but it does not constrain a superuser or a role that has explicitly been granted BYPASSRLS.

That means the runtime role is part of the design, not a deployment detail.

I prefer three boundaries:

• a migration role owns tables and changes the schema;
• an application role has only the table and sequence privileges it needs;
• the application role is not a superuser, does not have BYPASSRLS, and does not own the protected tables.

FORCE ROW LEVEL SECURITY then adds protection if ownership changes or a deployment accidentally runs under the owner. PostgreSQL documents the owner and role exceptions explicitly in its RLS policy rules and role attributes.

Ordinary grants still matter. RLS restricts rows after PostgreSQL has decided that a role may access the table at all. It does not grant SELECT, INSERT or UPDATE by itself. Least-privilege grants and row policies solve different parts of the problem.

I still keep explicit tenant predicates

Once the policy works, it is tempting to remove every tenant_id condition from application queries:

SELECT id, title
FROM documents
WHERE status = 'ACTIVE';

Under the correct tenant context and runtime role, RLS will still limit the result. There are places where an ID-only repository method can reasonably rely on that property.

I would not turn that into a blanket rule.

When the tenant is part of the business identity of the query, I keep it visible:

SELECT id, title
FROM documents
WHERE tenant_id = :tenant_id
  AND status = 'ACTIVE';

The explicit predicate serves the application. It states intent, keeps the expected tenant-first access path visible, and makes code review easier. The RLS policy serves the security boundary. It catches the query that omitted or mishandled the predicate.

Those are two coordinated controls, not duplicate work. If a repository deliberately performs an ID-only lookup, that should be a visible design choice inside the trusted transaction wrapper. It should not happen because the team decided that tenant filters are now somebody else's problem.

The outcome I wanted was not “never type WHERE tenant_id again”. It was “one forgotten WHERE tenant_id cannot expose another tenant”.

Test the connection that comes back

A normal test that inserts two tenants and checks one query is not enough. The risk lives in the order of transactions and the reuse of connections.

I would test at least these cases against a real PostgreSQL instance and the actual runtime role:

1. Tenant A can read its own rows.
2. Tenant A cannot read, update or delete Tenant B's rows, even by a known row ID.
3. Tenant A cannot insert or update a row with Tenant B's tenant_id.
4. A transaction with no tenant context sees no protected rows and cannot write one.
5. The context disappears after both COMMIT and ROLLBACK.
6. A later transaction for Tenant B sees only Tenant B after the same connection served Tenant A.
7. The runtime role is neither the table owner nor a role that can bypass RLS.

The pool-reuse test is the one I care about most. A pool with one connection makes the reuse deterministic:

pool = Pool(size=1)

await run_for_tenant(TENANT_A, assert_only_tenant_a_rows)

# The next operation must reuse the same physical connection.
await run_without_tenant(assert_no_protected_rows)

await run_for_tenant(TENANT_B, assert_only_tenant_b_rows)

Run the same sequence with the first transaction raising an exception. A rollback should clear the local setting just as reliably as a commit.

I would also inspect the effective role inside the test instead of assuming the test configuration matches production. RLS tests that accidentally run as the table owner can prove the opposite of what their name suggests.

PgBouncer's own configuration guide says its normal session reset query is not used in transaction-pooling mode because applications in that mode must not depend on session features. That is another reason not to outsource this security property to a pool cleanup hook. The transaction should clean up its own context by construction. See server_reset_query in the PgBouncer documentation.

What this pattern does not solve

Transaction-local context and RLS close one specific failure mode: a tenant-scoped SQL statement cannot operate on another tenant's protected rows merely because its application query forgot the filter.

They do not make the whole system tenant-safe by themselves.

They do not authorise the tenant selection. The application must still prove that the actor belongs to the selected tenant before installing the context.

They do not protect systems outside PostgreSQL. Cache keys, object-storage paths, search indexes, background jobs and analytics exports all need tenant context of their own.

They do not replace parameterised SQL. A party that gains arbitrary SQL execution under the application role may be able to change a custom setting. RLS is a defence against wrongly scoped queries, not permission to tolerate SQL injection or a compromised runtime.

They do not enforce cross-tenant relationships automatically. PostgreSQL notes that referential-integrity checks bypass RLS. If a child row must never reference a parent from another tenant, encode that relationship with tenant-aware keys, such as FOREIGN KEY (tenant_id, parent_id) REFERENCES parents (tenant_id, id). That deserves its own article; the important point here is that row visibility and relational correctness are different guarantees. See the PostgreSQL documentation on RLS and referential integrity and multi-column foreign keys.

They do not define an administrator path. Cross-tenant support, maintenance and migration operations need a separate, explicit role and audit story. “No tenant context” should mean “no tenant rows”, not “all tenant rows”.

The boundary is strong because it is narrow and testable. Pretending it solves more than it does would make it weaker.

The useful part is the lifetime

The complete flow is not complicated:

authenticate the actor
        |
resolve and authorise one tenant
        |
borrow a database connection
        |
begin a transaction
        |
install and verify local tenant context
        |
run tenant-scoped queries under RLS
        |
commit or roll back
        |
return a context-free connection to the pool

What matters is that every state has the right lifetime.

The request decides which tenant it is allowed to represent. The transaction carries that decision. RLS enforces it against each row. The setting disappears when the transaction ends. The connection can then live on without carrying one tenant into the next request.

That is why I would not describe this as a clever way to avoid writing WHERE tenant_id = .... It is a way to make tenant isolation survive the query somebody forgot, while ensuring the tenant context itself does not survive long enough to contaminate the query that comes next.