Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

psql or pg_dump still asks for a password although ~/.pgpass exists

ErrorPassword:

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.

  • Applies to PostgreSQL 16, PostgreSQL 17, Ubuntu 24.04, Windows 11
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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"
  2. 2Match what you see, then jump to its fix

    1. 1ls -l shows more than -rw------- and the password is asked every timeThe file is readable by group or othersFix
    2. 2The 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. 3The wrong password is sent, or the password contains : or \An earlier wildcard line matches first, or a character is not escapedFix
    4. 4It 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

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' ~/.pgpass

2Fix

Make the file private to its owner.

chmod 0600 ~/.pgpass

3Verify

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

Did this fix it?

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:secret

3Verify

Run the same command again with -w.

psql -w -h db.example.com -U alice mydb -c 'select 1'

Confirmed by postgresql.org

Did this fix it?

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 ~/.pgpass

2Fix

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-password

3Verify

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

Did this fix it?

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.sql

3Verify

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.