Hey everyone, How can I create RPC, not callable by the js client but only "internally"? Is something about the grants? If yes, then which one should I remove?

ALTER FUNCTION public.myinternalfunc(jsonb) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.myinternalfunc(jsonb) TO PUBLIC;
GRANT EXECUTE ON FUNCTION public.myinternalfunc(jsonb) TO anon;
GRANT EXECUTE ON FUNCTION public.myinternalfunc(jsonb) TO authenticated;
GRANT EXECUTE ON FUNCTION public.myinternalfunc(jsonb) TO postgres;
GRANT EXECUTE ON FUNCTION public.myinternalfunc(jsonb) TO service_role;

Hello @NARCISO! 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.

I create a schema called security and put functions I don’t want the API to have access to there.

How to create a RPC not callable by the js client

Hi @garyaustin, thanks for the feedback! That's a good solution but I guess it's not applicable to the problem I have now. Let me explain a bit further. I have a function "connected" to a trigger that adds a role to a user

after inserting

a row in a certain table. My concern at the moment is that the user could add the role he wants to himself calling the function as "rpc". And that's something should not happen. What do you think would be the best way to deny users to execute the function? Is there a way to permit to execute this function just to the "database" itself? The function and trigger looks like this:

-- Add worker role to user
create function public.add_worker_role()
  returns trigger as $$
begin
  -- Check if the user is the owner of the user profile
  if (new.user_id <> auth.uid()) then
    raise exception 'not_owner';
  end if;

  -- Check if the user profile just created exists
  if not exists(select 1 from public.user_profile where user_id = new.user_id) then
    raise exception 'user_profile_not_exists';
  end if;

  -- Add worker role to the user
  insert into public.user_role (user_id, role_id) values (new.user_id, 'WORKER'::role);

  return null;
end;
$$ language plpgsql security definer set search_path = public, pg_temp;

create trigger update_user_profile_trigger
  after insert on public.user_profile
  for each row
  execute procedure public.add_worker_role();

Thank you so much in advance!

I’m not sure why putting in a special schema would not work. The API and rpc call would not have access to it. Your trigger would by calling security.myfunction()

@garyaustin You are right. Now I see you solution. I have the last question tho. I have these grants at the moment:

pgsql
ALTER FUNCTION public.add_worker_role() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.add_worker_role() TO PUBLIC;
GRANT EXECUTE ON FUNCTION public.add_worker_role() TO anon;
GRANT EXECUTE ON FUNCTION public.add_worker_role() TO authenticated;
GRANT EXECUTE ON FUNCTION public.add_worker_role() TO postgres;
GRANT EXECUTE ON FUNCTION public.add_worker_role() TO service_role;

Do you think it will work even if I'll just REVOKE the execution from PUBLIC, anon and authenticated? to deny the users the direct invocation of the function?

If your trigger function is happening from a table insert/update coming from the API then no. If a super user only does the initial table operation then probably.

You mean that, having a trigger like the one above that works

after insert

on the table

public.user_profile

, if a user from the js-client

insert

some data in the table, it would not work revoking the execution? But if I would have a function (as security definer) that inserts the data, that would work?

The user is set for the entire session. Seq def won’t work cause you can’t even start the function without execute permission.

Uh okay, well I think at this point the best way to proceed is your method. About creating a "security" schema, should there be something I need to consider?

I just created it, no special grants. I use them in my RLS.

Sorry I did not understand what do you mean by "I use them in my RLS"

I put my security definer functions I use in RLS policies in that schema ( hence the name) so api can’t access them.

Could you give me a code example if you have it there? I'm not sure I understood correctly. You set RLS for the function? or in the schema for what? Sorry I'm pretty new to all this RLS and Postgres specific stuff 😁

I’m just saying I use this approach for functions I use in my policies so that users can’t access them thru rpc call. Like you want here. The last comment here is from a SB dev recommending same approach: https://github.com/supabase/supabase/discussions/3269

That's awesome! I created a

private

schema and put there all my private functions, but I was wondering about the grants. Which grants should I give to it? Just the

postgres

one? and REVOKE all the others

PostgREST

roles?

ALTER FUNCTION private.handle_new_user() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION private.handle_new_user() TO PUBLIC;
GRANT EXECUTE ON FUNCTION private.handle_new_user() TO anon;
GRANT EXECUTE ON FUNCTION private.handle_new_user() TO authenticated;
GRANT EXECUTE ON FUNCTION private.handle_new_user() TO postgres;
GRANT EXECUTE ON FUNCTION private.handle_new_user() TO service_role;

I leave all grants. But then I want them usable as part of my RLS. Only you can create a call to the function by using schema.function(). Trigger function can’t be called by rpc.

So basically leaving all the grants would give the PostgREST user to access them but the API would not access that schema, so it's safe to leave them, right?

Yes. An API user can be any of them but postgres and RLS would be run as that user. Also if you have a normal rpc function and want to call a security function you want that to work too.

I moved a

trigger function

handle_new_user

to the

private schema

that populate the user profile table based on

after insert

on

auth.users

. As you said, I could leave that in the

public

schema because it cannot be called as

rpc

, right?

pgsql
CREATE TRIGGER handle_new_user_trigger
    AFTER INSERT
    ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION private.handle_new_user();

That is my understanding. I’ve not personally tried it.

Allright, thank you so much for time!