[pgpool-general: 3699] Re: Question about authentication with pool_hba

[michail alexakis]

Thank you Tatsuo, you have been very helpful!

I also think it would be a good idea, if the part of your reply about the
authentication flow
was added to the manual at the section "Authentication / Access Controls"
because it
answers (at least for newcomers) some of the common security-related
worries.

Thanks again, and congratulations on the great work!


On Mon, May 11, 2015 at 3:38 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Hi Michail,
>
> Welcome to the pgpool-II world!
>
> > Hello list,
> >
> > First, i am a complete newbie to pgpool, so please forgive any
> > misunderstandings i may have. I am experimenting with pgpool in order to
> > use it in front of a master/standy postgresql cluster (streaming
> > replication) as the only connection endpoint for several web
> applications.
> > I have installed (via APT) pgpool2  3.1.3-5.
> >
> > I was trying to understand the authentication workflow (and in particular
> > how pool_hba works). I guessed that pool_hba is there to authenticate
> > client connections before (re)using an already authenticated connection
> > from the connection pool (to one of the backends).
> >
> > I use the following settings:
> > enable_pool_hba = on
> > pool_passwd = 'pool_passwd'
> >
> > I populated pool_passwd (owned by postgres, 0600) using pg_md5 as
> described
> > at the official manual:
> > pg_md5 --md5auth -p -u tester -f /etc/pgpool2/pgpool.conf
> > and it;s contents are now:
> > tester:md5c3271fc99b3e8cada6aa9d8b8f0d0938
> >
> > I have a pool_hba.conf file with an entry (let 192.168.1.0/24 be the
> > network from where applications connect to pgpool):
> > host    all         all         192.168.1.0/24        md5
> >
> > The backend's (only 1 for the time) pg_hba.conf is configured to trust
> > (method:trust) connections from user "tester" originating from pgpool's
> > host.
> >
> > So, i'd expect that when trying to connect to pgpool (e.g. with psql), i
> > would have been forced to authenticate against pool_passwd's entries, but
> > instead i passed without a prompt (just like connecting directly to the
> > backend). Is this the normal behavior?
>
> Yes. pgpool-II first asks backends if md5 auth is required or not. If
> required, pgpool-II asks md5 auth to frontend. In other word, if the
> backends do not require md5 auth, trust auth is used.
>
> > More generally, i was curious on how exactly are clients authenticated
> and
> > mapped to (opened?) connection slots from the pool?
>
> Each connection request from frontend is associated with user name and
> database name. pgpool-II also remembers opened connection's user name
> and database name. So pgpool-II can easily maps an incoming connection
> request to already opened connections (if any). In md5 auth case,
> pgpool-II also memories hashed password for the opened connection. So
> pgpool-II does the auth work without asking backends.
>
> > Thanks for your time!
> >
> > Michail Alexakis,
> > Software engineer,  RC "Athena", Athens, Greece
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150511/8b9f3b02/attachment.html>