https://supabase.com/ logo
Join Discord
Powered by
auth.uid() not working inside database function I...
# sql
e
auth.uid() not working inside database function I have a function like below:
Copy code
create or replace function test_function()
  RETURNS text
  LANGUAGE plpgsql
  security definer set search_path = auth
as $$
  declare user_role text;
  begin

    select raw_user_meta_data->>'role' 
    from auth.users
    into user_role
    where id = auth.uid()
    return user_role;
end
$$;
When I call this function from within my js app it returns null
Copy code
const {data, error} = await supabase.rpc('test_function')
When I replace auth.uid() with the id of an existing user it still returns null in my code but when I run
Copy code
select * from test_function()
in Supabase Studio's SQL Editor I do get the role I assigned to my user Why is there a difference between calling a function from code and calling it from the SQL Editor? And why is auth.uid() not working? To be clear; there is an authenticated user in the code that I'm calling from.
s
Try to define it as 
security invoker
instead of 
security definer
. If you're creating the function via the SQL editor in the dashboard, 
security definer
means it will be running as the dashboard user (e.g. 
supabase_admin
I think), whereas 
security invoker
will run it as the user which calls the function.
w
I have similar issue. I have a RLS policy which works with a hardcoded user id, but when I use auth.uid(), it doesn't work. Then I noticed that when you look at the policy via Authentication > Policies > three dots > Edit, the 
auth.uid()
is changed to just 
uid()
. Maybe it is somehow related.
j
Hey! what happens when you run
Copy code
select raw_user_meta_data->>'role' 
    from auth.users
    into user_role
    where id = auth.uid()
    return user_role;
