PostgreSQL – How to Upgrade/Renew SSL Server Certificate with OpenSSL

opensslpostgresqlssl-certificate-renewal

I have inherited a Company Postgres Server with SSL clients. Its around 100 users so far.

Two Problems: My Ca Cert (root.crt) expires next year and it seems to be still TlsV1.0. So an upgrade(renew) is urgently needed.

What I should avoid is making new client certificates for all users in one. It would become a nightmare for me 🙂

So I searched for a solution where old and new Certificats will work, until all old certs are expired. I fond a hell of hints (also on Serverfault), but nothing worked so far.

What I did so far:
I use old ca key (rootCa.key) and created a new root.crt), and I used old server key (server.key) to create a new server.crt. I installed revocation list (root.crl),root.crt, server.crt and server.key on my Postgres Backup Server.

I can connect with newUser.crt certificate, but not with old ones….as I expected.

I did search the web like hell and found a hint to merge old and new Certificates on the server. So I combined the certs with cat: cat oldRoot.crt >> root.crt for the other files as well. Revocation list didn't work, so I commented the Line: #ssl_crl_file = in Postgres.conf. Seems Postgres works also without revocation list.

Result:
Depending wich certificate comes first in server.crt, I could connect with old or with new certs, but never with both. To test it, I used merged root.crt with oldServer.cert and also with newServer.cert. Both worked with new or old.

That means, a merged root.crt ist working fine, but not a merged server.crt.

I checked it with openssl and merged root.crt and merged server.crt:
openssl verify -verbose -x509_strict -CAfile root.crt -CApath . old_cert.crt server.crt
old_cert.crt: OK
server.crt: OK

openssl verify -verbose -x509_strict -CAfile root.crt -CApath . new_cert.crt server.crt
new_cert.crt: OK
server.crt: OK

It seems openssl can manage merged certificates, but not Postgres.

Any idea how to solve this problem?

Any hint is appreciated.

Regards Schlauchi

server: Ubuntu 2104, Postgres13
backup server: Ubuntu 1604, Postgres13

Best Answer

My short answer to my question is: Yabadabbadooh....it is possible and works on my backup system!

It didn't let me sleep, so I started again from the scratch: First I checked old and new certs for any difference again, the only difference was a different order in the subject, but same contents.

#1 So I made a new root.crt and server.crt with old rootCa.key and old server.key with identically subject order. Now the text output was identically (subject, Signature Algorithm....)

#2 I made a new userCert with root.crt.

#3 I installed the certs in Postgres and tested -> working.

#4 this time I started with new certs on the top.

cat root_old.crt >> root.crt
cat server_old.crt >> server.crt
cat server_old.key >> server.key

postgres restart ...... AND now old and new certificates are working!

#5 Check the other way around copy old certs over the merged and then:

cat root_new.crt >> root.crt
cat server_new.crt >> server.crt
cat server_new.key >> server.key

postgres restart ...... and it is no longer working. Revert to step 4, all was working well again.

#6 Revocation list: I created a revocation list for the new certificates and installed it in postgres. then:

cat root_old.crl >> root.crl

uncommented the Line: ssl_crl_file = /root.crl postgres restart ...... AND all is working fine.

I have no clue why it is working now, I can't really believe its the subject order...guess I made something else wrong in the first tries. But I won't try to find out, it works and thats it :-)

Conclusion:

Make root.crt (CA) and server.crt with old keys and as identically as possible. (check subject and such)

Merge certificates, revocation list and key so the the old ones are at the bottom of the file

Now old certificates are working until they expire and you can create new certificates (with new CA) with upgraded Tls for example.

Some additional infos about Tls Version. When I upgraded my server to Ubuntu 2104 and Postgres 13, the Certificates didn't work. I found the hint to add this line in /etc/ssl/openssl.conf

MinProtocol = TLSv1.0

I know this is no good, but helped for the moment. I couldn't find a way to test if certificates are Tlsv1.0 or higher. The only test if its TlsV1.2 was using pgsql (13) or pgadmin4, both won't work with TlsV1.0

So in my config file for certificate creation, I use this:

MinProtocol = TLSv1.2

psql (13) and pgadmin4 now work with the new certificates. When all old ones are expired, I will change /etc/ssl/openssl.conf to that value again.

Upgrade to next Tls version hast lost its sting :-)

Related Topic