Topics

Supabase•5w ago

Salem Yaslem سالم يسلم

Cannot connect to database using supervisor port (5432) after change password

Hello, I built my own solution based on Supabase and it great success (~50 customer for now) and one day ... I was streaming and I exposed --db-url=PSQL_DSN by mistake and to fix that issue I changed password for many postgresql users (following this comment https://github.com/supabase/supabase/issues/22605#issuecomment-2455781878) somehow after setting the new password to .env of supabase and restart the app back to normal but now I cannot anymore connect to database ( postgresql://<user>.<tenant>:<password>@<ip>:5432/postgres) with new or old password to supervisor default port connecting to supervisor using PSQL DSN is important for me so I can push local changes to my production database 🙁 regards,

34 Replies

Salem Yaslem سالم يسلمOP•5w ago

I'm getting

psql: error: connection to server at "<ip>", port 5432 failed: FATAL:  Authentication error, reason: "Authentication query failed: %DBConnection.ConnectionError{message: \"connection not available and request was dropped from queue after 14999ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:\\n\\n  1. Ensuring your database is available and that you can connect to it\\n  2. Tracking down slow queries and making sure they are running fast enough\\n  3. Increasing the pool_size (although this increases resource consumption)\\n  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval\\n\\nSee DBConnection.start_link/2 for more information\\n\", severity: :error, reason: :queue_timeout}"

psql: error: connection to server at "<ip>", port 5432 failed: FATAL:  Authentication error, reason: "Authentication query failed: %DBConnection.ConnectionError{message: \"connection not available and request was dropped from queue after 14999ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:\\n\\n  1. Ensuring your database is available and that you can connect to it\\n  2. Tracking down slow queries and making sure they are running fast enough\\n  3. Increasing the pool_size (although this increases resource consumption)\\n  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval\\n\\nSee DBConnection.start_link/2 for more information\\n\", severity: :error, reason: :queue_timeout}"

inside supervisor container I get

16:28:24.126 region=local [error] Postgrex.Protocol (#PID<0.3621.0>) failed to connect: ** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "pgbouncer"

16:28:24.126 region=local [error] Postgrex.Protocol (#PID<0.3621.0>) failed to connect: ** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "pgbouncer"

but I already changed password for user pgbouncer

ihm40•5w ago

should the .env be where the password is stored for database connections? what is the command you are runnning

Salem Yaslem سالم يسلمOP•5w ago

yes, the variable POSTGRES_PASSWORD is there you mean the command for password change?

ALTER USER supabase_admin WITH PASSWORD '[REDACTED]';
ALTER USER postgres WITH PASSWORD '[REDACTED]';
ALTER USER authenticator WITH PASSWORD '[REDACTED]';
ALTER USER pgbouncer WITH PASSWORD '[REDACTED]';
ALTER USER supabase_auth_admin WITH PASSWORD '[REDACTED]';
ALTER USER supabase_functions_admin WITH PASSWORD '[REDACTED]';
ALTER USER supabase_storage_admin WITH PASSWORD '[REDACTED]';

ALTER USER supabase_admin WITH PASSWORD '[REDACTED]';
ALTER USER postgres WITH PASSWORD '[REDACTED]';
ALTER USER authenticator WITH PASSWORD '[REDACTED]';
ALTER USER pgbouncer WITH PASSWORD '[REDACTED]';
ALTER USER supabase_auth_admin WITH PASSWORD '[REDACTED]';
ALTER USER supabase_functions_admin WITH PASSWORD '[REDACTED]';
ALTER USER supabase_storage_admin WITH PASSWORD '[REDACTED]';

ihm40•5w ago

hmm i'm not sure altering the users like that is a good idea. What guide were you following? are you trying to connect to local or remote database?

Salem Yaslem سالم يسلمOP•5w ago

remote database self-hosted in vm

Salem Yaslem سالم يسلمOP•5w ago

I didn't find any offical guide and just followed: https://github.com/supabase/supabase/issues/22605#issuecomment-2455781878

GitHub

Also Add Password Changing Guidelines · Issue #22605 · supabase/s...

Improve documentation Link https://supabase.com/docs/guides/self-hosting/docker Describe the problem When password is changed like for postgres in .env , It causes an error when started again docke...

ihm40•5w ago

i'm going to try and take a look at this and get back to you though i'm not too experienced with self hosting supabase

inder•5w ago

Are the containers healthy after you made this change and recreated the instance

Salem Yaslem سالم يسلمOP•5w ago

yes, they are healthy

[REDACTED]  supabase/supavisor:2.5.1  "/usr/bin/tini -s -g…"  43 hours ago  Up 20 minutes (healthy)  0.0.0.0:5432->5432/tcp, [::]:5432->5432/tcp, 0.0.0.0:6543->6543/tcp, [::]:6543->6543/tcp                                [REDACTED]-supabase-pooler

[REDACTED]  supabase/supavisor:2.5.1  "/usr/bin/tini -s -g…"  43 hours ago  Up 20 minutes (healthy)  0.0.0.0:5432->5432/tcp, [::]:5432->5432/tcp, 0.0.0.0:6543->6543/tcp, [::]:6543->6543/tcp                                [REDACTED]-supabase-pooler

` the error only happen for supervisor/pooler

Salem Yaslem سالم يسلمOP•5w ago

the application is up and working: https://suhol.ai

suhol.ai - سهول

حول أفكارك إلى مشاريع منظمة ومهام قابلة للتنفيذ مع أداة تحليل المشاريع المدعومة بالذكاء الاصطناعي. واجهة بسيطة وسهلة الاستخدام.

inder•5w ago

Also the db?

Salem Yaslem سالم يسلمOP•5w ago

yes, it working and accept all requests

inder•5w ago

And you recreated containers after updating password?

Salem Yaslem سالم يسلمOP•5w ago

yes, all of container restarted

Salem Yaslem سالم يسلمOP•5w ago

I got only this error when try to connect to database with old/new password:

No description

inder•5w ago

I remember this guide working last time I had to update password. I'll test and get back to you. Just to be sure you took down the containers using docker compose down and then ran up again?

Salem Yaslem سالم يسلمOP•5w ago

it was just restart and I just tried down and up the container and still same issue

inder•5w ago

Ok I can reproduce the issue and I don't have a proper solution but a workaround. This seems to be an issue with supavisor. - If I revert back to old password, then I can login again. But as you have exposed the password, this won't be of any help to you - If I connect directly to db without supavisor in between then I can login to db

Salem Yaslem سالم يسلمOP•5w ago

I cannot use both workarounds because as you said first solution is to use already exposed password which is insecure second solution will not work with supabase cli

inder•5w ago

it will you can use --db-url flag

Salem Yaslem سالم يسلمOP•5w ago

yes, I'm using it but the database is exposed over supervisor or do you mean open port 5432 from database directly?

inder•5w ago

exactly. supavisor too is using 5432. just comment out that line

No description

inder•5w ago

No description

Salem Yaslem سالم يسلمOP•5w ago

I don't think that is safe anyway, why supervisor behave like that?

inder•5w ago

If you were running migrations before, then you were already using port 5432 as port 6543 is transaction mode don't know, this used to work before. maybe some version update messed up something One other workaround but it involves downtime backup and restore into a new instance

Salem Yaslem سالم يسلمOP•5w ago

@inder that more advance even for me xD

inder•5w ago

I'm looking into this issue but other than these workarounds I don't have any solution currently

Salem Yaslem سالم يسلمOP•5w ago

@inder did you reproduce the issue in latest versions of supavisor?

inder•4w ago

I've tried with version 2.5.1 to 2.6.2 no success @Salem Yaslem سالم يسلم I finally have a solution https://github.com/supabase/supabase/issues/22605#issuecomment-3323382144

Salem Yaslem سالم يسلمOP•4w ago

Thank you, i will try it @inder I just tried what you propose in your comment and still same issue

inder•4w ago

walk me through the steps or even better run this script https://github.com/singh-inder/supabase-automated-self-host/blob/main/docker/update-db-pass.sh add it inside docker directory

Salem Yaslem سالم يسلمOP•4w ago

nvm, it works now! I just need to connect to database _supabase instead of postgres thank you

inder•4w ago

No problem. This was actually a bug. Thanks for opening the issue In the future you can use the script to automate this

Salem Yaslem سالم يسلمOP•4w ago

yes I will do that, thank you a lot

Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.

42KMembers

View on Discord

Did you find this page helpful?