psql or pg_dump still asks for a password although ~/.pgpass exists
Password:Short answer
libpq reads the password file only when its rules are met. On Unix the file must be mode 0600, the line must be hostname:port:database:username:password, and the host field must equal the host you connect with. The first matching line wins. A cron job or another user may also look in a different home directory.
Fix it fast
1Find out which cause you have
Print the file's mode and the line you expect to match, and note exactly how you pass the host on the command line. Each cause below compares one of those facts with the libpq rules.
ls -l ~/.pgpass cat ~/.pgpass echo "HOME=$HOME PGPASSFILE=$PGPASSFILE"2Match what you see, then jump to its fix
- 1
ls -l shows more than -rw------- and the password is asked every timeThe file is readable by group or othersFix - 2
The password prompt appears only for some commands, for example 127.0.0.1 in the file but -h localhost on the command lineThe host in the file is not the host you connect withFix - 3
The wrong password is sent, or the password contains : or \An earlier wildcard line matches first, or a character is not escapedFix - 4
It works in your shell but not from cron, a systemd service or another accountThe process looks in another home directory, or on Windows in another placeFix
- 1
What does the error look like?
Password:The password prompt appears on every run although ~/.pgpass holds the right line
Cause 1 of 4
The file is readable by group or others
1Check
On Unix libpq ignores the file when it grants any access to group or world.
stat -c '%a %n' ~/.pgpass2Fix
Make the file private to its owner.
chmod 0600 ~/.pgpass3Verify
Connect without typing a password. -w makes psql fail instead of prompting.
psql -w -h localhost -U alice mydb -c 'select 1'Confirmed by postgresql.org
Cause 2 of 4
The host in the file is not the host you connect with
1Check
The first field is compared with the host parameter. If you pass no host, the name localhost is searched. localhost is also searched for a Unix-socket connection that uses libpq's default socket directory. A different name or an IP address for the same machine does not match.
grep -v '^#' ~/.pgpass
psql -w -h 127.0.0.1 -U alice mydb -c 'select 1'2Fix
Write the file line with the same host string you use in the connection, or use * in the host field. Wildcards are allowed in the first four fields.
# hostname:port:database:username:password
db.example.com:5432:mydb:alice:secret
*:5432:*:alice:secret3Verify
Run the same command again with -w.
psql -w -h db.example.com -U alice mydb -c 'select 1'Confirmed by postgresql.org
Cause 3 of 4
An earlier wildcard line matches first, or a character is not escaped
1Check
libpq uses the password from the first line that matches. A broad line near the top wins over a specific line below it. Inside a field, a colon or backslash must be written with a backslash in front.
cat -n ~/.pgpass2Fix
Put specific lines above wildcard lines, and escape : and \ in the password.
# specific first
localhost:5432:mydb:alice:pa\:ss\\word
# wildcard after
*:*:*:alice:other-password3Verify
Connect and check that the session user is the one you expect.
psql -w -h localhost -U alice mydb -c 'select current_user'Confirmed by postgresql.org
Cause 4 of 4
The process looks in another home directory, or on Windows in another place
1Check
On Unix the file is ~/.pgpass, taken from HOME, or from the effective user's home when HOME is unset. On Windows it is %APPDATA%\postgresql\pgpass.conf. The PGPASSFILE variable or the passfile connection parameter override both.
# inside the failing job
echo "$HOME"; id -un
echo "$PGPASSFILE"2Fix
Point the job at the file explicitly.
export PGPASSFILE=/home/alice/.pgpass
pg_dump -h localhost -U alice -w mydb > mydb.sql3Verify
Run the job once by hand as the same user and confirm no prompt appears.
sudo -u alice env PGPASSFILE=/home/alice/.pgpass psql -w -h localhost -U alice mydb -c 'select 1'Confirmed by postgresql.org
How do I stop it coming back?
- Create the file with chmod 0600 in the same script that writes the password.
- Use one host spelling everywhere: scripts, connection strings and the password file.
- In cron and systemd units, set PGPASSFILE instead of relying on HOME.
- Run scripts with -w so a missing password fails loudly instead of waiting at a prompt.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fixDatabases
manage.py test stops with 'permission denied to create database' on PostgreSQL
django.db.utils.ProgrammingError: permission denied to create database
Django creates a separate database called test_ plus your database name, using the DATABASES USER.
2 causes, each with a fixDatabases
An EC2 instance cannot reach its RDS database
Could not open connection to the host, on port <port>: Connect failed
RDS denies all access until a security group allows it.
3 causes, each with a fixDatabases
IntelliJ IDEA Test Connection fails for a PostgreSQL data source
Connection to localhost:5432 refused.
When Test Connection fails, work from the bottom up.
4 causes, each with a fixDatabases
pg_dump refuses to run: server version is newer than pg_dump
pg_dump: detail: server version: 17.2; pg_dump version: 14.13
pg_dump will not dump a server whose major version is newer than its own.
3 causes, each with a fix