Apache, Authentication and MySQL

Apache, Authentication and MySQL

I just spent a couple of hours trying to configure an Apache 2.2 server to do BASIC authentication using a MySQL database of usernames and passwords. The standard way to do this is via the mod_auth_mysql module, but much of the documentation on the web is out of date or has some hidden gotchas. Here is what I got to work.

For reference, I’m using Ubuntu 10.04 with all software installed via APT (apache2, mysql-server, libapache2-mod-auth-mysql).

To install mod_auth_mysql…

$ sudo apt-get install libapache2-mod-auth-mysql
$ sudo a2enmod auth_mysql

The biggest gotcha is that the configuration documentation for mod_auth_mysql is badly out of date. There have been some substantial changes to the configuration parameter names since that was written, although I could not find any definitive documentation of the new configuration parameters. There are a couple of other gotchas in there too, I’ll come to those in a minute.

Before configuring Apache, I set up a test database of usernames and passwords. This is what I did…

$ mysql -uroot -p
mysql> grant all on auth.* to auth_user@localhost identified by ‘XXX’;
mysql> flush privileges;
mysql> create database auth;
mysql> use auth;
CREATE TABLE user_info ( user_name CHAR(100) NOT NULL, user_passwd CHAR(100) NOT NULL, PRIMARY KEY (user_name) );
INSERT INTO `user_info` VALUES (‘test’, MD5(‘test’));
CREATE TABLE user_group ( user_name char(100) NOT NULL, user_group char(100) NOT NULL, PRIMARY KEY (user_name,user_group) );
INSERT INTO `user_group` VALUES (‘test’, ‘test-group’);

Note the length of the user_password field. 100 characters is probably more than needed, but you will definitely need more than the 20 characters suggested in some documentation if you want to use a password hash like MD5. (If the field is too short, then password hashes will get truncated when they’re inserted into the database.)

Then I configured mod_auth_mysql to authenticate users for my whole domain. In the appropriate virtual host configuration file (e.g., /etc/apache2/sites-enabled/000-default) I added the following…

# these lines force authentication to fall through to mod_auth_mysql
AuthBasicAuthoritative Off
AuthUserFile /dev/null

# begin auth_mysql configuration
AuthMySQL On
AuthMySQL_Host localhost
AuthMySQL_User auth_user
AuthMySQL_Password XXXX
AuthMySQL_DB auth
AuthMySQL_Password_Table user_info
AuthMySQL_Username_Field user_name
AuthMySQL_Password_Field user_passwd
AuthMySQL_Empty_Passwords Off
AuthMySQL_Encryption_Types PHP_MD5
AuthMySQL_Authoritative On
#AuthMySQL_Non_Persistent Off
#AuthMySQL_Group_Table user_group
#AuthMySQL_Group_Field user_group

# generic auth configuration
AuthType Basic
AuthName “auth_mysql test”
Require valid-user

Note the “PHP_MD5″ encryption type. (Some of the documented encryption types don’t seem to be available, e.g., “MD5″.)

Then…

$ sudo apache2ctl -t # check syntax
$ sudo apache2ctl restart

Then when browsing to the host, I get an authentication challenge, and can log in with username “test” and password “test”.

Using mod_authn_dbd Instead

There is another way to get Apache to use a relational database to look up usernames and passwords when authenticating – mod_authn_dbd. That module seems more current and has up-to-date documentation, see e.g., the Apache 2.2 mod_authn_dbd module docs and the Apache 2.2 docs on password encryption.

Note however that you cannot use normal MD5 encryption to store passwords in the database with this module. If you want to use MD5 you have to use the special Apache MD5 algorithm.

Also note that to get this working with MySQL you will need to install the MySQL driver for DBD, which you can do via APT:

$ sudo apt-get install libaprutil1-dbd-mysql

If you get a message like “DBD: Can’t load driver file apr_dbd_mysql.so” then this is what you need to do – don’t believe the articles that tell you you need to recompile APR

(by Alistair Miles)