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

Tatsuo Ishii ishii at sraoss.co.jp
Thu Nov 15 12:03:17 JST 2018

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

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: auth_fix.diff
Type: text/x-patch
Size: 908 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20181115/49d6c8c5/attachment.bin>

More information about the pgpool-hackers mailing list