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:

cd /usr/local/etc/ssl
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:

cd /usr/local/etc/ssl
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). ## 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: 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 postgres:\ :lang=en_US.UTF-8:\ :setenv=LC_COLLATE=C:\ :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. 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

psql template1


You should be greeted with a prompt that looks like:

psql (9.6.0)
Type "help" for help.

template1=#


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 in:

\h CREATE ROLE;


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

\q


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'
shared_buffers = 1GB
huge_pages = try
work_mem = 256MB
maintenance_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:

# 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.