HOWTO: MySQL User Authentication in Apache2 on Ubuntu Gutsy 7.10

This small HOWTO ties in with the previous two HOWTOs on getting MySQL user authentication setup in Apache2. My goal for my systems was to have one location for user authentication for e-mail, FTP, and web site access via a MySQL database so I did not have to create individual users on the Linux system itself. Another reason for this is because I run an replicated environment and only making the change on one database is nice so that it automatically replicates to the other server with the new information.

Again, please note that the MySQL database that I am referencing in this HOWTO is based on the last two HOWTOs where I used the same MySQL database. You can read further down about setting up E-Mail, webmail and others to get the MySQL database file that I have used. The MySQL database is called “horde” and the table that contains the user information is “horde_users”.

Ubuntu Linux Gutsy 7.10 has a problem with the libapache-mod-auth-sql through the aptitude repositories and will not work. I attempted this first and would get “user xxx not found: /locationofaccess”. Follow these steps to install the mod-auth-sql from source:

wget http://heanet.dl.sourceforge.net/sourceforge/modauthmysql/mod_auth_mysql-3.0.0.tar.gz
wget http://www.bleb.org/software/mod_auth_mysql-3.0.0-apache-2.2.3.patch
tar zxf mod_auth_mysql-3.0.0.tar.gz
apt-get install apache2-prefork-dev libmysqlclient15-dev; apt-get –purge remove libapache2-mod-auth-mysql
cd mod_auth_mysql-3.0.0
patch < ../mod_auth_mysql-3.0.0-apache-2.2.3.patch
sed -i ‘s|#include <mysql.h>|#include <mysql/mysql.h>|’ mod_auth_mysql.c
apxs2 -c -lmysqlclient -lm -lz mod_auth_mysql.c
apxs2 -i mod_auth_mysql.la
echo ‘LoadModule mysql_auth_module /usr/lib/apache2/modules/mod_auth_mysql.so’ > /etc/apache2/mods-available/auth_mysql.load
a2enmod auth_mysql

Now, in my setup, I wanted to have all the directives directly in the httpd.conf file and did not want to use .htaccess files. To find the complete list of AuthMySQL directives, please see the source information listed below.

In your httpd.conf file, you will have items that start with <Directory> where you want to require authentication for your users. Here is a copy of what I placed in mine:

AuthType Basic
AuthName “NAME_OF_REALM”
AuthUserFile /dev/null #tells apache not to use a passwd file
AuthBasicAuthoritative Off
AuthMySQLEnable On #enables the mod-sql authentication
AuthMySQLAuthoritative On #tells apache to only authenticate by MySQL
AuthMySQLDB horde #the name of the database that contains information
AuthMySQLUser horde #the user to use to access the database
AuthMySQLPassword PASSWORD_HERE #type the user’s password to access the database
AuthMySQLUserTable horde_users #the table that contains the user information
AuthMySQLNameField user_uid #the field in the table that holds the username
AuthMySQLPasswordField user_pass #the field in the table that holds the password
AuthMySQLPwEncryption none #the type of encryption the password is in
require user USERS_HERE #the users that are allowed to access this directory

If all is well, you should be able to restart the Apache server and you are ready to go!

sudo /etc/init.d/apache2 restart

Sources:
Bug #150649 in libapache-mod-auth-mysql (Ubuntu): “gutsy does not have a working apache+mysql authentication solution
mod-auth-mysql Apache Directives