Supabase RLS using Functions - Security Definers

I'm a BIG fan of Supabase for development and deployment of applications. Postgres is an incredible database and the functionality built on top of it using Supabase makes it great competitor in the market against tools like Firebase, AppSmith, etc.

Recently, I wanted to write a function that allowed me to pull out additional ids linked to a user based on the auth.uid() function. Effectively, I had the following:

I wanted to create RLS rules where I could see if an item belonged to a profile without having to write a select query in the policy. This is just a simple example, but imagine, for example, in the future that there are many tables all linked to profile instead of the users table.

So I created a function that would return the profile_id of the person querying but selecting that id where auth_user_id = auth.uid().  So my function looked like this,

CREATE OR REPLACE FUNCTION public.get_profile_id()
 RETURNS VARCHAR(21)
 PARALLEL SAFE
 LANGUAGE plpgsql
AS $function$
DECLARE
  v_profile_id VARCHAR(21);
BEGIN
  SELECT public.profile.profile_id
  INTO v_profile_id
  FROM public.profile
  WHERE public.profile.auth_user_id = auth.uid()
  LIMIT 1;

  RETURN v_profile_id;
END;
$function$
;

This makes sense to me and I felt that the logic was sound. I created a RLS policy on the table and I thought I was good to go,

create policy "Allow users to select their own items"
on "public"."profile"
as permissive
for select
to authenticated
using (public.get_profile_id() = (profile_id));

But, when I tried to use it, I experienced a strange issue.

{
    "error": {
        "code": "54001",
        "details": null,
        "hint": "Increase the configuration parameter \"max_stack_depth\" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.",
        "message": "stack depth limit exceeded"
    },
    "data": null,
    "count": null,
    "status": 413,
    "statusText": "Request Entity Too Large"
}

Why would the stack depth even be an issue? The only time we really see this is when a recursive function calls itself too many times... But this isn't a recursive function??

After a few searches I found out about the SECURITY DEFINER parameter in an SQL function. It's spoken about in great detail in the Supabase documentation but they do show that you use it when you want to extract a team id (in their example) which is in the same direction as where I was going.

It turned out that when I added the select function in the SQL function, it would refer to RLS to see if it could query the profile table, that in turn would call the function which would RLS to see if it could query the profile table, that in turn would call the function which would... you get the point!

So I needed to add SECURITY DEFINER so that Postgres knew that it doesn't have to use RLS during that query.

CREATE OR REPLACE FUNCTION public.get_profile_id()
 RETURNS VARCHAR(21)
 PARALLEL SAFE
 SECURITY DEFINER
 LANGUAGE plpgsql
AS $function$
DECLARE
  v_profile_id VARCHAR(21);
BEGIN
  SELECT public.profile.profile_id
  INTO v_profile_id
  FROM public.profile
  WHERE public.profile.auth_user_id = auth.uid()
  LIMIT 1;

  RETURN v_profile_id;
END;
$function$
;

And that's it! You're now able to use this function in your RLS policies.