Authenticate Apache against Redmine with AuthMySQL¶

For a student project we needed to authenticate an apache host against a MySQL database, in this a case we wanted to handle authentication for a Subversion repository with a Redmine database. I know that Redmine has its own solution for this problem using Redmine.pm, but for some reason that approach didn’t work and we didn’t have the time to bug around with it. This howto is written for the use with Redmine (especially the database view), but you should get the point how to set it up on other environments. The howto was done on an Ubuntu 8.10 box but should work on any other distro as well (except for the module installation). I assume that you got all the other stuff (apache, mysql, …) up and running.

First of all, install the auth-mysql Apache module:

$ aptitude install libapache2-mod-auth-mysql
$ a2enmod auth_mysql
$ /etc/init.d/apache2 restart

Now you have to think about which database tables you’d like to use to handle the authentication. Redmine uses a table called users, which holds the username and a password hashed as SHA1 string. Basically you could have Apache authenticate against this table and require any valid user BUT Redmine creates a record for anonymous users with empty login and password which would allow access to our secured site by just entering empty credentials so please do not use this approach. What I did was to use the projects (their identifier) the user is assigned to and using them as groups to authenticate against. So when I want to allow access to all users from a specific project, I just have to configure Apache to require a group which is named like the project’s identifier.

The simplest way to achieve this is to create a database view which aggregates all the information we need:

CREATE VIEW users_auth_external AS
SELECT u.login AS username,
       u.hashed_password AS passwd,
       GROUP_CONCAT(p.identifier) AS groups
FROM `members` m
INNER JOIN users u ON m.user_id = u.id
INNER JOIN projects p ON m.project_id = p.id
WHERE u.status = 1
GROUP BY username

You could go further and include just specific permissions (developer, administrator, …) in the group field, but we didn’t need any more authorization so we stopped here.

Having set up the database, you just need to tell Apache how to handle the authentication. In your vhost configuration use the following snippet (adjust location if the access control shouldn’t affect the whole vhost).

<Location />
       AuthType Basic
       AuthName "My Authentication" 
       AuthBasicAuthoritative Off
       AuthUserFile /dev/null
       AuthMySQL On
       AuthMySQL_Authoritative      on
       AuthMySQL_Host               localhost
       AuthMySQL_DB                 my_database
       AuthMySQL_User               my_database_user
       AuthMySQL_Password           my_database_password
       AuthMySQL_Password_Table     users_auth_external
       AuthMySQL_Group_Table        users_auth_external
       AuthMySQL_Username_Field     username
       AuthMySQL_Password_Field     passwd
       AuthMySQL_Group_Field        groups
       AuthMySQL_Encryption_Types   SHA1Sum
       Require group                myproject
</Location>

Reload your Apache (or restart if you didn’t after activating the module) and you should be up and running. If you are not able to log in, try to change the LogLevel of your vhost to debug and checking the log files.

Note: Redmine stores passwords as SHA1 hashes, so I’m using SHA1Sum in Encryption_Types. Possible values are (Source: DIRECTIVES in the Debian package):

• Plaintext: Pretty self-explanatory. Not recommended.
• Crypt_DES: Check the password via the standard Unix crypt() call, using DES hashing.
• Crypt_MD5: Check the password via the standard Unix crypt() call, using an MD5 hash.
• Crypt: Check the password via the standard Unix crypt() call, without preference for the hashing scheme employed. This is the generally preferred means of checking crypt()ed passwords, because it allows you to use other schemes which may be available on your system, such as blowfish.
• PHP_MD5: Compares with an MD5 hash, encoded in the way that PHP and MySQL handle MD5 hashes – 32 character hex code, with lowercase letters.
• SHA1Sum: Compares with a SHA1 hash, encoded the way that MySQL, PHP, and the sha1sum command produce their output (a 40 character lowercase hex representation).
• MySQL: The hashing scheme used by the MySQL PASSWORD function