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
Encryption_Types. Possible values are (Source:
DIRECTIVES in the Debian package):
- Pretty self-explanatory. Not recommended.
- Check the password via the standard Unix crypt() call, using DES hashing.
- Check the password via the standard Unix crypt() call, using an MD5 hash.
- 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.
- Compares with an MD5 hash, encoded in the way that PHP and MySQL handle MD5 hashes – 32 character hex code, with lowercase letters.
- Compares with a SHA1 hash, encoded the way that MySQL, PHP, and the sha1sum command produce their output (a 40 character lowercase hex representation).
- The hashing scheme used by the MySQL PASSWORD() function.