How to use apache2's mod_auth_mysql with a drupal 6 user database


September 2012.

Situation


You have a nice drupal 6 installation, with users and groups managed by goats or non technical people.

You have another website or system protected by apache and you want your users to be able to log in using their drupal credentials. That way the userbase can be managed through the drupal admin section and not through obscure htaccess and htgroup files.

Solution


Create a role in your drupal installation (here it's utilisateur_git) and add users into it.

Check that you fetch the information manually using a query like this one:


select users.name, users.pass from users LEFT JOIN users_roles ON (users.uid = users_roles.uid) LEFT JOIN role ON (users_roles.rid = role.rid) WHERE role.name = 'utilisateur_git';


Configure apache:


AuthName MyAuthName
AuthType Basic
AuthBasicAuthoritative Off
Auth_MySQL_Host 127.0.0.1
Auth_MySQL_User mysql_ser
Auth_MySQL_Password mysql_password
Auth_MySQL_DB databasename
Auth_MySQL_Password_Table "users LEFT JOIN users_roles ON (users.uid = users_roles.uid) LEFT JOIN role ON (users_roles.rid = role.rid)"
Auth_MySQL_Password_Clause " AND role.name = 'utilisateur_git'"
Auth_MySQL_Username_Field users.name
Auth_MySQL_Password_Field users.pass
Auth_MySQL_Encryption_Types PHP_MD5
require valid-user