PG docs, part 6. SSL

(This is a new documentation chapter from the PG project.)

ToC

• About & Installation
• The Client
• Connection Pool
• Arrays
• Notifications
• SSL
• COPY
• HoneySQL

In this chapter:

• Setup
• EDN Config
• Testing

The recent update of PG introduces SSL support. Install the newest version of PG as follows:

Lein:

[com.github.igrishaev/pg-client "0.1.8"]
[com.github.igrishaev/pg-ssl "0.1.8"] ;; optional, for a custom SSL context

Deps:

{com.github.igrishaev/pg-client {:mvn/version "0.1.8"}
 com.github.igrishaev/pg-ssl {:mvn/version "0.1.8"}}

Setup

The are two ways to set up an SSL connection to the database. The first, and simple, is to set the :ssl? boolean flag to true and just connect:

{:host "some.cloud.host.com"
 :port 5432
 :ssl? true
 ...}

In this case, the entire SSL pipeline is held by Java. It tries to find the corresponding keys and certificates using the standard KeyStore and TrustStore which you configure on your own.

The second and more flexible way is to provide a custom SSL context to the connection map. It must be an instance of the javax.net.ssl.SSLContext class. Building such an instance from scratch is quite miserable though. To make your life easier, there is a thin wrapper on top of the great Less Awful SSL library that takes a map of certificates and keys and returns an instance of SSLContext. Since it requires a third-party library, it’s shipped as a standalone package pg-ssl. Add it to the project:

[com.github.igrishaev/pg-client "0.1.8"] ;; lein
;; or
{com.github.igrishaev/pg-ssl {:mvn/version "0.1.8"}} ;; deps

Now pass the :ssl-context parameter in addition to :ssl?. It’s a map with the string keys :key-file, :cert-file, and :ca-cert-file:

(ns foo.bar
  (:require
    [pg.ssl :as ssl]))

{:host "some.cloud.host.com"
 :port 5432
 :ssl? true
 :ssl-context
 (ssl/context {:key-file "/path/to/client.key"
               :cert-file "/path/to/client.crt"
               :ca-cert-file "/path/to/root.crt"})}

The :ca-cert-file parameter might be missing if just :key-file and :cert-file are enough.

(ssl/context {:key-file "/path/to/client.key"
              :cert-file "/path/to/client.crt"})

EDN Config

Often, we store the configuration in an EDN file. To declare SSL context there, prepend it with a reader tag called #pg/ssl-context:

{:ssl? true
 :ssl-context #pg/ssl-context {:key-file ...}}

When reading EDN, pass that tag to the :readers map as follows:

{'pg/ssl-context pg.ssl/ssl-context-reader}

The tag wraps the map with a function that builds the SSLContext from it.

Some cloud platforms give you only the root certificate. In that case, generate both the client key and the the client certificate on your own using the root certificate. Something like:

umask u=rw,go= && openssl req -days 365 -new -nodes -subj '/C=US/ST=Test/L=Test/O=Personal/OU=Personal/emailAddress=test@test.com/CN=test' -keyout client.key -out client.csr
umask u=rw,go= && openssl x509 -days 365 -req  -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt

When generating the certificates, pay attention to the CN field which is “test” in our case. In terms of PostgreSQL, it should match the database user. Different users will have different certificates.

Testing

The SSL functionality is difficult to test in Docker so I’ve got to run a native instance. Here is a brief setup.

• In postgresql.conf, enable the ssl parameter and specify paths to the files:

ssl=on
ssl_cert_file='/Users/ivan/work/pg/certs/server.crt'
ssl_key_file='/Users/ivan/work/pg/certs/server.key'
ssl_ca_file = '/Users/ivan/work/pg/certs/root.crt'

• In pg_hba.conf, enable the “cert” validation type for SSL connections:

hostssl all all all cert

Finally, create a user with a name that matches the CN field:

create user <CN-field> with password '******';