<div dir="ltr">
<span></span>
<p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font face="arial, helvetica, sans-serif" size="4">Hi All,</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font face="arial, helvetica, sans-serif" size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font face="arial, helvetica, sans-serif" size="4">I have been working on supporting the new authentication methods in Pgpool-II, So here is the working <span style="font-variant-ligatures:no-common-ligatures">patch for supporting the SCRAM and CERT based authentication methods.</span></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">The patch needs a some compilation warning fixes and little bit of review of memory management and code cleanups (which I am working on) but other than that it is ready for review testing.</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font face="arial, helvetica, sans-serif" size="4"><font color="#000000"><span style="font-variant-ligatures:no-common-ligatures">The code is also checked-in to </span></font>SCRAM_AUTH branch in Pgpool's git repo
</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font face="arial, helvetica, sans-serif" size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font face="arial, helvetica, sans-serif" size="4">Below is the brief overview of changes made by the patch and how to use the newly added auth methods.</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><font size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><font size="4"></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><b style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif">Allow different auth methods for frontend and backend for user session:</font></b><br></p><font size="4"><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures">The patch made it possible to use </span><span style="font-variant-ligatures:no-common-ligatures">different authentication methods between client </span><span style="font-variant-ligatures:no-common-ligatures">to Pgpool-II and Pgpool-II to backend.</span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif"><span></span></font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif">For example: now we can use clear-text-password authentication between Pgpool-II and frontend applications while md5 or scram auth method to authenticate the backend connections for same session. Similarly the frontend can now be authenticated using the ssl certificate (CERT auth) while for backend we can use md5 or scram for the same session.</font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif"><br></font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><b><font size="4" face="arial, helvetica, sans-serif">Able to use MD5 and SCRAM auth methods without pool_passwd:</font></b></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif"><font style="font-weight:normal">The patch adds a new configuration parameter </font><span style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><i><b>allow_clear_text_frontend_auth</b></i><font style="font-weight:normal">, enabling this config allows the Pgpool-II to use clear-text-password authentication with frontend clients when pool_passwd file does not contains the password for the connecting user, and use that password (provided by client) to authenticate with the backend using MD5 and/or SCRAM authentication.</font></span></font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;font-size:small;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif" style="font-family:Arial,Helvetica,sans-serif">Note: </font><font size="4" face="monospace, monospace">allow_clear_text_frontend_auth</font><font size="4" face="arial, helvetica, sans-serif" style="font-family:Arial,Helvetica,sans-serif"> only works when pool_hba.conf is not enabled in pgpool.conf</font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;font-size:small;line-height:normal;font-family:Arial,Helvetica,sans-serif;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif"><br></font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:small;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4">For example: suppose PostgreSQL servers has a user named "<i style="font-weight:normal"><font face="monospace, monospace">some_user</font></i>" which can connect to database using SCRAM authentication, Now for this "some_user" to connect to PostgreSQL using SCRAM through Pgpool-II we must have the <i style="font-weight:normal;font-size:large;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><font face="monospace, monospace">some_user</font></i>'s password stored in the pool_passwd file, but if in some case when pool_passwd does not have the entry of "<i style="font-weight:normal;font-size:large;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><font face="monospace, monospace">some_user</font></i>" and<span style="font-weight:normal;font-family:Arial,Helvetica,sans-serif"> </span><i style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><font face="monospace, monospace">allow_clear_text_frontend_auth</font><b style="font-weight:normal"> </b></i>is enabled in the pgpool.conf then Pgpool-II will ask the connecting frontend to use clear-text-password auth method for authentication, and after receiving the password from the client, Pgpool-II will use that password to authenticate with backend using the required SCRAM auth.</font></span></p><br class="gmail-Apple-interchange-newline"></font><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><b><font size="4" face="arial, helvetica, sans-serif">Creating encrypted passwords:</font></b></span></p><p class="gmail-p1" style="font-weight:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4"><font style="font-family:arial,helvetica,sans-serif;font-variant-ligatures:no-common-ligatures">The patch adds a new utility </font><b style="font-family:arial,helvetica,sans-serif;font-variant-ligatures:no-common-ligatures"><i>pg_enc</i></b><font style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif"> to create AES encrypted password entries. The utility works similar in most ways as </font><font face="monospace, monospace">pg_md5</font><font face="arial, helvetica, sans-serif"> utility, with a some small differences,</font></font></font></p><p class="gmail-p1" style="font-weight:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4"><font style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif">pg_enc also requires the key for encrypting the password entries. later that same key is required by Pgpool-II to decrypt the passwords to be used for authentication.</font></font><br></font></p><p class="gmail-p1" style="font-weight:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif">Note: Pgpool-II must be build with ssl (--with-openssl) support to use this encrypted password feature.</font></span></p><font size="4" face="arial, helvetica, sans-serif"><br class="gmail-Apple-interchange-newline"></font><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><b><font size="4" face="arial, helvetica, sans-serif">Storing the encrypted password in pool_passwd file</font></b></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif">Since the SCRAM authentication method explicitly guards against the man-in-middle type attack so Pgpool-II can only use SCRAM auth method if it has the user password (or <i style="color:rgb(34,34,34);font-family:Arial,Helvetica,sans-serif;font-size:large;font-variant-ligatures:no-common-ligatures;text-decoration-style:initial;text-decoration-color:initial;background-color:rgb(255,255,255)"><font face="monospace, monospace">allow_clear_text_frontend_auth </font></i>is enabled). But storing the clear text password in a file is never a good idea, so for that reason pool_passwd file now allows to store user password in AES-256 encrypted format<br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif">for example:</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4" face="arial, helvetica, sans-serif"><font color="#000000">some_user:AESWJPIi/R2QJS4cHKTAtjATw==</font><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font color="#000000" size="4" face="arial, helvetica, sans-serif"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font color="#000000" size="4" face="arial, helvetica, sans-serif">Note: database passwords are encrypted using AES 256 encryption and then encoded into base64 for storing in pool_passwd file, AES prefix is added to the encoded password strings after encryption+encoding for identification.</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><br></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><b><font size="4" face="arial, helvetica, sans-serif">Ways to provide encryption key to pg_enc utility</font></b></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif">There are multiple ways to provide encryption key to pgenc utility.</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif">1- using stdin ( -P, --prompt-for-key)</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif">2- using command line argument (-K, --enc-key=ENCRYPTION_KEY)</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4" face="arial, helvetica, sans-serif"><span style="color:rgb(0,0,0);font-weight:normal">3- using key file (</span><font color="#000000">-k, --key-file=KEY_FILE)</font></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font color="#000000" size="4" face="arial, helvetica, sans-serif"> by default the pg_enc looks for the key in home/.pgpoolkey file</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><span style="color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif"> and the default location can be over ridden by PGPOOLKEYFILE</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4" face="arial, helvetica, sans-serif"><span style="color:rgb(0,0,0)"> environment variable</span></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><br></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><span style="color:rgb(0,0,0);font-family:arial,helvetica,sans-serif;font-size:large;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">Try </span><span style="color:rgb(0,0,0);font-size:large;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><font face="monospace, monospace">pg_enc --help</font></span><span style="color:rgb(0,0,0);font-family:arial,helvetica,sans-serif;font-size:large;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"> for more details </span><br></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><span style="color:rgb(0,0,0);font-family:arial,helvetica,sans-serif;font-size:large;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><br></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><span style="color:rgb(0,0,0)"><b><font size="4" face="arial, helvetica, sans-serif">Providing encryption key to Pgpool-II</font></b></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4" face="arial, helvetica, sans-serif"><span style="color:rgb(0,0,0)">Pgpool-II reads the encryption key from <i><b>pgpoolkey</b></i> file, the path to the key file can be specified using the new </span><span style="color:rgb(0,0,0)">(</span><font color="#000000" style="color:rgb(0,0,0)">-k, --key-file=KEY_FILE) command line argument, if the argument is not provided, Pgpool-II will try to look for the key file in user's home directory.</font></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4" face="arial, helvetica, sans-serif"><font color="#000000" style="color:rgb(0,0,0)">If you do not want to use the key file at default location and also do not want to specify the location in command line argument you can also specify the poolkey file in <b><i>PGPOOLKEYFILE</i></b> environment variable.</font><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif">If encryption key is not provided to Pgpool-II or the wrong encryption key is provided the encrypted password entries in pool_passwd becomes unusable.</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><font size="4" face="arial, helvetica, sans-serif"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1"><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures"><b>AES encrypted password entries can also be used for md5 auth method</b></span></font></span></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:400;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures">Previously only way to use md5 authentication method was to add md5 encoded password to the pool_passwd file for the user. now with this</span></font></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:400;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures">patch md5 authentication system in Pgpool-II can also use the AES encrypted passwords.</span></font></p><p class="gmail-p1" style="color:rgb(34,34,34);font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:400;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures">So same AES encrypted password can be used for SCRAM, clear-text and md5 authentications.</span><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;color:rgb(0,0,0)"><br></p><p class="gmail-p1" style="font-family:Menlo;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><b><font size="4">Using CERT authentication between Pgpool-II and frontend</font></b></span></p><p class="gmail-p1" style="font-weight:normal;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures">To use the cert authentication between Pgpool-II and frontend configure the following ssl configurations in pgpool.conf</span><br></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;margin:0px"><font size="4" face="monospace, monospace"><span style="font-variant-ligatures:no-common-ligatures">ssl_key = '/server.key'</span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;margin:0px"><font size="4" face="monospace, monospace"><span style="font-variant-ligatures:no-common-ligatures">ssl_cert = 'server.crt'</span><br></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;margin:0px"><font size="4" face="monospace, monospace"><span style="font-variant-ligatures:no-common-ligatures">ssl_ca_cert = 'root.crt'</span><br></font></p><span style="font-variant-ligatures:no-common-ligatures;text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><font size="4" face="arial, helvetica, sans-serif">Note: You must use the same ssl certificates in Pgpool-II that are used by backend PostgreSQL server.</font></span><div><span style="font-variant-ligatures:no-common-ligatures;text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><span><font size="4" face="arial, helvetica, sans-serif"><br></font></span></span></div><div><font size="4" face="arial, helvetica, sans-serif"><span style="font-variant-ligatures:no-common-ligatures;text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><span>Now </span></span><span style="font-variant-ligatures:no-common-ligatures">configure pool_hba.conf to use cert ( in this example we </span></font><span style="font-variant-ligatures:no-common-ligatures;font-family:arial,helvetica,sans-serif;font-size:large">want PostgreSQL user named cert_user to use cert auth)</span></div><div><p class="gmail-p1" style="font-weight:normal;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4" face="arial, helvetica, sans-serif"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"></span></font></p><p class="gmail-p1" style="font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px;background-color:rgb(255,255,255)"><span class="gmail-s1"><font size="4" face="monospace, monospace"><i><font><span style="font-variant-ligatures:no-common-ligatures">hostssl all cert_user 0/0 cert</span></font><br></i></font></span></p><p class="gmail-p1" style="font-weight:normal;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif"><br></font></span></p><font size="4" face="arial, helvetica, sans-serif">This will enable the cert authentication between pgpool and frontend clients. After this cert_user will only be able to connect to Pgpool-II when it will present the valid ssl client certificate with the certificate having the common name same as the database user name (cert_user in this case)</font><p></p><p class="gmail-p1" style="font-weight:normal;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif"><br></font></span></p><p class="gmail-p1" style="font-weight:normal;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4" face="arial, helvetica, sans-serif">You can use any other auth method for same cert_user in backend.</font></span></p><p class="gmail-p1" style="font-weight:normal;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4"><font face="arial, helvetica, sans-serif">I will also share the detailed step by step guide for using the cert authentication later. <br class="gmail-Apple-interchange-newline"></font><br></font></span></p><p class="gmail-p1" style="font-weight:normal;font-family:Menlo;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="font-family:Menlo;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><b><font size="4">Example test case for SCRAM authentication:</font></b></span></p><p class="gmail-p1" style="font-weight:normal;font-family:Menlo;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4"><br></font></p><p class="gmail-p1" style="font-weight:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">1-- create user in PostgreSQL with SCRAM type password</font></span></p><p class="gmail-p1" style="font-weight:normal;font-family:Menlo;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures">SET password_encryption = 'scram-sha-256';</span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures">CREATE ROLE scram_user PASSWORD 'scram_password';</span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font size="4"><font face="Menlo"><span style="font-variant-ligatures:no-common-ligatures">ALTER ROLE scram_user WITH LOGIN;</span></font><br></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures">2-- create the encryption key file in home directory</span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></p><p class="gmail-p1" style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><font face="monospace, monospace" size="4"><span style="font-variant-ligatures:no-common-ligatures">echo poolencryptionkey >> ~/.<span style="color:rgb(0,0,0);font-variant-ligatures:normal;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">pgpoolkey</span></span><br></font></p><p class="gmail-p1" style="font-weight:normal;font-family:Menlo;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="font-weight:normal;font-family:Menlo;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font size="4">3-- create the pool_passwd entry for scram_user</font></span></p><p class="gmail-p1" style="font-weight:normal;font-family:Menlo;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="font-weight:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;text-decoration-style:initial;text-decoration-color:initial;margin:0px"><span style="font-variant-ligatures:no-common-ligatures"><font face="monospace, monospace" size="4">pg_enc -m -f pgpool.conf -u sceam_user <span style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">scram_password</span></font></span></p><p></p><p class="gmail-p1" style="font-family:Menlo;font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">4-- adjust pg_hba.conf to use scram for scram_user</font></span></p><p class="gmail-p1" style="font-family:Menlo;font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="monospace, monospace" size="4"><span style="font-variant-ligatures:no-common-ligatures">host all scram_user 0/0 scram-sha-256</span><br></font></span></p><p class="gmail-p1" style="font-family:Menlo;font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">5-- configure pool_hba.conf to use scram for scram_user</font></span></p><p class="gmail-p1" style="font-family:Menlo;font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font size="4"><br></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="monospace, monospace" size="4"><span style="font-variant-ligatures:no-common-ligatures">host all scram_user 0/0 scram-sha-256</span><br></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures">6-- restart postgresql and run pgpool</span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures">7-- connect through pgpool using scram user</span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="monospace, monospace" size="4"><span style="font-variant-ligatures:no-common-ligatures">psql -h 127.0.0.1 -U scram_user -p 9999 postgres</span><br></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font size="4"><b>Regression test cases</b></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font size="4">Patch also contains following three regression test cases, You can have a look into those get a idea.</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4">020.allow_clear_text_frontend_auth</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4">021.pool_passwd_auth</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4"></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal"><font size="4">022.pool_passwd_alternative_auth</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font face="arial, helvetica, sans-serif" size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font face="arial, helvetica, sans-serif" size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font face="arial, helvetica, sans-serif" size="4">Comments and suggestions are most welcome.</font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font face="arial, helvetica, sans-serif" size="4"><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">Thanks</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">Best Regards</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span style="font-variant-ligatures:no-common-ligatures"><font face="arial, helvetica, sans-serif" size="4">Muhammad Usama</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><span class="gmail-s1"><font face="Menlo" size="4"><span style="font-variant-ligatures:no-common-ligatures"><br></span></font></span></p><p class="gmail-p1" style="font-family:Menlo;font-weight:normal;margin:0px;font-variant-ligatures:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;line-height:normal;background-color:rgb(255,255,255)"><font size="4"><br></font></p><div><font size="4"><br></font></div><div><br></div></div></div>