[pgpool-hackers: 2709] Re: Proposal to add local authentication along with local user-database store in pgpool-II

[Muhammad Usama]

Pasting below the conversations we had on this topic off the thread to keep
everyone in the loop.


On Thu, Feb 1, 2018 at 10:54 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> >> In my understanding the proposed feature requires Pgpool-II to have
> >> clear text passwords. That is different from the current
> >> implementation of md5 auth in Pgpool-II, at least it's not terribly
> >> easy to reconstruct original passwords from the md5 hashed password.
> >>
> >>
> > Enabling the SCRAM and other authentication methods supported by
> PostgreSQL
> > and not by Pgpool-II would be one of the advantages of implementing the
> > local authentication system. Apart form that as Korry mentioned another
> big
> > benefit of this will be to provide a guard against unauthorised access to
> > PostgreSQL through Pgpool-II, that can happen because of the
> > misconfigurations between pg_hba.conf and pool_hba.conf,  (Similar issue
> is
> > reported in http://www.pgpool.net/mantisbt/view.php?id=374 bug).
> > So effectively it will not only provide the framework for supporting new
> > authentication methods but will also enhance the overall security of the
> > Pgpool-II.
> >
> >
> >> So I am not sure the proposed feature (clear text + SCRAM) offeres
> >> a sperior authentication than current md5 auth.
> >>
> >
> >
> > I totally agree that storing the clear text password in a text file is a
> > bad idea and can cause a serious security hole. But there can be ways to
> > work around this problem. One solution that comes to my mind is to use a
> > passphrase encrypted file for storing the user/password informations and
> at
> > the time of startup, Pgpool-II asks for the passphrase, and decrypt the
> > file contents in the memory.
>
> Loading all users passwords into memory at once a little bit worries
> me. Isn't it better to load the passphrase into the memory at startup
> and decrypt each time frontend connects to Pgpool-II so that only one
> password used by current session is decrypted?
>

Yes I think it's a good workable idea.

Kind regards
Muhammad Usama

>
> >> >> Similar concept is also used by pgbouncer in form of
> authentication-file
> >> >> which contains the user-password pairs and pgbouncer use it to
> >> authenticate
> >> >> the connections with PostgreSQL backend and also the clients
> connecting
> >> to
> >> >> pgbouncer.
> >> >> https://pgbouncer.github.io/config.html#authentication-file-format
> >>
> >> It seems pgbouncer only uses the clear text format passwords to work
> >> with old PostgreSQL clear text password auth according to their doc
> >> above.
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >>
> >> > This is not a feature we want, but maybe a feature that we need.
> >> >
> >> > It's so easy to get an authentication mechanism wrong, and wrong in
> such
> >> a
> >> > way that the mechanism provides unintended access.
> >> >
> >> > Would be acceptable to support only single sign-on mechanisms instead?
> >> >
> >> >
> >> >        -- Korry
> >> >
> >> > On Thu, Jan 18, 2018 at 3:58 AM, Ahsan Hadi <
> ahsan.hadi at enterprisedb.com
> >> >
> >> > wrote:
> >> >
> >> >> Hi Guys,
> >> >>
> >> >> Can you share your feedback on the proposal below?
> >> >>
> >> >> -- Ahsan
> >> >>
> >> >> ---------- Forwarded message ----------
> >> >> From: Muhammad Usama <m.usama at gmail.com>
> >> >> Date: Thu, Jan 18, 2018 at 11:06 AM
> >> >> Subject: Proposal to add local authentication along with local
> >> >> user-database store in pgpool-II
> >> >> To: pgpool-hackers <pgpool-hackers at pgpool.net>, Tatsuo Ishii <
> >> >> ishii at sraoss.co.jp>, Ahsan Hadi <ahsan.hadi at enterprisedb.com>
> >> >>
> >> >>
> >> >> Hi,
> >> >>
> >> >> Since PostgreSQL10 has recently added a support for SCRAM
> authentication
> >> >> with future plans including its extension of channel binding. And
> >> because
> >> >> of the nature of SCRAM and other more secure authentication methods
> like
> >> >> ssl-certificate-authentication, Pgpool-II is not able to allow these
> >> >> auth-methods because of its current authentication system design.
> >> >> As almost all modern authentication methods are designed to guard
> >> against
> >> >> man-in-middle kind of attacks and middleware applications like
> Pgpool-II
> >> >> tries to exploit this very vulnerability to provide seamless
> >> authentication
> >> >> to users by forwarding the credentials provided by clients
> application
> >> to
> >> >> the backend servers. But fortunately or unfortunately with the modern
> >> auth
> >> >> protocols it is becoming almost next to impossible and it is a need
> of
> >> time
> >> >> to rethink the authentication system of Pgpool-II.
> >> >>
> >> >> My proposal is to add a configurable feature in the Pgpool-II 3.8 to
> >> allow
> >> >> it to have its own user-password database which it can use to
> >> authenticate
> >> >> the clients connecting to Pgpool-II and also use the same to
> >> authenticate
> >> >> the user with PostgreSQL backend.
> >> >>
> >> >> Similar concept is also used by pgbouncer in form of
> authentication-file
> >> >> which contains the user-password pairs and pgbouncer use it to
> >> authenticate
> >> >> the connections with PostgreSQL backend and also the clients
> connecting
> >> to
> >> >> pgbouncer.
> >> >> https://pgbouncer.github.io/config.html#authentication-file-format
> >> >>
> >> >> Also Pgpool-II already uses the password file for md5 authentication
> so
> >> >> this enhancement would not be a radical change to the existing users.
> >> And
> >> >> we can also provide the utility application with Pgpool-II to
> generate
> >> the
> >> >> pgpool-auth file from pg_shadow table to make this configuration
> hassle
> >> >> free. ( similar to mkauth.py included with pgbouncer)
> >> >>
> >> >> I think adding this feature will allow us make Pgpool-II more usable
> and
> >> >> secure and with this we will be able to support SCRAM and SSL-AUTH
> >> >> authentication methods in Pgpool-II. And it will also solve the
> problems
> >> >> like the one reported in the http://www.pgpool.net/mantisbt
> >> >> /view.php?id=374
> >> >>
> >> >> Finally this email just outlines the overview of the feature and
> once if
> >> >> we agree to go in the direction we can discuss it in more details
> like
> >> the
> >> >> file-format, user-password management for Pgpool-II and
> data-encryption
> >> on
> >> >> that file.
> >> >>
> >> >>
> >> >>
> >> >> Thoughts and comments are most welcome
> >> >>
> >> >> Thanks
> >> >> Best Regards
> >> >> Muhammad Usama
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> Ahsan Hadi
> >> >> Snr Director Product Development
> >> >> EnterpriseDB Corporation
> >> >> The Enterprise Postgres Company
> >> >>
> >> >> Phone: +92-51-8358874 <+92%2051%208358874>
> >> >> Mobile: +92-333-5162114 <+92%20333%205162114>
> >> >>
> >> >> Website: www.enterprisedb.com
> >> >> EnterpriseDB Blog: http://blogs.enterprisedb.com/
> >> >> Follow us on Twitter: http://www.twitter.com/enterprisedb
> >> >>
> >> >> This e-mail message (and any attachment) is intended for the use of
> the
> >> >> individual or entity to whom it is addressed. This message contains
> >> >> information from EnterpriseDB Corporation that may be privileged,
> >> >> confidential, or exempt from disclosure under applicable law. If you
> are
> >> >> not the intended recipient or authorized to receive this for the
> >> intended
> >> >> recipient, any use, dissemination, distribution, retention,
> archiving,
> >> or
> >> >> copying of this communication is strictly prohibited. If you have
> >> received
> >> >> this e-mail in error, please notify the sender immediately by reply
> >> e-mail
> >> >> and delete this message.
> >> >>
> >>
>
>

On Thu, Jan 18, 2018 at 11:06 AM, Muhammad Usama <m.usama at gmail.com> wrote:

> Hi,
>
> Since PostgreSQL10 has recently added a support for SCRAM authentication
> with future plans including its extension of channel binding. And because
> of the nature of SCRAM and other more secure authentication methods like
> ssl-certificate-authentication, Pgpool-II is not able to allow these
> auth-methods because of its current authentication system design.
> As almost all modern authentication methods are designed to guard against
> man-in-middle kind of attacks and middleware applications like Pgpool-II
> tries to exploit this very vulnerability to provide seamless authentication
> to users by forwarding the credentials provided by clients application to
> the backend servers. But fortunately or unfortunately with the modern auth
> protocols it is becoming almost next to impossible and it is a need of time
> to rethink the authentication system of Pgpool-II.
>
> My proposal is to add a configurable feature in the Pgpool-II 3.8 to allow
> it to have its own user-password database which it can use to authenticate
> the clients connecting to Pgpool-II and also use the same to authenticate
> the user with PostgreSQL backend.
>
> Similar concept is also used by pgbouncer in form of authentication-file
> which contains the user-password pairs and pgbouncer use it to authenticate
> the connections with PostgreSQL backend and also the clients connecting to
> pgbouncer.
> https://pgbouncer.github.io/config.html#authentication-file-format
>
> Also Pgpool-II already uses the password file for md5 authentication so
> this enhancement would not be a radical change to the existing users. And
> we can also provide the utility application with Pgpool-II to generate the
> pgpool-auth file from pg_shadow table to make this configuration hassle
> free. ( similar to mkauth.py included with pgbouncer)
>
> I think adding this feature will allow us make Pgpool-II more usable and
> secure and with this we will be able to support SCRAM and SSL-AUTH
> authentication methods in Pgpool-II. And it will also solve the problems
> like the one reported in the http://www.pgpool.net/
> mantisbt/view.php?id=374
>
> Finally this email just outlines the overview of the feature and once if
> we agree to go in the direction we can discuss it in more details like the
> file-format, user-password management for Pgpool-II and data-encryption on
> that file.
>
>
>
> Thoughts and comments are most welcome
>
> Thanks
> Best Regards
> Muhammad Usama
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20180206/7105270f/attachment-0001.html>