[Dovecot] help with AES_DECRYPT and password lookup - mysql password_query

[Gedalya]

On 04/28/2012 07:02 PM, Jeff Lacki wrote:
> Security is my #1 focus right now.
>
> Can someone explain the best solution?  Or is the best solution to just get
> an SSL cert and use plaintext?  (which is actually my future plan).
You absolutely must use SSL if you want security. A non-plaintext 
authentication mechanism only obfuscates the password itself during the 
login stage. The IMAP session itself (email content) needs to be secured 
and that can be more important than the email password (people emailing 
to each other passwords to more interesting things).

Getting your certificate signed by a recognized CA helps your clients to 
verify that the server they are talking to is the server they want to be 
talking to. It doesn't make the encryption any stronger. If your clients 
are willing to click "I know what I'm doing, I trust this certificate", 
then you have the same results.

You can try to get a free certificate here - http://www.startssl.com/ - 
their certificates are trusted by Mozilla and Microsoft products but not 
by RIM (blackberry) or java.

Anyway, given your current setup: you're not using SSL, you want to 
AES-encrypt your passwords in mysql (you don't trust your database 
server) and keep your encryption key in the dovecot configuration (you 
do trust your dovecot server), you can just do:

password_query = SELECT AES_DECRYPT(password, 'mykey') AS password, \
    userid AS user \
    FROM users WHERE userid='%u'

This would allow you to use a digest-based authentication mechanism.

However, you still have the liability of having your users' passwords in 
a reversibly encrypted format, with the key available nearby.
Once you get SSL set up, it would be better to store the passwords in a 
salted hash format such as SSHA, and use plaintext auth (over SSL, of 
course).