Supabase RPC returns fewer records than if I query the database itself

Question

I have a postgres (v15) function that takes a users id, and returns a list of other users that are similar to them. What similar means probably doesn't matter for the question here, but just in case, here is a simplified version of that function:

CREATE OR REPLACE FUNCTION public.get_similar(player_id uuid)
    RETURNS TABLE (user_id uuid)
AS
$$
BEGIN
    RETURN QUERY
        WITH player_stats AS (
        SELECT AVG(rs.total_distance_yards) AS total_distance,
               (SUM(rs.total_strokes) - SUM(rs.total_par))::NUMERIC(6, 1) AS astp,
                ... other stuff
          FROM (SELECT r.id, r2.*
                FROM rounds r,
                     round_stats r2
                WHERE r.id = r2.round_id
                  AND r.user_id = player_id
                  AND r.score > 0
                ORDER BY r.date DESC
                LIMIT 5) AS rs),
             similar_rounds AS (
             SELECT ro.user_id,
                       base.dastp AS total_score
                FROM (SELECT rs.round_id,
                      rs.total_distance_yards AS total_distance,
                      SQRT(POWER(((rs.total_strokes - rs.total_par) - player_stats.astp), 2))::NUMERIC(6, 1) AS dastp
                      ... other stuff
                      FROM round_stats rs,
                           rounds r,
                           player_stats
                      WHERE r.id = rs.round_id
                        AND r.holes_played = '18'
                        AND r.user_id <> player_id
                      ORDER BY ABS(rs.total_distance_yards - player_stats.total_distance)
                      LIMIT 100) AS base
                         JOIN rounds ro ON ro.id = base.round_id
                ORDER BY (base.dtd + base.dastp))
        SELECT f.user_id
        FROM (SELECT sr.user_id, AVG(sr.total_score)::NUMERIC(6, 1) AS final_score
              FROM similar_rounds sr
              GROUP BY sr.user_id
              ORDER BY AVG(sr.total_score)
              LIMIT 20) AS f;

    IF FOUND THEN
        RETURN;
    ELSE
        RETURN QUERY
            SELECT id::TEXT
            FROM users
            ORDER BY last_visit DESC
            LIMIT 20;
    END IF;
END;
$$ LANGUAGE plpgsql;

I call this function in a superbase edge function like so:

const compareQuery = supabase.rpc('get_similar', {'player_id': 'eca567ed-ddf7-4d13-916b-e91c90192869'});
type Compare = QueryData<typeof compareQuery>;
const {data, error} = await compareQuery;

When I do this, I get 2 records in the the results (data). There are 6 total users in the test database I'm using, so querying for any of them should return the other 5, not 2.

If I query the database directly: select * from get_similar('eca567ed-ddf7-4d13-916b-e91c90192869'); I get the 5 results I expect.

Answer 1

I realized as I was posting this question what the problem was, but figured I'd post anyway in case someone else has the same issue.

The problem was my row level security policy. While I do want the policy in place most of the time, in this one function I needed to bypass it.

To do that, I defined my postgres function as a 'Security Definer' - which uses the permission of the role that CREATED the function rather than the one querying it. Like so:

CREATE OR REPLACE FUNCTION public.get_similar(player_id uuid)
RETURNS TABLE (user_id uuid)
SECURITY DEFINER