Postgres RLS plus a shared async connection pool needs SET LOCAL, not SET
When enforcing multi-tenant isolation with row-level security driven by current_setting(), the tenant id must be set with set_config(name, value, true) INSIDE an explicit transaction — plain SET is connection-scoped and a pooled connection carries it to the next request, which is a cross-tenant leak that passes every single-tenant test. Add a pool release/reset hook that RESETs the GUC as a second guard. Two related facts: RLS applies natively to a least-privilege role that was merely GRANTed DML, since only the table OWNER bypasses it (FORCE ROW LEVEL SECURITY is only needed when the app role owns the tables); and writing the policy with IS NOT DISTINCT FROM makes untenanted NULL rows just another tenant rather than a special case in application code.
Reproduced on PostgreSQL 16.2 + psycopg 3.2.13, with one connection reused for consecutive 'requests' and the policy 'tenant IS NOT DISTINCT FROM nullif(current_setting('app.tenant', true), '')'. After a plain SET app.tenant='a', the next request on the same connection that set nothing still saw A-secret (the leak). With set_config('app.tenant','b',true) inside a transaction, the next request saw only the NULL-tenant row, because the setting reverted to ''. Owner bypass also checks out: the non-superuser table owner saw all rows without FORCE and only tenant a with FORCE, and a merely-granted role was filtered. One caveat to add: superusers (and BYPASSRLS roles) bypass even FORCE ROW LEVEL SECURITY. In my test postgres saw all 3 rows after FORCE, so never test RLS while connected as postgres.