Sunday, 24 April 2016

HTTPS for a small site

We all know it’s a good thing. Security, SEO and soon not being called out by Chrome and Firefox for being insecure. But for a small, personal site it’s a pain in the rear to set up and the certificate is prohibitively expensive, right? Right?

Maybe not. Let's try and change this:

The certificate

These days you can get a 90 day certificate for free from Let’s Encrypt, which is news to me and the reason I thought I’d give this a go.

Main stumbling block removed.

Apache config for SSL

Ok, I can write this config myself. However Let’s Encrypt has a magic tool which claims to do everything for me. Let’s find out.

git clone
cd letsencrypt
./letsencrypt-auto --help
  • It downloaded a python environment for me.
  • It did a thing with root privileges courtesy of sudo. Probably shouldn't have used a window in which I’d previously sudo'd something. Oops.
./letsencrypt-auto --apache
The automated thing doesn't detect my domain. It detects a load of others, but I’m not ready to destroy those yet. Boo.

Also, I'm guessing with letsencrypt-auto. It seems to pass flags to the letsencrypt script which is buried somewhere. Turns out I am right. Great.

I have to agree with the T&Cs to register with the ACME server. Aside from the obvious, ACME seem to mean Advisory Committee on Mathematics Education which I don’t think is relevant here so clearly I am getting a cert from the same people who supply anvils to Wile E. Coyote.

Seems legit. Let’s do this.

./letsencrypt-auto --apache -d
Still not finding my domain. Is it … confused by the number of domains? Nope, it doesn’t like files containing multiple vhosts. Oh. Reconfiguration time.


Ok, updated. Now time to fire this baby up. The original command now finds all the domains. Go! What could go wrong?


Well, shit.

Minor problem - apparently I'm loading my fonts over an insecure connection.


For those of you not up to speed with the arcane art of reading browser URL bars, the shield is gone which means the browser isn't blocking assets trying to load into a secure page over an insecure connection.

These certs expire in 90 days so time for a simple cron.

00 03 * * * $location/letsencrypt/letsencrypt-auto renew >> $location/letsencrypt/logs/renew.log 2>&1
Docs recommend checking daily, so that should keep things up to date. And potentially fill the filesystem. Meh.

So, my site is available over a secure connection. Hurrah! The “ensure all connections” setting seems to have set up a basic redirect, which is good although I'm going to have add the HSTS headers myself and hope that doesn't get toasted when I next run one of these scripts. Renew seems to behave though.


HSTS removes a vulnerable step when redirecting from an insecure to a secure connection. Details on the magic can be seen on the OWASP site.

The important bit of Apache magic is:

Header always set Strict-Transport-Security "max-age=31536000; includeSubdomains; preload"
Which is stuck into the https vhosts and requires mod_headers enabled.

Testing this was a world of fun. I'd recommend disabling the cache (in the dev tools), using a plugin to inspect the headers (I like Live HTTP Headers) and making liberal use of this secret page to check the status of the HSTS settings. This is all in Chrome.

Tidying up

It seems only the automagic script doesn't like my old Apache config. Now it’s all set up I can put everything back in the same file.
So now I am handling four different connections in the same file:
With 2. and 3. redirecting to 1. and 4. redirecting to 2. so as to pick up the extra HSTS headers.


Yeah, that can wait.

Overall though, this was not the trial I expected. Getting a cert is now really easy. The only parts that required any real thought were figuring out how to arrange my Apache config and checking the HSTS headers were being set correctly.

No excuses any more! Best do the others.

Sunday, 3 April 2016

Creating a database and user for local postgres development

So, yeah. I'm a postgres n00b. But I'm a n00b who wants to be able to create a non-superuser account and database, relate the two and also be able to remember how to do this again in two weeks time.


As superuser via UNIX user authentication:
sudo -u postgres psql postgres

As superuser directly (-W forces password prompt):
psql -U postgres -W

As a user to a specific database:
psql database -U username -W

Basic commands

Show tables:

List users:

List databases:


User management

CREATE USER username WITH PASSWORD 'password';
DROP USER username;

Database management


Granting ownership and permissions

ALTER DATABASE database OWNER TO username;
Dumb settings for local dev.

Also, if you're getting problems connecting try replacing local peer with local md5 in /etc/postgresql/VERSION/main/pg_hba.conf.

Much of this came from this post. I'm planning on using pgAdmin3 as a database explorer when I want something quicker than the command line (on Ubuntu).