Postgresql TLS cert not reloaded when renewed

[mkllnk]

Description

Certbot renews the certificates and then reload postgresql but that doesn't seem to be enough. N8N raised an error connecting to some databases. It worked again after restarting postgresql though. So we need to change the renewal script to restart, it seems. Let's read some docs and see if there's a better way.

Expected Behavior

Postgresql should always use the newest TLS certificate.

Actual Behaviour

Postgresql uses the TLS certificate from it's last restart.

Steps to Reproduce

1. Wait three months. 😬
2. Go to N8N -> Credentials -> any Postgres database which hasn't been restarted within 3 months
3. Make sure that SSL is required and SSL issues are not ignored.

Animated Gif/Screenshot

Severity

bug-s3: a feature is broken but there is a workaround

Your Environment

• Version used:
• Browser name and version:
• Operating System and version (desktop or mobile):

Possible Fix

diff --git a/roles/db_integrations/templates/cert-renewal-hook.sh.j2 b/roles/db_integrations/templates/cert-renewal-hook.sh.j2
index be29d67..2540cb8 100644
--- a/roles/db_integrations/templates/cert-renewal-hook.sh.j2
+++ b/roles/db_integrations/templates/cert-renewal-hook.sh.j2
@@ -13,4 +13,4 @@ cp "$SRC/privkey.pem" "$DST/"
 
 chown postgres:postgres "$DST/"*.pem
 
-systemctl reload postgresql
+systemctl restart postgresql

[mkllnk]

I searched for this issue and it seems to depend on the Postgresql version. That's why only some servers were affected by this issue. A reload is enough for Postgres 10 and newer. Since a restart is disrupting current connections, I don't think it's worth implementing for the n8n database connector. It would probably cause failed requests once a month and all newer servers don't need it.

• https://www.postgresql.org/message-id/6585cec4-bbb3-7d91-8799-f289deb58c22%402ndquadrant.com
• https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-SERVER-FILES

[mkllnk]

We got five production servers affected by this issue and we are planning to solve this in May 2024 with this task:

• Upgrade Ubuntu #157 (comment)