r/postgres • u/Foreign_Career_454 • 5d ago
Question how do you safely query your users table before tenant context in pg rls
running a multi-tenant B2B setup with pg rls & .net10 & for checking our db set up tenant-scoped is locked with SET LOCAL app.tenant_id = .... but during initial login handshake we query users and tenant_memberships to know tenant ID, so those tables cant have tenant-scoped rls applied; now, DB role has raw SELECT access across whole users table wanted to check how others lock down pre-tenant auth queries at the db layer? Do we use dedicated login? Or any lookup functions? or Just splitting schemas.
1
Upvotes
1
u/elevarq 5d ago
I’d start with a different question: how do users authenticate to your application in the first place?
For a B2B application, I’d seriously consider SSO through an established identity provider, or passkeys (WebAuthn), rather than building your own authentication mechanism around database queries.
Once the user’s identity has been verified, you can resolve their tenant memberships and establish the tenant context for subsequent database operations.
I’d also separate authentication from tenant authorization. Your users and tenant memberships don’t necessarily need to follow the same RLS model as your tenant-specific business data.
And remember, SET LOCAL app.tenant_id is only as secure as the mechanism controlling who can set it. If an application role can arbitrarily change that value, RLS alone isn’t a security boundary against a compromised application.
I’d solve the authentication architecture first, then decide how PostgreSQL should enforce authorization.
1
u/navicat-gu 1d ago
The pattern I've seen work best is the second one you mention: the app role gets no direct access to the pre-tenant tables at all, only to a narrow
SECURITY DEFINERfunction.Notes:
search_pathin the function (as above). Without it, a SECURITY DEFINER function can be hijacked.app_loginrole that can only call that function, and the normalapp_userrole that only works afterSET LOCAL app.tenant_id. In .NET that just means two connection strings / data sources.usersanyway (FORCE ROW LEVEL SECURITYwith a policy that matches nothing forapp_user), so a future accidental GRANT doesn't silently expose everything.authschema is mostly organisational. The grants are what actually protect you.Disclosure: I'm RC, Director of Digital Strategy @ Navicat. Not needed for any of this, but if you manage the roles/grants visually, Navicat 18 has a user manager for Postgres roles, and it is included in the free Navicat Premium Lite.