Thread: authentication failure

authentication failure

From
Jayadevan M
Date:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan

Re: authentication failure

From
Ashesh Vashi
Date:
Try "psql -W" for prompting the password forcefully.


On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan




--
--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA:
Enterprise PostgreSQL Company

 

http://www.linkedin.com/in/asheshvashi

Re: authentication failure

From
Jayadevan M
Date:
Nope -
psql -W
psql: FATAL:  password authentication failed for user "postgres"



On Fri, Jan 3, 2014 at 2:49 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
Try "psql -W" for prompting the password forcefully.


On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan




--
--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA:
Enterprise PostgreSQL Company

 

http://www.linkedin.com/in/asheshvashi


Re: authentication failure

From
dinesh kumar
Date:
Hi,

On Fri, Jan 3, 2014 at 3:32 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Nope -
psql -W
psql: FATAL:  password authentication failed for user "postgres"


There might be possible of the user's password expiration. Make the user's local authentication as trust, and reload the postgres instance, and check the "pg_user" table for the password expire date. If the password is expired, alter the user's expiration time.



On Fri, Jan 3, 2014 at 2:49 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
Try "psql -W" for prompting the password forcefully.


On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan




--
--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA:
Enterprise PostgreSQL Company

 

http://www.linkedin.com/in/asheshvashi



Re: authentication failure

From
Chris Curvey
Date:



On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan

Could it be a problem with your pg_hba.conf?  Perhaps password authentication is not enabled there? 



--
I asked the Internet how to train my cat, and the Internet told me to get a dog.

Re: authentication failure

From
Jayadevan M
Date:
I am able to login from the non-chroot environment. So it is not an issue with pg_hba.conf and not an issue of password expiration. Is there a debug psql option?




On Fri, Jan 3, 2014 at 5:09 PM, Chris Curvey <chris@chriscurvey.com> wrote:



On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan

Could it be a problem with your pg_hba.conf?  Perhaps password authentication is not enabled there? 



--
I asked the Internet how to train my cat, and the Internet told me to get a dog.

Re: authentication failure

From
dinesh kumar
Date:

On Fri, Jan 3, 2014 at 5:13 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
I am able to login from the non-chroot environment. So it is not an issue with pg_hba.conf and not an issue of password expiration. Is there a debug psql option?

OK.

Have you checked the PGPASSWORD environment variable, from where you are trying to login.

Regards,
Dinesh



On Fri, Jan 3, 2014 at 5:09 PM, Chris Curvey <chris@chriscurvey.com> wrote:



On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan

Could it be a problem with your pg_hba.conf?  Perhaps password authentication is not enabled there? 



--
I asked the Internet how to train my cat, and the Internet told me to get a dog.


Re: authentication failure

From
Jayadevan M
Date:
Yes. All the basic checks I have done. I upgraded from CENTOs 6.4 to 6.5. Another interesting thing - if I su - postgres and then try, it works. So it has something to do with the chrt user (root) settings.


On Fri, Jan 3, 2014 at 5:36 PM, dinesh kumar <dineshkumar02@gmail.com> wrote:

On Fri, Jan 3, 2014 at 5:13 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
I am able to login from the non-chroot environment. So it is not an issue with pg_hba.conf and not an issue of password expiration. Is there a debug psql option?

OK.

Have you checked the PGPASSWORD environment variable, from where you are trying to login.

Regards,
Dinesh



On Fri, Jan 3, 2014 at 5:09 PM, Chris Curvey <chris@chriscurvey.com> wrote:



On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
Hi,
I am trying to login from psql and consistently getting a
"psql: FATAL:  password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there is no .pgpass file.
Any clues? How do I trouble-shoot?
Other possibly relevant info - this is a chrooted environment. I upgraded the OS recently in this env and faced some issues with /dev/null /proc etc not being present and so on. Some issues there?
The database itself is running in the same server in the non-chroot environment. I am also running a python application which uses psycopg2 and that is working fine.
Regards,
Jayadevan

Could it be a problem with your pg_hba.conf?  Perhaps password authentication is not enabled there? 



--
I asked the Internet how to train my cat, and the Internet told me to get a dog.



Re: authentication failure

From
Adrian Klaver
Date:
On 01/03/2014 04:54 AM, Jayadevan M wrote:
> Yes. All the basic checks I have done. I upgraded from CENTOs 6.4 to
> 6.5. Another interesting thing - if I su - postgres and then try, it
> works. So it has something to do with the chrt user (root) settings.
>

