What is the correct (and secure) way to implement a security definer function that isn't callable via rpc (or other ways from outside) and can only be called by other functions?

Hello @Barschreck! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the

/title

command! We have solved your problem? Click the button below to archive it.

Create the function > revoke permissions:

REVOKE ALL ON my_schema.my_function(text, text, text, text) FROM public

Replace the args with the correct types and the function name and schema with the correct details. Repeat this for users

authenticated

,

anon

,

supabase_admin

and all other users (i.e. replace the last

public

with the name of the user)

Barschreck (2022-03-18)

If you get permissions error, find out which user your calling function is owned by, and allow that to call it by

GRANT EXECUTE ON

statement

I put my security definer functions in a schema called "security" and don't enable that in API to be available. @User is that not sufficient to prevent rpc or postgrest calls?

Yeah, it should be enough. I can't say I've tried it that way, but it's a good idea

Thank you 🙂 I'll try it out tomorrow

It for sure is from the postgrest API as it can only access schema's that are in the API settings field.

Can I prevent that by executing the following? REVOKE EXECUTE ON FUNCTION security.mysecretfunction() FROM PUBLIC;

I think if you want total lock down you are looking at Scott's method, or locking down the "security" schema. Depends on what you are trying to protect them from.

okay thank you very much 👍🏼 I will check what suits me the best. That was great input.

Just to be clear, the rpc API will not be able to use function in the "security" schema. You can however write your public rpc functions to use them in a controlled fashion. They can't be abused, unless you yourself abuse them in the rpc function you wrote.

Thread was archived by @Barschreck. Anyone can send a message to unarchive it.

Sorry for reopening this help request. I was taken down for a few days by covid. My usecase is a function that I only want to call in a RLS policy. So your described approach should be fine. I don't have a lot of experience with postgres, so I don't know a lot about schemas, search paths and their default behavior. Are there any adjustments to make to the security schema to be secure? This would be my implementation: Create schema security; Create function security.rls_function(uuid) returns boolean language sql security definer set search_path = security, pg_temp as $$ Select true * $$; Create policy "user_can_read_other_user_in_organisation" on public.users for select using ( security.rls_function(id) )

As long as you don’t put it in the API settings page for schemas it can use you are fine from the API.

Ok perfect. Thank you