S
Supabase•2y ago
Jota

Database Trigger

Is possible to create a database trigger wich can access the auth object of supabase? Or something like this...
9 Replies
Jota
JotaOP•2y ago
Basically, I need to create a trigger to automaticaly insert a value in a column based on wich user is making the insert on database
Socal
Socal•2y ago
just reference auth.uid() where you need the user id
Jota
JotaOP•2y ago
This is not working for me Function when called directly by the user, works fine using auth.uid() but it seems like when is on a trigger, doesnot works aut.uid() returns null @garyaustin what do you think?
garyaustin
garyaustin•2y ago
It will work in a trigger if the original call was from the API, or at least it should.
Jota
JotaOP•2y ago
in the paper it makes sense to work
garyaustin
garyaustin•2y ago
auth.uid() looks at the jwt value that was sent in,
Jota
JotaOP•2y ago
okay i will do more tests maybe im missing something here already tried a lot of things but seems to make sense this works
garyaustin
garyaustin•2y ago
Maybe show your code. It for sure works in triggers I have done that. And I think I have done it in security definer functions also. Since it looks right at the jwt value (stored as a setting) it is not impacted by service role.
Socal
Socal•2y ago
it could also be null if you're using the service_role secret to initialize the api call. at the top of your trigger you could just coalesce the auth.uid() with a default one you want on service role calls. otherwise you can handle the logic completely separately for these calls as below 
 create or replace
function public.jwt_has_tenant_role(tenant_id uuid,
tenant_role text) returns boolean language plpgsql as $function$
declare retval bool;

begin 
    if session_user = 'authenticator' then
    --------------------------------------------
        --calls from api - auth.uid() should be present here
    --------------------------------------------

        
    else
        --------------------------------------------
        --not a user session, probably being called from a trigger or something
        --- auth.uid() will not be here
    --------------------------------------------

        
    end if;
end;

$function$;
 create or replace
function public.jwt_has_tenant_role(tenant_id uuid,
tenant_role text) returns boolean language plpgsql as $function$
declare retval bool;

begin 
    if session_user = 'authenticator' then
    --------------------------------------------
        --calls from api - auth.uid() should be present here
    --------------------------------------------

        
    else
        --------------------------------------------
        --not a user session, probably being called from a trigger or something
        --- auth.uid() will not be here
    --------------------------------------------

        
    end if;
end;

$function$;

Did you find this page helpful?