Secure Connections With PostgreSQL

Most people also want to keep their data safe from prying eyes. One of the most trusted ways (despite some recent problems) to keep your data safe is to use certificates generated with OpenSSL.

Installing OpenSSL

Cryptography technology is changing all the time, and serious vulnerabilities in current software will probably always continue to be found. Both master and standby servers need OpenSSL installed before beginning. Before installing OpenSSL, make sure your ports collection is up-to-date:

# portsnap fetch && portsnap update
# cd /usr/ports/security/openssl
# make all install clean

Before deciding on build options, make sure to check out which protocols and ciphers are no longer secure. You definitely don't want any support for the SSL protocols, despite everyone using the term SSL.

Creating Certificates With OpenSSL

Server Certificates

Ideally, you might set up/use a Certificate Authority, but in this case, we will just use self-signed certificates. Depending on your resources, you may want to generate a 2048-bit key instead of 4096. man openssl has more information about the commands below.

$ mkdir ssl_stuff
$ cd ssl_stuff
$ openssl genrsa -out server.key 4096

Now, create an X.509 certificate signed with the key we just generated. OpenSSL will ask you for information to fill out the information required by X.509. The only thing that is important is that you use your exact domain name on the server for the Common Name. If you don't have a domain name for your server, you can use an IP address.

$ openssl req -new -x509 -days 3650 -key server.key -out server.crt

PostgreSQL also requires another certificate called root.crt to be provided. If you were using a CA, this certificate would be provided by them. Since we are using self-signed certificates, we can just copy the server.crt we just created to root.crt.

$ cp server.crt root.crt

Client Certificates

In order to connect to your server using certificates, a client certificate and key signed by the root certificate need to be in the user's ~/.postgresql directory. These are generated much like the server certificates, except they are named differently, and you have to use your username on the server as your Common Name. The challenge password and company name can be left blank.

$ openssl genrsa -out postgresql.key 4096
$ openssl req -new -key postgresql.key -out postgresql.csr
$ openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial

Putting Things In The Right Places

Now, change the ownership of the files to the correct user and group, and restrict their permissions so that group and other can't access them at all. We're setting this up so that our server on one machine can contact a server on another machine. Since both are running under the pgsql user, that's what we'll use here.

$ su
# chmod 600 *
# chown pgsql:pgsql *

On the client:

On the server:

Now restart your server.