It might be helpful to detail what are the 'basic' checks you did? Also
whenever I see unexplained behavior of this nature on Linux I suspect
SELinux. Is it enabled?

>

--
Adrian Klaver
adrian.klaver@gmail.com


Re: authentication failure

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/03/2014 04:54 AM, Jayadevan M wrote:
>> Yes. All the basic checks I have done. I upgraded from CENTOs 6.4 to
>> 6.5. Another interesting thing - if I su - postgres and then try, it
>> works. So it has something to do with the chrt user (root) settings.

> It might be helpful to detail what are the 'basic' checks you did? Also
> whenever I see unexplained behavior of this nature on Linux I suspect
> SELinux. Is it enabled?

I'm wondering if there are two Postgres instances on the machine,
and the apparent inconsistency comes from connecting to one or the other.

A different line of thought is that connections from inside and outside
the chroot are getting different treatment in pg_hba.conf.

In any case, debugging this from the client's perspective is almost
impossible, because the server intentionally doesn't give a lot of
detail about why authentication failed.  You need to be looking at
the postmaster log, possibly with log_min_messages cranked up.

            regards, tom lane


Re: authentication failure

From
Jayadevan M
Date:
There is only one instance -

 ps -eaf | grep bin/postgres | grep -v grep
postgres  3203     1  0  2013 ?        00:02:04 /usr/pgsql-9.3/bin/postgres

The basic checks I did -
Connectivity from other machines work (so server is accessible)
No .pgpass file in the system
Able to login as postgres and application users from the same system (as long as it is not from the chroot environment). So this is not a show-stopper- I am able to work and application is also working fine
Even in the chroot, as I mentioned, once I do a 'su - postgres', I am able to login.

Overall, it is just a minor inconvenience, but I would like to resolve this. The no password supplied message comes back so fast, it is as if it did not even attemp to connect.




On Fri, Jan 3, 2014 at 8:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/03/2014 04:54 AM, Jayadevan M wrote:
>> Yes. All the basic checks I have done. I upgraded from CENTOs 6.4 to
>> 6.5. Another interesting thing - if I su - postgres and then try, it
>> works. So it has something to do with the chrt user (root) settings.

> It might be helpful to detail what are the 'basic' checks you did? Also
> whenever I see unexplained behavior of this nature on Linux I suspect
> SELinux. Is it enabled?

I'm wondering if there are two Postgres instances on the machine,
and the apparent inconsistency comes from connecting to one or the other.

A different line of thought is that connections from inside and outside
the chroot are getting different treatment in pg_hba.conf.

In any case, debugging this from the client's perspective is almost
impossible, because the server intentionally doesn't give a lot of
detail about why authentication failed.  You need to be looking at
the postmaster log, possibly with log_min_messages cranked up.

                        regards, tom lane

Re: authentication failure

From
Adrian Klaver
Date:
On 01/03/2014 09:29 PM, Jayadevan M wrote:
> There is only one instance -
>
>   ps -eaf | grep bin/postgres | grep -v grep
> postgres  3203     1  0  2013 ?        00:02:04 /usr/pgsql-9.3/bin/postgres
>
> The basic checks I did -
> Connectivity from other machines work (so server is accessible)
> No .pgpass file in the system
> Able to login as postgres and application users from the same system (as
> long as it is not from the chroot environment). So this is not a
> show-stopper- I am able to work and application is also working fine
> Even in the chroot, as I mentioned, once I do a 'su - postgres', I am
> able to login.

So, if when you are in the chroot environment what user(s) fail to log
in and are they the same user(s) as outside the chroot?

>
> Overall, it is just a minor inconvenience, but I would like to resolve
> this. The no password supplied message comes back so fast, it is as if
> it did not even attemp to connect.

Well, per Toms suggestion you will need to look at the Postgres log file
to see from the servers perspective.

Also, in case there is third party program involved it would not hurt to
tail the system log during your connection attempts.

>
>
>
>

--
Adrian Klaver
adrian.klaver@gmail.com


Re: authentication failure

From
Jayadevan M
Date:
Log entries for 3 situations - 2 successful and one failed attempt -

From non-chroot, shell user  postgres

