[cvs] [Wiki] changed: SQLAuthHowTo

[Jan Schneider]

jan  Wed, 17 Sep 2008 04:50:56 -0400

Modified page: http://wiki.horde.org/SQLAuthHowTo
New Revision:  1.16
Change log:  Fix markup

@@ -7,10 +7,9 @@
  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:
+We will assume that dovecot's SQL database is setup like the example  
at http://wiki.dovecot.org/AuthDatabase/SQL:

  <code>
  CREATE TABLE users (
      userid VARCHAR(128) NOT NULL,
@@ -44,9 +43,9 @@

  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_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;}}
@@ -55,18 +54,16 @@

  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_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.
+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.