[pgpool-hackers: 2890] Re: New feature: supporting SCRAM and CERT based authentication in Pgpool-II

Muhammad Usama m.usama at gmail.com
Wed Jul 25 20:43:48 JST 2018


On Wed, Jul 25, 2018 at 12:09 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> Hi Usama,
>
> > Hi Ishii-San
> >
> >
> > On Mon, Jul 23, 2018 at 7:13 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >
> >> Usama,
> >>
> >> Thank you for the updation!
> >>
> >> Here are some commnets.
> >>
> >> 1) There are some traling space additions in the patch.
> >>
> >> t-ishii at localhost: git apply ~/scram_auth_feature_patch.diff
> >> /home/t-ishii/scram_auth_feature_patch.diff:1692: trailing whitespace.
> >>
> >> /home/t-ishii/scram_auth_feature_patch.diff:1696: trailing whitespace.
> >>
> >> /home/t-ishii/scram_auth_feature_patch.diff:1702: trailing whitespace.
> >>
> >> /home/t-ishii/scram_auth_feature_patch.diff:1709: trailing whitespace.
> >>
> >> /home/t-ishii/scram_auth_feature_patch.diff:2792: trailing whitespace.
> >>
> >> warning: squelched 87 whitespace errors
> >> warning: 92 lines add whitespace errors.
> >>
> >>
> > Yes there are few debug message cleanups and these whitespaces error
> exists
> > in the patch,
> > I will fix those in next version.
>
> Ok.
>
> >> 2) Design suggesions
> >>
> >> > Note: allow_clear_text_frontend_auth only works when pool_hba.conf is
> not
> >> > enabled in pgpool.conf
> >> >
> >> >
> >> > For example: suppose PostgreSQL servers has a user named "*some_user*"
> >> > 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 *some_user*'s password stored in the pool_passwd file, but
> if in
> >> > some case when pool_passwd does not have the entry of "*some_user*"
> and
> >> > *allow_clear_text_frontend_auth *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.
> >>
> >> Why do we need "allow_clear_text_frontend_auth"? Rather, can't we set
> >> "password" in the pool_hba.conf auth method for a user who is allow to
> >> connect to pgpool with clear text password? This way is more flexible
> >> since at the same we can force different user MD5 and/or SCRAM auth.
> >>
> >
> > Setting the "password" auth in pool_hba.conf  does work as you described,
> > while allow_clear_text_frontend_auth is for the cases when pool_hba is
> > disabled in pgpool.conf.
> > So setting allow_clear_text_frontend_auth allows the clients to connect
> > through pgpool
> > when they don't want to use pool_hba while backend requires SCRAM or MD5
> > auth.
>
> Ok, that makes sense.
>
> >> 4) Migration of pool_passwd
> >>
> >> If user have large number of entries in pool_passwd, migrating to new
> >> pool_passwd using AES encryption is pain. Is there any way to migate
> >> the pain?
> >>
> >
> > Yes that could be a problem, and also the case
> > when user want to change the encryption password for entries in
> pool_passwd.
> > I will try to enhance pg_enc to tackle these two cases separately.
> > But for the older versions pool_passwd file I don't think we can do much
> > about the existing
> > entries. since currently pool_passwd only supports MD5 entries which is
> one
> > way
> > hashing. So for old pool_passwd file either users have to create new
> > entries from scratch or have
> > to stick with MD5 auth.
> > Do you have some suggestion to work around that?
>
> No. Probably there's no clear answer to this for now. Let's leave as
> it until we come up with a better idea.
>

Sure

>
> BTW, in the new regression test: 021.pool_passwd_auth I see clear text
> password is stored in pool_passwd. I thought your implementation
> allows to store password in AES 256 encryption, which is great. Is
> there any reason you did not use the feature?
>

Not really, Actually I wrote the test case before completing the AES
encryption system,
I will add another test case to test encrypted passwords in pool_passwd

Thanks
Best Regards
Muhammad Usama

