Supabase•2d ago

Has the `supabase db pull` changed?

My workflow the last 6 months was to make changes on staging branch of the database, pull the changes localy so I can then push it to github. This allowed me to make quick changes for frontend developer and it was working perfectly until last week when the pull command started to act differently. Now when I pull I usually get this or similar error:

error diffing schema: error running container: exit 1:
[select_097e65a]: Executing query failed: SASL: SCRAM-SERVER-FINAL-MESSAGE: server signature is missing
main worker has been destroyed

error diffing schema: error running container: exit 1:
[select_097e65a]: Executing query failed: SASL: SCRAM-SERVER-FINAL-MESSAGE: server signature is missing
main worker has been destroyed

I relink the project with the correct project ref and password and the error usually goes away for some time. But the pull is still not pulling the correct stuff, I made one RLS change and when I pull, I get 1000 line migration which repeats for infinite pull commands. This happened now on three separate projects that all have branches enabled, I don't know if this is important.

17 Replies

TimmyOP•2d ago

For one of the projects I started getting this error on supabase db diff:

...
WARNING (01006): no privileges could be revoked for column "updated_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_confirmed_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_change" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_change_token" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_change_sent_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "confirmed_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "email_change_token_current" of relation "users"
WARNING (01006): no privileges could be revoked for column "email_change_confirm_status" of relation "users"
WARNING (01006): no privileges could be revoked for column "banned_until" of relation "users"
WARNING (01006): no privileges could be revoked for column "reauthentication_token" of relation "users"
WARNING (01006): no privileges could be revoked for column "reauthentication_sent_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "is_sso_user" of relation "users"
WARNING (01006): no privileges could be revoked for column "deleted_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "is_anonymous" of relation "users"
ERROR: permission denied for schema auth (SQLSTATE 42501)
At statement: 876
CREATE OR REPLACE FUNCTION auth.email()    
 RETURNS text  
 LANGUAGE sql
 STABLE 
AS $function$
  select
  coalesce(
    nullif(current_setting('request.jwt.claim.email', true), ''),               
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
  )::text
$function$

...
WARNING (01006): no privileges could be revoked for column "updated_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_confirmed_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_change" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_change_token" of relation "users"
WARNING (01006): no privileges could be revoked for column "phone_change_sent_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "confirmed_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "email_change_token_current" of relation "users"
WARNING (01006): no privileges could be revoked for column "email_change_confirm_status" of relation "users"
WARNING (01006): no privileges could be revoked for column "banned_until" of relation "users"
WARNING (01006): no privileges could be revoked for column "reauthentication_token" of relation "users"
WARNING (01006): no privileges could be revoked for column "reauthentication_sent_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "is_sso_user" of relation "users"
WARNING (01006): no privileges could be revoked for column "deleted_at" of relation "users"
WARNING (01006): no privileges could be revoked for column "is_anonymous" of relation "users"
ERROR: permission denied for schema auth (SQLSTATE 42501)
At statement: 876
CREATE OR REPLACE FUNCTION auth.email()    
 RETURNS text  
 LANGUAGE sql
 STABLE 
AS $function$
  select
  coalesce(
    nullif(current_setting('request.jwt.claim.email', true), ''),               
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
  )::text
$function$

silentworks•2d ago

Have you upgraded your version of the CLI since the last time it was working ok for you? One of the issues I see here is the change Supabase made to schemas that they create auth, storage and others where you are no longer allowed to modify them. It would seem your diff is trying to alter the users table which is inside of the auth schema and is no longer allowed.

TimmyOP•2d ago

yes I know that there was least one update of CLI since this was working the problem is that the database does not have users table... this really looks like CLI is connected to the wrong project but I checked and relinked multiple times

silentworks•2d ago

The database has a auth.users table which is by default in every Supabase project. This is what it seems to be complaining about.

TimmyOP•2d ago

oh yes I forgot about this table so if I understand correctly, I should grant usage of auth to service role, but I don't know how the service role lost this premission

silentworks•2d ago

No you aren't allowed to mess with the auth schema anymore. So you will need to check your diff and see what action it's trying to perform on that schema and remove it from the diff'd file.

TimmyOP•2d ago

Hmm there is a lot of REVOKE calls, but I don't know what happened Is there a way to delete all migrations and create just one from the live database? And will this complicate things when trying to merge staging branch into master (there is 5 migrations)

silentworks•2d ago

This will complicate things when merging staging branch into master. There is a squash feature which was supposed to sort this out but I've seen users reporting that it doesn't work well.

TimmyOP•2d ago

I squashed some migrations before branching this specific project, maybe this caused some of the problems we are currently not even using the master branch so maybe it will be the best to just clear all migrations because we don't care if the data is deleted from master?

silentworks•2d ago

Isn't the master branch your production branch?

TimmyOP•2d ago

yes but we are currently using only staging and non persistent branches for development. next week we will start the process of deploying the app to app store so that is when we will use master there is currently no data on master, just one migration that was squashed right before I created master

silentworks•2d ago

I think this is going to be your call as you know your setup better than I do.

TimmyOP•2d ago

what is the best way to do the change, i just delete all migrations from the local migrations directory and run supabase db pull?

Madnex•2d ago

sorry for hijacking here but i have a very similar issue. my las migration was half a year ago. now i upgraded supabase cli did a supabase db pull and i see 2000 lines of a migration file. most of it are revoke statements. i did some comparison and running this migration file locally would result in a db state that is different to the production db w.r.t. the roles. while before the migration it is in sync with the roles. is there a way to reset this properly? tried that but then it is out of sync with the remote migration history. not sure if it is possible / wise to delete the remote migration history as well..

TimmyOP•2d ago

when it is out of sync it should suggest to you some supabase migrations repair commands which should remove or apply migration to remote migration history. but I only tried this on one or two migrations not when I deleted all of them in my case I think I want to do this but I don't know what will happen when I try to merge those migrations into master (can the migrations from master be deleted while merging)

silentworks•2d ago

I cannot advice you on his as I don't use branching at all. I stick to good old practice of creating a project per environment.

TimmyOP•2d ago

okay, I'll try to figure this out myself, thank you very much for your help!

Gaming

Programming

Has the `supabase db pull` changed?

Did you find this page helpful?