Let’s say you have two PostgreSQL servers that connect to each other using postgres_fdw. They communicate over TLS and they use client certificates to authenticate. You have a Certificate Authority you have set up, and it has issued server and client certificates for each server. On server one you have
root.crt. On server two you have similar server and client certificates and keys, and the same
root.crt, which is the certificate of your Certificate Authority that was used to sign all the other certificates.
This works fine, but one day you realize that your CA certificate is about to expire. So you go and generate a new CA certificate, and replacements for your server and client certificates.
Now it’s time to install them. You get onto server one, and you copy its new server and client certificates and keys over what was there, and the new CA certificate over the
root.crt, and you send postgres a signal to reload so it picks up all the new files.
That seems straightforward, it should work, right?
Unfortunately, no. You have in fact just completely broken TLS communications between the servers. Why? Because the
root.crt on server one is used to validate the certificates on server two which haven’t yet been updated, and they have been signed using the old CA certificate which you no longer have. Meanwhile, server two, which still has the old CA certificate in its
root.crt can not validate your new certificates on server one for the same reason.
So what’s the right way to go about this? You need to do the update in two stages. First you need to install the new CA certificate on each server. But you don’t copy it over the top of the old certificate, you append it to the existing
root.crt file, so there are now two certificates in
root.crt, and that file can be used to validate certificates signed using either CA certificate. On each server, after doing this you signal postgres to reload. Only after you have done this on both servers can you install the remaining new server and client certificates.
If you follow this procedure you should experience no break in TLS communications as a result of your update.