2014-01-05 10:11:58 IST [17008]: [2-1] user=postgres,db=postgres LOG:  connection authorized: user=postgres database=postgres
2014-01-05 10:12:03 IST [17008]: [3-1] user=postgres,db=postgres LOG:  disconnection: session time: 0:00:04.413 user=postgres database=postgres host=::1 port=47944


From chroot, shell user root, db user postgres
2014-01-05 10:12:18 IST [17021]: [1-1] user=[unknown],db=[unknown] LOG:  connection received: host=::1 port=47945
2014-01-05 10:12:18 IST [17022]: [1-1] user=[unknown],db=[unknown] LOG:  connection received: host=::1 port=47946
2014-01-05 10:12:18 IST [17022]: [2-1] user=postgres,db=postgres FATAL:  password authentication failed for user "postgres"
2014-01-05 10:12:18 IST [17022]: [3-1] user=postgres,db=postgres DETAIL:  Connection matched pg_hba.conf line 90: "host    all             all             ::1/128                 md5"


chroot, shell user postgres

2014-01-05 10:12:48 IST [17051]: [1-1] user=[unknown],db=[unknown] LOG:  connection received: host=::1 port=47948
2014-01-05 10:12:51 IST [17052]: [1-1] user=[unknown],db=[unknown] LOG:  connection received: host=::1 port=47949
2014-01-05 10:12:51 IST [17052]: [2-1] user=postgres,db=postgres LOG:  connection authorized: user=postgres database=postgres



On Sat, Jan 4, 2014 at 8:43 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 01/03/2014 09:29 PM, Jayadevan M wrote:
There is only one instance -

  ps -eaf | grep bin/postgres | grep -v grep
postgres  3203     1  0  2013 ?        00:02:04 /usr/pgsql-9.3/bin/postgres

The basic checks I did -
Connectivity from other machines work (so server is accessible)
No .pgpass file in the system
Able to login as postgres and application users from the same system (as
long as it is not from the chroot environment). So this is not a
show-stopper- I am able to work and application is also working fine
Even in the chroot, as I mentioned, once I do a 'su - postgres', I am
able to login.

So, if when you are in the chroot environment what user(s) fail to log in and are they the same user(s) as outside the chroot?



Overall, it is just a minor inconvenience, but I would like to resolve
this. The no password supplied message comes back so fast, it is as if
it did not even attemp to connect.

Well, per Toms suggestion you will need to look at the Postgres log file to see from the servers perspective.

Also, in case there is third party program involved it would not hurt to tail the system log during your connection attempts.






--
Adrian Klaver
adrian.klaver@gmail.com

Re: authentication failure

From
Sameer Kumar
Date:

On Sat, Jan 4, 2014 at 1:29 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
There is only one instance -

 ps -eaf | grep bin/postgres | grep -v grep
postgres  3203     1  0  2013 ?        00:02:04 /usr/pgsql-9.3/bin/postgres

This only tells that there is one instance running!

There could be multiple PostgreSQL installations. And I guess that is what Tom meant here.

Can you try 
$ which psql
$ which pg_clt 

Can you set your $PGUSER variable to postgres and then try?

$ export $PGUSER=postgres



Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

email patch

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).

Attachment

Re: authentication failure

From
Jayadevan
Date:
Sameer Kumar wrote
> This only tells that there is one instance running!
>
> There could be multiple PostgreSQL installations. And I guess that is what
> Tom meant here.

I doubt that was what Tom meant. Anyway, we can see from the error that the
request did reach the server.


Sameer Kumar wrote
> Can you try
> $ which psql
> $ which pg_clt
>
> Can you set your $PGUSER variable to postgres and then try?
>
> $ export $PGUSER=postgres

It is already set. How would which psql is being used have an impact?




--
View this message in context: http://postgresql.1045698.n5.nabble.com/authentication-failure-tp5785193p5785366.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: authentication failure

From
Sameer Kumar
Date:

On Sun, Jan 5, 2014 at 6:45 PM, Jayadevan <maymala.jayadevan@gmail.com> wrote:
Sameer Kumar wrote
> This only tells that there is one instance running!
>
> There could be multiple PostgreSQL installations. And I guess that is what
> Tom meant here.

I doubt that was what Tom meant. Anyway, we can see from the error that the
request did reach the server.


Sameer Kumar wrote
> Can you try
> $ which psql
> $ which pg_clt
>
> Can you set your $PGUSER variable to postgres and then try?
>
> $ export $PGUSER=postgres

