PostgreSQL SSL setup in docker-postgis
Posted by: Admire Nyakudya | in Database, Docker, PostGIS | 1 year ago | Comments

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

    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/ --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!

Current rating: 3


Have any questions? Contact us.
We would love to hear from you!
Subscribe to our Mailing list (spam-free, industry-relevant occasional emails)

Subscribe to our mailing list

* indicates required