How to use SSL/TLS between server and client with Postgres?

Question

I don't know much with it comes to security. I'm deploying a virtual machine with my database in a cloud service. I would like to encrypt all the communication between this server and any clients. How can I do that ?

More information: I have read a lot of material from postgres documentation [1,2], but I can't understand much of it. I have followed this tutorial and now I have these files:

valter@eniac:test$ ll
total 28
drwxr-xr-x 2 valter valter 4096 Jan 25 12:54 ./
drwxr-xr-x 3 root   root   4096 Jan 25 12:50 ../
-rw-rw-r-- 1 valter valter 1834 Jan 25 12:53 privkey.pem
-rw-rw-r-- 1 valter valter 4783 Jan 25 12:54 server.crt
-rw------- 1 valter valter 1675 Jan 25 12:53 server.key
-rw-rw-r-- 1 valter valter 3672 Jan 25 12:53 server.req

But I don't know what to do with them. Where are root.crt and root.crt files ? How can I generate them ? Where do I put these generated files ? And what should I do on the client side ?

Answer 1

Do you just want to use a certificate on the server which the clients use to authenticate the server? (This is the normal mode of operation). The server then authenticates the clients using password challenges just as if ssl were not being used.

Or do you want to use client certificates to authenticate the clients to the server as well as the server to the clients? This is rare, as it requires a certificate authority to issue certs to all of the users, and requires users to properly manage them.

Assuming the first of these, you don't have need root.crt files on the server. All that you need to do now is edit postgresql.conf to turn ssl=on, and edit pg_hba.conf to either require or allow your clients to use ssl (hostssl requires it, or host allows it.)

Note that with the default client configuration (at least if the client is psql), all that you really get from using ssl is encryption, not true server authentication. That is because the client just accepts whatever certificate the server sends it, and uses that cert to set up an encrypted channel. So a bad guy can run a PostgreSQL server with a self-signed certificate, and if it can trick your clients to connect to it rather than your real PostgreSQL server, they will happily connect to it and send their queries to it.