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

[Muhammad Usama]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20181116/f9ea8b2d/attachment-0001.html>