# Postfix SASL MySQL not Authenticating

## bpatteson

In setting up Postfix Postfix 2.1 with Cyrus SASL 2.1.19 authenticating against a MySQL 4.0 Database, I am unable to get SASL authentication working properly, for authenticating mail accounts which are stored in the MySQL database "mailsql" and whose mail is stored at:"/home/$username/.maildir." 

I am using the guide on the Gentoo website:

http://www.gentoo.org/doc/en/virt-mail-howto.xml

Courier-IMAP is working properly with MySQL.

Unfortunately I cannot get SASL working to send mail through postfix from a remote location that is not inside $mynetworks.

My /etc/postfix/main.cf is:

```

myhostname = mail.collegefirstlook.com

mydomain = collegefirstlook.com

inet_interfaces = all

mydestination = $myhostname, localhost.$mydomain $mydomain

mynetworks = 192.168.1.0/24, 127.0.0.0/8, 67.0.0.0/8

home_mailbox = .maildir/

local_destination_concurrency_limit = 2

default_destination_concurrency_limit = 10

#SASL Authentication

smtpd_sasl_auth_enable = yes

smtpd_sasl2_auth_enable = yes

smtpd_sasl_security_options = noanonymous

broken_sasl_auth_clients = yes

smtpd_sasl_local_domain =

#Authorization Allowed

smtpd_recipient_restrictions =

        permit_sasl_authenticated,

        permit_mynetworks,

        reject_unauth_destination

#SSL/TLS Activation Using Generic SSL Key

smtpd_use_tls = yes

smtpd_tls_key_file = /etc/ssl/postfix/server.key

smtpd_tls_cert_file = /etc/ssl/postfix/server.crt

smtpd_tls_CAfile = /etc/ssl/postfix/server.pem

smtpd_tls_loglevel = 3

smtpd_tls_received_header = yes

smtpd_tls_session_cache_timeout = 3600s

tls_random_source = dev:/dev/urandom

alias_maps = mysql:/etc/postfix/mysql-aliases.cf

relocated_maps = mysql:/etc/postfix/mysql-relocated.cf

local_transport = local

local_recipient_maps = $alias_maps $virtual_mailbox_maps unix:passwd.byname

virtual_transport = virtual

virtual_mailbox_domains =

        collegefirstlook.com

virtual_minimum_uid = 1000

virtual_gid_maps = static:$vmail-gid

virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-maps.cf

virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf

virtual_uid_maps = static:$vmail-uid

virtual_mailbox_base = /

#virtual_mailbox_limit =

```

My /etc/sasl2/smtp.conf

```

#MYSQL Setup

pwcheck_method: auxprop

auxprop_plugin: sql

sql_engine: mysql

sql_hostnames: localhost

sql_user: mailsql

sql_passwd: <my password is here>

sql_database: mailsql

sql_select: select clear from users where email = '%u@%r'

mech_list: plain login

pwcheck_method: saslauthd

mech_list: LOGIN PLAIN

```

My /etc/conf.d/saslauthd

```

SASLAUTHD_OPTS="-a pam"

```

My /etc/pam.d/smtp

```

auth     optional       pam_mysql.so host=localhost db=mailsql user=mailsql passwd=<my password> table=users usercolumn=email passwdcolumn=clear crypt=0

account  required       pam_mysql.so host=localhost db=mailsql user=mailsql passwd=<my password> table=users usercolumn=email passwdcolumn=clear crypt=0

```

My /etc/pam.d/imap

```

auth     optional       pam_mysql.so host=localhost db=mailsql user=mailsql passwd=<my password> table=users usercolumn=email passwdcolumn=clear crypt=0

account  required       pam_mysql.so host=localhost db=mailsql user=mailsql passwd=<my password> table=users usercolumn=email passwdcolumn=clear crypt=0

```

My /etc/postfix/mysql-aliases.cf

```

user            = mailsql

password        = <my password>

dbname          = mailsql

table           = alias

select_field    = destination

where_field     = alias

hosts           = unix:/var/run/mysqld/mysqld.sock

```

My /etc/postfix/mysql-relocated.cf

```

user            = mailsql

password        = <my password>

dbname          = mailsql

table           = relocated

select_field    = destination

where_field     = email

hosts           = unix:/var/run/mysqld/mysqld.sock

```

My /etc/postfix/mysql-virtual-maps.cf

