\documentclass{article}
\usepackage{ulem}
\pagestyle{headings}
\begin{document}
\part{SQL Authentication Howto}
\textsl{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/\footnote{http://wiki.dovecot.org/} and configure dovecot to use MySQL as per the directions at http://wiki.dovecot.org/AuthDatabase/SQL\footnote{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\footnote{http://wiki.dovecot.org/AuthDatabase/SQL}:

\begin{verbatim}

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
);
\end{verbatim}

Now, configure IMP:

\begin{itemize}
\item{Edit/Create \texttt{horde/imp/config/servers.local.php} and set \texttt{'hordeauth' => 'full'} so users only need to login once (Horde passes authentication data to IMP)}
\end{itemize}
Now, configure Horde:

\begin{itemize}
\item{Backup your \texttt{horde/config/conf.php} file}
\item{Backup any relevant MySQL databases, if they have live/important data in them}
\item{Log in to Horde as an administrative user}
\item{Navigate: Administration -> Setup -> Horde, and select the Authentication tab.}
\item{Switch the authentication backend driver to "SQL authentication w/custom-made queries"}
\item{Set the phptype setting to "MySQL"}
\item{Set the protocol values appropriately for your situation.}
\item{Set the username and password parameters to the SQL database username and password you set when creating the database.}
\item{Set the database field to the name of the database that contains the credential data - in this example, "users".}
\item{Choose the appropriate encryption algorithm for your authentication data. This obviously should match the algorithm that was used to create any existing credentials.}
\end{itemize}
If you are not using domains, then use the following queries:

\begin{itemize}
\item{For query\_auth, enter: \texttt{SELECT * FROM users WHERE userid = \\L AND password = \\P}}
\item{For query\_add, enter: \texttt{INSERT INTO users (userid, password, home) VALUES (\\L, \\P), concat('/home/', \\L))} \textsl{NB: You may need to change the "home" value to point to their home directory or file space; This value is not needed/used by Horde.}}
\item{For query\_getpw, enter: \texttt{SELECT password FROM users WHERE userid = \\L}}
\item{For query\_update, enter: \texttt{UPDATE users SET userid = \\L WHERE userid = \\O}}
\item{For query\_resetpassword, enter: \texttt{UPDATE users SET password = \\P WHERE userid = \\L}}
\item{For query\_remove, enter: \texttt{DELETE FROM users WHERE userid = \\L}}
\item{For query\_list, enter: \texttt{SELECT * FROM users}}
\item{For query\_exists, enter: \texttt{SELECT 1 FROM users WHERE userid = \\L}}
\end{itemize}
If you are using domains, then use the following queries:

