Can't push foreign key add to remote DB

[madx]

Bug report

Describe the bug

When using supabase db push to deploy a schema change I get the following error:

$ supabase db push
Applying unapplied migrations...
Error: ERROR: must be owner of table leaderboard (SQLSTATE 42501)

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Add a foreign key to a table and generate a migration using supabase db commit. Here's what it produced for me:

ALTER TABLE IF EXISTS public.leaderboard
  ADD CONSTRAINT "leaderboard_userId_fkey" FOREIGN KEY ("userId")
  REFERENCES public.users ("userId") MATCH SIMPLE
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

2. Try to push the schema update using supabase db push
3. See the error message above

Expected behavior

I expect the remote schema to be updated as intended.

System information

• OS: Arch Linux
• Version of supabase-cli: 0.15.14
• Version of Node.js: 17.3.0

[madx]

If someone else has the same issue a quick workaround is to use the UI to temporarily change owners then revert back:

alter table public.yourtable owner to supabase_admin;
/* Run your migrations */
alter table public.yourtable owner to postgres;

[soedirgo]

Thanks @madx - the solution is to simply run ALTER TABLE your_table OWNER TO postgres. Right now tables created using the table editor is owned by supabase_admin but we might change this in the future.

i.e. do:

alter table public.yourtable owner to postgres;
/* Run your migrations */

[sweatybridge]

We have addressed this in https://github.com/orgs/supabase/discussions/9314 so all your tables and schemas are owned by postgres role.

If that is not the case, please file a support ticket so that we can fix it for your project.

[stephaniegoldman12]

@sweatybridge Hi, I'm getting this error all of the sudden on my prod DB. Can't update any columns "failed to update pg.tables with the given ID: must be owner of table [name of table]", and ERROR: 42501: must be owner of table exports results from trying to fix it with any SQL commands to alter the table owner. Can you help?