cli
cli copied to clipboard
supabase
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.
Sorry for the late reply. I can't reproduce this issue locally. Is it still happening to anyone's projects?
Yes, I do have 2 projects; this happens on both of them. Even following this guide, is not working as expected with cd/ci pipelines: https://supabase.com/docs/guides/cli/github-action/backups
Same issue here: Dumping data from remote database... pg_dump: error: connection to server at "aws-0-ap-southeast-1.pooler.supabase.com" port 6543 failed: error received from server in SCRAM exchange: Wrong password
If you are on Windows, try pasting the password with ctrl+v instead of typing https://github.com/supabase/cli/issues/1760#issuecomment-2160584536
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
I had the same issue and it turns out that when you have the URI link with [YOUR_PASSWORD] you need to delete the square brackets too! Silly error, but easy to make considering the way the whole links looks. I'd consider to remove the square brackets and just leave YOUR_PASSWORD to avoid confusion. @sweatybridge
I had the same issue and it turns out that when you have the URI link with [YOUR_PASSWORD] you need to delete the square brackets too! Silly error, but easy to make considering the way the whole links looks. I'd consider to remove the square brackets and just leave YOUR_PASSWORD to avoid confusion. @sweatybridge
I'm not using the [], but is not working stil
Even scaping I'm still getting that the door is invalid:
Run supabase db dump --db-url "$supabase_db_url" -f supabase/roles.sql --role-only
failed to parse connection string: cannot parse `******:6543/postgres`: failed to parse as URL (parse "postgresql://postgres.xxxxxx:***": invalid port ":***" after host)
Try rerunning the command with --debug to troubleshoot the error.
Error: Process completed with exit code 1.
In my case I am using SqlAlchemy and I was fighting with psycopg2 and asyncpg for a while. I eventually stopped trying asyncpg and in my main.py i still had a line trying async calls.
This worked
@app.on_event("startup")
def init_db():
with engine.connect() as connection:
Base.metadata.create_all(bind=connection)
This was causing the wrong password error:
@app.on_event("startup")
async def init_db():
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
I was facing the same issue:
- Supabase login worked
- Fresh db password
- No special character in the password
- Tried of windows & ubuntu
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.sql
I 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.sql
Then 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.
The latest CLI release no longer requires the database password for db dump.
npx supabase@latest db dump
Could you give it another try?
The latest CLI release no longer requires the database password for db dump.
npx supabase@latest db dumpCould you give it another try?
This seems to work for postgres@17 but not postgres@15. Posting here so other people can check their PG version
