[cvs] [Wiki] changed: SQLAuthHowTo

[Wiki Guest]

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

Modified page: http://wiki.horde.org/SQLAuthHowTo
New Revision:  1.13
Change log:  cleanup, add some notes

@@ -41,9 +41,9 @@
  * 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:
+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
@@ -53,9 +53,9 @@
  * 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:
+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)  
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);}}
@@ -63,7 +63,11 @@
  * 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.