SupabaseS
Supabase•4y ago
Atrox

Trouble with RLS and a custom authorize function

I have the following function defined to use as an authorization method in my RLS policies:
CREATE OR REPLACE FUNCTION authorize(requested_permission app_permission, profile_id uuid, project_id uuid) RETURNS boolean AS $$
DECLARE
    bind_permissions int;
BEGIN
    SELECT count(*)
        FROM public.role_permissions as p
        INNER JOIN public.projects_users as pu on p.role = pu.role
    WHERE p.permission = authorize.requested_permission
        AND pu.profile_id = authorize.profile_id
        AND pu.project_id = authorize.project_id
    INTO bind_permissions;
    RETURN bind_permissions > 0;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


In the roles_permissions table I have added a single permission: project.view for a role owner.
In the projects_users table I have a row with project_id, profile_id, and role.

Calling the authorize function manually works fine and returns true as I would expect. However, the RLS policy never passes. The projects table has a SELECT policy defined as authorize('projects.view'::app_permission, uid(), id).

Does anyone have any idea what's going wrong here?
Was this page helpful?