authentication failure

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

Try "psql -W" for prompting the password forcefully.

On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.enterprisedb.com>

*http://www.linkedin.com/in/asheshvashi*<http://www.linkedin.com/in/asheshvashi>

Nope -
psql -W
psql: FATAL: password authentication failed for user "postgres"

On Fri, Jan 3, 2014 at 2:49 PM, Ashesh Vashi
<ashesh(dot)vashi(at)enterprisedb(dot)com>wrote:

> Try "psql -W" for prompting the password forcefully.
>
>
> On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.enterprisedb.com>
>
>
>
> *http://www.linkedin.com/in/asheshvashi*<http://www.linkedin.com/in/asheshvashi>
>

Hi,

On Fri, Jan 3, 2014 at 3:32 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.

Regards,
Dinesh
manojadinesh.blogspot.com

>
> On Fri, Jan 3, 2014 at 2:49 PM, Ashesh Vashi <
> ashesh(dot)vashi(at)enterprisedb(dot)com> wrote:
>
>> Try "psql -W" for prompting the password forcefully.
>>
>>
>> On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.enterprisedb.com>
>>
>>
>>
>> *http://www.linkedin.com/in/asheshvashi*<http://www.linkedin.com/in/asheshvashi>
>>
>
>

On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.

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(at)chriscurvey(dot)com> wrote:

>
>
>
> On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.
>

On Fri, Jan 3, 2014 at 5:13 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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
manojadinesh.blogspot.com

>
>
> On Fri, Jan 3, 2014 at 5:09 PM, Chris Curvey <chris(at)chriscurvey(dot)com>wrote:
>
>>
>>
>>
>> On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.
>>
>
>

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(at)gmail(dot)com>wrote:

>
> On Fri, Jan 3, 2014 at 5:13 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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
> manojadinesh.blogspot.com
>
>
>>
>>
>> On Fri, Jan 3, 2014 at 5:09 PM, Chris Curvey <chris(at)chriscurvey(dot)com>wrote:
>>
>>>
>>>
>>>
>>> On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.
>>>
>>
>>
>

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(dot)klaver(at)gmail(dot)com

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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
>

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(dot)klaver(at)gmail(dot)com

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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com
>

On Sat, Jan 4, 2014 at 1:29 PM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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

[image: email patch]

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

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.

On Sun, Jan 5, 2014 at 6:45 PM, Jayadevan <maymala(dot)jayadevan(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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(dot)klaver(at)gmail(dot)com

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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com
>

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(at)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(dot)klaver(at)gmail(dot)com

Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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

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

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(dot)klaver(at)gmail(dot)com

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(at)sss(dot)pgh(dot)pa(dot)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
>

Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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

On 01/06/2014 02:42 PM, Tom Lane wrote:
> Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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