[Dovecot] help with AES_DECRYPT and password lookup - mysql password_query
Hello-
Sorry if this is a noob question, but I cant seem to get my password_query to work with dovecot + mysql. Im using 'Password verification by SQL server' at:
http://wiki2.dovecot.org/AuthDatabase/SQL
trying to modify it to work with my encrypted passwords in the DB.
Im using the following which isnt working:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_DECRYPT(password, 'mykey')=password
Is it even possible to do this via 'password_query'?
If so, what am I doing wrong?
Thanks, Jeff
/mf/home/jeep/shell/.signature
On 04/28/2012 04:54 PM, Jeff Lacki wrote:
Hello-
Sorry if this is a noob question, but I cant seem to get my password_query to work with dovecot + mysql. Im using 'Password verification by SQL server' at:
http://wiki2.dovecot.org/AuthDatabase/SQL
trying to modify it to work with my encrypted passwords in the DB.
Im using the following which isnt working:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_DECRYPT(password, 'mykey')=password
Is it even possible to do this via 'password_query'?
If so, what am I doing wrong?
Thanks, Jeff
/mf/home/jeep/shell/.signature The wiki says: "The password is in %w variable" Maybe you meant
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_DECRYPT(password, 'mykey')='%w'
/mf/home/jeep/shell/.signature
The wiki says: "The password is in %w variable" Maybe you meant
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_DECRYPT(password, 'mykey')='%w'
Thanks, but when I do that, I get an empty value for '%w', not sure why?
Apr 28 14:23:48 mydomain dovecot: auth-worker(13349): Debug: sql(jeff,127.0.0.1): query: SELECT NULL AS password, 'Y' as nopassword, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT(password, 'mykey')=''
/mf/home/jeep/shell/.signature
On 04/28/2012 05:25 PM, Jeff Lacki wrote:
/mf/home/jeep/shell/.signature The wiki says: "The password is in %w variable" Maybe you meant
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_DECRYPT(password, 'mykey')='%w'Thanks, but when I do that, I get an empty value for '%w', not sure why?
Apr 28 14:23:48 mydomain dovecot: auth-worker(13349): Debug: sql(jeff,127.0.0.1): query: SELECT NULL AS password, 'Y' as nopassword, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT(password, 'mykey')=''
/mf/home/jeep/shell/.signature
Are you using a plaintext authentication mechanism?
Hi Jeff,
Jeff Lacki wrote:
Sorry if this is a noob question, but I cant seem to get my password_query to work with dovecot + mysql. Im using 'Password verification by SQL server' at:
http://wiki2.dovecot.org/AuthDatabase/SQL
trying to modify it to work with my encrypted passwords in the DB.
Im using the following which isn't working:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_DECRYPT(password, 'mykey')=password
- Is it even possible to do this via 'password_query'?
Please provide your dovecot version and output of the following command: doveconf -n and the complete external sql query files without passwords.
You might alsolet the SQL server compare the encrypted password in the database with the encrypted string:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_ENCRYPT('%w','mykey')=password
Regards, Daniel
- Is it even possible to do this via 'password_query'?
Please provide your dovecot version and output of the following command: doveconf -n and the complete external sql query files without passwords.
You might alsolet the SQL server compare the encrypted password in the database with the encrypted string:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_ENCRYPT('%w','mykey')=passwordRegards, Daniel
Thank you Daniel. I downloaded and compiled 2.1.5 yesterday. The problem seems to be that '%w' evaulates to an empty string:
Debug: sql(jeff,127.0.0.1): query: SELECT NULL AS password, 'Y' as nopassword, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT('', 'key')=password
I also just noticed that version 2.0.15 in my output below is coming from somewhere? I did try setting things up under 2.0.15 initially last week, but wanted to be up to date so downloaded the latest yesterday. I never did get it all working under 2.0.15 either btw.
dovecot -n -c /opt/dovecot/etc/dovecot/dovecot.conf # 2.0.15: /opt/dovecot/etc/dovecot/dovecot.conf # OS: Linux 2.6.35.14-106.fc14.x86_64 x86_64 Fedora release 14 (Laughlin) ext4 auth_debug = yes auth_debug_passwords = yes auth_mechanisms = cram-md5 auth_verbose = yes auth_verbose_passwords = plain default_client_limit = 225 first_valid_uid = 1000 listen = * lock_method = flock mail_location = mbox:/var/mail/%d/%1n/%n:INDEX=/var/indexes/%d/%1n/%n mail_privileged_group = mail mbox_lock_timeout = 1 mins mbox_write_locks = fcntl namespace { inbox = yes location = prefix = separator = . type = private } passdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocols = imap service auth { inet_listener { port = 12345 } unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } user = $default_internal_user } service imap-login { inet_listener imap { port = 143 } service_count = 1 } ssl_cert = </etc/pki/dovecot/certs/dovecot.pem ssl_key = </etc/pki/dovecot/private/dovecot.pem userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocol imap { imap_idle_notify_interval = 1 mins imap_max_line_length = 64 k mail_max_userip_connections = 5 }
/mf/home/jeep/shell/.signature
On 04/28/2012 06:28 PM, Jeff Lacki wrote:
- Is it even possible to do this via 'password_query'? Please provide your dovecot version and output of the following command: doveconf -n and the complete external sql query files without passwords.
You might alsolet the SQL server compare the encrypted password in the database with the encrypted string:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_ENCRYPT('%w','mykey')=passwordRegards, Daniel Thank you Daniel. I downloaded and compiled 2.1.5 yesterday. The problem seems to be that '%w' evaulates to an empty string:
Debug: sql(jeff,127.0.0.1): query: SELECT NULL AS password, 'Y' as nopassword, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT('', 'key')=password
I also just noticed that version 2.0.15 in my output below is coming from somewhere? I did try setting things up under 2.0.15 initially last week, but wanted to be up to date so downloaded the latest yesterday. I never did get it all working under 2.0.15 either btw.
dovecot -n -c /opt/dovecot/etc/dovecot/dovecot.conf # 2.0.15: /opt/dovecot/etc/dovecot/dovecot.conf # OS: Linux 2.6.35.14-106.fc14.x86_64 x86_64 Fedora release 14 (Laughlin) ext4 auth_debug = yes auth_debug_passwords = yes auth_mechanisms = cram-md5 auth_verbose = yes auth_verbose_passwords = plain default_client_limit = 225 first_valid_uid = 1000 listen = * lock_method = flock mail_location = mbox:/var/mail/%d/%1n/%n:INDEX=/var/indexes/%d/%1n/%n mail_privileged_group = mail mbox_lock_timeout = 1 mins mbox_write_locks = fcntl namespace { inbox = yes location = prefix = separator = . type = private } passdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocols = imap service auth { inet_listener { port = 12345 } unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } user = $default_internal_user } service imap-login { inet_listener imap { port = 143 } service_count = 1 } ssl_cert =</etc/pki/dovecot/certs/dovecot.pem ssl_key =</etc/pki/dovecot/private/dovecot.pem userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocol imap { imap_idle_notify_interval = 1 mins imap_max_line_length = 64 k mail_max_userip_connections = 5 }
/mf/home/jeep/shell/.signature
Yeap, you seem to only allow cram-md5. In this case, you client isn't transmitting the actual password that the user is typing, so dovecot simply doesn't have the password you want it to put in %w. It rather has a digest of it. The only way to use a non-plaintext auth mechanism is to provide dovecot the correct password from the database in plaintext.
Gedalya <gedalya@gedalya.net> wrote:
On 04/28/2012 06:28 PM, Jeff Lacki wrote:
- Is it even possible to do this via 'password_query'? Please provide your dovecot version and output of the following command: doveconf -n and the complete external sql query files without passwords.
You might alsolet the SQL server compare the encrypted password in the database with the encrypted string:
password_query = SELECT NULL AS password,
'Y' as nopassword, userid AS user
FROM users WHERE userid='%u' AND AES_ENCRYPT('%w','mykey')=passwordRegards, Daniel Thank you Daniel. I downloaded and compiled 2.1.5 yesterday. The problem seems to be that '%w' evaulates to an empty string:
Debug: sql(jeff,127.0.0.1): query: SELECT NULL AS password, 'Y' as nopassword, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT('', 'key')=password
I also just noticed that version 2.0.15 in my output below is coming from somewhere? I did try setting things up under 2.0.15 initially last week, but wanted to be up to date so downloaded the latest yesterday. I never did get it all working under 2.0.15 either btw.
dovecot -n -c /opt/dovecot/etc/dovecot/dovecot.conf # 2.0.15: /opt/dovecot/etc/dovecot/dovecot.conf # OS: Linux 2.6.35.14-106.fc14.x86_64 x86_64 Fedora release 14 (Laughlin) ext4 auth_debug = yes auth_debug_passwords = yes auth_mechanisms = cram-md5 auth_verbose = yes auth_verbose_passwords = plain default_client_limit = 225 first_valid_uid = 1000 listen = * lock_method = flock mail_location = mbox:/var/mail/%d/%1n/%n:INDEX=/var/indexes/%d/%1n/%n mail_privileged_group = mail mbox_lock_timeout = 1 mins mbox_write_locks = fcntl namespace { inbox = yes location = prefix = separator = . type = private } passdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocols = imap service auth { inet_listener { port = 12345 } unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } user = $default_internal_user } service imap-login { inet_listener imap { port = 143 } service_count = 1 } ssl_cert =</etc/pki/dovecot/certs/dovecot.pem ssl_key =</etc/pki/dovecot/private/dovecot.pem userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } userdb { args = /opt/dovecot/etc/dovecot/conf.d/dovecot-sql.conf.ext driver = sql } protocol imap { imap_idle_notify_interval = 1 mins imap_max_line_length = 64 k mail_max_userip_connections = 5 }
/mf/home/jeep/shell/.signature
Yeap, you seem to only allow cram-md5. In this case, you client isn't transmitting the actual password that the user is typing, so dovecot simply doesn't have the password you want it to put in %w. It rather has a digest of it. The only way to use a non-plaintext auth mechanism is to provide dovecot the correct password from the database in plaintext.
Ok thank you both for your help. I see why I didnt get anything for %w now.
After reading the docs the past few days Im a little unsure of what setup I should use then for security purposes (primarily potential sniffing).
Obviously using DIGEST-MD5 (per the docs) is better than CRAM-MD5, but is that possible to use here if I am trying to match passwords out of my mysql DB? I certainly do not want to use plaintext (and quite honestly Im not sure what 'login' really means either, but thats another issue).
My desire is:
- Only virtual users
- All users info stored in mysql
- login is via squirrelmail ATM, and later imap/iphone or other.
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).
Thanks! Jeff
/mf/home/jeep/shell/.signature
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).
salted hash format such as SSHA, and use plaintext auth (over SSL, of course).
Thank you so much for your in depth reponse Gedalya, I appreciate it!
So now that I changed things up and my conf is: auth_mechanisms = plain login disable_plaintext_auth = no
I get the following, which I dont get, because now it seems to be telling me that my decrypted AES password needs to be MD5-CRYPT or PLAIN-MD5?
Debug: sql(jeff,127.0.0.1): query: SELECT AES_DECRYPT(password, 'mykey') AS password, userid AS user FROM users WHERE userid='jeff' AND AES_DECRYPT(password, 'mykey')='mypass' Error: sql(jeff,127.0.0.1): Invalid password 'mypass' in passdb: Not a valid MD5-CRYPT or PLAIN-MD5 password Debug: sql(jeff,127.0.0.1): MD5(mypass) != 'mypass', try PLAIN scheme instead Apr 28 19:18:56 mydomain dovecot: auth: Debug: client out: FAIL#0111#011user=jeff
This is even more confusing as my mechanisms say plain, not any type of MD5?
Thanks again! /mf/home/jeep/shell/.signature
On 4/28/2012 10:29 PM, Jeff Lacki wrote:
Error: sql(jeff,127.0.0.1): Invalid password 'mypass' in passdb: Not a valid MD5-CRYPT or PLAIN-MD5 password Debug: sql(jeff,127.0.0.1): MD5(mypass) != 'mypass', try PLAIN scheme instead In dovecot-sql.conf.ext, set: default_pass_scheme = PLAIN Dovecot needs to be told what format the stored password is in.
participants (3)
-
Daniel Parthey
-
Gedalya
-
jeep＠rahul.net