CODEWITHSUNDEEP
postgresql
Alexander Zeitler
Alexander Zeitler

Reputation: 13109

Restoring Postgres database fails: password authentication failed for user

I tried to restore a Postgres database like this:

cat backup.sql | docker run -i postgres:12 /usr/bin/psql \
  -h mydb.elephantsql.com -U mydbanduser mydbanduser

This is the output:

Password for user mydbanduser:
psql: error: FATAL:  password authentication failed for user "mydbanduser"
FATAL:  password authentication failed for user "mydbanduser"

As can be seen, the password prompt is shown but it just steps over so I can't enter a password.

Upvotes: 0

Views: 1310

Answers (3)

Ryan McGrath
Ryan McGrath

Reputation: 2688

My issue was that I was using docker-compose and python's dotenv to pass the PGPASSWORD variable. My password has special characters in it. This caused me issues in macOS

POSTGRES_PASSWORD=123#ABC

I was putting in the above, and having everything after 123 truncated due to the pound sign.

POSTGRES_PASSWORD=`123#ABC`

Single quotes fixed my issue.

Upvotes: 0

Alexander Zeitler
Alexander Zeitler

Reputation: 13109

This solved it for me:

cat backup.sql | docker run -e PGPASSWORD=mypassword -i postgres:12 /usr/bin/psql \
  -h mydb.elephantsql.com -U mydbanduser -d mydbanduser

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247950

Create a password file in the home directory of the user that runs psql:

The file .pgpass in a user's home directory can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile). Alternatively, a password file can be specified using the connection parameter passfile or the environment variable PGPASSFILE.

This file should contain lines of the following format:

hostname:port:database:username:password

Upvotes: 1

Related Questions