Creating trigger on auth.users table with postgres user is problematic

[silentworks]

I am currently creating all my tables, triggers, functions and views using the postgres user since I am using a db migration tool to handle this. However creating a trigger on auth.users table is working as expected, but the minute you try to delete that trigger you are hit with an issue of the table not belonging to the postgres user and this user doesn't have enough privilege to drop the trigger.

Does anyone have a workaround for this that doesn't involve elevating the postgres user to a superuser?

[w3b6x9]

@silentworks I can't think of a workaround for this as only the owner of auth.users may alter, drop, disable/enable the trigger. I think your best bet is to elevate postgres to superuser, like you mentioned, when creating and dropping triggers in your migration tool.

[silentworks]

I was so hoping there was a better solution, but I guess this is what I will have to work with.

[silentworks]

I found a flaw in this, I can't actually elevate postgres user to a superuser in a migration because the migration is being run by the same user who is not a superuser. Non-superusers can't elevate themselves.

[w3b6x9]

@silentworks you can elevate postgres to superuser via the SQL editor (supabase_admin superuser) in the Supabase dashboard:

ALTER USER postgres WITH SUPERUSER;

You can then revert this change:

ALTER USER postgres WITH NOSUPERUSER;

Just wanted to add in a caution as this is not best practice but necessary for your particular use case.

[ramonbrugman]

Leave it as it is. Try first to sideload a new db create the table and let it run on a different host implent after the variable table back with the auth lvl for SU be aware maybe there are more tables for this user so dont rush the coding

[silentworks]

@w3b6x9 yeah, I was thinking that would be the only way to get it done. Thanks for your help again.

@ramonbrugman not sure what you are saying here.