RedmineAndApacheAuthMySQL » History » Version 1

Terence Mill, 2010-11-23 20:56
Source found on "ailoo.net":http://ailoo.net/2009/03/authenticate-apache-against-redmine-with-authmysql/

1 1 Terence Mill
h1. Authenticate Apache against Redmine with AuthMySQL
2 1 Terence Mill
3 1 Terence Mill
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.
4 1 Terence Mill
5 1 Terence Mill
First of all, install the auth-mysql Apache module:
6 1 Terence Mill
7 1 Terence Mill
<pre>
8 1 Terence Mill
$ aptitude install libapache2-mod-auth-mysql
9 1 Terence Mill
$ a2enmod auth_mysql
10 1 Terence Mill
$ /etc/init.d/apache2 restart
11 1 Terence Mill
</pre>
12 1 Terence Mill
13 1 Terence Mill
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.
14 1 Terence Mill
15 1 Terence Mill
The simplest way to achieve this is to create a database view which aggregates all the information we need:
16 1 Terence Mill
17 1 Terence Mill
<pre>
18 1 Terence Mill
CREATE VIEW users_auth_external AS
19 1 Terence Mill
SELECT u.login AS username,
20 1 Terence Mill
       u.hashed_password AS passwd,
21 1 Terence Mill
       GROUP_CONCAT(p.identifier) AS groups
22 1 Terence Mill
FROM `members` m
23 1 Terence Mill
INNER JOIN users u ON m.user_id = u.id
24 1 Terence Mill
INNER JOIN projects p ON m.project_id = p.id
25 1 Terence Mill
WHERE u.status = 1
26 1 Terence Mill
GROUP BY username
27 1 Terence Mill
</pre>
28 1 Terence Mill
29 1 Terence Mill
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.
30 1 Terence Mill
31 1 Terence Mill
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).
32 1 Terence Mill
33 1 Terence Mill
<pre>
34 1 Terence Mill
<Location />
35 1 Terence Mill
       AuthType Basic
36 1 Terence Mill
       AuthName "My Authentication"
37 1 Terence Mill
       AuthBasicAuthoritative Off
38 1 Terence Mill
       AuthUserFile /dev/null
39 1 Terence Mill
       AuthMySQL On
40 1 Terence Mill
       AuthMySQL_Authoritative      on
41 1 Terence Mill
       AuthMySQL_Host               localhost
42 1 Terence Mill
       AuthMySQL_DB                 my_database
43 1 Terence Mill
       AuthMySQL_User               my_database_user
44 1 Terence Mill
       AuthMySQL_Password           my_database_password
45 1 Terence Mill
       AuthMySQL_Password_Table     users_auth_external
46 1 Terence Mill
       AuthMySQL_Group_Table        users_auth_external
47 1 Terence Mill
       AuthMySQL_Username_Field     username
48 1 Terence Mill
       AuthMySQL_Password_Field     passwd
49 1 Terence Mill
       AuthMySQL_Group_Field        groups
50 1 Terence Mill
       AuthMySQL_Encryption_Types   SHA1Sum
51 1 Terence Mill
       Require group                myproject
52 1 Terence Mill
</Location>
53 1 Terence Mill
</pre>
54 1 Terence Mill
55 1 Terence Mill
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.
56 1 Terence Mill
57 1 Terence Mill
Note: Redmine stores passwords as SHA1 hashes, so I’m using SHA1Sum in Encryption_Types. Possible values are (Source: DIRECTIVES in the Debian package):
58 1 Terence Mill
59 1 Terence Mill
* +Plaintext+: Pretty self-explanatory. Not recommended.
60 1 Terence Mill
* +Crypt_DES+: Check the password via the standard Unix crypt() call, using DES hashing.
61 1 Terence Mill
* +Crypt_MD5+: Check the password via the standard Unix crypt() call, using an MD5 hash.
62 1 Terence Mill
* +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.
63 1 Terence Mill
* +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.
64 1 Terence Mill
* +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).
65 1 Terence Mill
* +MySQL+: The hashing scheme used by the MySQL PASSWORD() function