In fall 2023, I’ve bought a set of four Raspberry Pi 4 and made a little rack out of them: RPi rack A set of four Raspberry Pi 4 Model B (4 GB RAM). I’m using an Utronics RPi rack and PoE so that I use the least amount of cables as possible. The two “boxes” on top of the rack are a master Raspberry Pi (on the left) which serves as a PXE server and a PoE enabled network switch (on the right).

Recently, I’ve been immersed in playing around with geometry data, more specifically playing around with PostGIS. I’ve been currently reading through an excellent publication on PostGIS1. Together with the book, there many helper scripts which create little playground databases and pump data into them. Since my work laptop is the same machine as personal laptop, I really didn’t want to pollute my local PostgreSQL instance with such data.

I’ve also wanted to try out a certificate-based authentication which was mentioned by Stephen Frost on his PostgreSQL conference in 20232. Since I already knew a little bit about Ansible as I had been setting up my RPi cluster using Ansible, I’ve decided to create a separate playbook which would automatically set up a SSL-enabled PostGIS server for me. Side note: since I’m booting my Raspberries in my rack using PXE, I also needed to configure the data directory which are stored on a separate SSD, managed and provided by the “master” Raspberry.

In fact, the only way you can connect to this PostgreSQL database server is via a SSL certificate, provided you’re not logged in to the server directly via SSH -which in my infrastructure is also possible only using certificates. Since I don’t want to use neither DNS domain nor CA-signed certificate, I generated self-signed certificates for the purposes of my PoC.

First, we will setup the PostgreSQL server. Here is what my server.yml playbook looks like:

- name: Create certificate directories
  ansible.builtin.file:
    path: "/{{ mount.dir }}/postgresql/{{ ser }}/{{ postgresql.version }}/main"
    state: directory
    mode: '0700'
  register: postgresql_certificate_dir

- name: Add postgres user to ssl-cert group
  user:
    name: postres
    groups: ssl-cert
    append: true

# This part will be most likely different in real applications as you would use the real key-certificate pair instead of generating a self-signed certificate.
- name: Generate self-signed OpenSSL certificate/key
  command: "openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout {{ postgresql_certificate_dir.path }}/{{ tls.server.key_name }} -out {{ postgresql_certificate_dir.path }}/{{ tls.server.cert_name }} -subj '{{ tls.server.subj_params }}'"
  args:
    creates: "{{ postgresql_certificate_dir.path }}/{ tls.server.cert_name }}"

- name: Create CA root key
  ansible.builtin.copy:
    src: "{{ postgresql_certificate_dir.path }}/{{ tls.server.cert_name }}"
    dest: "{{ postgresql_certificate_dir.path }}/{{ tls.server.ca_root_name }}"
    mode: '0444'
    remote_src: true

- name: Set permissions on TLS files
  ansible.builtin.file:
    path: "{{ item }}"
    mode: "{{ usr_read_only }}"
  loop:
    - "{{ postgresql_certificate_dir.path }}/{{ tls.server.cert_name }}"
    - "{{ postgresql_certificate_dir.path }}/{{ tls.server.key_name }}"
  vars:
    usr_read_only: '0400'

# This makes sure that with the default PostgreSQL config, you will enable the SSL module.
- name: Uncomment SSL configuration in postgresql.conf
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ postgresql.version }}/main/postgresql.conf"
    regexp: '^#ssl\s*=\s*off'
    line: 'ssl = on'

- name: Enable SSL port listen
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ postgresql.version }}/main/postgresql.conf"
    regexp: '^#listen_addresses\s*=\s*'
    line: "listen_addresses = '0.0.0.0'"

# Here, we link our CA cert...
- name: Uncomment ssl_ca_file configuration in postgresql.conf
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ postgresql.version }}/main/postgresql.conf"
    regexp: '^#ssl_ca_file\s*=\s*.*$'
    line: "ssl_ca_file = '{{ tls.server.ca_root_name}}'"

# ...and here the client cert.
- name: Uncomment ssl_cert_file configuration in postgresql.conf
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ postgresql.version }}/main/postgresql.conf"
    regexp: '^#ssl_cert_file\s*=\s*.*$'
    line: "ssl_cert_file = '{{ tls.server.cert_name }}'"

- name: Uncomment ssl_cert_file configuration in postgresql.conf
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ postgresql.version }}/main/postgresql.conf"
    regexp: '^#ssl_key_file\s*=\s*.*$'
    line: "ssl_key_file = '{{ tls.server.key_name }}'"

- name: Configure PostgreSQL so that TLS is forced (and available for local network nodes only)
  ansible.builtin.template:
    src: "{{ role_path }}/files/pg_hba.conf.j2"
    dest: /etc/postgresql/{{ postgresql.version }}/main/pg_hba.conf
    mode: '0644'

- name: Chown the cert dir
  ansible.builtin.file:
    path: "{{ postgresql_certificate_dir.path }}"
    state: directory
    owner: postgres
    group: postgres
    recurse: true

- name: Restart PSQL
  ansible.builtin.systemd_service:
    daemon_reload: true
    name: postgresql
    enabled: true
    state: restarted

A few important notes here. The way we tell PostgreSQL how it should handle authentication is using its feature called host-based authentication, namely the pg_hba.conf file located directly at the PostgreSQL server. The pg_hba.conf.j2 I’m using is a fork of the “vanilla” pg_hba.conf and has the following contents:

# If the user rpi_admin or postgres connects locally, give them full access.
local   all             rpi_admin                               trust
local   all             postgres                                trust
# Anyone outside must connect using SSL!
hostssl all             all                                     cert

First two of the three authentication methods are what PostgreSQL calls trust and what this means in practice is that the user is automatically authorized without any authentication. In general, this is not a recommended method for obvious security reasons. The part local all <user> tell PostgreSQL to authorize the user <user> to any database provided he is logged in directly on the PostgreSQL server (that’s the local part).

The last authentication enables anyone outside the PostgreSQL server authenticate to any database. Again, this rule would be much more strict in any real-world scenario.

  1. R. O. Obe and L. S. Hsu, PostGIS in action. Manning, Cop, 2015. Available at: https://www.manning.com/books/postgis-in-action-third-edition ↩

  2. Stephen Frost: Advanced Authentication and Encrypted Connections. PGConf.EU 2023. https://www.youtube.com/watch?v=dWO_uA1-Oxo ↩