> SECURITY INVOKER indicates that the procedure is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the procedure is to be executed with the privileges of the user that owns it.

Ah, ok from your original answer I wasn't sure if you ment that RLS itself relies on functions being inside the public schema or smth like that

Create a trigger so that when a profile is associated with a user we set a default value for name based on their email address. -------------------------------------------- CREATE OR REPLACE FUNCTION set_default_profile_name_from_user () RETURNS TRIGGER AS $$ BEGIN IF NEW.user_id IS NOT NULL AND NEW.name IS NULL THEN SELECT INTO NEW.name split_part(email, '@', 1) AS name FROM auth.users WHERE id = NEW.user_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE; --- AND CREATE TRIGGER create_profile_for_response BEFORE INSERT OR UPDATE ON profiles FOR EACH ROW EXECUTE PROCEDURE set_default_profile_name_from_user (); Don't allow users to null out their name ---------------------------------------- ALTER TABLE profiles ADD CONSTRAINT profiles_with_users_cannot_have_null_or_blank_name CHECK ( user_id IS NULL OR COALESCE(TRIM(name), '') <> '' )

but it's good to see Mikes example that does input checking etc

though I'm really just generating a random username and password client side and giving them later the option to pick their own

Function to return a profile as json (or a new id if passed null) ----------------------------------------------------------------- CREATE OR REPLACE FUNCTION get_profile (profile_id uuid DEFAULT NULL, user_id uuid DEFAULT NULL) RETURNS json AS $$ # variable_conflict use_variable DECLARE profile record; BEGIN IF profile_id IS NULL AND user_id IS NULL THEN -- If both profile_id and user_id are null, return a new (unsaved) profile SELECT INTO profile uuid_generate_v4 () AS id; END IF; IF profile_id IS NOT NULL AND user_id IS NULL THEN -- If the profile_id is not null, we return the profile (which may or may not be saved) SELECT INTO profile * FROM profiles WHERE id = profile_id; IF NOT found THEN SELECT INTO profile profile_id AS id; END IF; END IF; IF user_id IS NOT NULL THEN -- If there is a user and they have a profile, return that profile SELECT INTO profile * FROM profiles WHERE profiles.user_id = user_id; IF NOT found THEN -- If the user didn't have a profile, -- coalesce a NULL profile_id into a new random value. -- Attempt to insert the profile; if it exists then update it INSERT INTO profiles (id, user_id) VALUES (COALESCE(profile_id, uuid_generate_v4 ()), user_id) ON CONFLICT (id) DO UPDATE SET user_id = user_id RETURNING * INTO profile; END IF; END IF; RETURN row_to_json(profile); END; $$ LANGUAGE plpgsql VOLATILE;

Was “fun” having to think it through backwards