Unable to authenticate against mysql (Debian+postfix+sasl+mysql)

Francisco X. Vázquez fxvazquez.listas at gmail.com
Mon Apr 10 13:53:15 EDT 2006


Hello, list members:

I'm trying to configure a postfix mail server to authenticate against
mysql using Cyrus-SASL and I need some help.

I follow the instructions from "The postfix book" but replacing
courier-imap with cyrus-imap. I also read the information avaliable in
the postfix home site and many howto's found in the web about postfix
smtp authentication.

I use Debian sarge packages for both postfix and cyrus-sasl, exact version are:
Postfix 2.1.5
libsasl2 2.1.19

packages installed are:
- libsasl2, libsasl2-dev, libsasl2-modules, libsasl2-modules-sql, sasl2-bin
- postfix,  postfix-dev, postfix-doc, postfix-mysql, postfix-pcre, postfix-tls

I'm having several problem with sasl authentication, exactly when
trying to use mysql as backend.

When I try to authenticate in the smtp server I obtain the next
information in the logs:

----telnet session----------
# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
220 terminus.example.com ESMTP Postfix (Debian/GNU)
ehlo example.com
250-terminus.example.com
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-AUTH LOGIN PLAIN
250-AUTH=LOGIN PLAIN
250 8BITMIME
auth plain dXN1YXJpb0BleGFtcGxlLmNvbQB1c3VhcmlvQGV4YW1wbGUuY29tAHVzdWFyaW8=
535 Error: authentication failed

----/var/log/mail.log----------
Apr 10 19:58:11 localhost postfix/smtpd[26273]: match_list_match:
localhost.localdomain: no match
Apr 10 19:58:11 localhost postfix/smtpd[26273]: match_list_match:
127.0.0.1: no match
Apr 10 19:58:11 localhost postfix/smtpd[26273]: >
localhost.localdomain[127.0.0.1]: 250 8BITMIME
Apr 10 19:58:11 localhost postfix/smtpd[26273]: watchdog_pat: 0x807a8c8
Apr 10 20:01:41 localhost postfix/smtpd[26273]: <
localhost.localdomain[127.0.0.1]: auth plain
dXN1YXJpb0BleGFtcGxlLmNvbQB1c3VhcmlvQGV4YW1wbGUuY29tAHVzdWFyaW8=
Apr 10 20:01:41 localhost postfix/smtpd[26273]:
smtpd_sasl_authenticate: sasl_method plain, init_response
dXN1YXJpb0BleGFtcGxlLmNvbQB1c3VhcmlvQGV4YW1wbGUuY29tAHVzdWFyaW8=
Apr 10 20:01:41 localhost postfix/smtpd[26273]:
smtpd_sasl_authenticate: decoded initial response usuario at example.com
Apr 10 20:01:41 localhost postfix/smtpd[26273]: warning: SASL
authentication failure: Password verification failed
Apr 10 20:01:41 localhost postfix/smtpd[26273]: warning:
localhost.localdomain[127.0.0.1]: SASL plain authentication failed
Apr 10 20:01:41 localhost postfix/smtpd[26273]: >
localhost.localdomain[127.0.0.1]: 535 Error: authentication failed
Apr 10 20:01:41 localhost postfix/smtpd[26273]: watchdog_pat: 0x807a8c8
-------------------------------

More descriptive is /var/log/auth.log:

----/var/log/auth.log----------
Apr 10 19:58:05 localhost postfix/smtpd[26273]: sql auxprop plugin
using mysql engine
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin Parse the
username usuario at example.com
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin try and
connect to a host
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host 'localhost'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host localhost
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host '127.0.0.1'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host 127.0.0.1
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host '192.168.0.3'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host 192.168.0.3
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin couldn't
connect to any host
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin Parse the
username usuario at example.com
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin try and
connect to a host
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host 'localhost'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host localhost
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host '127.0.0.1'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host 127.0.0.1
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host '192.168.0.3'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host 192.168.0.3
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin couldn't
connect to any host
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin Parse the
username usuario at example.com
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin try and
connect to a host
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host 'localhost'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host localhost
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host '127.0.0.1'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host 127.0.0.1
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin trying to
open db 'mail' on host '192.168.0.3'
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin could not
connect to host 192.168.0.3
Apr 10 20:01:41 localhost postfix/smtpd[26273]: sql plugin couldn't
connect to any host
-------------------------------

I used saslfinger -s to obtain sasl related information from postfix
configuration:

