Re: authentication failure

> On Apr 12, 2018, at 9:12 AM, armand pirvu <armand(dot)pirvu(at)gmail(dot)com> wrote:
>
>
>> On Apr 12, 2018, at 9:08 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
> Yes I can that’s what is really puzzling me
>
> [armandp(at)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
>
>

Sorry for the double posting but could it be from

#authentication_timeout = 1min # 1s-600s

So if the server gets a bit oveloaded this could play a role ?