Version 4.0 of Pgpool-II added some very exciting security and authentication features to make it more relevant and useful for enterprise environments. It adds support for providing encrypted passwords in pool_passwd and configuration files and lets the administrators chose different authentication methods to be used for the Client<–>Pgpool-II and Pgpool-II<–>PostgreSQL authentications.
The two new authentication methods added in version 4.0 are SCRAM-SHA256 and SSL certificate authentication. SSL certificate authentication method, in particular, can be very useful for enterprises and large organizations as it provides a way to verify a user without requiring a password and saves from the hassle of password management and increase the overall security.

This article is a step by step how-to guide to setup SSL certificate authentication between client and Pgpool-II.

Generating SSL Certificates

Before we can enable the SSL Cert authentication between Pgpool-II and client applications, we require a set of server and client SSL certificates. Depending upon the requirements the CA (Certificate Authority) certificates can be purchased from a trusted organization and used for creating CA-Signed certificates for the services and applications. But for the purpose of this guide, we will generate a set of self-signed SSL certificates to use for cert authentication. 

Create a root CA

The first step to setting up certificate-based authentication for your Pgpool environment is to create a trusted root certificate authority (CA). Both the Pgpool-II and client will reference the root CA when determining if they can trust each other.

[postgres@Pgpool-II ~]$ openssl req -new -x509 -days 365 -nodes -out ca.crt -keyout ca.key -subj "/CN=root-ca"
Generating a 2048 bit RSA private key
..............+++
.............................................................................................+++
writing new private key to 'ca.key'
----- 

Generate server key and certificate

For enabling the certificate-based authentication between Pgpool-II and client applications, we will require a private key file and a server certificate that is signed by the root CA (certificate authority).

“common name” CN field in the subject should match the hostname of the server. 

Generate a Private Key

[postgres@Pgpool-II ~]$ openssl genrsa -des3 -passout pass:123456 -out server.key 1024 -noout
Generating RSA private key, 1024 bit long modulus
............................................++++++
...................++++++
 e is 65537 (0x10001)

Remove the passphrase from the key

Removing the passphrase is not a mandatory step. For passphrase protected private key files Pgpool-II and PostgreSQL prompt for the passphrase during startup.

[postgres@Pgpool-II ~]$ openssl rsa -in server.key -passin pass:123456 -out server.key
writing RSA key

Create a certificate request

[postgres@Pgpool-II ~]$ openssl req -new -key server.key -out server.csr -subj "/C=CN/ST=BC/O=HighGo/CN=localhost/emailAddress=m.usama@HighGo.ca"

Create the CA-signed server certificate

[postgres@Pgpool-II ~]$ openssl x509 -req -in server.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt
Signature ok subject=/C=CN/ST=BC/O=HighGo/CN=localhost/emailAddress=m.usama@HighGo.ca
Getting CA Private Key

Set the permissions on the server key file

PostgreSQL server and client application reject the private key files that have the group or world access permissions.

[postgres@Pgpool-II ~]$ chmod 0600 certificate_dir/server.key 

Generate client certificates

We have our server (Pgpool-II) side SSL certificates, Now we want to generate the client certificates. More or less we need to perform the same steps for generating the client certificate as we have done for server certificates.

Generate a Private Key

[postgres@Pgpool-II ~]$ openssl genrsa -des3 -passout pass:password -out client.key 1024 -noout
Generating RSA private key, 1024 bit long modulus
...++++++
..............................................................++++++ 
e is 65537 (0x10001)

Remove the passphrase from the key

[postgres@Pgpool-II ~]$ openssl rsa -in client.key -passin pass:password -out client.key 
writing RSA key

Create the certificate request

[postgres@Pgpool-II ~]$ openssl req -new -key client.key -out client.csr -subj "/C=CN/ST=BC/O=pgpool/CN=postgres"

“common name” CN must be the database user name

Create the CA-signed client certificate

[postgres@Pgpool-II ~]$ openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -out client.crt -CAcreateserial
Signature ok
subject=/C=CN/ST=BC/O=pgpool/CN=postgres
Getting CA Private Key

Set the permissions on the key file

[postgres@Pgpool-II ~]$ chmod 0600 certificate_dir/client.key 

Configure Pgpool-II

We have generated the server and client SSL certificates. The next step is to configure Pgpool-II to enable certificates authentication

pgpool.conf

Set the following configurations in pgpool.conf

#enable SSL
ssl = on

#configure SSL certificates
ssl_key = 'certificate_dir/server.key'
ssl_cert = 'certificate_dir/server.crt'
ssl_ca_cert = 'certificate_dir/ca.crt'

#enable pool_hba
enable_pool_hba = on

pool_hba.conf

Set pool_hba.conf to use cert authentication for all SSL connections

hostssl   all         all                0.0.0.0/0  cert

Configure PostgreSQL

When using the SSL connection between the client application and Pgpool-II, It is mandatory to use SSL communication between Pgpool-II and PostgreSQL server as well.

Below configurations expects that all the certificate and key files we generated in the above steps are placed in /certificate_dir/ directory.

postgresql.conf

Set the following configurations in postgresql.conf

#enable SSL
ssl = on

#configure SSL certificates
ssl_key_file = 'certificate_dir/server.key'
ssl_cert_file = 'certificate_dir/server.crt'
ssl_ca_file = 'certificate_dir/ca.crt'

Both Pgpool-II and PostgreSQL server must use the same SSL certificates

Connecting to Pgpool-II

We will use psql for connecting to the Pgpool service.

By default, the Libpq clients read the certificate and key files from “user_home/.postgresql/” (~/.postgresql/) directory

libpq looks for the following files in the (~/.postgresql/) directory

File	Contents
postgresql.crt	client certificate
postgresql.key	client private key
root.crt	trusted certificate authorities

We can also override the default path for certificate files using the environment variables and also by explicitly specifying the certificate file paths in the connection string.

Related environment variables

Environment variable	Overrides
PGSSLCERT	client certificate
PGSSLKEY	client private key
PGSSLROOTCERT	trusted certificate authorities

Related connection string parameters

Environment variable	Overrides
sslcert	client certificate
sslkey	client private key
sslrootcert	trusted certificate authorities

Connection test

First, try connecting with postgres user. Since the client certificate, we created is for postgres user (CN=postgres) so the connection should be successful

[postgres@Pgpool-II ]$ psql "sslmode=require port=9999 host=127.0.0.1 dbname=postgres user=postgres sslcert=/certificate_dir/client.crt sslkey=/certificate_dir/client.key sslrootcert=/certificate_dir/ca.crt"
psql (10.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 

Now try with some other use to test if the connection gets refused because of invalid certificate

[postgres@Pgpool-II ]$ psql "sslmode=require port=9999 host=127.0.0.1 dbname=postgres user=newuser sslcert=/certificate_dir/client.crt sslkey=/certificate_dir/client.key sslrootcert=/certificate_dir/ca.crt"

psql: ERROR:  CERT authentication failed
DETAIL:  no valid certificate presented 

Conclusion

The purpose of this how-to guide is to give an idea about how to set up the certificate-based authentication in Pgpool-II. As of now, Pgpool-II does not support certificate authentication between itself and PostgreSQL backend. So you can use any other authentication method for backend connections (between Pgpool-II and PostgreSQL) while deploying cert auth for clients connecting to Pgpool-II.