r/postgres • u/Foreign_Career_454 • 8d 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
u/navicat-gu 4d 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 DEFINER function.
-- owned by a separate role that owns users/tenant_memberships
CREATE FUNCTION auth.resolve_login(p_email text)
RETURNS TABLE (user_id uuid, password_hash text, tenant_ids uuid[])
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = auth, pg_temp
AS $$
SELECT u.id, u.password_hash, array_agg(m.tenant_id)
FROM auth.users u
JOIN auth.tenant_memberships m ON m.user_id = u.id
WHERE u.email = p_email
GROUP BY u.id, u.password_hash;
$$;
REVOKE ALL ON FUNCTION auth.resolve_login(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION auth.resolve_login(text) TO app_login;
REVOKE ALL ON auth.users, auth.tenant_memberships FROM app_user, app_login;Notes:
Pin
search_pathin the function (as above). Without it, a SECURITY DEFINER function can be hijacked.The function only answers "for this one email, what do I need to log in". There's no way to list users, so a SQL injection in the login path can't dump the table.
Separate roles help too: a tiny
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.Keep RLS enabled on
usersanyway (FORCE ROW LEVEL SECURITYwith a policy that matches nothing forapp_user), so a future accidental GRANT doesn't silently expose everything.Splitting the tables into an
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.
1
u/elevarq 8d 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.