```

#myql-virtual-maps.cf

user            = mailsql

password        = <my password>

dbname          = mailsql

table           = users

select_field    = maildir

where_field     = email

additional_conditions = and postfix = 'y'

hosts           = unix:/var/run/mysqld/mysqld.sock

```

My /etc/postfix/virtual.cf

```

# mysql-virtual.cf

user            = mailsql

password        = <my password>

dbname          = mailsql

table           = virtual

select_field    = destination

where_field     = email

hosts           = unix:/var/run/mysqld/mysqld.sock

```

MySQL is setup properly with the right users and information in a database called mailsql with the tables: alias, relocated, transport, users, virtual.  Courier IMAP is reading user name and passwords and authenticating properly with the instruction in the guide.

Postfix on the other hand will not authenticate with or without TLS/SSL.

Telneting in on Port 26 (Set to Port 26 cause my ISP has blocked 25 - both server and client can send email on this port when not using SASL)

I get this when I try to auth:

```

220 mail.collegefirstlook.com ESMTP Postfix

EHLO mail.collegefirstlook.com

250-mail.collegefirstlook.com

250-PIPELINING

250-SIZE 10240000

250-VRFY

250-ETRN

250-STARTTLS

250-AUTH LOGIN PLAIN

250-AUTH=LOGIN PLAIN

250 8BITMIME

```

So I know Postfix is offer authentication.  I just have no idea how to trouble shoot from here.  I do not know how to see where Cyrus SASL is failing in connecting with MySQL and how Pam.d plays any roll in the authentication.  I think what happens is Postfix goes to Cyrus SASL which goes to Pam.d which goes to MySQL, but I don't really understand the whole process.

My /var/log/mail/current reads this as the problem:

```

Nov  5 16:02:39 [postfix/smtpd] timeout after EHLO from unknown[68.106.111.177]

Nov  5 16:02:39 [postfix/smtpd] disconnect from unknown[68.106.111.177]

Nov  5 16:03:29 [postfix/smtpd] connect from unknown[68.106.111.177]

Nov  5 16:03:40 [postfix/smtpd] warning: SASL authentication failure: Couldn't find mech asdfasdfjsdksdflss

Nov  5 16:03:40 [postfix/smtpd] warning: unknown[68.106.111.177]: SASL asdfasdfjsdksdflss authentication failed

```

