<div dir="ltr"><br><br><div class="gmail_quote"><div dir="ltr">On Wed, Jul 25, 2018 at 12:09 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Usama,<br>
<br>
> Hi Ishii-San<br>
> <br>
> <br>
> On Mon, Jul 23, 2018 at 7:13 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> Usama,<br>
>><br>
>> Thank you for the updation!<br>
>><br>
>> Here are some commnets.<br>
>><br>
>> 1) There are some traling space additions in the patch.<br>
>><br>
>> t-ishii@localhost: git apply ~/scram_auth_feature_patch.diff<br>
>> /home/t-ishii/scram_auth_feature_patch.diff:1692: trailing whitespace.<br>
>><br>
>> /home/t-ishii/scram_auth_feature_patch.diff:1696: trailing whitespace.<br>
>><br>
>> /home/t-ishii/scram_auth_feature_patch.diff:1702: trailing whitespace.<br>
>><br>
>> /home/t-ishii/scram_auth_feature_patch.diff:1709: trailing whitespace.<br>
>><br>
>> /home/t-ishii/scram_auth_feature_patch.diff:2792: trailing whitespace.<br>
>><br>
>> warning: squelched 87 whitespace errors<br>
>> warning: 92 lines add whitespace errors.<br>
>><br>
>><br>
> Yes there are few debug message cleanups and these whitespaces error exists<br>
> in the patch,<br>
> I will fix those in next version.<br>
<br>
Ok.<br>
<br>
>> 2) Design suggesions<br>
>><br>
>> > Note: allow_clear_text_frontend_auth only works when pool_hba.conf is not<br>
>> > enabled in pgpool.conf<br>
>> ><br>
>> ><br>
>> > For example: suppose PostgreSQL servers has a user named "*some_user*"<br>
>> > which can connect to database using SCRAM authentication, Now for this<br>
>> > "some_user" to connect to PostgreSQL using SCRAM through Pgpool-II we<br>
>> must<br>
>> > have the *some_user*'s password stored in the pool_passwd file, but if in<br>
>> > some case when pool_passwd does not have the entry of "*some_user*" and<br>
>> > *allow_clear_text_frontend_auth *is enabled in the pgpool.conf then<br>
>> > Pgpool-II will ask the connecting frontend to use clear-text-password<br>
>> auth<br>
>> > method for authentication, and after receiving the password from the<br>
>> > client, Pgpool-II will use that password to authenticate with backend<br>
>> using<br>
>> > the required SCRAM auth.<br>
>><br>
>> Why do we need "allow_clear_text_frontend_auth"? Rather, can't we set<br>
>> "password" in the pool_hba.conf auth method for a user who is allow to<br>
>> connect to pgpool with clear text password? This way is more flexible<br>
>> since at the same we can force different user MD5 and/or SCRAM auth.<br>
>><br>
> <br>
> Setting the "password" auth in pool_hba.conf does work as you described,<br>
> while allow_clear_text_frontend_auth is for the cases when pool_hba is<br>
> disabled in pgpool.conf.<br>
> So setting allow_clear_text_frontend_auth allows the clients to connect<br>
> through pgpool<br>
> when they don't want to use pool_hba while backend requires SCRAM or MD5<br>
> auth.<br>
<br>
Ok, that makes sense.<br>
<br>
>> 4) Migration of pool_passwd<br>
>><br>
>> If user have large number of entries in pool_passwd, migrating to new<br>
>> pool_passwd using AES encryption is pain. Is there any way to migate<br>
>> the pain?<br>
>><br>
> <br>
> Yes that could be a problem, and also the case<br>
> when user want to change the encryption password for entries in pool_passwd.<br>
> I will try to enhance pg_enc to tackle these two cases separately.<br>
> But for the older versions pool_passwd file I don't think we can do much<br>
> about the existing<br>
> entries. since currently pool_passwd only supports MD5 entries which is one<br>
> way<br>
> hashing. So for old pool_passwd file either users have to create new<br>
> entries from scratch or have<br>
> to stick with MD5 auth.<br>
> Do you have some suggestion to work around that?<br>
<br>
No. Probably there's no clear answer to this for now. Let's leave as<br>
it until we come up with a better idea.<br></blockquote><div><br></div><div>Sure </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
BTW, in the new regression test: 021.pool_passwd_auth I see clear text<br>
password is stored in pool_passwd. I thought your implementation<br>
allows to store password in AES 256 encryption, which is great. Is<br>
there any reason you did not use the feature?<br></blockquote><div><br></div><div>Not really, Actually I wrote the test case before completing the AES encryption system,</div><div>I will add another test case to test encrypted passwords in pool_passwd</div><div><br></div><div>Thanks</div><div>Best Regards</div><div>Muhammad Usama</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
> Thanks<br>
> Best Regards<br>
> Muhammad Usama<br>
> <br>
> <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_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > Hi All,<br>
>> ><br>
>> ><br>
>> > I have been working on supporting the new authentication methods in<br>
>> > Pgpool-II, So here is the working patch for supporting the SCRAM and CERT<br>
>> > based authentication methods.<br>
>> ><br>
>> > The patch needs a some compilation warning fixes and little bit of review<br>
>> > of memory management and code cleanups (which I am working on) but other<br>
>> > than that it is ready for review testing.<br>
>> ><br>
>> > The code is also checked-in to SCRAM_AUTH branch in Pgpool's git repo<br>
>> ><br>
>> ><br>
>> > Below is the brief overview of changes made by the patch and how to use<br>
>> the<br>
>> > newly added auth methods.<br>
>> ><br>
>> ><br>
>> > *Allow different auth methods for frontend and backend for user session:*<br>
>> ><br>
>> > The patch made it possible to use different authentication methods<br>
>> between<br>
>> > client to Pgpool-II and Pgpool-II to backend.<br>
>> ><br>
>> > For example: now we can use clear-text-password authentication between<br>
>> > Pgpool-II and frontend applications while md5 or scram auth method to<br>
>> > authenticate the backend connections for same session. Similarly the<br>
>> > frontend can now be authenticated using the ssl certificate (CERT auth)<br>
>> > while for backend we can use md5 or scram for the same session.<br>
>> ><br>
>> ><br>
>> > *Able to use MD5 and SCRAM auth methods without pool_passwd:*<br>
>> ><br>
>> > The patch adds a new configuration parameter<br>
>> > *allow_clear_text_frontend_auth*, enabling this config allows the<br>
>> Pgpool-II<br>
>> > to use clear-text-password authentication with frontend clients when<br>
>> > pool_passwd file does not contains the password for the connecting user,<br>
>> > and use that password (provided by client) to authenticate with the<br>
>> backend<br>
>> > using MD5 and/or SCRAM authentication.<br>
>> ><br>
>> > Note: allow_clear_text_frontend_auth only works when pool_hba.conf is not<br>
>> > enabled in pgpool.conf<br>
>> ><br>
>> ><br>
>> > For example: suppose PostgreSQL servers has a user named "*some_user*"<br>
>> > which can connect to database using SCRAM authentication, Now for this<br>
>> > "some_user" to connect to PostgreSQL using SCRAM through Pgpool-II we<br>
>> must<br>
>> > have the *some_user*'s password stored in the pool_passwd file, but if in<br>
>> > some case when pool_passwd does not have the entry of "*some_user*" and<br>
>> > *allow_clear_text_frontend_auth *is enabled in the pgpool.conf then<br>
>> > Pgpool-II will ask the connecting frontend to use clear-text-password<br>
>> auth<br>
>> > method for authentication, and after receiving the password from the<br>
>> > client, Pgpool-II will use that password to authenticate with backend<br>
>> using<br>
>> > the required SCRAM auth.<br>
>> ><br>
>> > *Creating encrypted passwords:*<br>
>> ><br>
>> > The patch adds a new utility *pg_enc* to create AES encrypted password<br>
>> > entries. The utility works similar in most ways as pg_md5 utility, with a<br>
>> > some small differences,<br>
>> ><br>
>> > pg_enc also requires the key for encrypting the password entries. later<br>
>> > that same key is required by Pgpool-II to decrypt the passwords to be<br>
>> used<br>
>> > for authentication.<br>
>> ><br>
>> > Note: Pgpool-II must be build with ssl (--with-openssl) support to use<br>
>> this<br>
>> > encrypted password feature.<br>
>> ><br>
>> > *Storing the encrypted password in pool_passwd file*<br>
>> ><br>
>> > Since the SCRAM authentication method explicitly guards against the<br>
>> > man-in-middle type attack so Pgpool-II can only use SCRAM auth method if<br>
>> it<br>
>> > has the user password (or *allow_clear_text_frontend_auth *is enabled).<br>
>> But<br>
>> > storing the clear text password in a file is never a good idea, so for<br>
>> that<br>
>> > reason pool_passwd file now allows to store user password in AES-256<br>
>> > encrypted format<br>
>> ><br>
>> > for example:<br>
>> ><br>
>> > some_user:AESWJPIi/R2QJS4cHKTAtjATw==<br>
>> ><br>
>> ><br>
>> > Note: database passwords are encrypted using AES 256 encryption and then<br>
>> > encoded into base64 for storing in pool_passwd file, AES prefix is added<br>
>> to<br>
>> > the encoded password strings after encryption+encoding for<br>
>> identification.<br>
>> ><br>
>> ><br>
>> > *Ways to provide encryption key to pg_enc utility*<br>
>> ><br>
>> > There are multiple ways to provide encryption key to pgenc utility.<br>
>> ><br>
>> > 1- using stdin ( -P, --prompt-for-key)<br>
>> ><br>
>> > 2- using command line argument (-K, --enc-key=ENCRYPTION_KEY)<br>
>> ><br>
>> > 3- using key file (-k, --key-file=KEY_FILE)<br>
>> ><br>
>> > by default the pg_enc looks for the key in home/.pgpoolkey file<br>
>> ><br>
>> > and the default location can be over ridden by PGPOOLKEYFILE<br>
>> ><br>
>> > environment variable<br>
>> ><br>
>> ><br>
>> > Try pg_enc --help for more details<br>
>> ><br>
>> ><br>
>> > *Providing encryption key to Pgpool-II*<br>
>> ><br>
>> > Pgpool-II reads the encryption key from *pgpoolkey* file, the path to the<br>
>> > key file can be specified using the new (-k, --key-file=KEY_FILE) command<br>
>> > line argument, if the argument is not provided, Pgpool-II will try to<br>
>> look<br>
>> > for the key file in user's home directory.<br>
>> ><br>
>> > If you do not want to use the key file at default location and also do<br>
>> not<br>
>> > want to specify the location in command line argument you can also<br>
>> specify<br>
>> > the poolkey file in *PGPOOLKEYFILE* environment variable.<br>
>> ><br>
>> ><br>
>> > If encryption key is not provided to Pgpool-II or the wrong encryption<br>
>> key<br>
>> > is provided the encrypted password entries in pool_passwd becomes<br>
>> unusable.<br>
>> ><br>
>> ><br>
>> > *AES encrypted password entries can also be used for md5 auth method*<br>
>> ><br>
>> > Previously only way to use md5 authentication method was to add md5<br>
>> encoded<br>
>> > password to the pool_passwd file for the user. now with this<br>
>> ><br>
>> > patch md5 authentication system in Pgpool-II can also use the AES<br>
>> encrypted<br>
>> > passwords.<br>
>> ><br>
>> > So same AES encrypted password can be used for SCRAM, clear-text and md5<br>
>> > authentications.<br>
>> ><br>
>> ><br>
>> > *Using CERT authentication between Pgpool-II and frontend*<br>
>> ><br>
>> > To use the cert authentication between Pgpool-II and frontend configure<br>
>> the<br>
>> > following ssl configurations in pgpool.conf<br>
>> ><br>
>> > ssl_key = '/server.key'<br>
>> ><br>
>> > ssl_cert = 'server.crt'<br>
>> ><br>
>> > ssl_ca_cert = 'root.crt'<br>
>> > Note: You must use the same ssl certificates in Pgpool-II that are used<br>
>> by<br>
>> > backend PostgreSQL server.<br>
>> ><br>
>> > Now configure pool_hba.conf to use cert ( in this example we want<br>
>> > PostgreSQL user named cert_user to use cert auth)<br>
>> ><br>
>> ><br>
>> > *hostssl all cert_user 0/0 cert*<br>
>> ><br>
>> ><br>
>> > This will enable the cert authentication between pgpool and frontend<br>
>> > clients. After this cert_user will only be able to connect to Pgpool-II<br>
>> > when it will present the valid ssl client certificate with the<br>
>> certificate<br>
>> > having the common name same as the database user name (cert_user in this<br>
>> > case)<br>
>> ><br>
>> ><br>
>> > You can use any other auth method for same cert_user in backend.<br>
>> ><br>
>> > I will also share the detailed step by step guide for using the cert<br>
>> > authentication later.<br>
>> ><br>
>> ><br>
>> > *Example test case for SCRAM authentication:*<br>
>> ><br>
>> ><br>
>> > 1-- create user in PostgreSQL with SCRAM type password<br>
>> ><br>
>> ><br>
>> > SET password_encryption = 'scram-sha-256';<br>
>> ><br>
>> > CREATE ROLE scram_user PASSWORD 'scram_password';<br>
>> ><br>
>> > ALTER ROLE scram_user WITH LOGIN;<br>
>> ><br>
>> ><br>
>> > 2-- create the encryption key file in home directory<br>
>> ><br>
>> ><br>
>> > echo poolencryptionkey >> ~/.pgpoolkey<br>
>> ><br>
>> ><br>
>> > 3-- create the pool_passwd entry for scram_user<br>
>> ><br>
>> ><br>
>> > pg_enc -m -f pgpool.conf -u sceam_user scram_password<br>
>> ><br>
>> ><br>
>> > 4-- adjust pg_hba.conf to use scram for scram_user<br>
>> ><br>
>> ><br>
>> > host all scram_user 0/0 scram-sha-256<br>
>> ><br>
>> ><br>
>> > 5-- configure pool_hba.conf to use scram for scram_user<br>
>> ><br>
>> ><br>
>> > host all scram_user 0/0 scram-sha-256<br>
>> ><br>
>> ><br>
>> > 6-- restart postgresql and run pgpool<br>
>> ><br>
>> ><br>
>> > 7-- connect through pgpool using scram user<br>
>> ><br>
>> ><br>
>> > psql -h 127.0.0.1 -U scram_user -p 9999 postgres<br>
>> ><br>
>> ><br>
>> ><br>
>> > *Regression test cases*<br>
>> ><br>
>> > Patch also contains following three regression test cases, You can have a<br>
>> > look into those get a idea.<br>
>> ><br>
>> > 020.allow_clear_text_frontend_auth<br>
>> ><br>
>> > 021.pool_passwd_auth<br>
>> ><br>
>> > 022.pool_passwd_alternative_auth<br>
>> ><br>
>> ><br>
>> ><br>
>> > Comments and suggestions are most welcome.<br>
>> ><br>
>> ><br>
>> > Thanks<br>
>> ><br>
>> > Best Regards<br>
>> ><br>
>> > Muhammad Usama<br>
>><br>
</blockquote></div></div>