\begin{itemize}
\item{For query\_auth, enter: \texttt{SELECT * FROM users WHERE userid = SUBSTRING\_INDEX(\\L, '@', 1) AND domain = SUBSTRING\_INDEX(\\L, '@', -1) AND password = \\P}}
\item{For query\_add, enter: \texttt{INSERT INTO users (domain, userid, password, home) VALUES ( SUBSTRING\_INDEX(\\L, '@', -1), SUBSTRING\_INDEX(\\L, '@', 1), \\P, '/home/\\L')}}
\item{For query\_getpw, enter: \texttt{SELECT password FROM users WHERE userid = SUBSTRING\_INDEX(\\L, '@', 1) AND domain = SUBSTRING\_INDEX(\\L, '@', -1)}}
\item{For query\_update, enter: \texttt{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);}}
\item{For query\_resetpassword, enter: \texttt{UPDATE users SET password = \\P WHERE userid = SUBSTRING\_INDEX(\\L, '@', 1) AND domain = SUBSTRING\_INDEX(\\L, '@', -1)}}
\item{For query\_remove, enter: \texttt{DELETE FROM users WHERE userid = SUBSTRING\_INDEX(\\L, '@', 1) AND domain = SUBSTRING\_INDEX(\\L, '@', -1)}}
\item{For query\_list, enter: \texttt{SELECT * FROM users}}
\item{For query\_exists, enter: \texttt{SELECT 1 FROM users WHERE SUBSTRING\_INDEX(\\L, '@', 1) AND domain = SUBSTRING\_INDEX(\\L, '@', -1)}}
\end{itemize}
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\footnote{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.


\noindent\rule{\textwidth}{1pt}
\newline
\textit{Comment from another user: You can also do it the other way - let Dovecot authenticate against the Horde users table, like I do: }1. Add a dovecot user to the SQL server (I use PostgreSQL\footnote{http://example.com/index.php?page=PostgreSQL}).\newline
2. Amend permissions for the the 'horde\_users' table to give the 'dovecot' user read (SELECT) permission.\newline
3. Use the following (amend as needed - the example assumes you are using PostgreSQL\footnote{http://example.com/index.php?page=PostgreSQL}) in /etc/dovecot/dovecot-sql.conf:

\#/etc/dovecot/dovecot-sql.conf\newline
driver = pgsql\newline
connect = host=localhost dbname=horde user=dovecot password=\newline
default\_pass\_scheme = MD5-CRYPT\newline
password\_query = SELECT user\_uid AS username, user\_pass AS password    FROM horde\_users WHERE user\_uid = '\%u'\newline
iterate\_query = SELECT user\_uid AS username FROM users

4. I use a static user database in Dovecot, so I didn't need the "user\_query"\newline
5. Add the following to /etc/dovecot/dovecot.conf:

userdb \{\newline
  args = uid=vmail gid=vmail home=/srv/dovecot/\%u\newline
  driver = static\newline
\}

passdb \{\newline
  driver = sql\newline
  args = /etc/dovecot/dovecot-sql.conf\newline
\}

Amend the above to suit your setup. Don't forget to set MD5-CRYPT as password encryption in Horde as well. One advantage of doing things this way is that passwords can be changed and new users added through Horde - and they will apply to Dovecot as well.


\noindent\rule{\textwidth}{1pt}
\newline
\textit{Comment from Deny Dias <deny at macpress dot com dot br>: Integrating Horde to ViMbAdmin\footnote{http://example.com/index.php?page=ViMbAdmin}}From http://www.vimbadmin.net/\footnote{http://www.vimbadmin.net/}:

"The ViMbAdmin\footnote{http://example.com/index.php?page=ViMbAdmin} project (vim-be-admin) provides a web based virtual mailbox administration system allowing mail administrators to manage domains, mailboxes and aliases."

To integrate ViMbAdmin\footnote{http://example.com/index.php?page=ViMbAdmin} as an authentication backend to Horde, you can follow this quite simple steps. Pay attention that this method DO NOT take into account multiple virtual domains. It also do not provides the functions to UPDATE and DELETE users at ViMbAdmin\footnote{http://example.com/index.php?page=ViMbAdmin} database as this may extend vulnerability surface. Just count on ViMbAdmin\footnote{http://example.com/index.php?page=ViMbAdmin} for the regular user management (CRUD).

1. In your database, grant to the \texttt{horde} user just the minimal set of required permissions at ViMbAdmin\footnote{http://example.com/index.php?page=ViMbAdmin} \texttt{mailbox} table:

\texttt{GRANT SELECT (`username`, `password`, `active`) ON `vimbadmin`.`mailbox` TO 'horde'@'localhost';\newline
FLUSH PRIVILEGES;}

2. Configure basic database access as shown above, but use the \texttt{horde} user credentials and prefer unix socket to database connection.

3. Add these queries to Horde configuration:

query\_auth: \texttt{SELECT username, password FROM mailbox WHERE username = \\L AND password = \\P AND active = 1}\newline
query\_getpw: \texttt{SELECT password FROM mailbox WHERE username = \\L AND active = 1}\newline
query\_list: \texttt{SELECT username FROM mailbox WHERE active = 1}\newline
query\_exists: \texttt{SELECT 1 FROM mailbox WHERE username = \\L AND active = 1}

4. Leave \texttt{query\_add}, \texttt{query\_update}, \texttt{query\_resetpassword} and \texttt{query\_remove} blank.

5. Set \texttt{[auth][params][encryption]} to the algorithm that fit your needs.

Generate the new configuration file and you're done.

\end{document}
