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:
1 2 3 4 5 6 7 8 9
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:
1 2 3 4
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 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).
The initial configuration for PostgreSQL is pretty straight forward. Start by adding properties to /etc/rc.conf:
1 2 3 4 5 6
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
1 2 3 4
postgres:\ :lang=en_US.UTF-8:\ :setenv=LC_COLLATE=C:\ :tc=default:
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:
1 2 3 4
[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:
1 2 3 4
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:
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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:
1 2 3 4 5 6 7 8
# 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.
Footnotes and References¶
So what do you think? Leave your comments below.