BUG #13188: .pgpass does not work

On Tuesday, April 28, 2015, <bzb.dev001@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 13188
Logged by: wt
Email address: bzb.dev001@gmail.com <javascript:;>
PostgreSQL version: 9.4.1
Operating system: linux
Description:

Tried to use ~/.pgpass file to automatically specify the password when I
use
the psql console app. according to the info from "PostgreSQL 9.4.0
Documentation", Section 31.15. The Password File.

Postgresql is configured to use md5 for login.

file: pg_hba.conf
-----------------
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5

file: ~/.pgpass
---------------
#hostname : port : database : username : password
* : 5432 : * : postgres : somepassword

When I exec psql without specifying the password, I cannot login...

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

Best guess you don't have the correct permissions set.

http://www.postgresql.org/docs/devel/static/libpq-pgpass.html

Last paragraph.

I would also remove the padding white space. The syntax is documented
without and while the documentation is non-specific I've personally always
avoided it and have never had a problem.

David J.

On 2015-04-29 01:06 AM, David G. Johnston wrote:

On Tuesday, April 28, 2015, <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>> wrote:

The following bug has been logged on the website:

Bug reference: 13188
Logged by: wt
Email address: bzb.dev001@gmail.com <javascript:;>
PostgreSQL version: 9.4.1
Operating system: linux
Description:

Tried to use ~/.pgpass file to automatically specify the password
when I use
the psql console app. according to the info from "PostgreSQL 9.4.0
Documentation", Section 31.15. The Password File.

Postgresql is configured to use md5 for login.

file: pg_hba.conf
-----------------
local all all md5
host all all 127.0.0.1/32 <http://127.0.0.1/32&gt;
md5
host all all ::1/128 md5

file: ~/.pgpass
---------------
#hostname : port : database : username : password
* : 5432 : * : postgres : somepassword

When I exec psql without specifying the password, I cannot login...

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

Best guess you don't have the correct permissions set.

http://www.postgresql.org/docs/devel/static/libpq-pgpass.html

Last paragraph.

I would also remove the padding white space. The syntax is documented
without and while the documentation is non-specific I've personally
always avoided it and have never had a problem.

David J.

The permission settings for .pgpass is correct. I have even changed the
ownership of the file to postgres:postgres since I'm logged on as
another user and .pgpass is located in this user's home directory. Yet
it still does not work.

$ ls -l .pgpass
-rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com>
wrote:

On 2015-04-29 01:06 AM, David G. Johnston wrote:

On Tuesday, April 28, 2015, <bzb.dev001@gmail.com> wrote:

file: ~/.pgpass
---------------
#hostname : port : database : username : password
* : 5432 : * : postgres : somepassword

When I exec psql without specifying the password, I cannot login...

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

I would also remove the padding white space. The syntax is documented
without and while the documentation is non-specific I've personally always
avoided it and have never had a problem.

​And excessive whitespace?

Have you confirmed that you are able to connect by supplying the password
manually?

David J.​

On 2015-04-29 11:50 AM, David G. Johnston wrote:

On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>>wrote:

On 2015-04-29 01:06 AM, David G. Johnston wrote:

On Tuesday, April 28, 2015, <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>> wrote:

file: ~/.pgpass
---------------
#hostname : port : database : username : password
* : 5432 : * : postgres : somepassword

When I exec psql without specifying the password, I cannot
login...

$ psql --host=localhost --port=5432 --username=postgres
--no-password
psql: fe_sendauth: no password supplied

I would also remove the padding white space. The syntax is
documented without and while the documentation is non-specific
I've personally always avoided it and have never had a problem.

​ And excessive whitespace?

Have you confirmed that you are able to connect by supplying the
password manually?

David J.​

Yes, I have removed the spaces in the .pgpass file.
Yes, I can also connect to the postgresql server using a password.
I was wondering. I have configured postgresql server to use md5 as
specified in the pg_hba.conf below. Do you think that I need to put a
md5 encrypted string of my password in the .pgpass file or do I simply
put my password in clear text?

local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5

On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com>
wrote:
I have even changed the ownership of the file to postgres:postgres since
I'm logged on as another user and .pgpass is located in this user's home
directory. Yet it still does not work.

$ ls -l .pgpass
-rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass

​Sorry, I missed this the first time reading. This is your problem. The
"postgres" Linux user and the "postgres" PostgreSQL user are not related to
each other - particularly when using md5 authentication. The permissions
on the .pgpass file must be of the user executing "psql" - which you've
indicated is not "postgres".

So, likely your first attempt failed because you had whitespace. You
changed the permissions and now have two problems. You then fix the
whitespace and are back to a single problem. Change the permissions back
and it should now work.

David J.

​

On 2015-04-29 12:04 PM, David G. Johnston wrote:

On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>> wrote:
I have even changed the ownership of the file to postgres:postgres
since I'm logged on as another user and .pgpass is located in this
user's home directory. Yet it still does not work.

$ ls -l .pgpass
-rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass

