dovecot - Jul 2015 - HELP: wrinting dovecot-sql.conf to authenticate to an existing database

hi all, 

can anyone help me with dovecot-sql.conf please ? I'm not a mysql expert and
I'am trying to authenticate dovecot2 to an existing database.


I have a database "egroupware" with table "egw_accounts".
The field "account_lid" is the full email address and
"account_pwd" is the password in Blowfish format.

Here is my dovecot-sql.conf

driver = mysql
connect = host=localhost dbname=egroupware user=egroupware password=*********
default_pass_scheme = BLF-CRYPT
password_query = SELECT account_pwd AS password FROM egw_accounts WHERE
account_lid = '%u'



when I try to login to POP or IMAP I got this error in mail.log/mail.info

Error: sql(hidden at mydomain.com,::1): Invalid password in passdb: crypt()
failed: Invalid argument
auth: Debug: client passdb out: FAIL#0111#011user=hidden at mydomain.com
dovecot: pop3-login: Debug: Ignoring unknown passdb extra field:

I think I'm having trouble with the password value that is stored in the
account_pwd as:

{crypt}$2a$12$XTAolzXgwMC211MuZELQUedPv9tjZNs7Osh97PnZ50pAKuk3WPGmS

Reading at http://wiki2.dovecot.org/Authentication/PasswordSchemes

I suppose that the heading "{crypt}$2a$" instead of a simple
"$2a$" (witch identifies a Blowfish password) is my problem.

How can I fix this ? 

Here is my dovecot -n output:


# 2.2.13: /etc/dovecot/dovecot.conf
# OS: Linux 3.16.0-4-amd64 x86_64 Debian 8.1 xfs
auth_cache_size = 64 M
auth_debug = yes
auth_mechanisms = plain login
lda_mailbox_autocreate = yes
log_timestamp = "%Y-%m-%d %H:%M:%S "
login_log_format_elements = USER=<%u> METHOD=%m FROM=%r TO=%l %c
mail_location = maildir:/var/mail/%d/%n/Maildir
mail_plugins = quota
mail_privileged_group = vmail
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character
vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy
include variables body enotify environment mailbox date ihave spamtest
spamtestplus imapflags notify
namespace inbox {
? inbox = yes
? location = 
? mailbox Archive {
??? auto = no
??? special_use = \Archive
? }
? mailbox "Deleted Messages" {
??? auto = no
??? special_use = \Trash
? }
? mailbox Drafts {
??? special_use = \Drafts
? }
? mailbox Sent {
??? special_use = \Sent
? }
? mailbox "Sent Messages" {
??? special_use = \Sent
? }
? mailbox Spam {
??? auto = no
??? special_use = \Junk
? }
? mailbox Trash {
??? auto = no
??? special_use = \Trash
? }
? prefix = 
? separator = .
}
passdb {
? args = /etc/dovecot/dovecot-sql.conf.ext
? driver = sql
}
plugin {
? quota = maildir:User quota
? quota_exceeded_message = Storage quota for this account has been exceeded,
please try again later.
? quota_grace = 10%%
? quota_rule = *:storage=6G
? quota_rule2 = Trash:storage=+100M
? quota_rule3 = spam:ignore
? quota_warning = storage=95%% quota-warning 95 %u
? sieve = ~/dovecot.sieve
? sieve_before = /var/mail/sieve/before.sieve
? sieve_default = /var/mail/sieve/default.sieve
? sieve_dir = ~/sieve.d
? sieve_extensions = +notify +imapflags +spamtest +spamtestplus +relational
+comparator-i;ascii-numeric
? sieve_global_dir = /var/mail/sieve/global.d
? sieve_max_script_size = 1M
}
postmaster_address = postmaster@%d
protocols = " imap sieve pop3 sieve"
service auth {
? unix_listener /var/spool/postfix/private/auth {
??? group = postfix
??? mode = 0666
??? user = postfix
? }
}
service imap-login {
? inet_listener imap {
??? port = 143
? }
? inet_listener imaps {
??? port = 993
??? ssl = yes
? }
}
service managesieve-login {
? inet_listener sieve {
??? port = 4190
? }
? service_count = 1
? vsz_limit = 64 M
}
service managesieve {
? process_limit = 15
}
service quota-warning {
? executable = script /usr/local/bin/quota-warning.sh
? unix_listener quota-warning {
??? user = vmail
? }
? user = dovecot
}
ssl = required
ssl_cert = </etc/ssl/dharma/mail/mail_antonica_it.crt
ssl_key = </etc/ssl/dharma/mail/mail_antonica_it.key
userdb {
? args = uid=vmail gid=vmail home=/var/mail/%d/%n
? driver = static
}
protocol lda {
? mail_plugins = quota sieve
}
protocol imap {
? mail_plugins = quota imap_quota
}
protocol sieve {
? mail_max_userip_connections = 10
? managesieve_logout_format = bytes=%i/%o
? managesieve_max_line_length = 65536
}

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 7 Jul 2015, Filippo Zeus wrote:
> I have a database "egroupware" with table
"egw_accounts". The field "account_lid" is the full email
address and "account_pwd" is the password in Blowfish format.
>
> Here is my dovecot-sql.conf
>
> driver = mysql
> connect = host=localhost dbname=egroupware user=egroupware
password=*********
> default_pass_scheme = BLF-CRYPT
> password_query = SELECT account_pwd AS password FROM egw_accounts WHERE
account_lid = '%u'
>
> Error: sql(hidden at mydomain.com,::1): Invalid password in passdb: crypt()
failed: Invalid argument
> auth: Debug: client passdb out: FAIL#0111#011user=hidden at mydomain.com
> dovecot: pop3-login: Debug: Ignoring unknown passdb extra field:
>
> I think I'm having trouble with the password value that is stored in
the account_pwd as:
>
> {crypt}$2a$12$XTAolzXgwMC211MuZELQUedPv9tjZNs7Osh97PnZ50pAKuk3WPGmS
>
> Reading at http://wiki2.dovecot.org/Authentication/PasswordSchemes
>
> I suppose that the heading "{crypt}$2a$" instead of a simple
"$2a$" (witch identifies a Blowfish password) is my problem.
Hmm, did you tried simple string replacement?

