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 email@example.com:/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
# 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 firstname.lastname@example.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
# 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=>
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:
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=>
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.
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
# 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.
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.