Below are the steps that will help integrate the EFM +Pg_pool using SSL authentication.
You can enable SSL authentication for Failover Manager which is integrated with pgpool. All connecting clients are required to use SSL authentication which are coming from pgpool when connecting to any database server in the cluster.
Setting SSL on EDB database:
======================= EDB ==============================
0-$++++++++++++++++ check cluster status
/usr/edb/efm-4.4/bin/efm cluster-status efm
1-$++++++++++++++++ create a directory for cert files
sudo mkdir /var/lib/edb/as12/certfiles
2-$++++++++++++++++ push cert file in the indicated location
sudo cp -r /home/feiadmin/termdevcerts/ /var/lib/edb/as12/certfiles
3-$++++++++++++++++ change ownership and permission
sudo chown -R enterprisedb:enterprisedb /var/lib/edb/as12/certfiles
sudo chmod 600 /var/lib/edb/as12/certfiles/termdev_server.key
4-$++++++++++++++++ on standby Edit EDB recovery config file (for edb-as-11, 10)
sudo vi /var/lib/edb/as12/data/recovery.conf
Edit SSL mode parameter
sslmode =require
5-$++++++++++++++++ Edit EDB postgresql config file to add following parameters
sudo vi /var/lib/edb/as12/data/postgresql.conf
sudo vi /var/lib/edb/as12/data/postgresql.conf
# SSL encryption parameters
ssl = on
ssl_cert_file = '/var/lib/edb/as12/certfiles/termdev_server.crt'
ssl_key_file = '/var/lib/edb/as12/certfiles/termdev_server.key'
ssl_ca_file = '/var/lib/edb/as12/certfiles/termdev_bundle.crt'
ssl_crl_file = ''
Setting SSL on EFM:
======================= EFM ==============================
6-$++++++++++++++++ Edit EFM properties file
sudo vi /etc/edb/efm-4.4/efm.properties
jdbc.ssl=true
jdbc.ssl.mode=verify-ca
7-$+++++++++++++++convert the certificate to a form that Java understands ( cd to location cert files)
openssl x509 -in termdev_server.crt -out termdev_server.crt.der -outform der
8-$+++++++++++++++add the certificate to the Java trusted certificates file
keytool -keystore /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/jre/lib/security/cacerts -alias EDBAS-cert -import -file termdev_server.crt.der
keytool -list -keystore /etc/pki/java/cacerts -storepass changeit -alias EDBAS-cert
Stop efm on both standby and master and restart both starting with the master
9-$++++++++++++++++ ### Edit EDB hba config file (Coordinated with other teams before enforcing this)
sudo vi /var/lib/edb/as12/data/pg_hba.conf
hostnossl all all all reject
10-$++++++++++++++++### From the localcerts Dic To this PKI Trust location
sudo cp localcertfiles/ /etc/pki/ca-trust/source/anchors/
11-$++++++++++++++++### Restart or Reload edb-as-12.service
sudo systemctl stop edb-as-12.service
sudo systemctl start edb-as-12.service
systemctl status edb-as-12.service
Perform below steps on pgpool node :
======================= pgpool ==============================
0-$++++++++++++++++ create a directory for cert files
sudo mkdir /etc/sysconfig/edb/pgpool3.5/certfiles
1-$++++++++++++++++ push cert file in the indicated location
sudo cp -r /home/feiadmin/termdevcerts/ /etc/sysconfig/edb/pgpool3.5/certfiles
2-$++++++++++++++++ change ownership and permission of certfiles
sudo chown -R enterprisedb:enterprisedb /etc/sysconfig/edb/pgpool3.5/certfiles/*
sudo chmod 600 /etc/sysconfig/edb/pgpool3.5/certfiles/termdev_server.key
3-$++++++++++++++++### Edit pgpool config file with the following parameters
sudo vi /etc/sysconfig/edb/pgpool3.5/pgpool.conf
-- SSL parameters
ssl_key = '/etc/sysconfig/edb/pgpool3.5/certfiles/termdev_server.key'
ssl_cert = '/etc/sysconfig/edb/pgpool3.5/certfiles/termdev_server.crt'
ssl_ca_cert = '/etc/sysconfig/edb/pgpool3.5/certfiles/termdev_bundle.crt'
--Authentication parameters
enable_pool_hba = on
pool_passwd = 'pool_passwd'
4-$++++++++++++++++### Edit pool_hba.conf file (Coordinated with other teams before enforcing this)
sudo vi /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
hostnossl all all 0.0.0.0/0 reject
5-$++++++++++++++++### Restart or Reload edb-pgpool-3.5.service (Coordinated with other teams )
sudo systemctl stop edb-pgpool-3.5.service
sudo systemctl start edb-pgpool-3.5.service
sudo systemctl status edb-pgpool-3.5.service
Note:
If not active then remove the socket file from /tmp and start again
sudo rm -rf .s.PGSQL.9*
6-$++++++++++++++++ Set pool_hba.conf to use cert authentication for all SSL connections (skip this step if not using certification for authentication)
hostssl all all 0.0.0.0/0 cert