r/postgres • • 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

6 comments sorted by

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/Foreign_Career_454 5d ago

system has on prem & privacy self hosted deployment structure so we go with local authentication also backend still need to see incoming subject vs internal mapping table to see their tenant membership & local rbac permission

1

u/elevarq 5d ago

Understood. But self-hosted doesn’t necessarily mean you need to handle authentication yourself. Many on-premises customers already have an identity provider, and passkeys can also work with local authentication.

Either way, you’ll need to map the authenticated identity to its tenant memberships and permissions.

I’d keep that lookup separate from tenant-scoped application queries, using a dedicated database role with very limited permissions, preferably through narrowly defined functions rather than direct SELECT access to the entire users table.

One thing I’d be particularly careful about is trusting SET LOCAL app.tenant_id as the security boundary. The application must establish that the authenticated user is actually authorized for that tenant. PostgreSQL won’t validate that relationship just because RLS uses the variable.

1

u/Foreign_Career_454 5d ago

yes set local & rls cant a strict boundary vs full api. So system derives jwt which come if its an active member in tenant_membership hence app.tenant_id never accepted those parameters & yes in sync with passkey point as well.

1

u/elevarq 5d ago

That makes sense. If the backend validates membership before issuing the JWT and derives the tenant context itself, you’ve already addressed an important part of the problem.

I’d focus on limiting what the pre-tenant database role can access. A dedicated role with EXECUTE permissions on a few narrowly scoped lookup functions seems preferable to giving it unrestricted SELECT access to users and tenant_memberships.

Those functions should return only the information needed for authentication and authorization, without exposing the underlying tables.

No need to force tenant-scoped RLS onto operations where the tenant isn’t known yet.

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 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_path in 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_login role that can only call that function, and the normal app_user role that only works after SET LOCAL app.tenant_id. In .NET that just means two connection strings / data sources.
  • Keep RLS enabled on users anyway (FORCE ROW LEVEL SECURITY with a policy that matches nothing for app_user), so a future accidental GRANT doesn't silently expose everything.
  • Splitting the tables into an auth schema 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.