How to Configure SCRAM and MD5 Authentication in Pgpool-II



This post refers to my last one Authentication in Pgpool-II. In the post I introduced how Pgpool-II authentication mechanism works. In this post I will describe how to configure SCRAM and MD5 authentication methods in details.

scram-sha-256 Authentication Configuration

To perform the password-based authentication, Pgpool-II requires a password file which contains a list of database users and passwords in format username:password. The default password file name is pool_passwd.

If scram-sha-256 is specified as the authentication method in pool_hba.conf or pg_hba.conf, to use scram-sha-256 authentication, the decryption key to decrypt the passwords is required. We create the .pgpoolkey file in the home directory of Pgpool-II start user.

Here we assume that Pgpool-II is started using postgres user. 

# su - postgres
$ echo 'some string' > ~/.pgpoolkey
$ chmod 600 ~/.pgpoolkey
 

Assuming you've already created a database user pgpool. Execute the pg_enc to register the user pgpool and AES encrypted password to pool_passwd.  

# su - postgres
$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
db password:
trying to read key from file /var/lib/pgsql/.pgpoolkey
$ cat /etc/pgpool-II/pool_passwd
pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
 

MD5 Authentication Configuration

Execute the pg_md5 to register the user pgpool and md5 encrypted password to pool_passwd.  

# pg_md5 --md5auth -f /etc/pgpool-II/pgpool.conf -u pgpool -p
# cat /etc/pgpool-II/pool_passwd
pgpool:md5f24aeb1c3b7d05d7eaf2cd648c307092

Register user:password from a file

Since the coming major release 4.2, Pgpool-II supports for registering user:password from a file.

AES encrypted password

$ cat users.txt
username1:secretpassword1
username2:secretpassword2

$ pg_enc -m -f /etc/pgpool-II/pgpool.conf -i users.txt
trying to read key from file /var/lib/pgsql/.pgpoolkey

$ cat /etc/pgpool-II/pool_passwd
username1:AESnGfDT45wWxwDhy0CYzJ6RQ==
username2:AES8cwgIELJzHtCGXwubZpsHg==

MD5 encrypted password

$ cat users.txt
username3:secretpassword3
username4:secretpassword4

$ pg_md5 -m -f /etc/pgpool-II/pgpool.conf -i users.txt
trying to read username:password pairs from file users.txt

$ cat /etc/pgpool-II/pool_passwd
username3:md5300d92c36d9f411a7e0d634cc1a4d45c
username4:md586b493ed9d09d4aec56e25aa7eb87ce3 

Conclusion

In this blog I described how to configure SCRAM and MD5 authentication. As I mentioned in my last post, Pgpool-II supports several authentication methods. In future blogs, I am going to describe other authentication methods (e.g. Certificate Authentication, LDAP Authentication) in details.

Comments

Popular posts from this blog

Connection Pooling in Pgpool-II

Authentication in Pgpool-II