----saslfinger -s -------------
saslfinger - postfix Cyrus sasl configuration lun abr 10 15:15:33 CEST 2006
version: 1.0
mode: server-side SMTP AUTH

-- basics --
Postfix: 2.1.5
System: Debian GNU/Linux 3.1 \n \l

-- smtpd is linked to --
	libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0x401a1000)

-- active SMTP AUTH and TLS parameters for smtpd --
broken_sasl_auth_clients = yes
smtpd_sasl_application_name = smtpd
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain =
smtpd_sasl_security_options = noanonymous


-- listing of /usr/lib/sasl2 --
total 956
drwxr-xr-x   2 root root  4096 2006-04-10 13:06 .
drwxr-xr-x  48 root root 16384 2006-04-10 13:22 ..
-rw-r--r--   1 root root 13492 2006-04-10 12:17 libanonymous.a
-rw-r--r--   1 root root   851 2006-04-10 12:16 libanonymous.la
-rw-r--r--   1 root root 13824 2006-04-10 12:17 libanonymous.so
-rw-r--r--   1 root root 13824 2006-04-10 12:17 libanonymous.so.2
-rw-r--r--   1 root root 13824 2006-04-10 12:17 libanonymous.so.2.0.19
-rw-r--r--   1 root root 16298 2006-04-10 12:17 libcrammd5.a
-rw-r--r--   1 root root   837 2006-04-10 12:16 libcrammd5.la
-rw-r--r--   1 root root 16180 2006-04-10 12:17 libcrammd5.so
-rw-r--r--   1 root root 16180 2006-04-10 12:17 libcrammd5.so.2
-rw-r--r--   1 root root 16180 2006-04-10 12:17 libcrammd5.so.2.0.19
-rw-r--r--   1 root root 47520 2006-04-10 12:17 libdigestmd5.a
-rw-r--r--   1 root root   860 2006-04-10 12:16 libdigestmd5.la
-rw-r--r--   1 root root 43944 2006-04-10 12:17 libdigestmd5.so
-rw-r--r--   1 root root 43944 2006-04-10 12:17 libdigestmd5.so.2
-rw-r--r--   1 root root 43944 2006-04-10 12:17 libdigestmd5.so.2.0.19
-rw-r--r--   1 root root 13726 2006-04-10 12:17 liblogin.a
-rw-r--r--   1 root root   831 2006-04-10 12:16 liblogin.la
-rw-r--r--   1 root root 14028 2006-04-10 12:17 liblogin.so
-rw-r--r--   1 root root 14028 2006-04-10 12:17 liblogin.so.2
-rw-r--r--   1 root root 14028 2006-04-10 12:17 liblogin.so.2.0.19
-rw-r--r--   1 root root 31248 2006-04-10 12:17 libntlm.a
-rw-r--r--   1 root root   825 2006-04-10 12:16 libntlm.la
-rw-r--r--   1 root root 30692 2006-04-10 12:17 libntlm.so
-rw-r--r--   1 root root 30692 2006-04-10 12:17 libntlm.so.2
-rw-r--r--   1 root root 30692 2006-04-10 12:17 libntlm.so.2.0.19
-rw-r--r--   1 root root 20142 2006-04-10 12:17 libotp.a
-rw-r--r--   1 root root   825 2006-04-10 12:16 libotp.la
-rw-r--r--   1 root root 43184 2006-04-10 12:17 libotp.so
-rw-r--r--   1 root root 43184 2006-04-10 12:17 libotp.so.2
-rw-r--r--   1 root root 43184 2006-04-10 12:17 libotp.so.2.0.19
-rw-r--r--   1 root root 13886 2006-04-10 12:17 libplain.a
-rw-r--r--   1 root root   831 2006-04-10 12:16 libplain.la
-rw-r--r--   1 root root 14096 2006-04-10 12:17 libplain.so
-rw-r--r--   1 root root 14096 2006-04-10 12:17 libplain.so.2
-rw-r--r--   1 root root 14096 2006-04-10 12:17 libplain.so.2.0.19
-rw-r--r--   1 root root 21810 2006-04-10 12:17 libsasldb.a
-rw-r--r--   1 root root   852 2006-04-10 12:16 libsasldb.la
-rw-r--r--   1 root root 18692 2006-04-10 12:17 libsasldb.so
-rw-r--r--   1 root root 18692 2006-04-10 12:17 libsasldb.so.2
-rw-r--r--   1 root root 18692 2006-04-10 12:17 libsasldb.so.2.0.19
-rw-r--r--   1 root root 22168 2006-04-10 12:17 libsql.a
-rw-r--r--   1 root root   874 2006-04-10 12:16 libsql.la
-rw-r--r--   1 root root 22132 2006-04-10 12:17 libsql.so
-rw-r--r--   1 root root 22132 2006-04-10 12:17 libsql.so.2
-rw-r--r--   1 root root 22132 2006-04-10 12:17 libsql.so.2.0.19
-rw-r--r--   1 root root   393 2006-04-10 14:33 sample.conf


