[cvs] [Wiki] changed: SQLAuthHowTo

[Wiki Guest]

guest [128.83.155.230]  Tue, 16 Sep 2008 18:12:28 -0400

Modified page: http://wiki.horde.org/SQLAuthHowTo
New Revision:  1.12
Change log:  Fix for domains, and w/o domains

@@ -6,8 +6,22 @@

  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,
+to wit:
+
+<code>
+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
+);
+</code>

  Now, configure IMP:

  * Backup {{horde/imp/config/servers.php}} and {{imp/config/conf.php}}
@@ -26,15 +40,30 @@
  * 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 using no 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 (domain,userid,password)  
VALUES ('example.org', \L, MD5(\P));}} //NB: Change the domain to your  
domain in the above query.//
+* 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 = TRIM('@example.org' FROM \O) LIMIT 1;}} //NB: Change the  
domain to your domain in the above query.//
-* For query_resetpassword, enter: {{UPDATE users SET password =  
MD5(\P) WHERE userid = TRIM('@example.org' FROM \L) LIMIT 1;}} //NB:  
Change the domain to your domain in the above query.//
-* For query_remove, enter: {{DELETE FROM users WHERE userid =  
TRIM('@example.org' FROM \L) LIMIT 1;}}
+* 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,  
substituting your domain name for //example.com:
+
+* 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)  
VALUES ( SUBSTRING_INDEX(\L, '@', -1), SUBSTRING_INDEX(\L, '@', 1),  
MD5(\P));}}
+* 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);}}

  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.