Using MySQL as a backend for Apache Authentication

You might be wondering why you'd want to use MySQL as a backend to Apache Authorisation. For my planned use there was one very compelling reason: I already had a list of users with accounts in a MySQL database (from my CMS), and I didn't want to have to replicate the list, or make my users have another login.

Some quick searching on the internet yielded an Apache module called mod_auth_mysql, which sounded perfect. Unfortunately all is not as simple as it sounds. As a result I've compiled a guide that will hopefully be helpful to someone else too.

Install & Enable:

I'm using Ubuntu Natty and Apache2, which means that it's available from the repos, so:

    (sudo) $ apt-get install libapache2-mod-auth-mysql
    (sudo) $ a2enmod auth_mysql
    (sudo) $ apache2ctl restart

Configure:

For reasons unknown the online documentation on SourceForge is out of date, referring to a much older version than the one in the current Ubuntu. Thankfully I did find that as part of the package install some docs were also installed into /usr/share/doc/libapache2-mod-auth-mysql/. There I found a couple of useful GZipped files: USAGE.gz and DIRECTIVES.gz. Having found these it's much simpler than trying to piece things together from the old docs on SourceForge, but here are the highlights.

Disable other Auths:

For some reason Apache doesn't like using mod_auth_mysql concurrently with other auth types, so you need to disable any other auths for the location where you're trying to use MySQL auth. It's suggested that you can do this by disabling the other auth modules, but this simply confused things for me, and didn't seem to work. The alternative (which I recommend) is to locally disable them using directives:

    # You might only need one of these.
    AuthBasicAuthoritative Off
    AuthUserFile /dev/null

Create a MySQL account for apache:

You could use an existing account, but I wouldn't recommend this for two reasons:

1. The password needs to be stored in plaintext as part of the Apache config.
2. You'll probably want to restrict the access rights of the user down to just SELECT on the table the users are stored in.

I'm not going to detail how to do this here, except to note that I used PHPMyAdmin, which made it really easy.

Setup your MySQL details:

Using the DIRECTIVES file as a reference this was actually pretty simple, this is approximately what the MySQL bits of my config file look like:

    AuthType                     Basic
    Auth_MySQL                   On
    # My CMS uses PHP's md5()
    Auth_MySQL_Encryption_Types  PHP_MD5
    Auth_MySQL_Host              localhost
    Auth_MySQL_DB                my_cms_db
    Auth_MySQL_User              apache
    Auth_MySQL_Password          password
                             # ^Change this^ !
    Auth_MySQL_Password_Table    users
    Auth_MySQL_UserName_Field    username
    Auth_MySQL_Password_Field    password
    Auth_MySQL_Authoritative     On

As you can see it's pretty easy to figure out which directives do what once you know the names. The slight oddity is the Auth_MySQL_Password_Table directive, which is the table the users and their password are looked up in.

You're done

Sit back and enjoy managing your users in MySQL.