​Sorry, I missed this the first time reading. This is your problem.
The "postgres" Linux user and the "postgres" PostgreSQL user are not
related to each other - particularly when using md5 authentication.
The permissions on the .pgpass file must be of the user executing
"psql" - which you've indicated is not "postgres".

So, likely your first attempt failed because you had whitespace. You
changed the permissions and now have two problems. You then fix the
whitespace and are back to a single problem. Change the permissions
back and it should now work.

David J.

​

I've changed back the owner of .pgpass to the account that I've logged
in as. This is the account that I'm using to run psql.
All the spaces in .pgpass is removed.
Checked that the permission settings of .pgpass is ok.
restarted the server...

$ sudo service postgresql restart

Unfortunately, it is still not working.

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

Incidentally, the postgresql server is running as 'postgres' user.

On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com

wrote:

On 2015-04-29 12:04 PM, David G. Johnston wrote:

On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com <
bzb.dev001@gmail.com> wrote:
I have even changed the ownership of the file to postgres:postgres since
I'm logged on as another user and .pgpass is located in this user's home
directory. Yet it still does not work.

$ ls -l .pgpass
-rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass

​Sorry, I missed this the first time reading. This is your problem.
The "postgres" Linux user and the "postgres" PostgreSQL user are not
related to each other - particularly when using md5 authentication. The
permissions on the .pgpass file must be of the user executing "psql" -
which you've indicated is not "postgres".

So, likely your first attempt failed because you had whitespace. You
changed the permissions and now have two problems. You then fix the
whitespace and are back to a single problem. Change the permissions back
and it should now work.

I've changed back the owner of .pgpass to the account that I've logged in
as. This is the account that I'm using to run psql.
All the spaces in .pgpass is removed.
Checked that the permission settings of .pgpass is ok.
restarted the server...

$ sudo service postgresql restart

Unfortunately, it is still not working.

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

Incidentally, the postgresql server is running as 'postgres' user.

​I am out of ideas. I am not currently running 9.4.x but if the problem
was a bug within PostgreSQL there would be more than a single report.

David J.

On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com

wrote:

I've changed back the owner of .pgpass to the account that I've logged in
as. This is the account that I'm using to run psql.
All the spaces in .pgpass is removed.
Checked that the permission settings of .pgpass is ok.
restarted the server...

$ sudo service postgresql restart

Unfortunately, it is still not working.

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

I wonder if it is even finding the file in the first place.

strace psql --host=localhost --port=5432 --username=postgres
--no-password 2>&1 | fgrep pgpass

This is what I get:

stat("/home/jjanes/.pgpass", {st_mode=S_IFREG|0600, st_size=196, ...}) = 0
open("/home/jjanes/.pgpass", O_RDONLY) = 3

For me it found the file and opened it successfully.

Cheers,

Jeff

On 2015-04-29 03:32 PM, David G. Johnston wrote:

On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>>wrote:

On 2015-04-29 12:04 PM, David G. Johnston wrote:

On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>> wrote:
I have even changed the ownership of the file to
postgres:postgres since I'm logged on as another user and .pgpass
is located in this user's home directory. Yet it still does not
work.

$ ls -l .pgpass
-rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass

​ Sorry, I missed this the first time reading. This is your
problem. The "postgres" Linux user and the "postgres" PostgreSQL
user are not related to each other - particularly when using md5
authentication. The permissions on the .pgpass file must be of
the user executing "psql" - which you've indicated is not "postgres".

So, likely your first attempt failed because you had whitespace.
You changed the permissions and now have two problems. You then
fix the whitespace and are back to a single problem. Change the
permissions back and it should now work.

I've changed back the owner of .pgpass to the account that I've
logged in as. This is the account that I'm using to run psql.
All the spaces in .pgpass is removed.
Checked that the permission settings of .pgpass is ok.
restarted the server...

$ sudo service postgresql restart

Unfortunately, it is still not working.

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

Incidentally, the postgresql server is running as 'postgres' user.

​I am out of ideas. I am not currently running 9.4.x but if the
problem was a bug within PostgreSQL there would be more than a single
report.

David J.

No problem.
Thanks for you help.

On 2015-04-29 04:41 PM, Jeff Janes wrote:

On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com
<mailto:bzb.dev001@gmail.com>> wrote:

I've changed back the owner of .pgpass to the account that I've
logged in as. This is the account that I'm using to run psql.
All the spaces in .pgpass is removed.
Checked that the permission settings of .pgpass is ok.
restarted the server...

$ sudo service postgresql restart

Unfortunately, it is still not working.

$ psql --host=localhost --port=5432 --username=postgres --no-password
psql: fe_sendauth: no password supplied

I wonder if it is even finding the file in the first place.

strace psql --host=localhost --port=5432 --username=postgres
--no-password 2>&1 | fgrep pgpass

This is what I get:

stat("/home/jjanes/.pgpass", {st_mode=S_IFREG|0600, st_size=196, ...}) = 0
open("/home/jjanes/.pgpass", O_RDONLY) = 3

For me it found the file and opened it successfully.

Cheers,

Jeff

I finally figured out why it didn't work. the port# specified in my
.pgpass was incorrect.
My stupidity.
Thanks to all for the help.