In Fall 2023, I’ve bought a set of four Raspberry Pi 4 and made a cute little rack out of them:
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 PostGIS in Action. 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 2023.
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 the 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 own 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
- 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'
- 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'"
- 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}}'"
- 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: restartedA 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:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket.
#
# This auth configuration is just a proof of concept of configuring TLS
# using Ansible; the 'trust' mode for localhost connections should in no
# way be neither used in a real environment, nor should it use self-signed certs!
local all mzezulka trust local all postgres trust
# Anyone outside must connect using SSL!
hostssl all all {{ networking.all_local }} certFirst of the two authentication methods is what PostgreSQL calls trust and what this means in practice is that the user is automatically authorized without any authentication (and in general, this is not a recommended method for obvious reasons). The first three words local all mzezulka tell PostgreSQL to authorize user mzezulka to any database provided he is logged in directly on the PostgreSQL server (that’s the local part).
The second authentication is the more interesting one.