PostgreSQL Row-Level Security and Session Context

PostgreSQL Row-Level Security depends on how you manage session context.

Your Row-Level Security policy can be perfectly written and still expose the wrong tenant's data when the application treats a pooled PostgreSQL connection as if it belongs to one request for its entire lifetime.

Session Context and Connection Pools

An RLS policy can filter rows using 𝘵𝘦𝘯𝘢𝘯𝘵_𝘪𝘥 = 𝘤𝘶𝘳𝘳𝘦𝘯𝘵_𝘴𝘦𝘵𝘵𝘪𝘯𝘨('𝘢𝘱𝘱.𝘵𝘦𝘯𝘢𝘯𝘵_𝘪𝘥', 𝘵𝘳𝘶𝘦), but that expression only enforces tenant boundaries when the value matches the authenticated identity of the request running the query.

Now imagine request A calls 𝘚𝘌𝘛 𝘢𝘱𝘱.𝘵𝘦𝘯𝘢𝘯𝘵_𝘪𝘥 = '𝘵𝘦𝘯𝘢𝘯𝘵_𝘢' and returns its connection to the pool: request B might inherit that session value, because the database connection outlives the HTTP request.

Scope Context to the Transaction

The safer boundary is a transaction: 𝘉𝘌𝘎𝘐𝘕, establish the tenant from a trusted authenticated identity using 𝘚𝘌𝘛 𝘓𝘖𝘊𝘈𝘓 or 𝘴𝘦𝘵_𝘤𝘰𝘯𝘧𝘪𝘨(..., 𝘵𝘳𝘶𝘦), run every protected query on that transaction, then 𝘊𝘖𝘔𝘔𝘐𝘛 or 𝘙𝘖𝘓𝘓𝘉𝘈𝘊𝘒.

𝘚𝘌𝘛 𝘓𝘖𝘊𝘈𝘓 expires at transaction end, which makes the context predictable under connection reuse, but only if every protected statement stays inside that transaction and no unsafe session-level setting remains.

Roles and Verification

RLS also depends on the role executing SQL: superusers and 𝘉𝘠𝘗𝘈𝘚𝘚𝘙𝘓𝘚 roles skip policies, table owners normally do too, and 𝘍𝘖𝘙𝘊𝘌 𝘙𝘖𝘞 𝘓𝘌𝘝𝘌𝘓 𝘚𝘌𝘊𝘜𝘙𝘐𝘛𝘠 changes the owner behavior without restricting bypass roles.

Tests should alternate tenant identities through a small pool, include missing tenant context, inspect 𝘜𝘚𝘐𝘕𝘎 and 𝘞𝘐𝘛𝘏 𝘊𝘏𝘌𝘊𝘒 behavior for writes, and verify that queries cannot run with stale or client-controlled identity.

RLS is a database enforcement mechanism, not a replacement for authentication. Its guarantee is only as strong as the process that binds request identity to connection, transaction, and privileges.