<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Feb 7, 2018 at 10:50 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Usama,<br>
<br>
If a user has multiple Pgpool-II installation (typically with watchdog<br>
enabled), it may be annoying he/she needs to manage multiple copies of<br>
account information. Is there any workaround for this?<br></blockquote><div><br></div><div>I think we can device a way to sync the encrypted file over the watchdog.</div><div>But this needs a more brainstorming. I will update on this after sorting out</div><div>the details and best possible way for that.</div><div><br></div><div>Thanks</div><div>Best Regards</div><div>Muhammad Usama</div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div class="h5"><br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
<br>
> Pasting below the conversations we had on this topic off the thread to keep<br>
> everyone in the loop.<br>
><br>
><br>
> On Thu, Feb 1, 2018 at 10:54 AM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
><br>
>> >> In my understanding the proposed feature requires Pgpool-II to have<br>
>> >> clear text passwords. That is different from the current<br>
>> >> implementation of md5 auth in Pgpool-II, at least it's not terribly<br>
>> >> easy to reconstruct original passwords from the md5 hashed password.<br>
>> >><br>
>> >><br>
>> > Enabling the SCRAM and other authentication methods supported by<br>
>> PostgreSQL<br>
>> > and not by Pgpool-II would be one of the advantages of implementing the<br>
>> > local authentication system. Apart form that as Korry mentioned another<br>
>> big<br>
>> > benefit of this will be to provide a guard against unauthorised access to<br>
>> > PostgreSQL through Pgpool-II, that can happen because of the<br>
>> > misconfigurations between pg_hba.conf and pool_hba.conf, (Similar issue<br>
>> is<br>
>> > reported in <a href="http://www.pgpool.net/mantisbt/view.php?id=374" rel="noreferrer" target="_blank">http://www.pgpool.net/<wbr>mantisbt/view.php?id=374</a> bug).<br>
>> > So effectively it will not only provide the framework for supporting new<br>
>> > authentication methods but will also enhance the overall security of the<br>
>> > Pgpool-II.<br>
>> ><br>
>> ><br>
>> >> So I am not sure the proposed feature (clear text + SCRAM) offeres<br>
>> >> a sperior authentication than current md5 auth.<br>
>> >><br>
>> ><br>
>> ><br>
>> > I totally agree that storing the clear text password in a text file is a<br>
>> > bad idea and can cause a serious security hole. But there can be ways to<br>
>> > work around this problem. One solution that comes to my mind is to use a<br>
>> > passphrase encrypted file for storing the user/password informations and<br>
>> at<br>
>> > the time of startup, Pgpool-II asks for the passphrase, and decrypt the<br>
>> > file contents in the memory.<br>
>><br>
>> Loading all users passwords into memory at once a little bit worries<br>
>> me. Isn't it better to load the passphrase into the memory at startup<br>
>> and decrypt each time frontend connects to Pgpool-II so that only one<br>
>> password used by current session is decrypted?<br>
>><br>
><br>
> Yes I think it's a good workable idea.<br>
><br>
> Kind regards<br>
> Muhammad Usama<br>
><br>
>><br>
>> >> >> Similar concept is also used by pgbouncer in form of<br>
>> authentication-file<br>
>> >> >> which contains the user-password pairs and pgbouncer use it to<br>
>> >> authenticate<br>
>> >> >> the connections with PostgreSQL backend and also the clients<br>
>> connecting<br>
>> >> to<br>
>> >> >> pgbouncer.<br>
>> >> >> <a href="https://pgbouncer.github.io/config.html#authentication-file-format" rel="noreferrer" target="_blank">https://pgbouncer.github.io/<wbr>config.html#authentication-<wbr>file-format</a><br>
>> >><br>
>> >> It seems pgbouncer only uses the clear text format passwords to work<br>
>> >> with old PostgreSQL clear text password auth according to their doc<br>
>> >> above.<br>
>> >><br>
>> >> Best regards,<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
>> >><br>
>> >> > This is not a feature we want, but maybe a feature that we need.<br>
>> >> ><br>
>> >> > It's so easy to get an authentication mechanism wrong, and wrong in<br>
>> such<br>
>> >> a<br>
>> >> > way that the mechanism provides unintended access.<br>
>> >> ><br>
>> >> > Would be acceptable to support only single sign-on mechanisms instead?<br>
>> >> ><br>
>> >> ><br>
>> >> > -- Korry<br>
>> >> ><br>
>> >> > On Thu, Jan 18, 2018 at 3:58 AM, Ahsan Hadi <<br>
>> <a href="mailto:ahsan.hadi@enterprisedb.com">ahsan.hadi@enterprisedb.com</a><br>
>> >> ><br>
>> >> > wrote:<br>
>> >> ><br>
>> >> >> Hi Guys,<br>
>> >> >><br>
>> >> >> Can you share your feedback on the proposal below?<br>
>> >> >><br>
>> >> >> -- Ahsan<br>
>> >> >><br>
>> >> >> ---------- Forwarded message ----------<br>
>> >> >> From: Muhammad Usama <<a href="mailto:m.usama@gmail.com">m.usama@gmail.com</a>><br>
>> >> >> Date: Thu, Jan 18, 2018 at 11:06 AM<br>
>> >> >> Subject: Proposal to add local authentication along with local<br>
>> >> >> user-database store in pgpool-II<br>
>> >> >> To: pgpool-hackers <<a href="mailto:pgpool-hackers@pgpool.net">pgpool-hackers@pgpool.net</a>>, Tatsuo Ishii <<br>
>> >> >> <a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>>, Ahsan Hadi <<a href="mailto:ahsan.hadi@enterprisedb.com">ahsan.hadi@enterprisedb.com</a>><br>
>> >> >><br>
>> >> >><br>
>> >> >> Hi,<br>
>> >> >><br>
>> >> >> Since PostgreSQL10 has recently added a support for SCRAM<br>
>> authentication<br>
>> >> >> with future plans including its extension of channel binding. And<br>
>> >> because<br>
>> >> >> of the nature of SCRAM and other more secure authentication methods<br>
>> like<br>
>> >> >> ssl-certificate-<wbr>authentication, Pgpool-II is not able to allow these<br>
>> >> >> auth-methods because of its current authentication system design.<br>
>> >> >> As almost all modern authentication methods are designed to guard<br>
>> >> against<br>
>> >> >> man-in-middle kind of attacks and middleware applications like<br>
>> Pgpool-II<br>
>> >> >> tries to exploit this very vulnerability to provide seamless<br>
>> >> authentication<br>
>> >> >> to users by forwarding the credentials provided by clients<br>
>> application<br>
>> >> to<br>
>> >> >> the backend servers. But fortunately or unfortunately with the modern<br>
>> >> auth<br>
>> >> >> protocols it is becoming almost next to impossible and it is a need<br>
>> of<br>
>> >> time<br>
>> >> >> to rethink the authentication system of Pgpool-II.<br>
>> >> >><br>
>> >> >> My proposal is to add a configurable feature in the Pgpool-II 3.8 to<br>
>> >> allow<br>
>> >> >> it to have its own user-password database which it can use to<br>
>> >> authenticate<br>
>> >> >> the clients connecting to Pgpool-II and also use the same to<br>
>> >> authenticate<br>
>> >> >> the user with PostgreSQL backend.<br>
>> >> >><br>
>> >> >> Similar concept is also used by pgbouncer in form of<br>
>> authentication-file<br>
>> >> >> which contains the user-password pairs and pgbouncer use it to<br>
>> >> authenticate<br>
>> >> >> the connections with PostgreSQL backend and also the clients<br>
>> connecting<br>
>> >> to<br>
>> >> >> pgbouncer.<br>
>> >> >> <a href="https://pgbouncer.github.io/config.html#authentication-file-format" rel="noreferrer" target="_blank">https://pgbouncer.github.io/<wbr>config.html#authentication-<wbr>file-format</a><br>
>> >> >><br>
>> >> >> Also Pgpool-II already uses the password file for md5 authentication<br>
>> so<br>
>> >> >> this enhancement would not be a radical change to the existing users.<br>
>> >> And<br>
>> >> >> we can also provide the utility application with Pgpool-II to<br>
>> generate<br>
>> >> the<br>
>> >> >> pgpool-auth file from pg_shadow table to make this configuration<br>
>> hassle<br>
>> >> >> free. ( similar to mkauth.py included with pgbouncer)<br>
>> >> >><br>
>> >> >> I think adding this feature will allow us make Pgpool-II more usable<br>
>> and<br>
>> >> >> secure and with this we will be able to support SCRAM and SSL-AUTH<br>
>> >> >> authentication methods in Pgpool-II. And it will also solve the<br>
>> problems<br>
>> >> >> like the one reported in the <a href="http://www.pgpool.net/mantisbt" rel="noreferrer" target="_blank">http://www.pgpool.net/mantisbt</a><br>
>> >> >> /view.php?id=374<br>
>> >> >><br>
>> >> >> Finally this email just outlines the overview of the feature and<br>
>> once if<br>
>> >> >> we agree to go in the direction we can discuss it in more details<br>
>> like<br>
>> >> the<br>
>> >> >> file-format, user-password management for Pgpool-II and<br>
>> data-encryption<br>
>> >> on<br>
>> >> >> that file.<br>
>> >> >><br>
>> >> >><br>
>> >> >><br>
>> >> >> Thoughts and comments are most welcome<br>
>> >> >><br>
>> >> >> Thanks<br>
</div></div>>> >> >> Best Regards<br>
>> >> >> Muhammad Usama<br>
>> >> >><br>
>> >> >><br>
>> >> >><br>
>> >> >><br>
>> >> >> --<br>
>> >> >> Ahsan Hadi<br>
>> >> >> Snr Director Product Development<br>
>> >> >> EnterpriseDB Corporation<br>
>> >> >> The Enterprise Postgres Company<br>
>> >> >><br>
>> >> >> Phone: <a href="tel:%2B92-51-8358874" value="+92518358874">+92-51-8358874</a> <+92%2051%208358874><br>
>> >> >> Mobile: <a href="tel:%2B92-333-5162114" value="+923335162114">+92-333-5162114</a> <+92%20333%205162114><br>
>> >> >><br>
>> >> >> Website: <a href="http://www.enterprisedb.com" rel="noreferrer" target="_blank">www.enterprisedb.com</a><br>
>> >> >> EnterpriseDB Blog: <a href="http://blogs.enterprisedb.com/" rel="noreferrer" target="_blank">http://blogs.enterprisedb.com/</a><br>
>> >> >> Follow us on Twitter: <a href="http://www.twitter.com/enterprisedb" rel="noreferrer" target="_blank">http://www.twitter.com/<wbr>enterprisedb</a><br>
>> >> >><br>
>> >> >> This e-mail message (and any attachment) is intended for the use of<br>
>> the<br>
>> >> >> individual or entity to whom it is addressed. This message contains<br>
>> >> >> information from EnterpriseDB Corporation that may be privileged,<br>
>> >> >> confidential, or exempt from disclosure under applicable law. If you<br>
>> are<br>
>> >> >> not the intended recipient or authorized to receive this for the<br>
>> >> intended<br>
>> >> >> recipient, any use, dissemination, distribution, retention,<br>
>> archiving,<br>
>> >> or<br>
>> >> >> copying of this communication is strictly prohibited. If you have<br>
>> >> received<br>
>> >> >> this e-mail in error, please notify the sender immediately by reply<br>
>> >> e-mail<br>
>> >> >> and delete this message.<br>
<div><div class="h5">>> >> >><br>
>> >><br>
>><br>
>><br>
><br>
> On Thu, Jan 18, 2018 at 11:06 AM, Muhammad Usama <<a href="mailto:m.usama@gmail.com">m.usama@gmail.com</a>> wrote:<br>
><br>
>> Hi,<br>
>><br>
>> Since PostgreSQL10 has recently added a support for SCRAM authentication<br>
>> with future plans including its extension of channel binding. And because<br>
>> of the nature of SCRAM and other more secure authentication methods like<br>
>> ssl-certificate-<wbr>authentication, Pgpool-II is not able to allow these<br>
>> auth-methods because of its current authentication system design.<br>
>> As almost all modern authentication methods are designed to guard against<br>
>> man-in-middle kind of attacks and middleware applications like Pgpool-II<br>
>> tries to exploit this very vulnerability to provide seamless authentication<br>
>> to users by forwarding the credentials provided by clients application to<br>
>> the backend servers. But fortunately or unfortunately with the modern auth<br>
>> protocols it is becoming almost next to impossible and it is a need of time<br>
>> to rethink the authentication system of Pgpool-II.<br>
>><br>
>> My proposal is to add a configurable feature in the Pgpool-II 3.8 to allow<br>
>> it to have its own user-password database which it can use to authenticate<br>
>> the clients connecting to Pgpool-II and also use the same to authenticate<br>
>> the user with PostgreSQL backend.<br>
>><br>
>> Similar concept is also used by pgbouncer in form of authentication-file<br>
>> which contains the user-password pairs and pgbouncer use it to authenticate<br>
>> the connections with PostgreSQL backend and also the clients connecting to<br>
>> pgbouncer.<br>
>> <a href="https://pgbouncer.github.io/config.html#authentication-file-format" rel="noreferrer" target="_blank">https://pgbouncer.github.io/<wbr>config.html#authentication-<wbr>file-format</a><br>
>><br>
>> Also Pgpool-II already uses the password file for md5 authentication so<br>
>> this enhancement would not be a radical change to the existing users. And<br>
>> we can also provide the utility application with Pgpool-II to generate the<br>
>> pgpool-auth file from pg_shadow table to make this configuration hassle<br>
>> free. ( similar to mkauth.py included with pgbouncer)<br>
>><br>
>> I think adding this feature will allow us make Pgpool-II more usable and<br>
>> secure and with this we will be able to support SCRAM and SSL-AUTH<br>
>> authentication methods in Pgpool-II. And it will also solve the problems<br>
</div></div>>> like the one reported in the <a href="http://www.pgpool.net/" rel="noreferrer" target="_blank">http://www.pgpool.net/</a><br>
<span class="">>> mantisbt/view.php?id=374<br>
>><br>
>> Finally this email just outlines the overview of the feature and once if<br>
>> we agree to go in the direction we can discuss it in more details like the<br>
>> file-format, user-password management for Pgpool-II and data-encryption on<br>
>> that file.<br>
>><br>
>><br>
>><br>
>> Thoughts and comments are most welcome<br>
>><br>
>> Thanks<br>
</span>>> Best Regards<br>
>> Muhammad Usama<br>
>><br>
>><br>
</blockquote></div><br></div></div>