I tried to use MySQL stored procedures from dovecot:
password_query = CALL user_pass_check('%n', '%d', '%w')
user_query = CALL user_info('%n', '%d')This failed with the message: User query failed: PROCEDURE imap.user_info can't return a result set in the given context
The root of this problem is that mysql_real_connect() needs to be called with option CLIENT_MULTI_RESULTS and mysql_next_result() called to retrieve extra results (that will not exist in the way that we use it). I attach a patch that fixes this problem -- the patch is against dovecot-1.2.10. This works for me ... but could probably do with testing by other people.
BTW: I got the same problem with exim this morning, wrote a patch that has now been accepted. The dovecot patch is similar.
I am looking to use mysql procedures, there are some interesting things that can be done. Two suggestions that I have will help with this:
There be variable (say) %o - this be the obscured password, ie what password_query returns.
that dovecot look for either ''password_query'' as above, or ''password_check''. password_check would NOT return a password, but would be given %o and determine itself if the password is correct. It would return the other values (user, userdb_home, ...) and auth_result that would encode success/retry/fail (0, 1, 2 - or maybe more structured [**]) and auth_reason some human readable reason. The ''nologin'' value encodes some of this.
The motivation for this is that my stored procedure will record the number of successive login failures and lock the account after 3 of them. It would also be possible to do time based restrictions & the such.
Also: by passing %o the password is not sent in plain to the database server - which will increase security.
I will publish my stored procedures when done.
Regards
[**] eg taking ideas from the HTTP codes: 200 - OK 300 5 - try again in 5 minutes 301 2 9 - try again on tuesday at 9am 400 - Login forbidden, no reason given 401 - bad username and/or password 402 - account locked administratively 403 - too many failed login attempts 500 - authentication system error The above would allow a native language version of auth_reason to be produced
--
Alain Williams
Linux/GNU Consultant - Mail systems, Web sites, Networking, Programmer, IT Lecturer.
+44 (0) 787 668 0256 http://www.phcomp.co.uk/
Parliament Hill Computers Ltd. Registration Information: http://www.phcomp.co.uk/contact.php
Past chairman of UKUUG: http://www.ukuug.org/
#include