https://supabase.com/ logo
Join Discord
Powered by
Heya - a question about PostgreSQL Functions. 🙂 I...
# sql
t
Heya - a question about PostgreSQL Functions. 🙂 It is about the 
SECURITY DEFINER SET search_path = public
thingy that I have to use when writing Functions when using Supabase, but I really do not see other tutorials or the like use.. Am I allowed to just add 
extensions
to it along with public, or is there any security issues with that? I can only get pgjwt (https://github.com/michelp/pgjwt) to work with that added. 🙂 Here is the full function:
Copy code
CREATE OR REPLACE FUNCTION handle_new_user() RETURNS trigger
    SECURITY DEFINER SET search_path = public, extensions
AS
$$
BEGIN
    INSERT INTO profiles (id, jwt)
    VALUES (NEW.id, (SELECT SIGN('{
      "sub": "1234567890",
      "name": "John Doe"
    }', 'secret')));

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
g
This github discussion covers it... https://github.com/supabase/supabase/issues/462
Basically if you did not set the path it would work using the defaults, but as soon as you set it you have to include everywhere it needs to look, including extensions if you are using them.
t
Heya, thank you for the reply and sorry for the late one from me now. 🙂 
SECURITY DEFINER SET search_path = public, extensions
if I do not use this, both public and extensions, stuff wont work properly. Extensions for the 
pgjwt
extension I use and public for the other stuff I guess. - the way I test this function is by inviting an user via the Supabase dashboard. That will just give an error if no SECURITY DEFINER at all is set. :/ I am fine by using it, I just wanted to make sure it was safe to add. 🙂
g
FYI SECURITY DEFINER says your function runs with the level of the user that created (versus the logged in user), so bypasses RLS and other checks. You need this if your function accesses data not "owned" by the logged in user when it runs. The SET search_path is a separate thing. If you leave it out when using SECURITY DEFINER the function can access anything but will run. As a safety the link I gave you recommends set search_ path to extensions, public, pg_temp if you use extensions. follow the links in the article for info on what/why if you want to understand more.
Adding. I've never used set path as my functions are launched from triggers, or if a user function only use public tables
t
I start to understand it more and more, thank you. 🙂 I'll definitely read up on it! Still quite confused because I just tried leaving in the 
SECURITY DEFINER
without the 
SET....
part, and it still won't work without. 🙂 Did I misunderstand you, or?
Copy code
CREATE OR REPLACE FUNCTION handle_new_user() RETURNS trigger
    SECURITY DEFINER
AS
$$
BEGIN
    INSERT INTO profiles (id, jwt)
    VALUES (NEW.id, (SELECT SIGN('{
      "sub": "1234567890",
      "name": "John Doe"
    }', 'secret')));

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- -------------------------------------------

CREATE TRIGGER on_user_signup
    AFTER INSERT
    ON auth.users
    FOR EACH ROW
EXECUTE PROCEDURE handle_new_user();
This gives an error when inviting users from the dashboard. Adding 
SET search_path = public, extensions
to the SECURITY DEFINER makes it work just fine. 🧐
g
OK, just leave it then. I don't have it mine that calls http: extension, but maybe I set the default system path early on.
t
Weird - would love to know why, but maybe some reading would help. Thank you anyways buddy!
s
You could omit the 
search_path
in your function if you do 
SELECT extensions.SIGN(..)
PreviousNext
Powered by