>
> > Thanks
> > Best Regards
> > Muhammad Usama
> >
> >
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >>
> >> > Hi All,
> >> >
> >> >
> >> > I have been working on supporting the new authentication methods in
> >> > Pgpool-II, So here is the working patch for supporting the SCRAM and
> CERT
> >> > based authentication methods.
> >> >
> >> > 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.
> >> >
> >> > The code is also checked-in to SCRAM_AUTH branch in Pgpool's git repo
> >> >
> >> >
> >> > Below is the brief overview of changes made by the patch and how to
> use
> >> the
> >> > newly added auth methods.
> >> >
> >> >
> >> > *Allow different auth methods for frontend and backend for user
> session:*
> >> >
> >> > The patch made it possible to use different authentication methods
> >> between
> >> > client to Pgpool-II and Pgpool-II to backend.
> >> >
> >> > 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.
> >> >
> >> >
> >> > *Able to use MD5 and SCRAM auth methods without pool_passwd:*
> >> >
> >> > The patch adds a new configuration parameter
> >> > *allow_clear_text_frontend_auth*, 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.
> >> >
> >> > Note: allow_clear_text_frontend_auth only works when pool_hba.conf is
> not
> >> > enabled in pgpool.conf
> >> >
> >> >
> >> > For example: suppose PostgreSQL servers has a user named "*some_user*"
> >> > 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 *some_user*'s password stored in the pool_passwd file, but
> if in
> >> > some case when pool_passwd does not have the entry of "*some_user*"
> and
> >> > *allow_clear_text_frontend_auth *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.
> >> >
> >> > *Creating encrypted passwords:*
> >> >
> >> > The patch adds a new utility *pg_enc* to create AES encrypted password
> >> > entries. The utility works similar in most ways as pg_md5 utility,
> with a
> >> > some small differences,
> >> >
> >> > 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.
> >> >
> >> > Note: Pgpool-II must be build with ssl (--with-openssl) support to use
> >> this
> >> > encrypted password feature.
> >> >
> >> > *Storing the encrypted password in pool_passwd file*
> >> >
> >> > 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 *allow_clear_text_frontend_auth *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
> >> >
> >> > for example:
> >> >
> >> > some_user:AESWJPIi/R2QJS4cHKTAtjATw==
> >> >
> >> >
> >> > 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.
> >> >
> >> >
> >> > *Ways to provide encryption key to pg_enc utility*
> >> >
> >> > There are multiple ways to provide encryption key to pgenc utility.
> >> >
> >> > 1- using stdin ( -P, --prompt-for-key)
> >> >
> >> > 2- using command line argument (-K, --enc-key=ENCRYPTION_KEY)
> >> >
> >> > 3- using key file (-k, --key-file=KEY_FILE)
> >> >
> >> >    by default the pg_enc looks for the key in home/.pgpoolkey file
> >> >
> >> >    and the default location can be over ridden by PGPOOLKEYFILE
> >> >
> >> >    environment variable
> >> >
> >> >
> >> > Try  pg_enc --help  for more details
> >> >
> >> >
> >> > *Providing encryption key to Pgpool-II*
> >> >
> >> > Pgpool-II reads the encryption key from *pgpoolkey* file, the path to
> the
> >> > key file can be specified using the new (-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.
> >> >
> >> > 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 *PGPOOLKEYFILE* environment variable.
> >> >
> >> >
> >> > 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.
> >> >
> >> >
> >> > *AES encrypted password entries can also be used for md5 auth method*
> >> >
> >> > 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
> >> >
> >> > patch md5 authentication system in Pgpool-II can also use the AES
> >> encrypted
> >> > passwords.
> >> >
> >> > So same AES encrypted password can be used for SCRAM, clear-text and
> md5
> >> > authentications.
> >> >
> >> >
> >> > *Using CERT authentication between Pgpool-II and frontend*
> >> >
> >> > To use the cert authentication between Pgpool-II and frontend
> configure
> >> the
> >> > following ssl configurations in pgpool.conf
> >> >
> >> > ssl_key = '/server.key'
> >> >
> >> > ssl_cert = 'server.crt'
> >> >
> >> > ssl_ca_cert = 'root.crt'
> >> > Note: You must use the same ssl certificates in Pgpool-II that are
> used
> >> by
> >> > backend PostgreSQL server.
> >> >
> >> > Now configure pool_hba.conf to use cert ( in this example we want
> >> > PostgreSQL user named cert_user to use cert auth)
> >> >
> >> >
> >> > *hostssl    all         cert_user         0/0     cert*
> >> >
> >> >
> >> > 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)
> >> >
> >> >
> >> > You can use any other auth method for same cert_user in backend.
> >> >
> >> > I will also share the detailed step by step guide for using the cert
> >> > authentication later.
> >> >
> >> >
> >> > *Example test case for SCRAM authentication:*
> >> >
> >> >
> >> > 1-- create user in PostgreSQL with SCRAM type password
> >> >
> >> >
> >> > SET password_encryption = 'scram-sha-256';
> >> >
> >> > CREATE ROLE scram_user PASSWORD 'scram_password';
> >> >
> >> > ALTER ROLE scram_user WITH LOGIN;
> >> >
> >> >
> >> > 2-- create the encryption key file in home directory
> >> >
> >> >
> >> > echo poolencryptionkey >> ~/.pgpoolkey
> >> >
> >> >
> >> > 3-- create the pool_passwd entry for scram_user
> >> >
> >> >
> >> > pg_enc -m -f pgpool.conf -u sceam_user scram_password
> >> >
> >> >
> >> > 4-- adjust pg_hba.conf to use scram for scram_user
> >> >
> >> >
> >> > host      all   scram_user     0/0    scram-sha-256
> >> >
> >> >
> >> > 5-- configure pool_hba.conf to use scram for scram_user
> >> >
> >> >
> >> > host    all         scram_user         0/0     scram-sha-256
> >> >
> >> >
> >> > 6-- restart postgresql and run pgpool
> >> >
> >> >
> >> > 7-- connect through pgpool using scram user
> >> >
> >> >
> >> > psql -h 127.0.0.1 -U scram_user -p 9999 postgres
> >> >
> >> >
> >> >
> >> > *Regression test cases*
> >> >
> >> > Patch also contains following three regression test cases, You can
> have a
> >> > look into those get a idea.
> >> >
> >> > 020.allow_clear_text_frontend_auth
> >> >
> >> > 021.pool_passwd_auth
> >> >
> >> > 022.pool_passwd_alternative_auth
> >> >
> >> >
> >> >
> >> > Comments and suggestions are most welcome.
> >> >
> >> >
> >> > Thanks
> >> >
> >> > Best Regards
> >> >
> >> > Muhammad Usama
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20180725/adbf7b51/attachment-0001.html>


More information about the pgpool-hackers mailing list