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