2

I am trying for the first time a Postgres cluster provided as a managed service by DigitalOcean.com.

As part of configuring the new cluster, their web interface gives me a user name, password, port number and such. On that web page is a tiny link saying Download the CA certificate. I assume CA means Certificate Authority.

➥ Why would I use or not use that link to download the certificate?

When I click the link, I end up with a file on my machine named ca-certificate.crt. How would I use this file?

Improve this question

2 Answers 2

Reset to default
4

You can use it with TLS to verify that you are connecting to the correct server.

The connection string for that would look like:

host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-certificate.crt

If you cannot specify the connection string that way, you can use the environment variables PGSSLMODE and PGSSLROOTCERT.

Then the PostgreSQL client library will verify that the server's certificate is actually signed by the CA certificate and that the server name is correct.

Of course that requires that your PostgreSQL client library was configured --with-openssl.

Improve this answer
2
  • By “CA certificate”, are we talking about the certificates that belong to the authority organizations that sign other people’s certificates? Would this be the same as the certificates bundled inside the various web browsers? So we are making up for the fact that no such cerrificates are bundled with our various Postgres connection libraries and admin tools? So I am downloading the certificate of the authority who signed the certificate that the D.O. server will provide my client when negotiating TLS conn? Or does “CA certificate” represent something specific to Digital Ocean (in my case here)?
    – Basil Bourque
    Jul 10, 2020 at 16:38
  • You'd have to examine the certificate to know if it is from DigitalOcean or not. Perhaps they have their own certification authority that is not widely accepted by web browsers.
    – Laurenz Albe
    Jul 13, 2020 at 5:33
3

To elaborate on Laurenz's answer, if someone can intercept your internet traffic intended for Digital Ocean they could set up a malicious PostgreSQL server that you would connect to unwittingly and then they could record your queries, results, and probably even your password.

Using verify-full is a way to make sure the server you are actually connected to has access to a cert (and the corresponding key) which has been issued by the CA to the hostname which you specified for the connection. So unless the man-in-the-middle either compromised the actual CA, or tricked you into installing a malicious CA cert, he can't pull this trick anymore.

Even if you are not particularly concerned about a MITM, you should download the cert (over https!) and install and configure it to be used, as a learning experience if nothing else. You can probably test it by specifying the IP address rather than the hostname in your connection string, and it should be blocked as the IP address is (probably) not in the server's cert.

Improve this answer
2
  • You probably meant "set up a malicious" proxy, not the actual Postgres server.
    – mustaccio
    Jul 10, 2020 at 16:10
  • I'd set up a malicious PostgreSQL server, as I know how to do that. I couldn't respond intelligently to queries without being a proxy or knowing your schema, but once I have your password I might not need anything more than that and could just drop the connection. But sure, someone else could implement a real recording proxy.
    – jjanes
    Jul 10, 2020 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.