[Security] match_users RPC lets authenticated users probe arbitrary email addresses (account enumeration)

[atul-upadhyay-7]

Summary

The match_users SECURITY DEFINER function is executable by authenticated clients and derives the user to exclude purely from a caller-supplied target_email argument. Any authenticated user can invoke it directly with arbitrary emails to test whether an address is registered and harvest profile data — a reliable account-existence oracle.

Evidence

supabase/migrations/20260601000001_fix_match_users_security_definer.sql:

-- line 79
WHERE p.email != target_email
-- ...
-- lines 92-100
REVOKE ALL ON FUNCTION public.match_users(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.match_users(...) TO authenticated;

The backend (backend/controllers/matchController.js) calls this via the service-role client with the caller's own email. There is no server-side path that requires the RPC to be invoked by authenticated clients — the authenticated GRANT exists only so attackers can reach it directly.

Exploit

# call RPC directly with a candidate email, then with a bogus one
supabase.rpc('match_users', { target_email: 'victim@example.com', ... })   # returns victim's row minus email-matching
supabase.rpc('match_users', { target_email: 'nobody@nowhere.invalid', ... })
# diff the result sets -> confirms whether victim@example.com is registered
# and reveals name, skills, interests, teach/learn subjects of all users

Impact

• Account existence oracle: confirms any email is registered.
• Unauthorized user-directory dump (names, skills, interests) without using the app.

Suggested Fix

1. REVOKE EXECUTE on match_users from authenticated (service-role backend calls keep working).
2. Defense in depth inside the function: when auth.uid() is present (i.e., invoked by an authenticated caller), require target_email to equal the caller's own email, otherwise RAISE EXCEPTION.
3. Validate target_email is a well-formed email before executing.