How to Configure SCRAM and MD5 Authentication in Pgpool-II

This post refers to my previous 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

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

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

$ 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

MD5 encrypted password

$ cat users.txt

$ 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


In this blog I described how to configure SCRAM and MD5 authentication. As I mentioned in my previous 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.


Popular posts from this blog

Query Load Balancing in Pgpool-II

Installing Pgpool-II on Debian/Ubuntu

Connection Pooling in Pgpool-II