Hiding utility functions in Supabase

In Supabase, it is often useful to have utility functions to use in RLS Policies to re-use common logic like is_admin(uuid) or get_all_items(uuid), or when you need to create security definer functions to bypass RLS policies.

But the problem is that these functions is also exposed through our API and could be called by users using supabase.rpc(). The way we could hide these functions is to move and create them in another schema.

The Solution

To do this, go ahead to the SQL editor and create the schema:

create schema utils;

To create functions on this schema, we can prepend utils. on our function names when creating them.

create or replace 
function utils.is_admin (uid uuid) 
returns boolean 
language plpgsql as $$
begin
  -- ...
end
$$;

You can ignore the function body and focus on the function name utils.is_admin, this creates a function in the utils schema instead of the default public schema. You can check that our schema and function has been created if you look at the Database section on the dashboard.

Only the functions and tables in the public schema will be exposed through the API so our utility functions cannot be called using supabase.rpc().

To use these functions, you'll need to write the full <schema>.<function_name>(), just like the built-in storage.foldername() function that Supabase provides. So to use the function above, you can call it by doing utils.is_admin().

Granting Access

But wait! We're not done yet, if we create RLS policies with our functions now then Supabase will not complain, however, once we test it out through our application we get an error that we are not permitted to use functions on the utils schema, this is because we haven't granted permission to our users that they can access these functions:

To do this, head back to the editor:

grant usage on schema utils to PUBLIC;
grant execute on all functions in schema utils to PUBLIC;

The first line is to allow usage of our schema, the second line allows the public to execute every function on our schema. The keyword PUBLIC means everyone, so here we are granting access to our functions to anyone. (still not exposed though)

This way, we can now have functions that are not exposed to the users, but can also be used for RLS policies. This is especially safer than exposing security definer functions to the public, and much cleaner than to just leave it in the public schema.

Granting Access to specific users

We could also just give access to certain users, the authenticated keyword is for users who are currently signed-in:

grant usage on schema utils to authenticated;
grant execute on all functions in schema utils to authenticated;

This will make sure only users who are signed in can access our utility functions, anyone who isn't will get the error before. Remember to revoke the privileges from PUBLIC if you previously granted access:

revoke usage on schema utils from public;
revoke execute on all functions in schema utils from public;