Permission denied for stored procedure that updates user's email address in auth.users

[GodGardensGuns]

I created a stored procedure (aka function) in the database that I hoped would allow the user's email address to be updated from the supabase JS client. When I ran it, I was met with an error message: 'permission denied for schema auth'

Here's what my 'CREATE FUNCTION' query looks like:

CREATE FUNCTION updateauthemail (new_email text, auth_id text)
RETURNS text AS $updated_data$
	DECLARE
		updated_data text;
	BEGIN
		UPDATE auth.users set email = new_email where id = auth_id;
		RETURN updated_data;
END;
$updated_data$ LANGUAGE plpgsql;

Do I need to add something to the function when creating it, or pass a certain param through the JS client on my Node.js server? Here's the function I'm calling through the JS client:

const { data: rpcData, error: rpcErr } = await supabase.rpc(
      "updateauthemail",
      {
        auth_id: authId,
        new_email: updatedData.email,
      }
    );

Originally posted by @dayvista in supabase/supabase#573 (reply in thread)

[kiwicopple]

Hey @dayvista, I think you're running up against this issue:
supabase/supabase#393 (reply in thread)

Basically you will want to add security definer and the search_path to your function:

I'll close this for now, but if that doesn't work feel free to re-open

[GodGardensGuns]

I added a security definer and the search_path, and now I'm running into another issue. Originally, I created the function as:

CREATE FUNCTION updateauthemail (new_email text, auth_id text)

... but that returned an error message:

message: 'operator does not exist: uuid = text'

When I changed the CREATE FUNCTION to define auth_id as the uuid type:

CREATE FUNCTION updateauthemail (new_email text, auth_id uuid)

... the operation seems to run successfully without an error, but it returns null and the email isn't updated in auth.users. I'm thinking it's just an issue with how my SQL function is written/structured (it's basically the first legit one I've made). Here it is in it's entirety:

CREATE OR REPLACE FUNCTION update_auth_email (new_email text, auth_id uuid)
RETURNS text AS $updated_data$
	DECLARE
		updated_data text;
	BEGIN
		UPDATE auth.users set email = new_email where auth.uid() = auth_id;
		RETURN updated_data;
END;
$updated_data$ LANGUAGE plpgsql
			   SECURITY DEFINER
    		   SET search_path = public, auth;

[GodGardensGuns]

P.S. I don't believe I had the necessary permissions to re-open this issue. I believe figuring out an in-app way to do this would be really beneficial for those using Supabase in production. Allowing users to update the emails they use to log in is a common need.

[steve-chavez]

@dayvista The auth.uid() = auth_id; part in the UPDATE looks wrong.

Shouldn't that be:

UPDATE auth.users set email = new_email where id = auth.uid();

[GodGardensGuns]

Thanks for the tip, @steve-chavez
That almost did it, but I had to compare my declared auth_id variable against id rather than auth.uid(), like so:

UPDATE auth.users set email = new_email where id = auth_id;

That enabled me to pass the id through Node.js like this:

const { data: rpcData, error: rpcErr } = await supabase.rpc(
      "update_auth_email",
      {
        auth_id: authId,
        new_email: updatedData.email,
      }
    );

... and it worked! Thanks a ton to both of you for pointing me in the right direction.