postgresql: .pgpass not working
BashPostgresqlDatabase BackupsBash Problem Overview
I have created a .pgpass
file in my home directory which looks like this
localhost:5432:somedb:someuser:somepass
I am using a shell script which creates a directory and puts a pg_dump of somedb
there :
mkdir directory
pg_dump somedb > directory/somefile.dump
It still prompts for the password.
Where is the mistake here ?
Bash Solutions
Solution 1 - Bash
Did you try specifying the host, user, & db?
pg_dump -U someuser -h localhost somedb > directory/somefile.dump
Solution 2 - Bash
- Create .pgpass file with content
> host:5432:somedb:someuser:somepass
- set the permissions
> sudo chmod 600 .pgpass
- Set the file owner as the same user using which you logged in :
> sudo chown login_username:login_username .pgpass
- Set PGPASSFILE environment variable :
> export PGPASSFILE='/home/user/.pgpass'
Now check by connecting to database :
> psql -h host -U someuser somedb
It did not prompt for a password, and logged-in to postgresql.
Solution 3 - Bash
Although question has already been answered and accepted, it may also happen that permissions on .pgpass file are not properly set. It has to have the world and group access disallowed:
/bin/chmod 0600 ~/.pgpass
Solution 4 - Bash
psql
(startup.c) calls PQconnectdbParams
(fe-connect.c), and then passwordFromFile
is called. Here’s a checklist to make sure the pgpass file will be used:
- Make sure the flags
--password
/-W
andpassword=
in the connection string are unset. Otherwise, the pgpass file will not be used. - Make sure the environment variable
PGPASSWORD
is unset (echo $PGPASSWORD
). Otherwise, the pgpass file will not be used. - Make sure the pgpass file is in the right location (
$PGPASSFILE
or default~/.pgpass
or%APPDATA%\postgresql\pgpass.conf
) - Make sure the passfile is not readable, writable, or executable by group or other (e.g.
chmod 600 ~/.pgpass
); otherwisepsql
will print a warning. - Make sure the passfile is a file (not a symlink); otherwise
psql
will print a warning. - Make sure the passfile is readable by the
psql
user (e.g.cat ~/.pgpass
); otherwisepsql
will ignore it without warning. Make sure that it is owned by the user, and that all its ancestor directories are executable by the user. - Make sure that the pgpass file has the correct format
hostname:port:database:username:password
(The Password File,passwordFromFile
). Each field (other than password) can be*
. The characters:
and\
must be escaped\:
and\\
(even in password). The password ends at:
or the end of the line and can include any byte other than\r
,\n
, or\0
. Any lines that aren’t formatted right or don’t match the host and user are ignored without warning as if they were comments. - Make sure the hostname, port, dbname, username of the line in the pgpass file match the server or are
*
. The server’s “pwhost” that is checked is thehost
name if non-empty, or thehostaddr
ip address. Otherwise, the line will be ignored without warning.
Unfortunately, there is no logging within these files, so if this doesn’t help, then you may need to compile psql
and libpq
yourself and run it in a debugger.
Solution 5 - Bash
Check the owner of the .pgpass file. Just lost half an hour to find out I had created my .pgpass file with sudo. The command for my user and location was chown postgres:postgres /var/lib/pgsql/.pgpass
.
Solution 6 - Bash
In my case, it was I already set PGPASSWORD environment variable (to a wrong password), so psql
was picking that instead of what's inside .pgpass
Solution 7 - Bash
If your password happens to include a colon, you have to escape it by preceding it with a \ in order for the password to be recognised.