cli: Supabase db dump results in wrong password error
Describe the bug supabase db dump results in error: pg_dump: error: connection to server at “aws-0-us-east-1.pooler.supabase.com” (44.216.29.125), port 5432 failed: error received from server in SCRAM exchange: Wrong password
To Reproduce Steps to reproduce the behavior:
- Run supabase link. (Link project requires the correct password to authenticate, and succeeds)
- Run supabase db dump.
- Receive error.
Expected behavior Supabase db dump should authenticate successfully.
Screenshots n/a
System information
Rerun the failing command with --create-ticket flag.
- Ticket ID: 7de07aac103942d89638f1b3d92b0cc2
- Version of OS: Microsoft Windows 11 Home 10.0.22631 N/A Build 22631
- Version of CLI: 1.167.4
- Version of Docker: 4.20.1
- Versions of services: SERVICE IMAGE │ LOCAL │ LINKED ─────────────────────────┼──────────────────────┼──────────── supabase/postgres │ 15.1.1.44 │ 15.1.1.44 supabase/gotrue │ v2.151.0 │ v2.151.0 postgrest/postgrest │ v12.0.2 │ v12.0.2 supabase/realtime │ v2.28.32 │ - supabase/storage-api │ v1.2.1 │ v1.2.1 supabase/edge-runtime │ v1.49.0 │ - supabase/studio │ 20240506-2976cd6 │ - supabase/postgres-meta │ v0.80.0 │ - supabase/logflare │ 1.4.0 │ - bitnami/pgbouncer │ 1.20.1-debian-11-r39 │ - darthsim/imgproxy │ v3.8.0 │ -
Additional context I found a post with a similar issue where the recommended advice was to reset the password, which resolved the issue for me initially (sorry I couldn’t find that post), however the same issue occurred a day later. It is not a suitable solution for us to rotate passwords every time this occurs.
If I run supabase db dump -p [password] then it works.
About this issue
- Original URL
- State: open
- Created 2 years ago
- Reactions: 1
- Comments: 28 (2 by maintainers)
Sorry for the late reply. I can’t reproduce this issue locally. Is it still happening to anyone’s projects?
I was facing the same issue:
After an hour I found a fix. Instead of using the command like this:
pg_dump --dbname="postgresql://postgres.{dbname}:[your db password without the square brackets]@aws-0-us-east-1.pooler.supabase.com:5432/postgres" > database-dump.sqlI used the command without the password in it:pg_dump --dbname="postgresql://postgres.{dbname}@aws-0-us-east-1.pooler.supabase.com:5432/postgres" > database-dump.sqlThen copy pasting the password when asked worked.
I just solved this issue with scratching my head on this for three days. I was using SqlAlcgemy and tried everything from changing passwords and what not. The problem is that supabase does not auto create your role when you use transaction pooling which is why it throws this error. For it to work, you have to create the user by SQL query. Here is what worked for me: – CREATE the pooler role do $$ begin if not exists ( select 1 from pg_roles where rolname = ‘postgres.<YOUR-PROJECT-REF>’ ) then create role “postgres.<YOUR-PROJECT-REF>” with login encrypted password ‘NEW_POOLER_PASSWORD’; else raise notice ‘Role already exists, skipping create.’; end if; end$$;
– Make sure it can connect to the database grant connect on database postgres to “postgres.<YOUR-PROJECT-REF>”;
– Give it permission to use the public schema (adjust if you use others) grant usage on schema public to “postgres.<YOUR-PROJECT-REF>”; grant all privileges on all tables in schema public to “postgres.<YOUR-PROJECT-REF>”;
Then after that, you have to run another query: – Force MD5 for this one statement set password_encryption = ‘md5’;
alter role “postgres.<PROJECT_REF>” with password ‘NEW_MD5_PASSWORD’;
This is because Postgres is expecting a SCRAM-SHA-256 password for this role, while PgBouncer (the pooler) can only present MD5 hashes. So even though you “created the role” and “set a password”, you’ve done it with the default = SCRAM format, and PgBouncer will always fail.
Doing this will def work.
Hopes this helps.
My issue was my password had
$, i just escaped the sign with\$.having the same issue on my local machine.
pg_restore: connecting to database for restore pg_restore: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 6543 failed: error received from server in SCRAM exchange: Wrong password