It is already set.

I suggested this since you mentioned su - postgresql does not give you the same error.

Which user are you using (while you get the error)?

 
How would which psql is being used have an impact?



I was just trying to understand more about your environment. This will let us know how many installations you have.

What is the value for your $PGDATA, $PGPASSWORD?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/authentication-failure-tp5785193p5785366.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: authentication failure

From
Adrian Klaver
Date:
On 01/04/2014 08:46 PM, Jayadevan M wrote:
> Log entries for 3 situations - 2 successful and one failed attempt -
>
>  From non-chroot, shell user  postgres
>
> 2014-01-05 10:11:58 IST [17008]: [2-1] user=postgres,db=postgres LOG:
> connection authorized: user=postgres database=postgres
> 2014-01-05 10:12:03 IST [17008]: [3-1] user=postgres,db=postgres LOG:
> disconnection: session time: 0:00:04.413 user=postgres database=postgres
> host=::1 port=47944
>
>
>  From chroot, shell user root, db user postgres
> 2014-01-05 10:12:18 IST [17021]: [1-1] user=[unknown],db=[unknown] LOG:
> connection received: host=::1 port=47945
> 2014-01-05 10:12:18 IST [17022]: [1-1] user=[unknown],db=[unknown] LOG:
> connection received: host=::1 port=47946
> 2014-01-05 10:12:18 IST [17022]: [2-1] user=postgres,db=postgres FATAL:
> password authentication failed for user "postgres"
> 2014-01-05 10:12:18 IST [17022]: [3-1] user=postgres,db=postgres
> DETAIL:  Connection matched pg_hba.conf line 90: "host
> all             all             ::1/128                 md5"
>

Alright so it (psql I assume) makes the connection but is failing when
the password is supplied. That would point to an erroneous password
being supplied from the root shell. This would seem to mean there is a
~/.pgpass file with an incorrect value or a PGPASSWORD environment
variable with an incorrect value. There is also the possibility that
PGPASSFILE was set to point to a file other than .pgpass. To confirm
that it is a password issue you could temporarily change the pg_hba.conf
line above to trust and attempt the connection to see if it succeeds.

>
> chroot, shell user postgres
>
> 2014-01-05 10:12:48 IST [17051]: [1-1] user=[unknown],db=[unknown] LOG:
> connection received: host=::1 port=47948
> 2014-01-05 10:12:51 IST [17052]: [1-1] user=[unknown],db=[unknown] LOG:
> connection received: host=::1 port=47949
> 2014-01-05 10:12:51 IST [17052]: [2-1] user=postgres,db=postgres LOG:
> connection authorized: user=postgres database=postgres
>
>



--
Adrian Klaver
adrian.klaver@gmail.com


Re: authentication failure

From
Jayadevan M
Date:
With md5
psql
psql: FATAL:  password authentication failed for user "postgres"

with trust
psql -h localhost
psql (9.3.2)
Type "help" for help.

back to md5
psql -h localhost
psql: FATAL:  password authentication failed for user "postgres"

But...

find / -name .pgpass
<no file found>

$ env | grep PG
PGPORT=1234
PGUSER=postgres
PGHOST=localhost



On Sun, Jan 5, 2014 at 9:03 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 01/04/2014 08:46 PM, Jayadevan M wrote:
Log entries for 3 situations - 2 successful and one failed attempt -

 From non-chroot, shell user  postgres

2014-01-05 10:11:58 IST [17008]: [2-1] user=postgres,db=postgres LOG:
connection authorized: user=postgres database=postgres
2014-01-05 10:12:03 IST [17008]: [3-1] user=postgres,db=postgres LOG:
disconnection: session time: 0:00:04.413 user=postgres database=postgres
host=::1 port=47944


 From chroot, shell user root, db user postgres
2014-01-05 10:12:18 IST [17021]: [1-1] user=[unknown],db=[unknown] LOG:
connection received: host=::1 port=47945
2014-01-05 10:12:18 IST [17022]: [1-1] user=[unknown],db=[unknown] LOG:
connection received: host=::1 port=47946
2014-01-05 10:12:18 IST [17022]: [2-1] user=postgres,db=postgres FATAL:
password authentication failed for user "postgres"
2014-01-05 10:12:18 IST [17022]: [3-1] user=postgres,db=postgres
DETAIL:  Connection matched pg_hba.conf line 90: "host
all             all             ::1/128                 md5"