in the sql editor (replacing auth.uid() with the uid that your user has?
@Waldemar what you were seeing with the edited policy was expected
w
@jaitaiwan oh so the function was changed from auth.uid() to just uid()? Still doesn't explain why the policy works with hardcoded uid, but doesn't with auth.uid() or uid() 🤕
j
@Waldemar there's this thing called "search_path" so if there's only one uid function and it's in the search_path (aka public, auth etc) then it will find it in auth.
@Waldemar I'd say something else is going on as well, so if you can post your example I can take a look
e
@jaitaiwan If I define the function like this
Copy code
create or replace function test_function()
  RETURNS text
  LANGUAGE plpgsql
  security definer set search_path = auth
as $$
  declare user_role text;
  begin
    select raw_user_meta_data->>'role' 
    from auth.users
    into user_role
    where id = 'f32fc3d5-b9a5-4b62-a5d1-d4e59ed6e6ea';
    return user_role;
end
$$;
I will see the correct role in both the SQL Editor when running
Copy code
select * from test_function()
But when I change to
Copy code
security invoker
I will still get a correct answer in the SQL editor but null in my Javacript.
I guess the JS code is using the anon key and has not enough rights to query the auth.users table
j
@edelacruz why are you changing it to security invoker?
e
I was thinking that from my code to get the correct auth.uid() maybe I should invoke the finction as the called not as the owner of the function
j
Nah you don’t have to. The uid comes from the token that’s sent via the request to the backend
e
ah ok. But whatever I do, it's not returning the uid of the caller
j
You can’t access the auth table either without definer 🙂
Have you tried uid with definer called from the front end?
e
yes
j
Can you please show me that function here?
e
const {data, error2} = await supabase.rpc('test_function')
console.log('[Users Endpoint] Get users test function', data)
j
I mean what does the resulting sql look like?
e
Sorry, I don't understand
You want to see the function?
j
Yes please
e
Copy code
create or replace function test_function()
  RETURNS text
  LANGUAGE plpgsql
  security definer set search_path = auth
as $$
  declare user_role text;
  begin
    select raw_user_meta_data->>'role' 
    from auth.users
    into user_role
    where id = auth.uid()
    return user_role;
end
$$;
j
It strikes me that raw user meta data might be incorrectly spelled
e
That is how it is called in the table
By in the front-end it's shown as user_metadata
j
Ahh yep my mistake
e
If I replace where id = 'existing uuid' I do get a correct answer
So the rest of the function works
Maybe auth.uid() only works in a policy
j
That means to me that possibly rpc isn’t sending the auth token. I don’t have the code in front of me to be sure but if that’s the case it’s the same issue with the edge functions
No it works outside of policies
I use it in many of my functions
e
I should see that in the logs right?
j
Possibly. I haven’t found the logs very helpful but ymmv
e
I don't see any auth headers in the log
"headers": [ { "accept": "*/*", "cf_connecting_ip": "96.9.94.158", "cf_ipcountry": "KH", "cf_ray": "72e369e55e8da4a7", "content_length": "0", "content_range": null, "content_type": "application/json", "date": null, "host": "gtkajsenghjcmgojjgku.supabase.co", "referer": null, "user_agent": "undici", "x_client_info": "supabase-js/1.35.4", "x_forwarded_proto": "https", "x_forwarded_user_agent": null, "x_real_ip": "96.9.94.158" } ], "host": "gtkajsenghjcmgojjgku.supabase.co", "method": "POST", "path": "/rest/v1/rpc/test_function", "protocol": "https:", "sb": [], "search": null, "url": "https://gtkajsenghjcmgojjgku.supabase.co/rest/v1/rpc/test_function"
j
I can confirm it’s not setting the correct headers
Actually misread on my part
e
I will file an issue in github for this. I mean the auth.uid() not working
It's important to me because I use a function to query the auth.users. I'm not a fan of making an extra users table and then fuill it though triggers. That is too hacky for me.
I have this function to query the auth.users table and it works fine:
Copy code
create or replace function users_by_tenant_id(tenant_id_input text) RETURNS TABLE (
    user_id uuid,
    user_email varchar,
    user_name text,
    user_role text,
    user_alias text,
    user_tenant_id text
  ) LANGUAGE plpgsql security definer
set search_path = auth as $$
declare user_role text;
begin
select raw_user_meta_data->>'role'
from auth.users into user_role
where id = auth.uid();
if user_role != 'admin' then raise notice 'Not allowed';
else return query
select u.id,
  u.email,
  u.raw_user_meta_data->>'name' as name,
  u.raw_user_meta_data->>'role' as role,
  u.raw_user_meta_data->>'alias' as alias,
  u.raw_user_meta_data->>'tenant_id' as tenant_id
from auth.users u
where u.raw_user_meta_data->>'tenant_id' = $1;
end if;
end $$;
This works except for the security part because that depends in the auth.uid() function to work
j
@edelacruz when in your application are you calling the rpc funciton
From what I can tell, it will trigger fine if the user is not logged in, but if the user is logged in then the auth token will be a part of the request
Out of curiosity if you run:
Copy code
select proname,prosrc from pg_proc where proname= 'uid';
Do you get:
Copy code
select 
      coalesce(
        nullif(current_setting('request.jwt.claim.sub', true), ''),
        (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
    )::uuid
?
g
You are using auth.raw_user_meta_data for a role claim? A user can change that data with a http call at any time if they have a valid jwt (are logged in) and make themselves an admin.
auth.uid() works in a security definer function accessed by an rpc call (at least in general). This returns the user's id and tests accessing auth.users table:
Copy code
declare myid uuid;
begin
select id from auth.users where id = auth.uid() into myid;
return myid;
end
And works fine, returning the user's id to the client.
w
@jaitaiwan thanks for the offer! I'm in contact with support guys, so I'll try there first.
e
I solved my problem by creating a new Supabase project. I guess I messed up the first project (it was my first Supabase and Progress project so I tried a lot and made a lot of mistakes). For completeness here is my working function:
Copy code
-- A function to return all users from the auth.users table
-- but only if the requestor has an admin role in raw_user_meta_data.role
create or replace function get_all_users() 
  returns table (
    user_email varchar,
    user_name text,
    user_role text
  )
  language plpgsql
  security definer set search_path = auth
  as $$ 
  declare user_role text;
  begin
    select raw_user_meta_data->>'role' 
      from auth.users
      into user_role
      where id = auth.uid();
    if user_role <> 'admin' then
      return;
    else
      return query
        select
          email,
          raw_user_meta_data->>'name',
          raw_user_meta_data->>'role'
          from auth.users;
    end if;
end $$;
You are using auth.raw_user_meta_data for a role claim? A user can change that data with a http call at any time if they have a valid jwt (are logged in) and make themselves an admin. Are you sure about this? So I should use app_metadata instead?
j
@edelacruz most definitely. And my bad for not picking this up, thanks Gary. User metadata can be modified by the user during registration etc. App metadata is only modifiable by db
e
Ah, I found confirmation already. Here is a nice article: https://dev.to/supabase/supabase-custom-claims-34l2
PreviousNext
Powered by