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

Muhammad Usama m.usama at gmail.com
Sun Jul 22 06:03:25 JST 2018


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/20180722/674a20e4/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: scram_auth_feature_patch.diff
Type: application/octet-stream
Size: 4613976 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20180722/674a20e4/attachment-0001.obj>


More information about the pgpool-hackers mailing list