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

[Muhammad Usama]

On Wed, Feb 7, 2018 at 10:50 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> Usama,
>
> If a user has multiple Pgpool-II installation (typically with watchdog
> enabled), it may be annoying he/she needs to manage multiple copies of
> account information. Is there any workaround for this?
>

I think we can device a way to sync the encrypted file over the watchdog.
But this needs a more brainstorming. I will update on this after sorting out
the details and best possible way for that.

Thanks
Best Regards
Muhammad Usama



> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > 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/20180207/c8ed45a7/attachment-0001.html>