# Cyrus-sasl 2.1.25 + Mysql Problem



## camelspk5 (Feb 2, 2012)

Hi all, this my first post up here, so please kindly fogive be if I make some mistake. 

I have a problem with cyrus-sasl2 to authenticate a user using MySQL. During the installation I've checked the MySQL option and in /usr/local/lib/sasl2 I've got all four libsql* files. When I'm trying to send mail, I get such as info:


```
Feb  1 09:15:22 poczta postfix/smtpd[68250]: connect from unknown[10.100.11.223]
Feb  1 09:15:22 poczta postfix/smtpd[68250]: warning: unknown[10.100.11.223]: SASL PLAIN authentication
 failed: no mechanism available
```

All this because sasl considers only one auxprop mechanism: sasldb


```
poczta# pluginviewer
Installed and properly configured auxprop mechanisms are:
sasldb
List of auxprop plugins follows
Plugin "sasldb" ,       API version: 8
        supports store: yes
```

despite of the fact that my smtpd.conf consists of:


```
pwcheck_method: auxprop
auxprop_plugin: mysql
sql_engine: mysql
allowanonymouslogin: no
allowplaintext: yes
mech_list: login plain mysql
password_format plaintext
sql_user: [user]
sql_hostnames: 127.0.0.1
sql_passwd: [passwd]
sql_database: postfix
sql_select: select password from mailbox where username = '%u'@'%r' sql_verbose: yes
```

My current configuration is:

```
FreeBSD 8.2-RELEASE FreeBSD 8.2-RELEASE
postfix-base-2.8.7,1
mysql-server-5.5.19
mysql-client-5.5.19
courier-authlib-mysql-0.63.0_3
courier-authlib-0.63.0_3
courier-authlib-mysql-0.63.0_3
cyrus-sasl-2.1.25_1
cyrus-sasl-saslauthd-2.1.25
```

My main.cf looks like this:


```
smtpd_recipient_restrictions =
        permit_mynetworks,
        reject_authenticated_sender_login_mismatch,
        permit_sasl_authenticated,
        reject_unauth_destination,

smtpd_sasl_auth_enable = yes
broken_sasl_auth_clients = yes
smtpd_sasl_local_domain = seacrew.pl
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = cyrus
smtpd_sasl_authenticated_header = yes
smtpd_sasl_path = smtpd
cyrus_sasl_config_path = /usr/local/lib/sasl2/smtpd.conf
```

I don't know what other information would be useful. I have read about some bug related to this version 2.1.25 and I was considering to downgrade to 2.1.23 but currently I don't know how and where to find it. I would appreciate if someone could give me some advise.

Many thanks,
Peter.


----------



## camelspk5 (Feb 2, 2012)

Additionally I've notice that when I'm sending an email, my /var/log/auth.log log:


```
Feb  2 15:14:20 poczta postfix/smtpd[69818]: sql_select option missing
Feb  2 15:14:20 poczta postfix/smtpd[69818]: auxpropfunc error no mechanism available
```

It's weird because sql_select in smtpd.conf seems fine as far as I concerned. If you have any suggestion please right it here.


----------



## suntzu (Feb 2, 2012)

What si the output of:


```
saslauthd -v
```

the corect syntax of smtpd.conf is:


```
auxprop_plugin: sql
```

I belive the corect line in smtpd.conf for the mechanism list is:


```
mech_list: PLAIN LOGIN CRAM-MD5 DIGEST-MD5 NTLM
```


----------



## camelspk5 (Feb 2, 2012)

Thanks for your answer Suntzu.

The output is:


```
saslauthd 2.1.25
authentication mechanisms: sasldb getpwent kerberos5 pam rimap

Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: t-1447.mag.pl[10.100.11.223]: SASL CRAM-MD5
 authentication failed: authentication failure
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: t-1447.mag.pl[10.100.11.223]: SASL NTLM
 authentication failed: authentication failure
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: t-1447.mag.pl[10.100.11.223]: SASL PLAIN
 authentication failed: authentication failure
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: SASL authentication failure: no user in db
Feb  1 13:50:26 poczta postfix/smtpd[70323]: warning: t-1447.mag.pl[10.100.11.223]: SASL LOGIN
 authentication failed: authentication failure
```

All it is trying to do is searching sasldb2.db which is empty now, because I want to use sql, not sasldb mechanism.


----------



