asked on

psql not taking password from pgpass file

I ran the command - strace psql -U postgres -h db -U seenter -d seenter
It gave the following output :

stat("/cs/home/seenter/.pgpass", {st_mode=S_IFREG|0600, st_size=46, ...}) = 0
open("/cs/home/seenter/.pgpass", O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0600, st_size=46, ...}) = 0
mmap(NULL, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f5705186000
read(3, "db.eecs.yorku.ca:5432:senter:sen"..., 8192) = 46
read(3, "", 8192)

Open in new window

related to pgpass. So it's checking the entry for .pgpass but still asks for password.
Not sure why. If I put db:5432:senter... it still asks for the password.
doing a ping of db shows that it's connecting to the server db.eecs.yorku.ca
what could be the reason for this ?

Could it be that the psql server is running on a different port than 5432 ? But when I am specifying the psql command and enter the password it does connect to the psql server without me specifying anything in port.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

David Favor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

Could it be that the psql server is running on a different port than 5432 ? But when I am specifying the psql command and enter the password it does connect to the psql server without me specifying anything in port.

You just answered the port question and no - it can't run on different port and ask for password - you'd get different message if that was the case. Maybe this happens because of some setting in pg_hba.conf that forces password authentication. I think you can do that if following lines are in the hba.conf

# for users connected via local IPv4 or IPv6 connections, always require md5 
host    all   all        127.0.0.1/32          md5 
host    all   all        ::1/128               md5

Open in new window

Or maybe the permissions on .pgpass file are not properly set. I believe they must be set to have the world and group access disallowed

lcohan

Below is a checklist to make sure the pgpass file will be used:

Make sure the flags --password/-W and password= 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); otherwise psql 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); otherwise psql 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 the host name if non-empty, or the hostaddr ip address. Otherwise, the line will be ignored without warning.

Rohit Bajaj

ASKER

My bad the .pgpass file was incorrect. I missed a character

David Favor

Ah... it's always the little things...

Glad you found the problem!