0

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.

Alexander Zeitler
  • 11,919
  • 11
  • 81
  • 124

3 Answers3

1

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

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
Alexander Zeitler
  • 11,919
  • 11
  • 81
  • 124
  • Typing password into shell is [considered vulnerable since it potentially reveals password in shell history](https://stackoverflow.com/a/6405296/653539). I would recommend to OP to prefer Laurenz's solution. – Tomáš Záluský Jul 07 '22 at 05:49
0

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.

Ryan McGrath
  • 2,180
  • 1
  • 11
  • 10