Unable to connect to Postgres DB (Postgres-sUUI) - Password Authentication Failed & Cannot Reset Password

andriyfit
HOBBYOP

a month ago

Hello, I am unable to connect to my PostgreSQL database service named "Postgres-sUUI" from an external client (DBeaver). Error Message: The error I consistently receive is: FATAL: password authentication failed for user "railway". Troubleshooting Steps I've Already Taken: 1. I confirmed the service "Postgres-sUUI" is deployed and online. 2. I confirmed my data volume "postgres-f42a-volume" is correctly attached to this service. 3. I am using the correct public TCP proxy address to connect: tramway.proxy.rlwy.net:20830. The connection reaches the server, but authentication fails. 4. I have carefully re-copied the PGUSER ("railway") and PGPASSWORD from the 'Variables' tab multiple times, but the error persists. 5. I then tried to regenerate the password. However, I cannot find the button or panel to do this. I have looked in the "Settings", "Variables", and "Database" tabs for the "Postgres-sUUI" service. 6. A warning message in the UI previously told me to "Use the Credentials panel in the Data tab", but I cannot locate this panel on the page. My Request: Could you please help me safely regenerate the password for the "Postgres-sUUI" service? Or show me where the "Credentials panel" is located if I am missing it. My main goal is to regain access to my data. Thank you!

$10 Bounty
+11

3 Replies

brody
EMPLOYEE

a month ago

This thread has been marked as public for community involvement, as it does not contain any sensitive or personal information. Any further activity in this thread will be visible to everyone.

Status changed to Open brody • about 1 month ago

dalinkstone
FREE

a month ago

I found the prompt you mentioned by doing the following: Navigate to your project > Variables > Click the vertical ellipsis menu for the 'PGPASSWORD' variable > Click 'Edit' > Click on the 'Recommended Solution' which links to the 'Database' Menu > Click 'Credentials' underneath the 'Database' option you previously selected > Click 'Regenerate Password'

You could also try editing the 'PGUSER' variable to be a different name. Try attempting this first and if that doesn't work then perhaps there are new errors.

samgordon
MODERATOR

a month ago

Hey! I'm going to just paste in my instructions for resetting your Postgres password. It's a pretty long process. I'd also like to add that the default PGUSER is postgres, not railway. You likely need to change this back.

Before you do any of this, disable all public access domains. Just in case.

⠀

  1. Install the Railway CLI. You can do so here: https://docs.railway.com/guides/cli

  2. Copy the SSH command by right clicking the PostgreSQL service

  3. Once you're in, type in the following command: (Copy paste this EXACTLY as written.)

    sed -i 's/host all all all scram-sha-256/host all all ::\/0 trust/' /var/lib/postgresql/data/pgdata/pg_hba.conf

  4. Redeploy your database.

  5. Use the SSH command again, and once you're in, type psql.

  6. Type the following SQL command. my_password should be replaced with whatever it actually says in your PGPASSWORD variable is set to.

    ALTER USER postgres with password 'my_password';

  7. Type exit

  8. Reset your authentication settings with the following command:

    sed -i 's/host all all ::\/0 trust/host all all all scram-sha-256/' /var/lib/postgresql/data/pgdata/pg_hba.conf

  9. Redeploy your database once again. You have now changed the password.

⠀

Let me know if this works!

Loading...