SSL, PostgreSQL

This covers the setup of SSL (CA, keys, and certs) for the services we will run, and also the PostgreSQL database.

SSL Setup

We want all our services to connect securely, even if its to something on the local machine. This will reduce the work involved if we need to separate services, or if we get stuck with audit requirements for having all the communication traffic encrypted.

Option 1: Use self-signed certs. As a general rule, there is nothing wrong with this. There is no cryptographic difference between a self-signed cert and a cert that has been signed by the likes of Thawte, Verisign, Comodo, etc. The only place you may wish to go with a cert that has been signed by a trusted entity is for the web interfaces (domain/mailbox administration, database administration, or webmail).

If you don’t need a full blown private CA, you can use this to quickly generate a self-signed cert and key:

1cd /usr/local/etc/ssl
2openssl genrsa -des3 -out server.key 2048
3openssl req -new -key server.key -out server.csr
4openssl x509 -req -days 3650 -in server.csr -signkey server.key -out server.crt
5openssl rsa -in server.key -out server.key.nopass
6mv server.key.nopass server.key
7openssl req -new -x509 -sha256 -extensions v3_ca -keyout cakey.pem -out cacert.pem -days 3650
8chmod 600 server.key
9chmod 600 cakey.pem

There are several services that will make use of a Diffie Hellman key for Perfect Forward Secrecy. Not all applications can use a 2048bit key, so we’ll generate several sizes and then we can configure any app to use the appropriate key. We can generate the key with:

1cd /usr/local/etc/ssl
2openssl dhparam -out dh2048.pem 2048
3openssl dhparam -out dh1024.pem 1024
4openssl dhparam -out dh512.pem 512

I stash the certs here because I will be using them for all the various components in this writeup. This pair uses SHA256 (sha2) and will be good for 10yrs (probably consider redoing things before that time is up anyway).

Option 2: Use letsencrypt to get trusted, signed certs for free. letsencrypt quick start . I’ve been fairly happy with the acme.sh script. It covers the basics well.

Option 3: Use a traditional Certificate Authority to sign your certs (this can be costly, anywhere from $50 to hundreds of dollers each, depending on the quantity and type of cert).

Configuring PostgreSQL

I chose PostgreSQL because its a mature reliable performant database. It is the one true (relational) database. Documentation for PostgreSQL is here .

The initial configuration for PostgreSQL is pretty straight forward. Start by adding properties to /etc/rc.conf:

1sudo sysrc postgresql_enable=YES
2sudo sysrc postgresql_class="postgres"
3sudo sysrc postgresql_initdb_flags="--no-locale -E=UTF8 -n -N"
4sudo sysrc postgresql_data="/var/db/postgres/data96"
5sudo sysrc postgresql_enable="YES"
6sudo sysrc postgresql_user="postgres"

Follow the suggestion from the port when you installed. Create a postgres login class with

1postgres:\
2        :lang=en_US.UTF-8:\
3        :setenv=LC_COLLATE=C:\
4        :tc=default:

Don’t forget to run cap_mkdb /etc/login.conf

There are some best practices we should follow for using PostgreSQL on ZFS. We need to set the recordsize (zfs) to match the block size in PostgreSQL for both the pgsql and pg_xlog volumes. We also need to create a zfs container for the postgres properties we want to define in zfs.

1zfs create -o canmount=no -o setuid=off -o exec=off zroot/var/db
2zfs create -o canmount=no -o setuid=off -o exec=off -o recordsize=16K -o logbias=throughput zroot/var/db/postgresql

Now we can initialize the database.

1sudo service postgresql initdb

If you want Postgres to use a dhparam for its SSL connections, Copy the dh1024.pem into $PGDATA (/var/db/postgres/data96 if you used the above example). Make sure its owned by the Postgres user.

You can start the service by typing in

1sudo service postgresql start

Make sure that the process is running with ps(1) . It should return something like:

1[louisk@mx louisk 67 ]$ ps ax | grep sql
23067  2  S      0:00.39 /usr/local/bin/postgres -D /usr/local/pgsql/data
36272  4  R+     0:00.00 grep sql
4[louisk@mx louisk 68 ]$

Before we move on, lets connect with psql(1) and create an administrative user. As the user pgsql (or postgres), type in

1psql template1

You should be greeted with a prompt that looks like:

1psql (9.6.0)
2Type "help" for help.
3
4template1=#

Now type in the following (You will need to replace ‘username’ and ‘password’ with what you wish to use for the administrative user):

1CREATE ROLE username WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'password';

If you want to know what all the options are for creating a user, you can type in:

1\h CREATE ROLE;

To quit the psql client and return to the shell, you can type:

1\q

To test that you can login with your new user, run this:

1psql -U username -d template1

You should be prompted for the administrative password and then presented with the same info as before.

Now we need to configure PostgreSQL SSL options. By default, the config files are in /usr/local/pgsql/data. The first file we’ll work with is postgresql.conf. You will need to uncomment/enable/define the following choices:

 1listen_addresses = 'localhost'
 2port = 5432
 3superuser_reserved_connections = 3
 4ssl = on
 5ssl_ciphers = 'HIGH:!SSLv2:!SSLv3:!aNULL'
 6ssl_prefer_server_ciphers = on
 7ssl_ecdh_curve = 'prime256v1'
 8ssl_cert_file = '/usr/local/etc/ssl/server.crt'
 9ssl_key_file = '/usr/local/etc/ssl/db_server.key'
10password_encryption = on
11shared_buffers = 1GB
12huge_pages = try
13work_mem = 256MB
14maintenance_work_mem = 256MB

A couple things worth noting:

1. Postgres likes to be the owner of the ssl key file, so I made a copy of it and changed the permissions to 600, and owner is pgsql.
2. I’ve restricted the ciphers to 128bit or higher (currently defined by PostgreSQL developers as ‘HIGH’. I’ve also disabled the SSLv2 and SSLv3, and told it that negotiations should prefer the server offered ciphers.

Now we can configure Postgres to only accept encrypted (SSL) connections. We need to edit the pg_hba.conf file and make the following changes:

1# TYPE  DATABASE        USER            ADDRESS                 METHOD
2
3# "local" is for Unix domain socket connections only
4local   all             all                                     trust
5# IPv4 local connections:
6hostssl all             all             127.0.0.1/32            trust
7# IPv6 local connections:
8hostssl all             all             ::1/128                 trust

This removes the ability for non-SSL (TCP/IP) connections for both IPv4 and IPv6. You can now restart Postgres with:

1sudo service postgresql restart

For most things, you can do a reload instead of a restart, but adding or modifying encryption ciphers requires an actual restart.

The recommended value for ‘shared_buffers’ is between 5-15% of available system memory. For a box with 4G, that’s about 640MB. If you see a high system load w/o corresponding processes, you may want to look into faster storage. Further tuning information can be found in the footnotes at the bottom.

Upgrading

There are 2 moderately common paths for upgrading. The one that’s been around the longest is the dump/restore. Doing something like pg_dumpall > db_backup.sql and then psql < db_backup.sql to restore it. The second is pg_upgrade. Most of the time, this works, and it much faster beacuse it doesn’t actually make a copy of the data. It does require that there be no storage changes between the versions of PostgreSQL (this isn’t common). It also requires that you have both versions of PostgreSQL installed/available at the same time. If you use ZFS snapshots, you can do this fairly easily by taking a snapshot before upgrading, mount the snapshot (read-only is fine), upgrade PostgreSQL, do pg_upgrade, and point at the snapshot for the old version of PostgreSQL.

Footnotes and References