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: [pgpool user’s 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
Reference: https://b-peng.blogspot.com/2020/09/how-to-configure-scram-and-md5.html
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.