MySQL based authentication in Apache

I like HTTP. A simple, robust protocol that (in itself) has so many lovely features already if one knows how to use them. As such, I wanted to try and see if I could use the standard HTTP authentication more often for my websites (especially those that I don’t want anyone to access).

Of course, everyone who has ever looked at protecting their phpMyAdmin directory on their website probably came across the standard .htpasswd way of doing things in Apache. However, in my latest server setup, everything is run out of a MySQL database (e.g. email users & addresses for dovecot & postfix, this blog, my webmail, my website …). So I thought: maybe instead of having to constantly maintain .htpasswd files, I could utilize my database to do so. And it works quite nicely!

Prerequisits

mod_auth_mysql from somewhere (build yourself or get a package…)
Load it into your Apache configuration with this or something similiar…
```
LoadModule auth_mysql_module /usr/lib/apache2/modules/mod_auth_mysql.so
```

Setting up the DB

This is my sample setup and not the ultimate truth, so it is possible to change DB name, user, password, tables and all of that (the Apache config shows that later)!

Create a database, user apacheUser and password secretPassword

CREATE DATABASE `apache` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `apache`;
CREATE USER 'apacheUser'@'localhost' IDENTIFIED BY 'secretPassword';
GRANT SELECT ON `apache` . * TO 'apacheUser'@'localhost';

I use a setup with two tables: Users and groups

CREATE TABLE IF NOT EXISTS `apache`.`users` (
 `username` char(100) NOT NULL,
 `passwd` char(100) NOT NULL,
 PRIMARY KEY (`username`)
);

CREATE TABLE IF NOT EXISTS `apache`.`groups` (
 `username` char(100) NOT NULL,
 `group` char(100) NOT NULL,
 PRIMARY KEY (`username`,`group`)
);

And of course, adding a sample user and group

INSERT INTO `apache`.`users` (`username` ,`passwd`)
   VALUES ('csh', MD5( 'secret' ));
INSERT INTO `apache`.`groups` (`username` ,`group`)
   VALUES ('csh', 'testGroup');

Obviously, one could create a foreign key relationship or even a group header table, but that’s too much hassle for the time being… The password column is filled with MD5 hashes of passwords (which is probably insecure, I know, but that was the easiest hash I could find in the configuration).

Setting up a password protection

Now this can go into any .htaccess file or (in my case), I put that in the actual virtual host definition for the hosts I want to protect.

<Directory /http/test> 
   Options none 
   AllowOverride All 
   Order allow,deny 
   Allow from all 
   AuthBasicAuthoritative Off 
   AuthMYSQL on 
   AuthMySQL_Authoritative on 
   AuthMySQL_DB apache 
   Auth_MySQL_Host localhost 
   Auth_MySQL_User apacheUser 
   Auth_MySQL_Password secretPassword
   AuthMySQL_Password_Table users 
   AuthMySQL_Username_Field users.username 
   AuthMySQL_Password_Field users.passwd 
   AuthMySQL_Empty_Passwords off 
   AuthMySQL_Encryption_Types PHP_MD5 
   AuthMySQL_Group_Table "users, groups" 
   AuthMySQL_Group_Clause " AND users.username = groups.username" 
   AuthMySQL_Group_Field groups.group 
   AuthType Basic 
   AuthName "Please log in" 
   Require group testGroup 
</Directory>

Just to point out the obvious, the above Apache configuration will force a user to authenticate against a username in table `users` with a password which (MD5 hashed) equals the string in the passwd column of `users`. Also, there needs to be an entry in `groups` with the username and the group ‘testGroup’, otherwise the user is not allowed either.

The values in bold are obviously things you probably want to change (mostly in accordance with the DB settings/user/pwd created above. The really important bit to figure out here was that AuthMySQL essentially takes these “settings” and just concatenates them into one large SQL statement. It took me a bit of source code browsing to figure out the two table approach described here, and I’m sure one could even go further to split it even more thoroughly.