MySQL Basic Auth for Apache on u14.04

Ubuntu 14.04 comes with Apache 2.4, rather than the 2.2 of 12.04, and as such has new bits that require some changes. I struggled for some time to get the database driven http authentication working with 2.4, and here’s how I finally got it working, along with some pitfalls I ran into.

I’ll be assuming that an existing auth database exists that has:
1. a ‘users’ table containing a ‘username’ and a ‘password’ column.
2. a ‘groups’ table containing a ‘username’ and a ‘groups’ column.

I’m also assuming that you have apache installed and working.

Here are the additional installs you’ll need to do :

sudo apt-get install apache2-utils libaprutil1-dbd-mysql

You’ll need to enable some apache modules as well :

sudo a2enmod dbd authn_dbd dbd_mysql authz_dbd

And now we can start configuring things. I placed the dbd info in its own config file in my apache configuration directory and name it dbd.conf. It should look like this :

DBDriver mysql
DBDParams "host=HOSTNAME port=3306 user=DB_USERNAME pass=DB_PASSWORD dbname=DB_NAME"
DBDMin 2
DBDKeep 5
DBDMax 10
DBDExptime 120

The min, keep, max, and exp values should match your environment and what you expect to need, but in most cases can probably be pretty low.

And now for the vhost configuration. Here’s a sample configuration file for the default ubuntu page on 14.04 :

<virtualhost *:80>
DocumentRoot /var/www/html
AuthName "login required"
AuthType Basic
AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT password AS password FROM users WHERE username = %s"
AuthzDBDQuery "SELECT groups FROM groups WHERE username = %s"
Order allow,deny
satisfy any

Here is where there are a lot of various ways to do things. If you don’t have groups, and just want to check for a valid user, then get rid of the ‘AuthzDBDQuery’, ‘Order’ and ‘satisfy’ lines and change ‘Require’ to :

Required valid-user

If you want to allow some other condition, such as an IP, then add


before the ‘satisfy’ line.

If you are using plaintext passwords in the DB, then your ‘AuthDBDUserPWQuery’ line should look like :

AuthDBDUserPWQuery "SELECT encrypt(password) AS password FROM users WHERE username = %s"

And finally, the bad news. There’s a bug in the authz_dbd that comes with 14.04 that doesn’t properly handle users that have multiple groups defined in the DB. So if the group query would return something like this :


…only the last group (monkeys, here) is used when checking the dbd-group requirements. This is a known bug, and a fixed version appears to exist, but it isn’t in the ubuntu package repos. You can always build the authz_dbd from scratch and possibly have better luck.

References :

MySQL Basic Auth for Apache on u14.04