Обсуждение: authentication failure

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

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

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

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

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

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

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.klaver@gmail.com

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

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