Alright so it (psql I assume) makes the connection but is failing when the password is supplied. That would point to an erroneous password being supplied from the root shell. This would seem to mean there is a ~/.pgpass file with an incorrect value or a PGPASSWORD environment variable with an incorrect value. There is also the possibility that PGPASSFILE was set to point to a file other than .pgpass. To confirm that it is a password issue you could temporarily change the pg_hba.conf line above to trust and attempt the connection to see if it succeeds.


chroot, shell user postgres

2014-01-05 10:12:48 IST [17051]: [1-1] user=[unknown],db=[unknown] LOG:
connection received: host=::1 port=47948
2014-01-05 10:12:51 IST [17052]: [1-1] user=[unknown],db=[unknown] LOG:
connection received: host=::1 port=47949
2014-01-05 10:12:51 IST [17052]: [2-1] user=postgres,db=postgres LOG:
connection authorized: user=postgres database=postgres





--
Adrian Klaver
adrian.klaver@gmail.com

Re: authentication failure

From
Adrian Klaver
Date:
On 01/05/2014 07:47 AM, Jayadevan M wrote:
> With md5
> psql
> psql: FATAL:  password authentication failed for user "postgres"
>
> with trust
> psql -h localhost
> psql (9.3.2)
> Type "help" for help.
>
> back to md5
> psql -h localhost
> psql: FATAL:  password authentication failed for user "postgres"

Just noticed while testing here:

aklaver@panda:~> psql -d test -U aklaver -h localhost
psql: FATAL:  password authentication failed for user "aklaver"
password retrieved from file "/home/aklaver/.pgpass"

So psql tells you when you are getting password from pgpass.


>
> But...
>
> find / -name .pgpass
> <no file found>
>
> $ env | grep PG
> PGPORT=1234
> PGUSER=postgres
> PGHOST=localhost

Hmm, I am going to have to think about this.

>
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: authentication failure

