PostgreSQL Logo PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. When installing Bacula on FreeBSD it is also the default database for the catalog.

In my previous posts on Bacula, the PostgreSQL database has resided on the same server as the Bacula Director and there has been no need to secure the Login Credentials for PostgreSQL as these never traversed the network. I have recently moved the PostgreSQL server of the Bacula Director machine and now it is advisable that these credentials are secured.

Today I will enable SSL on both PostgreSQL and Bacula. The following instructions are for a Bacula server that is already in place. If you need to set up from scratch this post will help you most of the way, however, I highly recommend viewing Staf Wagemakers post on the subject as it goes into much more detail.

NOTE: I am running Bacula and PostgreSQL on OmniOS, however the following information should be suitable for all systems with minor variations.

Create Certs on CA

The connection relies on SSL server and client certificates. I create these on the Certificate Authority I documented in an earlier post.

Issue postgres.pbdigital.org server certificate

First step is create the certificate signing request and forwad this to the Certificate Authority:

# openssl genrsa -out postgres.pbdigital.org.key 2048
# openssl req -new -key postgres.pbdigital.org.key -out postgres.pbdigital.org.csr
# scp  postgres.pbdigital.org.csr root@ca.pbdigital.org:/etc/ssl/ca.pbdigital.org/csr/

On the Certificate Authority I issue the Certificate:

# openssl ca -config root-ca.cnf -in csr/postgres.pbdigital.org.csr -out certs/postgres.pbdigital.org.crt -extensions server_ext

Back on the PostgreSQL server I import the server and root authority ceritficate, into the /var/opt/ooce/pgsql/pgsql-12 directory:

# scp ca.pbdigital.org:/etc/ssl/ca.pbdigital.org/root-ca.crt .
# scp ca.pbdigital.org:/etc/ssl/ca.pbdigital.org/certs/postgres.pbdigital.org.crt .

Issue bacula.pbdigital.org client certificate

On the Bacula server I create the .postgres directory in the Bacula Home Directory and repeat the above steps to create a certificate signing request:

# cd /var/opt/ooce/bacula
# mkdir .postgres
# cd .postgres
# openssl genrsa -out bacula.pbdigital.org-client.key 2048
# openssl req -new -key client.key -out bacula.pbdigital.org-client.csr
# scp bacula.pbdigital.org-client.csr root@ca.pbdigital.org:/etc/ssl/ca.pbdigital.org/csr/

On the Certificate Authority, I issue the client cerificate:

# openssl ca -config root-ca.cnf -in csr/bacula.pbdigital.org-client.csr  -out certs/bacula.pbdigital.org-client.crt -extensions client_ext

Back on the Bacula server I import the client and root authority ceritficate, into the /var/opt/ooce/bacula/.postgres directory:

# scp ca.pbdigital.org:/etc/ssl/ca.pbdigital.org/certs/bacula.pbdigital.org-client.crt .
# scp ca.pbdigital.org:/etc/ssl/ca.pbdigital.org/root-ca.crt .

Once the approriate permissions have been set on the certificates and keys, this is all that is needed to meet the certificate requirements.

Enabling SSL for PostgreSQL

First I edit /var/opt/ooce/pgsql/pgsql-12/postgresql.conf to require an SSL connection when connecting to PostgreSQL. The lines that I am interested in are as below:

listen_addresses = '192.168.100.137'               # what IP address(es) to listen on;
ssl = on
ssl_ca_file = 'root-ca.crt'
ssl_cert_file = 'postgres.pbdigital.org.crt'
ssl_crl_file = ''
ssl_key_file = 'postgres.pbdigital.org.key'
ssl_ciphers = 'HIGH:MEDIUM:!3DES:!aNULL' # allowed SSL ciphers 

Secondly I edit /var/opt/ooce/pgsql/pgsql-12/pg_hba.conf, to allow connections to the Bacula database from the Bacula host. The following can be appended to the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl bacula          bacula          192.168.100.137/32      md5 clientcert=1

With both these files edited, PostgreSQL needs to be restarted to apply the changes:

# svcadm disable postgresql12
# svcadm enable postgresql12

Testing the SSL Connection to PostgreSQL

I can make a quick test to verify that PostgreSQL is only accepting SSL connections and confirm that we have made the correct changes. The following command from the Bacula host will test our SSL certificates and configuration:

# psql "sslmode=verify-full host=postgres.pbdigital.org dbname=bacula sslcert=`pwd`/bacula.pbdigital.org-client.crt sslkey=`pwd`/bacula.pbdigital.org-client.key sslrootcert=`pwd`/root-ca.crt"
                                                                                                                                                                
Password for user bacula: 
psql (12.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

bacula=> 

Success!

PostgreSQL Enviroment File

To make things more simple we can create a file that stores all the neccessary PostgreSQL/SSL enviromnment variables, which later, Bacula will make use of:

# cat /var/opt/ooce/bacula/psql.env
PGHOST=postgres.pbdigital.org
PGUSER=bacula
PGSSLMODE=verify-full
PGSSLCERT=/var/opt/ooce/bacula/.postgres/bacula.pbdigital.org-client.crt
PGSSLKEY=/var/opt/ooce/bacula/.postgres/bacula.pbdigital.org-client.key
PGSSLROOTCERT=/var/opt/ooce/bacula/.postgres/root-ca.crt

export PGHOST
export PGUSER
export PGSSLMODE
export PGSSLCERT
export PGSSLKEY
export PGSSLROOTCERT

Test Connection with PostgreSQL Environment File

Before I test with Bacula, it is a good idea that the PostgreSQL Environment File works. First, I source the file:

#. /var/opt/ooce/bacula/psql.env

Then test the connection:

# psql bacula
                                                                                                                                                                
Password for user bacula: 
psql (12.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

bacula=> 

Success!

Enabling Bacula to Connect via SSL to PostgreSQL

To allow Bacula to source thes PostgreSQL Environment File, we make the /opt/ooce/bacula/share/bacula-ctl-dir source the environment file when Bacula starts. Early on in the bacula-ctl-dir file we want to add the following line.

. /var/opt/ooce/bacula/psql.env

This allows Bacula to run with all the PostgreSQL/SSL environment variables set.

Restart and Test with bconsole

We have made all the changes we need to now, so it is a simple matter or restarting Bacula and making a connection with the bconsole program.

# svcadm disable bacula-dir
# svcadm enable bacula-dir
# bconsole
Connecting to Director bacula.pbdigital.org:9101
1000 OK: 103 bacula.pbdigital.org-dir Version: 9.6.3 (09 March 2020)
Enter a period to cancel a command.
*

bconsole should connect normally as above. If there are problems, bconsole will inform you that you have messages, and print the error message that it has for when the connection has failed.

Wrapping Up

I hope this has been of some help to you. If you need further detail, please look at Staf Wagemakers post on the subject as it goes in to much greater detail.