-- content of /etc/postfix/sasl/smtpd.conf --
# Parametros globais
log_level: 10
pwcheck_method: auxprop
mech_list: PLAIN LOGIN

# <- CRAM-MD5 DIGEST-MD5

# Parametros de auxprop
auxprop_plugin: sql
sql_engine: mysql
sql_hostnames: localhost, 127.0.0.1, 192.168.0.3
sql_database: mail
sql_user: --- replaced ---
sql_password: postfix
sql_select: SELECT userpassword FROM virtual_users where email = '%u'
AND auth = '1';
sql_verbose: yes


-- active services in /etc/postfix/master.cf --
# service type  private unpriv  chroot  wakeup  maxproc command + args
#               (yes)   (yes)   (yes)   (never) (100)
smtp      inet  n       -       -       -       -       smtpd -v
pickup    fifo  n       -       -       60      1       pickup
cleanup   unix  n       -       -       -       0       cleanup
qmgr      fifo  n       -       -       300     1       qmgr
rewrite   unix  -       -       -       -       -       trivial-rewrite
bounce    unix  -       -       -       -       0       bounce
defer     unix  -       -       -       -       0       bounce
trace     unix  -       -       -       -       0       bounce
verify    unix  -       -       -       -       1       verify
flush     unix  n       -       -       1000?   0       flush
proxymap  unix  -       -       n       -       -       proxymap
smtp      unix  -       -       -       -       -       smtp
relay     unix  -       -       -       -       -       smtp
showq     unix  n       -       -       -       -       showq
error     unix  -       -       -       -       -       error
local     unix  -       n       n       -       -       local
virtual   unix  -       n       n       -       -       virtual
lmtp      unix  -       -       n       -       -       lmtp
anvil     unix  -       -       n       -       1       anvil
maildrop  unix  -       n       n       -       -       pipe
  flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}
uucp      unix  -       n       n       -       -       pipe
  flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)
ifmail    unix  -       n       n       -       -       pipe
  flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient)
bsmtp     unix  -       n       n       -       -       pipe
  flags=Fq. user=bsmtp argv=/usr/lib/bsmtp/bsmtp -d -t$nexthop
-f$sender $recipient
scalemail-backend unix	-	n	n	-	2	pipe
  flags=R user=scalemail argv=/usr/lib/scalemail/bin/scalemail-store
${nexthop} ${user} ${extension}
cyrus     unix  -       n       n       -       -       pipe
  user=cyrus argv=/usr/sbin/cyrdeliver -e -r ${sender} -m ${extension}
${user}.${nexthop}

-- mechanisms on localhost --
250-AUTH LOGIN PLAIN
250-AUTH=LOGIN PLAIN

-- end of saslfinger output --
-------------------------------

I compiled  de sample server and client from the sasl source
distribuition and symlinked /etc/postfix/sasl/smtpd.conf to
/var/lib/sasl2/sample.conf.
I tried the authentication with the following commands:

-----sample-server ---------
# /usr/local/sasl-sample/sample-server -s rcmd -p 8000
trying 10, 1, 6
trying 2, 1, 6
bind: Address already in use
accepted new connection
send: {11}
LOGIN PLAIN
recv: {5}
PLAIN
recv: {1}
Y
recv: {53}
usuario at example.com[0]usuario at example.com[0]usuario
starting SASL negotiation: user not foundclosing connection
----------------------------
-----sample-client ---------
# /usr/local/sasl-sample/sample-client -p 8000 -m PLAIN 127.0.0.1
receiving capability list... recv: {11}
LOGIN PLAIN
LOGIN PLAIN
please enter an authentication id: usuario at example.com
please enter an authorization id: usuario at example.com
Password:
send: {5}
PLAIN
send: {1}
Y
send: {53}
usuario at example.com[0]usuario at example.com[0]usuario
authentication failed
closing connection
----------------------------

Auth log for this test looks like the one from the postfix test before:

