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

[Muhammad 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.



> 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.



> 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?

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/20180723/e2508f04/attachment-0001.html>