https://supabase.com/ logo
Join Discord
Powered by
# sql
  • h
    because seems like user is a string by default that contains admin / anon ig?
  • h
    I cud just change the column name to user_id but i wanted to know how to do it anyway
  • h
    like how we wud assign data in select
    Copy code
    select msg.name, usr.name as user from messages msg, users usr
  • u
    Thank you!
  • p
    How do I use the result of a function inside a policy UPDATE? eg.
    Copy code
    CREATE FUNCTION auth.firebase_uid()
RETURNS TEXT
AS $$
    SELECT nullif(current_setting('request.jwt.claim.sub', true), '')::TEXT
$$ LANGUAGE sql;

CREATE FUNCTION getIsUserEditorOrAdmin(id TEXT)
RETURNS BOOLEAN
AS $$
    SELECT role = 'admin' OR role = 'editor' FROM userAdminMeta WHERE id = $1
$$ LANGUAGE sql;

CREATE POLICY "Users can update their own profile OR staff can update any profile."
    ON users
    FOR UPDATE USING (
        auth.firebase_uid()::TEXT = users.id OR
        getIsUserEditorOrAdmin(auth.firebase_uid()) = TRUE
    );
    This doesn't seem to work: postgREST returns 404 getIsUserEditorOrAdmin returns "true" for my user ID (which I provide using a JWT token) If I update my OWN user it works perfect It looks like an issue with my getIsUserEditorOrAdmin() function. Have I defined it wrong? Do I need to change how it returns?
  • j
    does it work if you add a 
    select
    before it?
    Copy code
    CREATE POLICY "Users can update their own profile OR staff can update any profile."
    ON users
    FOR UPDATE USING (
        auth.firebase_uid()::TEXT = users.id OR
        (SELECT getIsUserEditorOrAdmin(auth.firebase_uid())) = TRUE
    );
  • j
    > postgREST returns 404 what is the full error message?
  • j
    and what did you run?
  • p
    Nope tried this and still same error:
    Copy code
    CREATE POLICY "Users can update their own profile OR staff can update any profile."
    ON users
    FOR UPDATE USING (
         auth.firebase_uid()::TEXT = users.id OR
        (SELECT(getIsUserEditorOrAdmin(auth.firebase_uid())) = TRUE)
    );
  • p
    I dont get any error message (no body). Just a 404 status code. When I disable the RLS or modify it remove the 
    getIsUderEditorAdmin()
    call I get a 204 (success). So definitely the policy
  • p
    The REST call is 
    PATCH {{serverurl}}/rest/v1/users?id=eq.abcdef
    where the doc id is a real id
  • p
    Is there a way to debug function calls or policies? This trial and error is hard
  • j
    hmm cant really tell from that nor reproduce. one way i try to debug these things is to use the sql editor and impersonate users making requests:
    Copy code
    SET LOCAL request.jwt.claim.sub = <YOUR USER ID>;
SET LOCAL ROLE authenticated;

SELECT getIsUserEditorOrAdmin(auth.firebase_uid()) = TRUE
    this should return true
  • j
    (btw i normally use 
    IS TRUE
    instead of 
    = TRUE
    but it shouldnt make a diff in this case i think...)
  • p
    Do I need to impersonate someone to set my role? I get a no permission error
  • p
    (I am logged in as 
    postgres
    using my own pg client)
  • j
    oh right 
    postgres
    doesnt have permission to do that
  • j
    try it on supabase?
  • p
    Is there a way to do this without using the web console?
  • j
    i dont think so :/
  • p
    Tried it in the web console and got 
    missing FROM-clause entry for table "users"
    so progress!
  • p
    Oh
  • p
    Nvm it's cause 
    users
    comes from the policy
  • j
    oh right sorry let me edit what i pasted above
  • j
    i removed the first section since u alr said that works
  • p
    Copy code
    SET LOCAL request.jwt.claim.sub = 'MY USER ID';
SET LOCAL ROLE authenticated;

SELECT getIsUserEditorOrAdmin(auth.firebase_uid()) = TRUE

> Success. No rows returned
  • p
    So when I set my sub/role via web console I always get 0 results. Even if I do a simple "select all users" query. When I remove the sub/role it works and I get results. It could be another policy screwing it
  • p
    The only other RLS policy I have is:
    Copy code
    CREATE POLICY "Everyone can read all users."
    ON users
    FOR SELECT USING (
        true
    );
  • j
    oh i think the web console only returns results for the first sql command. ok then
    Copy code
    SET request.jwt.claim.sub = 'MY USER ID';
SET ROLE authenticated;
    then remove that code, then run the remaining 
    SELECT getIsUserEditorOrAdmin(auth.firebase_uid()) = TRUE
    btw later once you're done debugging all this, remember to set it back to what it was:
    Copy code
    SET request.jwt.claim.sub = '';
SET ROLE supabase_admin
;
  • p
    That works - I get a different response now
1...8910...52Latest