----/var/log/auth.log----------
Apr 10 20:18:58 localhost lt-server: sql auxprop plugin using mysql engine
Apr 10 20:20:19 localhost lt-server: sql plugin Parse the username
usuario at example.com
Apr 10 20:20:19 localhost lt-server: sql plugin try and connect to a host
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host 'localhost'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host localhost
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host '127.0.0.1'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host 127.0.0.1
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host '192.168.0.3'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host 192.168.0.3
Apr 10 20:20:19 localhost lt-server: sql plugin couldn't connect to any host
Apr 10 20:20:19 localhost lt-server: sql plugin Parse the username
usuario at example.com
Apr 10 20:20:19 localhost lt-server: sql plugin try and connect to a host
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host 'localhost'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host localhost
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host '127.0.0.1'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host 127.0.0.1
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host '192.168.0.3'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host 192.168.0.3
Apr 10 20:20:19 localhost lt-server: sql plugin couldn't connect to any host
Apr 10 20:20:19 localhost lt-server: sql plugin Parse the username
usuario at example.com
Apr 10 20:20:19 localhost lt-server: sql plugin try and connect to a host
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host 'localhost'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host localhost
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host '127.0.0.1'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host 127.0.0.1
Apr 10 20:20:19 localhost lt-server: sql plugin trying to open db
'mail' on host '192.168.0.3'
Apr 10 20:20:19 localhost lt-server: sql plugin could not connect to
host 192.168.0.3
Apr 10 20:20:19 localhost lt-server: sql plugin couldn't connect to any host
Apr 10 20:20:19 localhost lt-server: Password verification failed
-------------------------------

I granted select permissions to the database user configured, while
testing I tried too with an user that has granted all privileges, no
one worked.
Database structure is: (I will modify it soon, is the book example and
I don't use many things).
-------------------------------
mysql> describe virtual_users;
+-----------------+------------------+------+-----+------------------+----------------+
| Field           | Type             | Null | Key | Default          |
Extra          |
+-----------------+------------------+------+-----+------------------+----------------+
| id              | int(11) unsigned |      | PRI | NULL             |
auto_increment |
| username        | varchar(255)     |      |     | 0                |
               |
| userrealm       | varchar(255)     |      |     | state-of-mind.de |
               |
| userpassword    | varchar(255)     |      |     | AendrM1ch!       |
               |
| auth            | tinyint(1)       | YES  |     | 1                |
               |
| active          | tinyint(1)       | YES  |     | 1                |
               |
| email           | varchar(255)     |      | MUL |                  |
               |
| virtual_uid     | smallint(5)      | YES  |     | 1000             |
               |
| virtual_gid     | smallint(5)      | YES  |     | 1000             |
               |
| virtual_mailbox | varchar(255)     | YES  |     | NULL             |
               |
+-----------------+------------------+------+-----+------------------+----------------+
10 rows in set (0.00 sec)
-------------------------------
mysql> select * from virtual_users\G
[...]
*************************** 2. row ***************************
             id: 2
       username: usuario
      userrealm: example.com
   userpassword: usuario
           auth: 1
         active: 1
          email: usuario at example.com
    virtual_uid: 1000
    virtual_gid: 1000
virtual_mailbox: NULL
*************************** 3. row ***************************
[...]
4 rows in set (0.00 sec)
-------------------------------

The paradox of this all is that Cyrus-imap with the equivalent
configuration to authenticate agaist mysql works without problem, it's
linked with the same sasl lib than postfix.
And Postfix authenticates perfectly if using saslauthd with the
following configuration:

----/etc/postfix/sasl/smtpd.conf_saslauthd ------
mech_list: plain login
pwcheck_method: saslauthd
-------------------------------------------------

In Debian postfix runs chrooted, so I mounted the directory where the
mysql socket listen this way:

-------------------------------------------------
# mount --bind /var/run/mysqld /var/spool/postfix/var/run/mysqld

# mount
[...]
/var/run/mysqld on /var/spool/postfix/var/run/mysqld type none (rw,bind)
-------------------------------------------------

this worked perfectly to allow postfix to obtain configuration of
virtual mailboxes, etc from mysql database, so I imagine that it
should work too for authentication.
I tried to remove postfix from the chroot modifying master.cf, but
with identical problems, I wasn't able to authenticate agaist the
database.

Does anybody know what I'm configuring bad?, I don't know what more
can I try. I read lots of messages from the archive of this mailig
list and no one of the proposed solutions for similar problems worked
for me.

Thanks in advance.


More information about the Cyrus-sasl mailing list