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

[Tatsuo Ishii]

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.

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.

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?

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