authentication failure

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.enterprisedb.com&gt;

*http://www.linkedin.com/in/asheshvashi*&lt;http://www.linkedin.com/in/asheshvashi&gt;

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:

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.

Regards,
Dinesh
manojadinesh.blogspot.com

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.

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 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
manojadinesh.blogspot.com

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 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

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

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

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

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:

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

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

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 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

[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.

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

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?

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

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

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/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

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

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

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