Configure SSL Mode

We use mandatory SSL encryption on our endpoint (this is called sslmode=require in libpq terminology). Note that this protects you against eavesdropping, but not against MITM attacks, since PostgreSQL clients by default don't verify the server certificate (verify-ca or verify-full).

In some cases your client may require full verification of the certificate presented by Splitgraph, e.g. with sslmode=require or sslmode=verify-ca.

You will need to trust the Root CA, and in some cases may need to generate a self signed cert for yourself.

You can download the Root CA (we use LetsEncrypt) here: https://www.identrust.com/dst-root-ca-x3.

If you're using psql, you can download this file into ~/.postgresql/root.crt and specify sslmode=verify-ca in the connection URI, e.g.:

psql "postgres://$USERNAME:$PASSWORD@data.splitgraph.com:5432/ddn?sslmode=verify-ca"

Alternatively, you can specify a folder where to download the root cert, and include that location in the connection URI, e.g.: download it to ~/.splitgraph/data.splitgraph.com.crt and then connect via:

psql "postgres://$USERNAME:$PASSWORD@data.splitgraph.com:5432/ddn?sslmode=verify-ca&sslrootcert=$HOME/.splitgraph/data.splitgraph.com.crt"

Getting the certificate with openssl

You can also get the root certificate using openssl. First, verify the certificate by running:

echo | openssl s_client -starttls postgres -connect data.splitgraph.com:5432 -showcerts

You can then get the certificate by running:

echo | openssl s_client -starttls postgres -connect data.splitgraph.com:5432  2>/dev/null | openssl x509 > ~/.splitgraph/data.splitgraph.com.crt

On pre-1.1.1 versions of s_client that don't support PostgreSQL, you can get data.splitgraph.com's certificate from HTTPS, since it presents the same certificate there:

echo | openssl s_client -connect data.splitgraph.com:443  2>/dev/null | openssl x509