The reads that happen before we know the clinic

Row level security fails closed: with no clinic in the transaction, tenant queries return nothing. That creates a puzzle. Signing in means reading a user by email before we know who’s asking, and the application role can’t do that.

Users are isolated per user

A person can work at several clinics, so users and sessions aren’t tenant data. They have their own context: withUser(db, userId, fn) sets the user and leaves the clinic empty. A signed-in person can reach their own rows, and nobody else’s.

A role with one job

That still leaves the moment before we know the user. Those reads are SECURITY DEFINER functions, owned by identity_definer, a NOLOGIN role. Nothing can connect as it, and its policies cover exactly these reads.

alter function identity.find_login(citext) owner to identity_definer;
alter function identity.resolve_session(bytea, interval) owner to identity_definer;
alter function identity.my_tenants() owner to identity_definer;

Execute is revoked from public, then granted to the application role.

• find_login looks up a sign-in by email.
• resolve_session takes a token hash, not a token, and an idle window. On that table the role can only update last_seen_at and idle_expires_at.
• my_tenants lists the clinics the current user belongs to.

Added sparingly

A definer function is a hole in the wall, however small, so each new one is a decision. Our working rules name the cases (login, session lookup, my clinics, slug check) and say to add others sparingly. Everything else goes through withTenant or withUser, as app_rw, with no BYPASSRLS.