Databases are the cornerstone of most web applications and also act as a central repository for the storage of data.

There are many ways to get a running PostgreSQL database on your host machine. Our preferred method of setting up the the database is using Docker and Kartoza provides a PostGIS Docker image that is quite flexible and easy to configure.

The docker image provides SSL support out of the box using the default snake oil certificates. However, the out of the box configuration for SSL the image does not force all clients connecting to the database to use SSL connections. To force all clients connecting to the database to use the SSL we had to manually edit the pg_hba.conf file which could be an irritation since you need to either store the hba file in a different volume, or make the changes whenever you redeploy the container. Fortunately, this will be a thing of the past as we have added an environment variable `FORCE_SSL=TRUE` as an option in the configuration. When this option is enabled, connecting to the database will be through SSL.

Let's walk through the process

Set up a database connection using the docker image

docker run -p 25433:5432 -e FORCE_SSL=TRUE --name ssl -d kartoza/postgis:13-3.1

Setup QGIS to connect to the database

Note the SSL Mode set to 'Require' above now!

Using user-defined certificates

I had to generate some certificates using OpenSSL

1.  Create a bash script to use when setting up the container
   
   

   !/usr/bin/env bash
   
   CERT_DIR=/etc/certs
   mkdir $CERT_DIR
   openssl req -x509 -newkey rsa:4096 -keyout ${CERT_DIR}/privkey.pem -out \
         ${CERT_DIR}/fullchain.pem -days 3650 -nodes -sha256 -subj '/CN=localhost'
   cp $CERT_DIR/fullchain.pem $CERT_DIR/root.crt
   chmod -R 0700 ${CERT_DIR}
   chown -R postgres ${CERT_DIR}

2.  Run the command to set up the PostgreSQL container
   

   docker run -p 25433:5432 -e FORCE_SSL=TRUE  -v /tmp/postgres/setup.sh:/docker-entrypoint-initdb.d/setup.sh --name ssl -d kartoza/postgis:13-3.1

3.  Create a file ssl.conf with the following contents.
   

   ssl = true
   ssl_cert_file = '/etc/certs/fullchain.pem'
   ssl_key_file = '/etc/certs/privkey.pem'
   ssl_ca_file = '/etc/certs/root.crt' 

4. Copy the file ssl.conf into the container using the command
   

   docker cp ssl.conf ssl:/etc/postgresql/13/main/ssl.conf

5.  Restart the docker container using `docker restart ssl`
6. Copy the root.crt from the docker container to the path /home/$user/.postgresql/
   

   docker cp ssl:/etc/certs/root.crt /home/$user/.postgresql/

7. Setup PostgreSQL connection in QGIS
   

Note the SSL mode set to 'verify-full' now!

• ← A step forward in Kartoza's QGIS and GeoNode Training
• Collaborating on a QGIS Project saved in a PostgreSQL database →