This covers the setup of SSL (CA, keys, and certs) for the services we will
run, and also the PostgreSQL database.
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:
openssl genrsa -des3 -out server.key 2048
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 3650 -in server.csr -signkey server.key -out server.crt
openssl rsa -in server.key -out server.key.nopass
mv server.key.nopass server.key
openssl req -new -x509 -sha256 -extensions v3_ca -keyout cakey.pem -out cacert.pem -days 3650
chmod 600 server.key
chmod 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:
openssl dhparam -out dh2048.pem 2048
openssl dhparam -out dh1024.pem 1024
openssl 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).
I chose PostgreSQL because its a mature reliable performant database. It
is the one true (relational) database. Documentation for PostgreSQL is
The initial configuration for PostgreSQL is pretty straight forward. Start
by adding properties to /etc/rc.conf:
sudo sysrc postgresql_enable=YES
sudo sysrc postgresql_class="postgres"
sudo sysrc postgresql_initdb_flags="--no-locale -E=UTF8 -n -N"
sudo sysrc postgresql_data="/var/db/postgres/data96"
sudo sysrc postgresql_enable="YES"
sudo sysrc postgresql_user="postgres"
Follow the suggestion from the port when you installed. Create a postgres
login class with
Don’t forget to run
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.
zfs create -o canmount=no -o setuid=off -o exec=off zroot/var/db
zfs 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.
sudo 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
sudo service postgresql start
Make sure that the process is running with ps(1). It should return something like:
[louisk@mx louisk 67 ]$ ps ax | grep sql
3067 2 S 0:00.39 /usr/local/bin/postgres -D /usr/local/pgsql/data
6272 4 R+ 0:00.00 grep sql
[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
You should be greeted with a prompt that looks like:
Type "help" for help.
Now type in the following (You will need to replace ‘username’ and ‘password’
with what you wish to use for the administrative user):
CREATE 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
To quit the psql client and return to the shell, you can type:
To test that you can login with your new user, run this:
psql -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:
listen_addresses = 'localhost'
port = 5432
superuser_reserved_connections = 3
ssl = on
ssl_ciphers = 'HIGH:!SSLv2:!SSLv3:!aNULL'
ssl_prefer_server_ciphers = on
ssl_ecdh_curve = 'prime256v1'
ssl_cert_file = '/usr/local/etc/ssl/server.crt'
ssl_key_file = '/usr/local/etc/ssl/db_server.key'
password_encryption = on
shared_buffers = 1GB
huge_pages = try
work_mem = 256MB
maintenance_work_mem = 256MB
A couple things worth noting:
- 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.
- 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:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
hostssl all all 127.0.0.1/32 trust
# IPv6 local connections:
hostssl 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:
sudo 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.
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.