https://supabase.com/ logo
Join Discord
Powered by
I use FlyWay for DB migrations, which allows a par...
# sql
c
I use FlyWay for DB migrations, which allows a particular migration to be re-run everytime the contents of the migration file changes. This is quite handy for logic (e.g. views, functions, triggers) since you don't need to create a new file for every change. This also works quite well with PostgreSQL's "CREATE OR REPLACE" statements.
However, since Supabase is still at PostgreSQL 13, which does NOT support CREATE OR REPALCE for triggers, I need to resort to "DROP TRIGGER IF EXISTS..." followed by "CREATE TRIGGER"
This, however, seems to be problematic, because while I can create a trigger on the auth.users table with the postgres user, I CANNOT drop the trigger with the postgres user (DROP TRIGGER requires ownership of the table whose trigger is dropped)
s
I know what this is
That's on the 
auth.users
table right
c
yep
s
Ok the solution is to elevate the postgres user to superuser from inside of the SQL editor in the supabase dashboard
But do remember to de-elevate it after you are done
There you go, I had this exact same issue.
c
great, thanks!
I checked the discussions, I guess I did a bad job at the search
s
You don't want to keep 
postgres
as a 
SUPERUSER
as it would mean if someone was to get access to it, it would allow them to delete your entire database.
c
btw, do you know when Supabase will upgrade to PostgreSQL 14? It seems that the issue there will be resolved since it will support CREATE OR REPLACE TRIGGER
s
Yeah I knew what to search since it was an issue I raised
c
yep, I will make sure to revert once I am done
s
Don't know as yet, but lets see what is announced next week for Launch Week.
c
ok, thanks again
v
An alternative approach: Create a function with the superuser, with 
SECURITY DEFINER
that does 
DROP TRIGGER
. You can call it with the postgres user, but 
SECURITY DEFINER
means it will be executed with privileges to drop the trigger
c
That's an interesting approach, I haven't thought about it, but I think the issue with it is that it still relies on a manual action - creating the function with the superuser (i.e. you need to do this outside of the regular migrations executed against the db, which are NOT executed with the superuser account). Isn't that right?
v
Yeah that's right. The benefit I guess is that you only have to do it once and then you can execute the function in your migrations
PreviousNext
Powered by