[pgpool-hackers: 3129] Re: Authetication broken in Pgpool-II 4.0?

[Tatsuo Ishii]

Hi Usama,

Thank you!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi Ishii-San
> 
> Thank you for catching the issue and for the fix.
> 
> I have committed the patch in 4_0 and master branches.
> 
> Thanks
> Best Regards
> Muhammad Usama
> 
> 
> On Thu, Nov 15, 2018 at 8:03 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> Hi Usama,
>>
>> I think I found the cause of the issue.
>>
>> While authenticating against backend in connection_do_auth(), it
>> returns to caller as soon as backend returns auth ok response.  So
>> authentication itself established fine. However connection_do_auth()
>> does not proceed until it receives "Ready for query", it is required
>> according to the frontend/backend protocol.
>>
>> So subsequent calls to check health checking or replication delay
>> receives "ready for query" message from backend, instead of row data
>> packets. This leads to following error.
>> > LOG: get_query_result: no rows returned
>>
>> Actually connection_do_auth() has the code to process "ready for
>> query". So possible fix is, just remove "return" and let the function
>> process remaining code. Attached patch does it. Can you please check
>> it?
>>
>> BTW, not only md5, but other methods including clear text password and
>> crypt password has the same problem (too early return). The attached
>> patch includes the fix as well.
>>
>> > If we want sr_check or health_check authenticated against PostgreSQL
>> > with md5, it seems it's not working anymore in 4.0 or later. I mean:
>> >
>> > - health_check_password and/or sr_check_password are correctly set (in
>> >   clear text or AES does not matter).
>> >
>> > - auth method specified for health_check_user and/or sr_check_user in
>> pg_hba.conf is md5.
>> >
>> > - password for health_check_user and/or sr_check_user are encrypted in
>> md5 in pg_shadow.
>> >
>> > This works fine in 3.7 or before. So I guess there's something wrong
>> > in 4.0's md5 auth routine.
>> >
>> > Since default auth method in PostgreSQL 10 or 11 are still md5, this
>> > could happen frequently if user does not change "password_encryption"
>> > in postgresql.conf (it's still md5).
>> >
>> > In fact we see multiple complains from users.
>> > Typical symptom of this issue is something like:
>> >
>> > DEBUG: do_query: extended:0 query:"SELECT pg_is_in_recovery()"
>> > LOG: get_query_result: no rows returned
>> >
>> > Actually what happens here in PostgreSQL side at this point is:
>> >
>> > 9716 2018-11-15 10:04:15 JST LOG:  statement: SELECT pg_is_in_recovery()
>> > 9716 2018-11-15 10:04:15 JST LOG:  could not send data to client: Broken
>> pipe
>> >
>> > I think we need to fix this as soon as possible. Can you please look
>> into this?
>> >
>> > Best regards,
>> > --
>> > Tatsuo Ishii
>> > SRA OSS, Inc. Japan
>> > English: http://www.sraoss.co.jp/index_en.php
>> > Japanese:http://www.sraoss.co.jp
>> > _______________________________________________
>> > pgpool-hackers mailing list
>> > pgpool-hackers at pgpool.net
>> > http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>>