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.