<div dir="ltr">Hi Ishii-San<div><br></div><div><br><div class="gmail_quote"><div dir="ltr">On Mon, Jul 23, 2018 at 7:13 AM 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">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></blockquote><div><br></div><div>Yes there are few debug message cleanups and these whitespaces error exists in the patch,</div><div>I will fix those in next version.</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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 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 auth<br>
> method for authentication, and after receiving the password from the<br>
> client, Pgpool-II will use that password to authenticate with backend 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></blockquote><div><br></div><div>Setting the "password" auth in pool_hba.conf does work as you described,</div><div>while <span style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">allow_clear_text_frontend_aut</span><span style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">h </span>is for the cases when pool_hba is disabled in pgpool.conf.</div><div><span style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><span style="text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">So setting <span style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;background-color:rgb(255,255,255);float:none;display:inline">allow_clear_text_frontend_aut</span><span style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;background-color:rgb(255,255,255);float:none;display:inline">h </span></span></span>allows the clients to connect through pgpool </div><div>when they don't want to use pool_hba while backend requires SCRAM or MD5 auth.</div><div><br></div><div><span style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><span style="text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><span style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;background-color:rgb(255,255,255);float:none;display:inline"><br></span></span></span></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<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></blockquote><div><br></div><div>Yes that could be a problem, and also the case</div><div>when user want to change the encryption password for entries in pool_passwd.</div><div>I will try to enhance pg_enc to tackle these two cases separately.</div><div>But for the older versions pool_passwd file I don't think we can do much about the existing</div><div>entries. since currently pool_passwd only supports MD5 entries which is one way</div><div>hashing. So for old pool_passwd file either users have to create new entries from scratch or have</div><div>to stick with MD5 auth.</div><div>Do you have some suggestion to work around that?</div><div><br></div><div>Thanks</div><div>Best Regards</div><div>Muhammad Usama</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<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 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 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 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 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 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 auth<br>
> method for authentication, and after receiving the password from the<br>
> client, Pgpool-II will use that password to authenticate with backend 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 used<br>
> for authentication.<br>
> <br>
> Note: Pgpool-II must be build with ssl (--with-openssl) support to use 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 it<br>
> has the user password (or *allow_clear_text_frontend_auth *is enabled). But<br>
> storing the clear text password in a file is never a good idea, so for 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 to<br>
> the encoded password strings after encryption+encoding for 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 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 not<br>
> want to specify the location in command line argument you can also 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 key<br>
> is provided the encrypted password entries in pool_passwd becomes 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 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 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 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 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 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>
</blockquote></div></div></div>