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

[Tatsuo Ishii]

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?

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
>>
>>