password_query = SELECT replace(account_pwd, '{crypt}',
'{BLF-CRYPT}') AS
password FROM egw_accounts WHERE account_lid = '%u'

Also, did you've read:

http://wiki2.dovecot.org/Authentication/PasswordSchemes

"BLF-CRYPT: This is the Blowfish crypt (bcrypt) scheme. It is generally 
considered to be very secure. The encrypted password will start with $2a$ 
(Note: bcrypt is not available on most Linux distributions)"

Did you've tried:

doveadm pw -s BLF-CRYPT

to verify if your system supports Blowfish at all?

- -- 
Steffen Kaiser
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQEVAwUBVZy4jHz1H7kL/d9rAQIuGAgApTmCyW2uU16+cDRCHjjqhcUQrPwCSELm
Bh9MFs9K5Lsx1JmMYZbwg9kVhnKTiJ1MjUB1oCYTQIN4cJvQv28jpfSseQWGWx7z
4H04Xn5hD6F2U7J69g3IQoaA5XiiJITKrihAfTkFy9QrGQcAc/Mjl2aGMeA6Aghq
0gHiFCmKtFyrp0n8GwDvzGuXdAeY1OeSlM/OSqNkemtaCBJBZ940lOwv7Ldw4xGC
rP3fDPnqbh1h5rfDS11XoSvlfXMimTU73s9wkw0Pr0cnSkdE5/95PDJ9SozD/0hu
AC2+0LxnV0Jzl2uB9sJ1OuH0/HOezz5U0ba0KuU/qYdg/wFCqD1liQ==52bK
-----END PGP SIGNATURE-----