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

[Tatsuo Ishii]

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.

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?

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