From
Tom Lane
Date:
Jayadevan M <maymala.jayadevan@gmail.com> writes:
> back to md5
> psql -h localhost
> psql: FATAL:  password authentication failed for user "postgres"
> [ but there's no .pgpass file ]

Perhaps the postgres user has a password that's marked as expired
in pg_authid.rolvaliduntil?  Try
select rolname, rolvaliduntil from pg_authid;

            regards, tom lane


Re: authentication failure

From
Tom Lane
Date:
I wrote:
> Perhaps the postgres user has a password that's marked as expired
> in pg_authid.rolvaliduntil?

Ah, no, scratch that: a look at the code shows the backend doesn't
check rolvaliduntil until after the client has given a valid password.
Seems like psql *must* be getting a password from someplace.

Perhaps it's time to strace psql and see what files it accesses.

            regards, tom lane


Re: authentication failure

From
Adrian Klaver
Date:
On 01/05/2014 07:47 AM, Jayadevan M wrote:
> With md5
> psql
> psql: FATAL:  password authentication failed for user "postgres"
>
> with trust
> psql -h localhost
> psql (9.3.2)
> Type "help" for help.
>
> back to md5
> psql -h localhost
> psql: FATAL:  password authentication failed for user "postgres"
>
> But...
>
> find / -name .pgpass
> <no file found>
>
> $ env | grep PG
> PGPORT=1234
> PGUSER=postgres
> PGHOST=localhost
>
>

So the env is from the root account in the chroot?

Is it the same as the postgres account?

The only suggestion I have left is to look at the chroot setup.

See Toms post for another suggestion.


--
Adrian Klaver
adrian.klaver@gmail.com


Re: authentication failure

From
Jayadevan M
Date:
I am able to login as postgres with password from the same machine. So it is not an expiry issue (as you too concluded).  Output from strace is about 500 lines. I am pasting what I feel may be relevant. I hope this will be useful.

execve("/usr/pgsql-9.3/bin/psql", ["psql", "-h", "localhost"], [/* 24 vars */]) = 0
brk(0)                                  = 0x1962000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb252e42000
access("/etc/ld.so.preload", R_OK)      = -1 ENOENT (No such file or directory)
open("/etc/ld.so.cache", O_RDONLY)      = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=40284, ...}) = 0
mmap(NULL, 40284, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7fb252e38000
close(3)                                = 0
open("/usr/pgsql-9.3/lib/libpq.so.5", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\240\225\0\0\0\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=188904, ...}) = 0
mmap(NULL, 2284256, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x7fb2529f6000
mprotect(0x7fb252a21000, 2097152, PROT_NONE) = 0
....
munmap(0x7fb252e41000, 4096)            = 0
open("/usr/pgsql-9.3/share/locale/en_IN/LC_MESSAGES/psql-9.3.mo", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/pgsql-9.3/share/locale/en/LC_MESSAGES/psql-9.3.mo", O_RDONLY) = -1 ENOENT (No such file or directory)
geteuid()                               = 0
socket(PF_FILE, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
close(3)                                = 0
socket(PF_FILE, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
close(3)                                = 0
....
stat("/root/.pgpass", 0x7fffc631e030)   = -1 ENOENT (No such file or directory)
socket(PF_NETLINK, SOCK_RAW, 0)         = 3
bind(3, {sa_family=AF_NETLINK, pid=0, groups=00000000}, 12) = 0
.....
socket(PF_FILE, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
close(3)                                = 0
socket(PF_FILE, SOCK_STREAM|SOCK_CLOEXEC|SOCK_NONBLOCK, 0) = 3
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
close(3)                                = 0
open("/etc/host.conf", O_RDONLY)        = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=9, ...}) = 0
....
sendto(3, "\0\0\0T\0\3\0\0user\0postgres\0database\0p"..., 84, MSG_NOSIGNAL, NULL, 0) = 84
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\f\0\0\0\5:\314\261\264", 16384, 0, NULL, NULL) = 13
close(3)                                = 0
open("/dev/tty", O_RDONLY)              = 3
open("/dev/tty", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, 0x7fffc631e550) = -1 ENOTTY (Inappropriate ioctl for device)
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B0 -opost -isig icanon -echo ...}) = -1 ENOTTY (Inappropriate ioctl for device)
fstat(4, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb252e41000
write(4, "Password: ", 10)              = 10
fstat(3, {st_mode=S_IFREG|0644, st_size=10, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb252e40000
read(3, "Password: ", 4096)             = 10
read(3, "", 4096)                       = 0
read(3, "", 4096)                       = 0
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B0 -opost -isig icanon -echo ...}) = -1 ENOTTY (Inappropriate ioctl for device)
write(4, "\n", 1)                       = 1
close(3)                                = 0
munmap(0x7fb252e40000, 4096)            = 0
close(4)                                = 0
munmap(0x7fb252e41000, 4096)            = 0
socket(PF_NETLINK, SOCK_RAW, 0)         = 3
....
connect(3, {sa_family=AF_INET, sin_port=htons(1234), sin_addr=inet_addr("212.71.253.117")}, 16) = 0
getsockname(3, {sa_family=AF_INET6, sin6_port=htons(49998), inet_pton(AF_INET6, "::ffff:212.71.253.117", &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, [28]) = 0
close(3)                                = 0
socket(PF_NETLINK, SOCK_RAW, 0)         = 3
bind(3, {sa_family=AF_NETLINK, pid=0, groups=00000000}, 12) = 0
getsockname(3, {sa_family=AF_NETLINK, pid=3632, groups=00000000}, [12]) = 0
sendto(3, "\24\0\0\0\22\0\1\3m4\312R\0\0\0\0\0\0\0\0", 20, 0, {sa_family=AF_NETLINK, pid=0, groups=00000000}, 12) = 20
recvmsg(3, {msg_name(12)={sa_family=AF_NETLINK, pid=0, groups=00000000}, msg_iov(1)=[{"0\4\0\0\20\0\2\0m4\312R0\16\0\0\0\0\4\3\1\0\0\0I\0\1\0\0\0\0\0"..., 4096}], msg_controllen=0, msg_flags=0}, 0) = 3244
close(3)                                = 0
socket(PF_INET6, SOCK_STREAM, IPPROTO_IP) = 3
setsockopt(3, SOL_TCP, TCP_NODELAY, [1], 4) = 0
fcntl(3, F_SETFL, O_RDONLY|O_NONBLOCK)  = 0
fcntl(3, F_SETFD, FD_CLOEXEC)           = 0
setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
connect(3, {sa_family=AF_INET6, sin6_port=htons(1234), inet_pton(AF_INET6, "::1", &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, 28) = -1 EINPROGRESS (Operation now in progress)
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
getsockopt(3, SOL_SOCKET, SO_ERROR, [5972663263635177472], [4]) = 0
getsockname(3, {sa_family=AF_INET6, sin6_port=htons(49516), inet_pton(AF_INET6, "::1", &sin6_addr), sin6_flowinfo=0, sin6_scope_id=0}, [28]) = 0
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
sendto(3, "\0\0\0\10\4\322\26/", 8, MSG_NOSIGNAL, NULL, 0) = 8
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
sendto(3, "\0\0\0T\0\3\0\0user\0postgres\0database\0p"..., 84, MSG_NOSIGNAL, NULL, 0) = 84
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\f\0\0\0\5\245}\301\263", 16384, 0, NULL, NULL) = 13
sendto(3, "p\0\0\0(md5db443609787c21b3171e5eed"..., 41, MSG_NOSIGNAL, NULL, 0) = 41
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "E\0\0\0aSFATAL\0C28P01\0Mpassword aut"..., 16384, 0, NULL, NULL) = 98
write(2, "psql: FATAL:  password authentic"..., 65) = 65



On Sun, Jan 5, 2014 at 11:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
> Perhaps the postgres user has a password that's marked as expired
> in pg_authid.rolvaliduntil?

Ah, no, scratch that: a look at the code shows the backend doesn't
check rolvaliduntil until after the client has given a valid password.
Seems like psql *must* be getting a password from someplace.

Perhaps it's time to strace psql and see what files it accesses.

                        regards, tom lane

Re: authentication failure

From
Tom Lane
Date:
Jayadevan M <maymala.jayadevan@gmail.com> writes:
> I am able to login as postgres with password from the same machine. So it
> is not an expiry issue (as you too concluded).  Output from strace is about
> 500 lines. I am pasting what I feel may be relevant. I hope this will be
> useful.

Well, this is pretty interesting:

> open("/dev/tty", O_RDONLY)              = 3
> open("/dev/tty", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
> ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, 0x7fffc631e550) = -1 ENOTTY
> (Inappropriate ioctl for device)
> ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B0 -opost -isig icanon -echo
> ...}) = -1 ENOTTY (Inappropriate ioctl for device)
> fstat(4, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
> mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
> 0x7fb252e41000
> write(4, "Password: ", 10)              = 10
> fstat(3, {st_mode=S_IFREG|0644, st_size=10, ...}) = 0
> mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
> 0x7fb252e40000
> read(3, "Password: ", 4096)             = 10
> read(3, "", 4096)                       = 0
> read(3, "", 4096)                       = 0
> ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B0 -opost -isig icanon -echo
> ...}) = -1 ENOTTY (Inappropriate ioctl for device)
> write(4, "\n", 1)                       = 1
> close(3)                                = 0
> munmap(0x7fb252e40000, 4096)            = 0
> close(4)                                = 0

It *is* prompting for a password, or so it thinks.  Apparently /dev/tty
is connected to the bit bucket in your environment?

            regards, tom lane


Re: authentication failure

From
Rob Sargent
Date:
On 01/06/2014 02:42 PM, Tom Lane wrote:
Jayadevan M <maymala.jayadevan@gmail.com> writes:
I am able to login as postgres with password from the same machine. So it
is not an expiry issue (as you too concluded).  Output from strace is about
500 lines. I am pasting what I feel may be relevant. I hope this will be
useful.
Well, this is pretty interesting:

open("/dev/tty", O_RDONLY)              = 3
open("/dev/tty", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, 0x7fffc631e550) = -1 ENOTTY
(Inappropriate ioctl for device)
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B0 -opost -isig icanon -echo
...}) = -1 ENOTTY (Inappropriate ioctl for device)
fstat(4, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x7fb252e41000
write(4, "Password: ", 10)              = 10
fstat(3, {st_mode=S_IFREG|0644, st_size=10, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x7fb252e40000
read(3, "Password: ", 4096)             = 10
read(3, "", 4096)                       = 0
read(3, "", 4096)                       = 0
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B0 -opost -isig icanon -echo
...}) = -1 ENOTTY (Inappropriate ioctl for device)
write(4, "\n", 1)                       = 1
close(3)                                = 0
munmap(0x7fb252e40000, 4096)            = 0
close(4)                                = 0
It *is* prompting for a password, or so it thinks.  Apparently /dev/tty
is connected to the bit bucket in your environment?
		regards, tom lane


I've been bit where foreground colour == background colour.

rjs