How to Configure SCRAM and MD5 Authentication in Pgpool-II

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 pgpoolExecute 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: [pgpool user’s 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


Adding to pool_passwd

pg_enc can be used for pool_passwd passwords with:

     pg_enc -m -f /path/to/pgpool.conf -u username -p
     db password: [your password]

which will add an entry for username with the password given.

To avoid password prompt or password in command parameter, pg_enc can read user name:password pairs from file. It will add all user names and encrypted password to pool_passwd authentication file.