So it looks to me like SASL is not able to find the password authentication mechanism whether it be MySQl or PAM.d (I don't know).  Any help would be appreciated.  I have been working along time on this.

I want SASL working, so my remote users can send email without me having to track what network they are on and update mynetworks.  I also like being able to update user accounts via the web with phpmyadmin.  I am not using a Virutal Domain.  I just followed the instructions exactly and there is stuff in there for a virutal domain, but I do not know what is irrelevent.

Also I am trying to use TLS/SSL and I am using the generic key setup at /etc/ssl/postfix/server.key & server.crt, however I am not sure how I update my Microsoft Entourage email client certificate to trust this cert.  I assume it is possible to use SASL without TLS/SSL so I have been trying to authenticate without using TLS/SSL.  Please let me know if this is not possible.

Thanks,

Brent Patteson

----------

## bpatteson

I thought that I would add that the postfix package has been emerged with PAM, MySQL, SSL and SASL support.

```

gateway root # etcat -u postfix

[ Colour Code : set unset ]

[ Legend   : (U) Col 1 - Current USE flags        ]

[          : (I) Col 2 - Installed With USE flags ]

 U I [ Found these USE variables in : mail-mta/postfix-2.1.5-r1 ]

 - - ipv6        : Adds support for IP version 6

 + + pam         : unknown

 - - ldap        : Adds LDAP support (Lightweight Directory Access Protocol)

 + + mysql       : Adds mySQL support

 - - postgres    : Adds support for the postgresql database

 + + ssl         : Adds support for Secure Socket Layer connections

 + + sasl        : Adds support for the Simple Authentication and Security Layer

 - - vda         : Adds support for virtual delivery agent quota enforcing

 - - mailwrapper : Adds mailwrapper support to allow multiple MTAs to be installed

 - - mbox        : Adds support for mbox (/var/spool/mail) style mail spools

```

----------

## langthang

try this:

```
MYSQL Setup

pwcheck_method: auxprop

auxprop_plugin: sql

sql_engine: mysql

sql_hostnames: localhost

sql_user: mailsql

sql_passwd: <my password is here>

sql_database: mailsql

sql_select: select clear from users where email = '%u@%r'

#mech_list: plain login

#pwcheck_method: saslauthd

mech_list: LOGIN PLAIN 
```

----------

## hpeters

/etc/sasl2/smtp.conf

#MYSQL Setup

pwcheck_method: auxprop

auxprop_plugin: sql

sql_engine: mysql

sql_hostnames: localhost

sql_user: mailsql

sql_passwd: <my password is here>

sql_database: mailsql

sql_select: select clear from users where email = '%u@%r'

mech_list: plain login

pwcheck_method: saslauthd    <- remove this

mech_list: LOGIN PLAIN             <- remove this

The above is not needed if using mysql. Saslauthd is just another database for storing users.

Put a copy of smtp.conf or symlink it to /usr/lib/sasl2/smtp.conf

Make sure postfix is not running chrooted. (check master.cf)

Harley

----------

## bpatteson

I fixed smtp.conf

/etc/sasl2/smtp.conf updated:

```

pwcheck_method: auxprop

auxprop_plugin: sql

sql_engine: mysql

sql_hostnames: localhost

sql_user: mailsql

sql_passwd: <password>

sql_database: mailsql

sql_select: select clear from users where email = '%u@%r'

mech_list: plain login

```

I also copied /etc/sasl2/smtpd.conf to /usr/lib/sasl2/smtpd.conf

I am still getting:

```

Nov  5 18:57:42 [postfix/smtpd] warning: unknown[68.106.111.177]: SASL LOGIN authentication failed

Nov  5 18:57:43 [postfix/smtpd] disconnect from unknown[68.106.111.177]

Nov  5 18:57:49 [postfix/smtpd] connect from unknown[68.106.111.177]

Nov  5 18:57:50 [postfix/smtpd] warning: unknown[68.106.111.177]: SASL LOGIN authentication failed

Nov  5 18:57:50 [postfix/smtpd] disconnect from unknown[68.106.111.177]

```

What do I look for in master.cf to determine if I am running in a chroot jail or not.  Here is /etc/postfix/master.cf

```

# ==========================================================================

# service type  private unpriv  chroot  wakeup  maxproc command + args

#               (yes)   (yes)   (yes)   (never) (100)

# ==========================================================================

smtp      inet  n       -       n       -       -       smtpd -v  

26        inet  n       -       -       -       -       smtpd

#submission inet n      -       n       -       -       smtpd

#       -o smtpd_etrn_restrictions=reject

#smtps    inet  n       -       n       -       -       smtpd

#  -o smtpd_tls_wrappermode=yes -o smtpd_sasl_auth_enable=yes

#submission   inet    n       -       n       -       -       smtpd

#  -o smtpd_etrn_restrictions=reject

#  -o smtpd_enforce_tls=yes -o smtpd_sasl_auth_enable=yes

#628      inet  n       -       n       -       -       qmqpd

pickup    fifo  n       -       n       60      1       pickup

cleanup   unix  n       -       n       -       0       cleanup

qmgr      fifo  n       -       n       300     1       qmgr

#qmgr     fifo  n       -       n       300     1       oqmgr

#tlsmgr   fifo  -       -       n       300     1       tlsmgr

rewrite   unix  -       -       n       -       -       trivial-rewrite

bounce    unix  -       -       n       -       0       bounce

defer     unix  -       -       n       -       0       bounce

trace     unix  -       -       n       -       0       bounce     

verify    unix  -       -       n       -       1       verify

flush     unix  n       -       n       1000?   0       flush

proxymap  unix  -       -       n       -       -       proxymap   

smtp      unix  -       -       n       -       -       smtp

relay     unix  -       -       n       -       -       smtp

#       -o smtp_helo_timeout=5 -o smtp_connect_timeout=5

showq     unix  n       -       n       -       -       showq

error     unix  -       -       n       -       -       error

local     unix  -       n       n       -       -       local

virtual   unix  -       n       n       -       -       virtual

lmtp      unix  -       -       n       -       -       lmtp

anvil     unix  -       -       n       -       1       anvil

lmtp      unix  -       -       n       -       -       lmtp

anvil     unix  -       -       n       -       1       anvil

# 

# Interfaces to non-Postfix software. Be sure to examine the manual

# pages of the non-Postfix software to find out what options it wants.

#               

# maildrop. See the Postfix MAILDROP_README file for details.

#

maildrop  unix  -       n       n       -       -       pipe 

  flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}

#       

# The Cyrus deliver program has changed incompatibly, multiple times.

#  

old-cyrus unix  -       n       n       -       -       pipe

  flags=R user=cyrus argv=/cyrus/bin/deliver -e -m ${extension} ${user}

# Cyrus 2.1.5 (Amos Gouaux)

# Also specify in main.cf: cyrus_destination_recipient_limit=1

cyrus     unix  -       n       n       -       -       pipe  

  user=cyrus argv=/cyrus/bin/deliver -e -r ${sender} -m ${extension} ${user}

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=foo argv=/usr/local/sbin/bsmtp -f $sender $nexthop $recipient

```

----------

## langthang

you may have emerge cyrus-sasl without mysql USE flag? what is the out put of `emerge cyrus-sasl -vp`?

----------

## bpatteson

Results of etcat -u cyrus-sasl:

```

gateway root # etcat -u cyrus-sasl

[ Colour Code : set unset ]

[ Legend   : (U) Col 1 - Current USE flags        ]

[          : (I) Col 2 - Installed With USE flags ]

 U I [ Found these USE variables in : dev-libs/cyrus-sasl-2.1.19-r1 ]

 + + berkdb      : Adds support for sys-libs/db (Berkeley DB for MySQL)

 + + gdbm        : Adds support for sys-libs/gdbm (GNU database libraries)

 - + ldap        : Adds LDAP support (Lightweight Directory Access Protocol)

 + + mysql       : Adds mySQL support

 - - postgres    : Adds support for the postgresql database

 - - kerberos    : Adds kerberos support

 - - static      : !!do not set this during bootstrap!! Causes things to be statically linked instead of dynamically

 + + ssl         : Adds support for Secure Socket Layer connections

 - - java        : Adds support for Java

 + + pam         : unknown

 - - authdaemond : Enable Courier-IMAP authdaemond's unix socket support.

 - - debug       : Tells configure and the makefiles to build for debugging. Effects vary across packages, but generally it will at least add -g to CFLAGS. Remember to set FEATURES=nostrip too

```

----------

## hpeters

Ok your going to need to have both of these back in smtpd.conf in order to authenticate against pam-mysql.

pwcheck_method: saslauthd 

mech_list: plain login

I would try removing 

pwcheck_method: auxprop

instead it's worth a try.

Also I would remove

smtpd_sasl2_auth_enable = yes

from main.cf

As far as I can tell this is not an option. You can run postconf to see all the options your version of postfix supports.

Also the quide that you referenced actually says to compile sasl without mysql support since your connecting to mysql through pam.

Harley

----------

## hpeters

Also try running 

saslpasswd2 -c some_fake_user

to create an sasl2db database I have read that all though you won't be using sasl2db for authentication it still needs to exists.

Harley

----------

## bpatteson

I put SASLDB back into AUTH METHOD: in /etc/sasl2/smtp.conf as well as reemerged Cyrus SASL, using the How to Guide, which looks like it was updated, by removing LDAP and telling it to use mySQL support:

```

 mkdir /etc/portage

 echo "dev-libs/cyrus-sasl -ldap mysql" >> /etc/portage/package.use

 emerge cyrus-sasl

```

Still not able to authenicate with SASL with PAM against MySQL.

Brent

----------

## bpatteson

Okay, I have definately narrowed it down to postfix being the problem I think.  When I run the test utility testsaslauthd... it verifies my username and password from the MySQL Database, through PAM.

```
testsaslauthd -u username -p password
```

However, Postfix is still seeing it is not authenticating, even though SASL authenticates the same username and password without any problem:

```

Nov  8 14:49:41 [postfix/smtpd] warning: SASL authentication failure: Password verification failed

Nov  8 14:49:41 [postfix/smtpd] warning: unknown[68.106.111.177]: SASL PLAIN authentication failed

Nov  8 14:49:41 [postfix/smtpd] lost connection after AUTH from unknown[68.106.111.177]

Nov  8 14:49:41 [postfix/smtpd] disconnect from unknown[68.106.111.177]

```

As I mentoned before... Postfix has been emerged with SASL support.

----------

