[cvs] [Wiki] changed: SQLAuthHowTo

[Wiki Guest]

guest [85.181.13.157]  Mon, 02 Feb 2009 13:04:56 -0500

Modified page: http://wiki.horde.org/SQLAuthHowTo
New Revision:  1.17
Change log:  added the comments on where to find a howto with dovecot,  
postfix and mysql; and changed the query, since the MD5 in the query  
is wrong

@@ -42,28 +42,40 @@
  * 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_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;}}
+* 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_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);}}
+* For query_exists, enter: {{SELECT 1 FROM users WHERE  
SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1);}}
+
+''Comment from a user: please use howto for installing dovecot and  
postfix with mysql found here: ''
+
+http://wiki.dovecot.org/HowTo/DovecotLDAPostfixAdminMySQL
+
+'' Next you need to change the query for auth at least to:''
+For query_auth, enter: {{SELECT * FROM mailbox WHERE username = L AND  
password = P);}}
+Select the encrytption to md5-hex
+
+''This should do it to login, changing passwords and so on, will not  
work with this ''

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