Using Horde with workaround.org-style setup

Goal: Be able to use Horde/Imp with the SQL-scheme from the workaround.org tutorial. (I'm no SQL guru at all so I'm sure there's room for improvement)

Backup your Horde/Imp conf.php files and relevant MySQL databases
Log in to Horde
Administration, Setup, Horde, Tab Authentication
Switch authentication backend to SQL authentication w/custom-made queries
Set The encryption to use to store the password in the table to plain
Adjust Database name/tablespace to the one defined in the tutorial ("mailserver")
Edit imp/config/servers.php and set 'hordeauth' => 'full' so users only need to login once and Horde then passes authentication data to Imp

Now paste these queries into the textfields or place code below to horde/config/conf.php:

In the WebGUI

Authenticate the user

SELECT * FROM view_users WHERE email = \L AND password = MD5(\P);

Add user

Ugly: hardcoded domain-id.

INSERT INTO mailserver.virtual_users (id,domain_id,user,password) VALUES (NULL, 1, \L, MD5(\P));

Update user

UPDATE mailserver.virtual_users SET password = MD5(\P) WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;

I think it makes no sense to change the basic username, so the only thing you can do here (that is, relevant to the mailserver database) is update your password. Attention, hardcoded domain.

Remove user

DELETE FROM mailserver.virtual_users WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;

List users

SELECT * FROM view_users;

In the config file

Locate the conf.php from your Horde installation. Usually found in /var/www/[horde-install]/config/conf.php. Be sure to change hardcoded values explained above.

$conf['auth']['params']['query_auth'] = 'SELECT * FROM view_users WHERE email = \L AND password = MD5(\P);';
$conf['auth']['params']['query_add'] = 'INSERT INTO mailserver.virtual_users (id,domain_id,user,password) VALUES (NULL, 1, \L, MD5(\P));';
$conf['auth']['params']['query_update'] = 'UPDATE mailserver.virtual_users SET password = MD5(\P) WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;';
$conf['auth']['params']['query_remove'] = 'DELETE FROM mailserver.virtual_users WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;';
$conf['auth']['params']['query_list'] = 'SELECT * FROM view_users;';

Written on 7 September 2007 by Oliver Ladner. Last updated on 7 November 2007.