6.4. Using AES256 encrypted passwords in pool_passwd

SCRAM authentication guards against the man-in-the-middle type of attack, so Pgpool-II requires the user password to authenticate with the PostgreSQL backend.

However, storing the clear text passwords in the "pool_passwd" file is not a good idea.

You can instead store AES256 encrypted passwords, which will be used for authentication. The password is first encrypted using the AES256 encryption with the user provided key and then the encrypted password is base64 encoded and an AES prefix is added to the encoded string.

Note: You can use the pg_enc utility to create the properly formatted AES256 encrypted password.

6.4.1. Creating encrypted password entries

pg_enc can be used to create AES encrypted password entries in pool_passwd file. pg_enc requires the key for encrypting the password entries. Later that same key will be required by Pgpool-II to decrypt the passwords to use for authentication.

Note: Pgpool-II must be built with SSL (--with-openssl) support to use the encrypted password feature.

6.4.2. Providing decryption key to Pgpool-II

If you have AES encrypted passwords stored in the pool_passwd file, then Pgpool-II will require the decryption key to decrypt the passwords before using them, Pgpool-II tries to read the decryption key at startup from the .pgpoolkey file. .pgpoolkey is a plain text file which contains the decryption key string.

By default the Pgpool-II will look for the .pgpoolkey file in the user's home directory or the file referenced by environment variable PGPOOLKEYFILE. You can also specify the key file using the (-k, --key-file=KEY_FILE) command line argument to the pgpool command. The permissions on .pgpoolkey must disallow any access to world or group. Change the file permissions by the command chmod 0600 ~/.pgpoolkey.