Adding tenant_id to a query is not a complete isolation design, and enabling row security does not constrain every database identity or entry point. Connect authenticated identity, the actual execution role, policy expressions and connection lifecycle into one verifiable chain. This guide targets PostgreSQL 18 and server-side read-only order queries. It provides designs and expected results, not measurements from a production deployment or a claim that a particular product uses these mechanisms.
Define the threat boundary before choosing how to carry identity
Row-level security, or RLS, lets database policies determine which records may be read or modified. PostgreSQL 18 documents default denial when enabled without an applicable policy, but also bypass paths for superusers, BYPASSRLS roles and normally table owners.[1] Begin acceptance with a role inventory: who logs in, owns tables, runs migrations, can switch roles, and actually serves application queries. A query alone is not a sufficient input to the review.
This design assumes a trusted backend verifies login and obtains the tenant from a controlled authorization mapping. A browser-supplied tenant ID can identify the requested object, but cannot itself confer authority. The backend must confirm the user's current access and reject unknown or expired mappings. Model-generated text, SQL fragments and hidden browser fields are untrusted inputs; none may declare its own authorized tenant.
Distinguish preventing developer omissions from resisting arbitrary SQL execution. A shared role with transaction-local tenant context can protect controlled queries that forget a filter. If an attacker or model can freely execute set_config, SET ROLE or privileged functions, the variable is not an unforgeable credential. Do not claim a policy expression alone creates strong isolation under that threat model. Use a narrower execution interface, separate identities or separate databases, and constrain accessible objects.
The input contract includes the identity-mapping version, tenant ID type, database major version, pooling mode, application role, tables and views, query entry points and cache rules. The output contract includes permitted record identifiers, aggregates and explicit denial states. A missing authorization and a legitimate tenant with no orders are different outcomes. Presenting a permission failure as zero revenue can mislead a business decision.
Establish independent expected results using five order records
All records here are constructed. Amounts are in CNY, excluding tax, returns and currency conversion. The orders fields are tenant_id, order_id and amount. Tenant A has A01 for 100 and A02 for 200; tenant B has B01 for 400 and B02 for 500. X01 for 700 has no tenant. The production-shaped table should reject missing ownership; X01 belongs in a quarantine area for ingestion-failure testing, not in the normal queryable table.
An ordinary A identity must see exactly A01 and A02: two records totaling 300. B must see B01 and B02: two records totaling 900. A request without a valid tenant should be rejected at the backend; the database policy provides a second boundary that releases no rows. X01 belongs to neither tenant and must not default to the first tenant or a global shared scope. Write this independent expected-result sheet before testing instead of generating it with the query under review.
The object key must also represent its scope. If order_id is unique only within a tenant, enforce uniqueness on tenant_id plus order_id and join order details using both. Add a branch in which A and B each have an O01 with different amounts: each identity must see only its own version. This checks that identity travels with the object key and requires no real customer records in the test environment.
One controlled query can request SELECT order_id, amount FROM app.orders ORDER BY order_id without a user-supplied tenant predicate. It must still return only the authorized range. This deliberately tests an omitted filter; it is not advice to remove every explicit predicate. Production queries may retain explicit scope for clarity and planning, but it cannot be the sole security boundary or enlarge what the database permits.
Shared-role design: bind context inside a complete transaction
The first design uses an application role that neither owns the table nor has superuser or BYPASSRLS privileges. A trusted backend supplies tenant context. A policy can compare tenant_id with NULLIF(current_setting('app.tenant_id', true), ''). Both sides are text in this example. Missing or empty context becomes NULL, so equality is not true and no row is admitted. Reject malformed identifiers at the backend rather than using permissive conversion that could map them to another identity.
current_setting can return NULL for an undefined setting when missing_ok is true. The third argument to set_config selects transaction-local behavior.[2] These functions read and write state; they do not authenticate the tenant. The expression above illustrates a mechanism. Validate the actual types, roles and migration scripts before deployment rather than copying it into a live database and declaring isolation complete.
The complete sequence is: acquire a connection; explicitly BEGIN; obtain the tenant from the authenticated mapping; execute parameterized SELECT set_config('app.tenant_id', $1, true); run approved queries on the same backend connection inside the same transaction; collect results; COMMIT. On failure, ROLLBACK before returning the connection under the pool's rules. Do not run the business query without valid context or continue with old state after context binding fails.
SET LOCAL expires when a transaction ends, whereas ordinary SET can remain in the session after commit. Rolling back to an earlier savepoint also cancels local settings established after it.[3] Setting context in autocommit mode and then separately querying uses different transactions. Likewise, do not assume identity survives a rollback to a savepoint preceding the binding. A complete transaction retry must revalidate applicable authority and bind context again.
PgBouncer assigns a backend only for a transaction in transaction pooling, while statement pooling disallows multi-statement transactions. Its compatibility table warns against relying on cross-transaction session SET/RESET state.[4] This design instead requires local binding and reading within one explicit transaction. Test the real driver, proxy and pool combination. Neither a belief that pools usually clean connections nor support for selected startup parameters proves isolation of every custom variable.
The trusted boundary includes the backend allowed to set the variable. Arbitrary SQL that starts with SELECT can still invoke side-effecting functions, so a prefix check is insufficient. Use approved templates or a tightly restricted syntax tree and function allowlist; reject multiple statements, arbitrary function calls and role changes. If that execution boundary cannot be established, use stronger identity isolation rather than adding more string-filter rules.
Separate roles or databases move identity out of shared session state
The second design associates tenants with restricted database login roles and maps those roles to tenant_id through controlled state using current_user. Give each role only necessary read privileges, no ability to switch to other tenant roles and no ownership of policy or business tables. Keep pools separated by actual database identity. Changing a custom parameter then does not change that identity, although credential handling, membership and privileged functions remain acceptance boundaries.
A shared account able to SET ROLE to every tenant is not an independent-identity design if it also accepts arbitrary SQL. Trace authentication from the external user through the trusted authorization map to a corresponding restricted connection. Ordinary tenants must not modify the mapping. Pool keys cannot include only a database address while omitting user identity. Provision roles, grants, pool configuration and removal procedures as one traceable onboarding change.
A shared role suits a trusted backend, narrow execution interfaces and larger tenant populations needing unified administration; its costs lie in transaction wrappers, entry-point review and regression testing. Separate roles add role and connection management but bring database identity into the boundary. Stronger operational, failure or contractual separation can justify separate databases, at the cost of migrations, monitoring and cross-tenant reporting. A large role count alone does not justify the heaviest option.
In the constructed example, the shared path has one execution role but requires reliable wrappers around every order-reading entry point. The role-based path needs at least separate A and B identities and pools, with proof that they cannot switch into one another. Both must produce the same correct totals of 300 and 900. Choose based on attacker control and sustainable administration, not configuration length. There is no universal tenant-count threshold in this guide.
Policy combinations, views and functions can change effective access
Verify that policies are active, not merely defined. Enable RLS on the test table, grant the application role necessary SELECT rights and keep that role from owning the table. FORCE ROW LEVEL SECURITY can subject the owner to policies but does not constrain superusers or BYPASSRLS roles.[1] Separate migration and serving roles. If a missing policy breaks legitimate reads, repair the policy rather than temporarily granting bypass privileges.
PostgreSQL combines permissive policies with OR and restrictive policies with AND; at least one permissive policy must grant access.[5] An apparently temporary permissive USING (true) policy can therefore enlarge tenant scope. Review the effective combination for the role and command, not only a single correct-looking tenant policy. Expand memberships and inherited grants as part of that review.
This read-only example needs no write grants. If writes are added, design USING and WITH CHECK independently: the former governs existing-row visibility or operation scope, while the latter checks the resulting new or changed row.[5] Test an attempt to change A01's tenant to B and a direct insertion of a B record; neither should succeed for A. Passing read tests does not prove write safety. Whole-table operations such as TRUNCATE do not belong in ordinary application privileges.
A view is not automatically a secure wrapper. PostgreSQL 18 normally uses the view owner's privileges and relevant row policies for underlying relations. A security_invoker view can use the caller's instead, requiring the corresponding underlying privileges.[6] Query the table and actual exposed view using the real application role and compare identifier sets. Do not confuse what CURRENT_USER returns inside a view with the rules governing underlying permission checks; inspect definitions and execution behavior.
SECURITY DEFINER functions run with their owner's privileges. Official guidance highlights safe search_path handling and default function execution privileges for PUBLIC.[7] Avoid this capability when normal reads do not need it. When necessary, constrain object names, callers and input, and separately test whether it bypasses the intended tenant boundary. RLS protects a particular database access path; it cannot automatically protect a full result already obtained by another privileged function.
Test connection reuse and recovery with deterministic event sequences
Case one is missing identity. Using the real application role and no transaction context, issue a controlled query. The backend should reject it, and the direct database boundary must release no business rows. Then bind valid A and expect two rows totaling 300. Retain mapping, transaction-boundary and result-ID evidence. If missing context exposes everything, examine fallback branches and policy composition; rerun both absent and valid identities after repair so that denying all requests cannot masquerade as success.
Case two forces backend reuse. Reduce the test pool to one controlled connection. Let A query and commit, then let B use that backend. A must see A01 and A02; B must see B01 and B02; a subsequent request with no identity must see neither tenant. Record the backend identifier to prove reuse occurred. If B sees A, inspect session SET, autocommit, wrappers and pool keys, then repeat the complete A–B–missing sequence.
Case three forces abnormal exit. After A's context is bound, cause a controlled query error, confirm rollback, then let B reuse the connection. B must still receive two rows totaling 900 without A's records. A connection left in a failed transaction must be cleaned or discarded before reuse. Add disconnect and client-cancellation branches and inspect return-to-pool handling. Testing only normal COMMIT misses the branches where identity residue can matter most.
Case four tests context forgery. Let A's request body or generated query attempt to declare B. The trusted mapping or constrained execution interface must reject it. For a shared-variable design, separately demonstrate that a role with arbitrary SQL capability may change the variable and record that as outside the supported threat boundary. Do not remove the counterexample and claim resistance to arbitrary database code. If the requirement includes that threat, change the identity design and retest role switching, function calls and object access.
Case five checks object bypass. Prepare direct-table, actual business-view and permitted-function paths; compare A's and B's identifier sets across each. In the isolated environment, also observe table-owner execution and verify that deployment checks detect an incorrect serving account. If a view or function adds rows, repair the execution identity or restrict the entry point, then rerun every path rather than merely hiding the extra rows in the page.
Case six covers savepoints and retries. Create a savepoint before binding A, bind A, then roll back to that savepoint and attempt to continue. The wrapper must stop or rebind a verified identity rather than retain a false assumption. Complete transaction retries must bind again as well. Check retry counts, authorization versions and results. Do not place the first request's tenant in mutable global state that another concurrent request can overwrite.
After database tests, inspect caches, aggregates and performance
Application caches sit outside the database. If the cache key is only the question 'this month's order total', B may receive A's cached 300 without any RLS evaluation. Bind cache scope to the trusted tenant, relevant authorization scope or version, and business-data version; enforce current authorization on delivery. Run the same question first as A and then as B, identifying whether the result came from the database or the correct cache partition. An absence of unauthorized SQL is not enough.
Background work also needs explicit identity. Subscriptions, exports and asynchronous analysis cannot borrow an interactive thread's temporary variable or default to a migration account. Store a controlled tenant identifier and approved purpose with the task, recheck authorization at execution and use the same isolated entry point. The method for RAG revocation and in-flight requests addresses time boundaries for long-running work. Correct isolation of one query does not solve every authorization question at final delivery.
Aggregates should be computed within the authorized range, but allowed totals can still reveal another range by subtraction. If A may see the group total of 1,200 and knows its own 300, it can infer B's 900. Whether that disclosure is allowed is a separate data policy; returning no B detail rows does not settle it. List permitted group totals, drilldowns and query combinations, enforce the business rule at the output, and do not mislabel a security restriction as missing statistical data.
Measure performance with actual roles, real policy complexity and representative tenant sizes. Examine tenant_id indexes, extra policy lookups and cold versus warm data, but do not mistake an index for a security mechanism. Compare plans, latency and rows scanned while confirming unchanged results. A bypass role cannot supply a valid security-aware benchmark. With skewed data, test small and large tenants separately; an average cannot explain the slowest operational path.
A policy that consults extra authorization tables introduces permissions, query costs and concurrent-change semantics. Do not generalize this example's current-row comparison into a proof for an arbitrary authorization graph. Cross-table mappings, direct partition access, replicas and offline exports need explicit scope. If the organization cannot maintain these exceptions, expose a small verified query set and expand it with new tests instead of granting every table at once.
Make the isolation guarantee a release and regression checklist
For each entry point, record the real login role, object and policy version, pooling mode, identity source, transaction-wrapper location, allowed and prohibited identifier sets, failure outcome and evidence. Cover normal A, normal B, missing identity, backend reuse, rollback, forged identity, view/function bypass, cache hits and background tasks. Allowed cases must work and prohibited cases must release no forbidden rows. Untested paths remain unaccepted rather than passing by default.
Before release, capture comparable snapshots of role attributes, ownership, RLS flags, policies, view options and function privileges. New application paths, database upgrades, pool-mode changes, policy migrations and cache changes should trigger relevant regression tests. Fictional order IDs and configuration versions are sufficient evidence; copying sensitive production bodies into logs is unnecessary. This reusable checklist cannot be replaced by a permission screenshot.
When cross-tenant exposure is suspected, suspend the affected entry point, preserve necessary redacted evidence and establish the affected scope before repairing identity and object-access paths. Roll back only to a verified safe configuration; disabling RLS to restore query availability is not a safe rollback. Before reopening, repeat the exact failure sequence, positive cases for other tenants and all shared paths. Record database acceptance and final-user-output acceptance separately.
These mechanisms and expected values support an isolated test environment, but the real guarantee depends on deployment identities, executable SQL scope, application outputs and ongoing maintenance. A satisfactory tenant-isolation delivery explains why each request receives only its permitted set, how failures preserve the boundary and which paths remain uncovered. It is not a one-time switch that completes the work permanently.