Re: authentication failure - Mailing list pgsql-general

On Apr 12, 2018, at 9:12 AM, armand pirvu <armand.pirvu@gmail.com> wrote:

On Apr 12, 2018, at 9:08 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/12/2018 06:59 AM, armand pirvu wrote:

Please reply to list also.
Ccing list.

Yes and worked fine until two days ago
I use .pgpass

So can you connect from wherever the process is run to the server manually? Something like:


psql -d birstab -U csidba -h some_server

I will also check and see if there are not too many hands in the cookie jar so to speak and things happen without being communicated

On Apr 12, 2018, at 8:56 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/12/2018 06:51 AM, armand pirvu wrote:

Hi there
I have a process in place which runs several queries from one host to another one
All of a sudden I started noticing authentication failures
Like below
.009 ms  statement: COPY  NACDS.tf_show_code_response_person FROM STDIN with csv;",,,,,,,,,"psql"
2018-04-12 00:10:48.765 CDT,"csidba","birstdb",7553,"172.16.20.4:40330",5aceea2e.1d81,1,"UPDATE",2018-04-12 00:10:06 CDT,24/0,0,LOG,00000,"duration: 425
90.993 ms  statement: UPDATE
    csischema.tf_transaction_person
SET
    is_deleted = 'TRUE',
    birst_is_deleted = 'TRUE',
    update_datetime = now()::timestamp(0)
WHERE
    show_id = '984BIOWC18' AND
    birst_is_deleted = 'FALSE' AND
    person_transaction_id IN (
        SELECT a.person_transaction_id
        FROM csischema.tf_transaction_person a
             LEFT JOIN BIOWC.tf_transaction_person b
             ON a.person_transaction_id=b.person_transaction_id
        WHERE a.show_id = '984BIOWC18' AND b.person_transaction_id IS NULL
    )
;",,,,,,,,,"psql"
2018-04-12 00:10:48.823 CDT,"csidba","birstdb",7755,"172.16.20.4:40455",5aceea58.1e4b,1,"authentication",2018-04-12 00:10:48 CDT,3/20320168,0,FATAL,28P0
1,"password authentication failed for user ""csidba""","Connection matched pg_hba.conf line 84: ""host all all 0.0.0.0/0 md5""",,,,,,,,""
2018-04-12 00:10:48.841 CDT,"csidba","birstdb",7756,"172.16.20.4:40456",5aceea58.1e4c,1,"authentication",2018-04-12 00:10:48 CDT,3/20320169,0,FATAL,28P0
1,"password authentication failed for user ""csidba""","Connection matched pg_hba.conf line 84: ""host all all 0.0.0.0/0 md5""",,,,,,,,""
2018-04-12 00:10:48.957 CDT,"csidba","birstdb",7759,"172.16.20.4:40459",5aceea58.1e4f,1,"authentication",2018-04-12 00:10:48 CDT,3/20320172,0,FATAL,28P0
pg_hba.conf
# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
local  replication  csidba  md5
host   replication  csidba  127.0.0.1/32  md5
host   replication  csidba  0.0.0.0/0     md5
local  replication  repuser  md5
host   replication  repuser  127.0.0.1/32  md5
host   replication  repuser  0.0.0.0/0     md5
local  all         repuser  md5
host   all         repuser  127.0.0.1/32  md5
host   all         repuser  0.0.0.0/0     md5
Did I run in somthing similar to a racong condition ?
Any ideas ?

Is the process using the correct password?

Many thanks
— Armand

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Yes I can that’s what is really puzzling me

[armandp@devweb2004 ~]$ /usr/pgsql-9.5/bin/psql -d birstdb -U csidba -h 172.16.26.4

psql (9.5.12, server 9.5.8)

Type "help" for help.

birstdb=# 

It is almost like the authnetication stops working for whatever reason

I did check the .pgpass and all that and nothing chaged

Is there a possibility like say 70 processes try to authenticate in the same time and postgres authentication gets a bit lost ?

Thank you