## DutchDaemon (Feb 2, 2012)

Format your posts, camelspk5.


----------



## suntzu (Feb 2, 2012)

How did you create virtual users? Postfixadmin? 

[cmd=]mysql -p[/cmd]
[cmd=]use database postfix;[/cmd]
[cmd=]show tables;[/cmd]
[cmd=]select * from {your table with mailboxes};[/cmd]


----------



## dh (Feb 2, 2012)

Is there any particular reason why you use security/cyrus-sasl2? World became much more beautiful for me when I switched from security/cyrus-sasl2 to mail/dovecot2.


----------



## camelspk5 (Feb 3, 2012)

Hi Suntzu,

I use phpmyadmin to add the user. I've checked command that you show me and they show exactly the same as my phpmyadmin. It works couse because courier-imap-auth uses it to authenticate the user when they retrieve their mail from the server using pop3d.

I have added one of my users to sasldb. I've used *saslpasswd2* to add the user and *sasldblistusers2* afterwards. Then I write a sample message from and sent using that particular user and it worked. It proves that my postfix/smtpd deamon still using sasldb plugin instead mysql plugin. I still don't know how to change it.

*dh* -> If I changed my cyrus-sasl2 to dovecot2 I would have to give up with courier-imap too, or maybe I'm wrong? I use cyrus only to authenticate user who's trying to send an email(using smtpd deamon), not retrieving the message using pop3d or imapd.

*DutchDaemon* -> sorry, I hope that from now my formating will be ok.


----------



## dh (Feb 3, 2012)

camelspk5 said:
			
		

> *dh* -> If I changed my cyrus-sasl2 to dovecot2 I would have to give up with courier-imap too, or maybe I'm wrong? I use cyrus only to authenticate user who's trying to send an email(using smtpd deamon), not retrieving the message using pop3d or imapd.



You don't have to use dovecot's IMAP and POP3, just its SASL authentication capabilities.
I use postfix for SMTP, dovecot for SASL authentication and nginx as POP3/IMAP proxy on the same server. Real IMAP and POP3 services (offered by courier-imap) are on different physical servers.

Just make sure that [font=fixed]protocols = none[/font] is in your dovecot.conf.


----------



## camelspk5 (Feb 3, 2012)

dh said:
			
		

> You don't have to use dovecot's IMAP and POP3, just its SASL authentication capabilities.
> I use postfix for SMTP, dovecot for SASL authentication and nginx as POP3/IMAP proxy on the same server. Real IMAP and POP3 services (offered by courier-imap) are on different physical servers.
> 
> Just make sure that [font=fixed]protocols = none[/font] is in your dovecot.conf.



Thanks for your answer, can you tell me where to start? If I install dovecot, I will have to turn off all the entry related to *cyrus-sasl* in *main.cf* and put there some entry for dovecot. Can you show how to do that? Can dovecot use mysql to authenticate?


----------



## dh (Feb 3, 2012)

Yes, dovecot can use mysql

This is my postfix sasl configuration.

```
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_local_domain = $myhostname
broken_sasl_auth_clients = yes
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
```
NB! For this to work postfix must have dovecot support compiled in (using WITH_DOVECOT=yes or WITH_DOVECOT2=yes options)


dovecot.conf (most of this is just default configuration, the important stuff is in the auth* parts):

```
protocols = none
disable_plaintext_auth = no
syslog_facility = mail
mail_location = mbox:~/mail/:INBOX=/var/mail/%u
mail_privileged_group = mail
dotlock_use_excl = yes
verbose_proctitle = yes
first_valid_uid = 1000
first_valid_gid = 1000
maildir_copy_with_hardlinks = yes
protocol imap {
  imap_client_workarounds = delay-newmail netscape-eoh tb-extra-mailbox-sep
}  
protocol pop3 {
  pop3_uidl_format = %08Xu%08Xv
  pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
}
protocol lda {
  sendmail_path = /usr/sbin/sendmail
}
auth_username_format = %Lu
auth_verbose = yes
auth_debug = no
auth_debug_passwords = no
auth default {
  mechanisms = plain login
  passdb sql {
    args = /usr/local/etc/dovecot-sql.conf
  }
  userdb sql {
    args = /usr/local/etc/dovecot-sql.conf
  }
  user = root
  socket listen {
    master {
      path = /var/run/dovecot/auth-master
      mode = 0660
    }
    client {
      path = /var/spool/postfix/private/auth
      mode = 0666
      user = postfix
    }
  }
}
dict {
}
plugin {
}
```
You should read through the comments of the default dovecot.conf to learn what all those options mean before using them.

