Best way to allow backend access for certain functions?

[prescience-data]

I'm wondering if there is a suggested pattern for allowing backend access to specific functions using the service user key?

For context, we decided to implement the schema isolation design discussed here whereby we have a private data schema for all tables, and only allow access via postgres functions.

In a frontend scenario, I am running ownership checks based on auth.uid() however in the backend case, I don't imagine there is a user "signed in" the same way when using the service key?

Given that Supabase (last checked) only exposes the public schema for functions, I'm trying to work out a way to provide secured functions to the backend.

My first impression is that it could be as simple as only allowing EXECUTE to a specific user, but not sure:
a) how this would work if the functions are defined as DEFINER;
b) how to figure out the user associated with the service key;
c) if this is even a good idea / if there are major security holes implied with this pattern?

Thanks!

[thorwebdev]

What's the context your backend operations will be running in? Are they also meant to use the authenticated user context?

You could run your backend exclusively with the anon key also, and pass the users access token from the client to the backend to use it there vis supabase.auth.setAuth(access_token).

When using the service_role key, you would need to evaluate authorization within your backend logic. E.g. you can use supabase.auth.api.getUser(access_token) to get the user based on the session access_token from the client, and then use their uid to filter data for them.

Here an example of getting the user from their token: https://github.com/supabase/supabase/blob/master/examples/nextjs-with-supabase-auth/pages/api/getUser.js

[steve-chavez]

In a frontend scenario, I am running ownership checks based on auth.uid() however in the backend case, I don't imagine there is a user "signed in" the same way when using the service key?

If you check the service key contents, it has a role claim which is mapped to a service_role postgres role(you can check this with auth.role()). So yes, it should be similar with the difference that an auth.uid() is not present because service_role is not a web user(not mapped to a row in auth.users).

My first impression is that it could be as simple as only allowing EXECUTE to a specific user, but not sure:

Yes, that would work, you could create another role and do GRANT EXECUTE on your functions on it. Keep in mind that there's a gotcha right now for adding extra roles #1925.

a) how this would work if the functions are defined as DEFINER;

If your new role can EXECUTE the function then the DEFINER property will work as usual.

b) how to figure out the user associated with the service key;

By creating a custom JWT with a "role"claim equal to your new postgres role and passing that as a header(postgrest-js has a function for that).

c) if this is even a good idea / if there are major security holes implied with this pattern?

As long as you test your role can only execute whitelisted functions and not read/write tables/views, etc - it should be a safe pattern.