1

I have a supabase project where I want the users to be able to delete their account. Every user has a profile in public.profiles. I thought I could let the users delete their profile and then handle the deletion of the account using a trigger.

So I created this:

CREATE OR REPLACE FUNCTION deleteUser() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM auth.users WHERE auth.users.id = OLD.user_id;
    RETURN OLD;
END $$ LANGUAGE 'plpgsql';

CREATE TRIGGER deleteUserTrigger 
    AFTER DELETE
ON public.profiles
FOR EACH ROW 
    EXECUTE PROCEDURE deleteUser();

In the supabase table-editor interface it works great, but when I delete an account through my application it fails with: code: "42501", message: "permission denied for table users".

const { error } = await client.from("profiles").delete().eq("user_id", user.id);
console.log(error)

Without the trigger there is no error and the profile is deleted (but the account persists obviously).

Any help would be much appreciated!

I found this but I'm not sure about it..

Anton
  • 563
  • 4
  • 13

1 Answers1

2

If anybody is having this same issue, it can be fixed by adding security definer

so the sql becomes this:

CREATE OR REPLACE FUNCTION deleteUser() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM auth.users WHERE auth.users.id = OLD.user_id;
    RETURN OLD;
END $$ LANGUAGE 'plpgsql' security definer;

CREATE TRIGGER deleteUserTrigger 
    AFTER DELETE
ON public.profiles
FOR EACH ROW 
    EXECUTE PROCEDURE deleteUser();
Anton
  • 563
  • 4
  • 13
  • Is the necessity to add "security definer" a new thing? I don't think I needed that in the past. – Brandon Peck Mar 14 '23 at 19:35
  • `security definer` specifies that the function should be executed with the permissions of the user that owns it. Without `security definer` the function is executed with the permissions of the user that invokes it. Chances are that the invoker doesn't have permission to delete from `auth.users`. See https://www.postgresql.org/docs/current/sql-createfunction.html#:~:text=SECURITY%20DEFINER%20specifies%20that%20the,functions%20not%20only%20external%20ones. for more info – Wouter Raateland Mar 24 '23 at 13:57