Use /usr/local/share/doc/dovecot/example-config/dovecot-sql.conf.ext as an example how to create your own dovecot-sql.conf


----------



## camelspk5 (Feb 6, 2012)

*[SOLVED] Problem solved*

Hi all, I've finally made it! *M*y postfix works with cyrus-sasl2 and mysql. Below is my configuration:

ain.cf

```
smtpd_sasl_auth_enable = yes
broken_sasl_auth_clients = no
smtpd_sasl_local_domain =
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = cyrus
smtpd_sasl_authenticated_header = yes
smtpd_sasl_path = smtpd
cyrus_sasl_config_path = /usr/lib/sasl2
```

Please take note that cyrus_sasl_config_path should indicate just on directory where sasl has got lib file and configuration file fo smtp = smtpd.conf.

You should also take note that you should have a symlink from /usr/local/lib/sasl2 to /usr/lib/sasl2.

/usr/lib/sasl2/smtpd.conf

```
log_level: 7
pwcheck_method: saslauthd auxprop
auxprop_plugin: sql
sql_engine: mysql
allowanonymouslogin: no
allowplaintext: true
mech_list: login plain
password_format: plaintext
saslauthd_path: /var/run/saslauthd/mux
sql_user: [password]
sql_hostnames: 127.0.0.1, 192.168.xxx.xxx, xxxxxx.pl
sql_passwd: [password]
sql_database: postfix
sql_select: select password from mailbox where username = '%u@%r'
```

Previously in my sql_select I have had a mistake, because there was: 

```
sql_select: select password from mailbox where username = '%u'@'%r' sql_verbose: yes
```

The real problem was that I saw such as info in my /var/log/maillog file:

```
Feb  6 12:43:20 poczta postfix/smtpd[78030]: warning: SASL per-process initialization failed: generic failure
Feb  6 12:43:20 poczta postfix/smtpd[78030]: fatal: SASL per-process initialization failed
Feb  6 12:43:21 poczta postfix/master[53647]: warning: process /usr/libexec/postfix/smtpd pid 78030 exit status 1
Feb  6 12:43:21 poczta postfix/master[53647]: warning: /usr/libexec/postfix/smtpd: bad command startup -- throttling
```

*M*y pluginviewer constantly was giving me wrong information that none SASL mechanism was installed and worked correctly.


```
poczta# pluginviewer
Installed and properly configured auxprop mechanisms are:
<none>
Installed and properly configured SASL (server side) mechanisms are:
  EXTERNAL PLAIN LOGIN
Available SASL (server side) mechanisms matching your criteria are:
  PLAIN LOGIN
```

I've finally checked mysql.log and figured out that the sql plugin in sasl2 really works. 

*dh* -> many thanks for interesting in my case and for your help, now everything works great for me.


----------



## wmoreno3 (Jun 6, 2012)

*sql_select option missing, auxpropfunc error no mechanism available*



			
				camelspk5 said:
			
		

> Additionally I've notice that when I'm sending an email, my /var/log/auth.log log:
> 
> 
> ```
> ...


When:

```
/usr/local/etc/rc.d/slapd restart
Jun  6 16:36:24 server slapd[2037]: sql_select option missing
Jun  6 16:36:24 server slapd[2037]: auxpropfunc error no mechanism available

cyrus-sasl-2.1.25_2

saslpasswd2 -c username
If you want to enable SMTP AUTH with the system Sendmail, read Sendmail.README
If you want to authenticate your user by /etc/passwd, PAM or LDAP, install
 ports/security/cyrus-sasl2-saslauthd and set sasl_pwcheck_method to saslauthd
 after installing the Cyrus-IMAPd 2.X port.
You should also check the /usr/local/lib/sasl2/*.conf files for the correct pwcheck_method

cyrus-sasl-saslauthd-2.1.25

http://cyrusimap.web.cmu.edu/
saslauthd_enable="YES" in your /etc/rc.conf.

postfix-2.9.3,1

/etc/mail/mailer.conf
postfix_enable="YES" in your rc.conf
```
I have a problem with OpenLDAD with 
	
	



```
sql_select option missing
```
 I don't know how to solve it.


----------

