Postfix + SASL using wrong query to authenticate

Question

I recently set up postfix + dovecot on a aws ec2 instance following this guide: http://flurdy.com/docs/postfix/#config-secure-auth

Currently I'm stuck with SASL.

The SQL query doesn't seem to be what has been configured. Here are the configs:

/etc/postfix/sasl/smtpd.conf

shows

pwcheck_method: saslauthd
mech_list: plain login
log_level: 7
allow_plaintext: true
auxprop_plugin: sql
sql_engine: mysql
sql_hostnames: 127.0.0.1
sql_user: mail
sql_passwd: passwd
sql_database: maildb
sql_select: select crypt from users where id='%u@%r' and enabled = 1

and

/etc/sasl2/smtpd.conf

shows

pwcheck_method: saslauthd
mech_list: plain login
log_level: 7
allow_plaintext: true
auxprop_plugin: sql
sql_engine: mysql
sql_hostnames: 127.0.0.1
sql_user: mail
sql_passwd: password
sql_database: maildb
sql_select: select crypt from users where id='%u@%r' and enabled = 1

and

/etc/pam.d/smtp

#%PAM-1.0
auth required pam_mysql.so user=mail passwd=password host=127.0.0.1 db=maildb table=users usercolumn=id passwdcolumn=crypt crypt=1 debug
account sufficient pam_mysql.so user=mail passwd=password host=127.0.0.1 db=maildb table=users usercolumn=id passwdcolumn=crypt crypt=1 debug

Now the config files defined select crypt from users where id='%u@%r' and enabled = 1 as the select query for the password.

When the auth failed and I checked /var/log/secure I found out that a wrong select query is used:

Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - SELECT crypt FROM users WHERE id = 'admin'
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - SELECT returned no result.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_mysql_check_passwd() returning 1.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_mysql_sql_log() called.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_mysql_sql_log() returning 0.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_sm_authenticate() returning 10.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_mysql_release_ctx() called.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_mysql_destroy_ctx() called.
Jun  1 15:50:13 ip-172-31-23-97 saslauthd[19892]: pam_mysql - pam_mysql_close_db() called.

What could be the cause of this?

If `SELECT crypt FROM users WHERE id = 'admin'` was wrong query, what is the right query that you expect? — masegaloeh, Jun 01 '16 at 16:12
it is `select crypt from users where id='%u@%r' and enabled = 1` which was defined in the config. — maddo7, Jun 01 '16 at 16:27
After running `sudo strace -f saslauthd -d -a pam` I don't see any access to `smtpd.conf`. It seems to get ignored. — maddo7, Jun 01 '16 at 18:45

score 2 · Accepted Answer · answered Jun 02 '16 at 18:42

There two common methods to use postfix SASL with MySQL:

Using saslauthd service combined with pam_mysql
Using sasl auxprop with sql plugin

Unfortunately, the second method has serious security drawback: you must store the password in plaintext format. So, in your case you need to use saslauthd and pam_mysql.

In your config above, you mixed these two methods: in pwcheck_method you put saslauthd but you configure sasl to use auxprop_plugin with sql. The pwcheck_method will override this, so parameter auxprop_plugin (and all of your sql_* parameters) become useless. That's explain why you get wrong query. Instead SASL will execute query provided /etc/pam.d/smtp. To satisfy your requirement, put additional parameter where in pam configuration

... passwdcolumn=crypt crypt=1 debug where=enabled=1
... passwdcolumn=crypt crypt=1 debug where=enabled=1

And for your missing realm (%r) problem, try to set smtpd_sasl_local_domain to your default domain.

Postfix + SASL using wrong query to authenticate

1 Answers1