SQL Authentication Howto

NOTE: This page is a work in progress, and has not been tested. It may or may not be of any use, may contain numerous errors, and may turn your tongue a strange color. Use at your own risk.

First, install and configure MySQL (root user and password, access rules, firewall rules, etc) following the directions found on numerous web sites on the internet. This will be operating system dependent, and vary based on your needs.

Next, install Horde and IMP, following directions found elsewhere on this wiki. Specific instructions vary by operating system and your access to the system.

Then, install dovecot, following the instructions on http://wiki.dovecot.org/ and configure dovecot to use MySQL as per the directions at http://wiki.dovecot.org/AuthDatabase/SQL

We will assume that dovecot's SQL database is setup like the example at http://wiki.dovecot.org/AuthDatabase/SQL:

CREATE TABLE users (

    userid VARCHAR(128) NOT NULL,

    domain VARCHAR(128) NOT NULL,

    password VARCHAR(64) NOT NULL,

    home VARCHAR(255) NOT NULL,

    uid INTEGER NOT NULL,

    gid INTEGER NOT NULL

);

Now, configure IMP:

• Backup horde/imp/config/servers.php and imp/config/conf.php
  • Edit horde/imp/config/servers.php and set 'hordeauth' => 'full' so users only need to login once (Horde passes authentication data to IMP)

Now, configure Horde:

• Backup your horde/config/conf.php file
  • Backup any relevant MySQL databases, if they have live/important data in them
  • Log in to Horde as an administrative user
  • Navigate: Administration -> Setup -> Horde, and select the Authentication tab.
  • Switch the authentication backend driver to "SQL authentication w/custom-made queries"
  • Set the phptype setting to "MySQL"
  • Set the protocol setting to "TCP/IP"
  • Leave port as the default "3306"
  • Set the hostspec to "localhost" (or to your SQL host if it is not on the same machine)
  • Set the username and password paramters to the SQL database username and password you set when creating the database
  • Set the encryption to use to store the password in the table to crypt-md5 (is this correct?)
  • Set the database field to the one defined when you configured dovecot's SQL database (in this case, we will use "users" like on the dovecot website)

If you are not using domains, then use the following queries:

• For query_auth, enter: SELECT * FROM users WHERE userid = \L AND password = MD5(\P);
  • For query_add, enter: INSERT INTO users (userid,password,home) VALUES (\L, MD5(\P), '/home/\L'); NB: You may need to change the "home" value to point to their home directory or file space
  • For query_getpw, enter: SELECT password FROM users WHERE userid = \L;
  • For query_update, enter: UPDATE users SET userid = \L WHERE userid = \O) LIMIT 1;
  • For query_resetpassword, enter: UPDATE users SET password = MD5(\P) WHERE userid = \L;
  • For query_remove, enter: DELETE FROM users WHERE userid = \L;
  • For query_list, enter: SELECT * FROM users;
  • For query_exists, enter: SELECT 1 FROM users WHERE userid = \L;

If you are using domains, then use the following queries:

• For query_auth, enter: SELECT * FROM users WHERE userid = SUBSTRING_INDEX(\L, '@', 1) AND domain = SUBSTRING_INDEX(\L, '@', -1) AND password = MD5(\P);
  • For query_add, enter: INSERT INTO users (domain,userid,password,home) VALUES ( SUBSTRING_INDEX(\L, '@', -1), SUBSTRING_INDEX(\L, '@', 1), MD5(\P), '/home/\L'); NB: You may need to change the "home" value to point to their home directory or file space
  • For query_getpw, enter: SELECT password FROM users WHERE userid = SUBSTRING_INDEX(\L, '@', 1) AND domain = SUBSTRING_INDEX(\L, '@', -1);
  • For query_update, enter: UPDATE users SET userid = SUBSTRING_INDEX(\L, '@', 1) AND domain = SUBSTRING_INDEX(\L, '@', -1) WHERE userid = SUBSTRING_INDEX(\O, '@', 1) AND domain = SUBSTRING_INDEX(\O, '@', -1);
  • For query_resetpassword, enter: UPDATE users SET password = MD5(\P) WHERE userid = SUBSTRING_INDEX(\L, '@', 1) AND domain = SUBSTRING_INDEX(\L, '@', -1);
  • For query_remove, enter: DELETE FROM users WHERE userid = SUBSTRING_INDEX(\L, '@', 1) AND domain = SUBSTRING_INDEX(\L, '@', -1);
  • For query_list, enter: SELECT * FROM users;
  • For query_exists, enter: SELECT 1 FROM users WHERE SUBSTRING_INDEX(\L, '@', 1) AND domain = SUBSTRING_INDEX(\L, '@', -1);

NB: We do not (in this wiki page) use the uid/gid fields. If you need these fields, you will need to modify the queries to include them, as appropriate. In the same vain, you could add additional fields as well, if needed or desired.

If you need to use multiple virtual domains, you might see the web page http://wiki.vpslink.com/HOWTO:_ISP-style_Email_Server_with_Debian-Etch_and_Postfix_2.3 which could provide much inspiration for the sql database setup.