Решено: Exim, не отправляются письма, SQL запрос (Кривой запрос или туплю ли я?)

AlfaStriker · Сообщение **AlfaStriker** » 09.04.2012 08:39

Доброе утро!

Имеется на тестовой машине:
Exim 4.77, Dovecot 2.9, Mysql, PostfixAdmin

Все это добро благополучно установлено на CentOS 6.2 x32

Теперь о проблеме:

Когда клиенть пытается получить письмо на сервере в логах начинает красоватся ошибка

Код:

2012-04-08 23:34:37 auth_login authenticator failed for (client1) [192.168.50.10] I=[192.168.50.2]:25: 535 Incorrect authentication data (set_id=a@point.local)

Чтобы выяснить откуда это растет, был запушен debug режим командой:

# exim -bd -d+all

В результате получено следующее:

Код:

[root@mx ~]#  exim -bd -d+all
23:41:57  2831 Exim version 4.77 uid=0 gid=0 pid=2831 D=fffdffff
Berkeley DB: Berkeley DB 4.7.25: (June  4, 2010)
Support for: crypteq iconv() IPv6 PAM Perl Expand_dlfunc TCPwrappers OpenSSL Content_Scanning DKIM Old_Demime Experimental_SPF Experimental_SRS
Lookups (built-in): lsearch wildlsearch nwildlsearch iplsearch cdb dbm dbmnz dnsdb dsearch ldap ldapdn ldapm nis nis0 nisplus passwd sqlite
Authenticators: cram_md5 cyrus_sasl dovecot plaintext spa
Routers: accept dnslookup ipliteral manualroute queryprogram redirect
Transports: appendfile/maildir/mailstore/mbx autoreply lmtp pipe smtp
Fixed never_users: 0
Size of off_t: 8
Compiler: GCC [4.4.5 20110214 (Red Hat 4.4.5-6)]
Library version: OpenSSL: Compile: OpenSSL 1.0.0-fips 29 Mar 2010
                          Runtime: OpenSSL 1.0.0-fips 29 Mar 2010
Library version: Cyrus SASL: Compile: 2.1.23
                             Runtime: 2.1.23 [Cyrus SASL]
Library version: PCRE: Compile: 7.8
                       Runtime: 7.8 2008-09-05
23:41:57  2831 Loading lookup modules from /usr/lib/exim/4.77-48.el6/lookups
23:41:57  2831 Loaded "mysql.so" (1 lookup types)
23:41:57  2831 Loaded 1 lookup modules
23:41:57  2831 Total 19 lookups
Library version: MySQL: Compile: 5.1.52 [Source distribution]
                        Runtime: 5.1.61
                        Exim version 4.77
Library version: SQLite: Compile: 3.6.20
                         Runtime: 3.6.20
WHITELIST_D_MACROS unset
TRUSTED_CONFIG_LIST: "/etc/exim/trusted-configs"
23:41:57  2831 changed uid/gid: forcing real = effective
23:41:57  2831   uid=0 gid=0 pid=2831
23:41:57  2831   auxiliary group list: <none>
23:41:57  2831 seeking password data for user "exim": cache not available
23:41:57  2831 getpwnam() succeeded uid=93 gid=93
23:41:57  2831 seeking password data for user "root": cache not available
23:41:57  2831 getpwnam() succeeded uid=0 gid=0
23:41:57  2831 configuration file is /etc/exim/exim.conf
23:41:57  2831 log selectors = 0000cefe 00233821
23:41:57  2831 cwd=/root 3 args: exim -bd -d+all
23:41:57  2831 trusted user
23:41:57  2831 admin user
23:41:57  2831 seeking password data for user "exim": cache not available
23:41:57  2831 getpwnam() succeeded uid=93 gid=93
23:41:57  2831 originator: uid=0 gid=0 login=root name=root
23:41:57  2831 listening on all interfaces (IPv6) port 25
23:41:57  2831 listening on all interfaces (IPv4) port 25
23:41:57  2831 pid written to /var/run/exim.pid
23:41:57  2831 changed uid/gid: running as a daemon
23:41:57  2831   uid=93 gid=93 pid=2831
23:41:57  2831   auxiliary group list: 12 93 490
23:41:57  2831 LOG: MAIN
23:41:57  2831   exim 4.77 daemon started: pid=2831, no queue runs, listening for SMTP on port 25 (IPv6 and IPv4)
23:41:57  2831 set_process_info:  2831 daemon: no queue runs, listening for SMTP on port 25 (IPv6 and IPv4)
23:41:57  2831 daemon running with uid=93 gid=93 euid=93 egid=93
23:41:57  2831 Listening...
23:42:05  2831 Connection request from 192.168.50.10 port 3401
23:42:05  2831 interface address=192.168.50.2 port=25
23:42:05  2831 search_tidyup called
23:42:05  2831 1 SMTP accept process running
23:42:05  2831 Listening...
23:42:05  2835 sender_fullhost = [192.168.50.10]
23:42:05  2835 sender_rcvhost = [192.168.50.10]
23:42:05  2835 Process 2835 is handling incoming connection from [192.168.50.10]
23:42:05  2835 host in host_lookup? no (option unset)
23:42:05  2835 set_process_info:  2835 handling incoming connection from [192.168.50.10] I=[192.168.50.2]:25
23:42:05  2835 host in host_reject_connection? no (option unset)
23:42:05  2835 duplicate IP address 127.0.0.1 (MX=-1) removed
23:42:05  2835 gethostbyname2 looked up these IP addresses:
23:42:05  2835   name=localhost address=::1
23:42:05  2835   name=localhost address=127.0.0.1
23:42:05  2835 host in "localhost:127.0.0.0/8:192.168.50.0/24 #add the hosts from which you allow relaying here"? no (malformed IPv4 address or address mask)
23:42:05  2835 host in sender_unqualified_hosts? no (end of list)
23:42:05  2835 cached no match for +relay_from_hosts
23:42:05  2835 host in recipient_unqualified_hosts? no (end of list)
23:42:05  2835 host in helo_verify_hosts? no (option unset)
23:42:05  2835 host in helo_try_verify_hosts? no (option unset)
23:42:05  2835 host in helo_accept_junk_hosts? yes (matched "192.168.50.0/24")
23:42:05  2835 expanding: $primary_hostname, ESMTP EXIM $version_number
23:42:05  2835    result: mx.point.local, ESMTP EXIM 4.77
23:42:05  2835 SMTP>> 220 mx.point.local, ESMTP EXIM 4.77
23:42:05  2835 Process 2835 is ready for new message
23:42:05  2835 smtp_setup_msg entered
23:42:05  2835 SMTP<< EHLO client1
23:42:05  2835 client1 in helo_lookup_domains? no (end of list)
23:42:05  2835 sender_fullhost = (client1) [192.168.50.10]
23:42:05  2835 sender_rcvhost = [192.168.50.10] (helo=client1)
23:42:05  2835 set_process_info:  2835 handling incoming connection from (client1) [192.168.50.10] I=[192.168.50.2]:25
23:42:05  2835 host in pipelining_advertise_hosts? yes (matched "*")
23:42:05  2835 host in auth_advertise_hosts? yes (matched "*")
23:42:05  2835 host in tls_advertise_hosts? no (option unset)
23:42:05  2835 SMTP>> 250-mx.point.local Hello client1 [192.168.50.10]
23:42:05  2835 250-SIZE 67108864
23:42:05  2835 250-PIPELINING
23:42:05  2835 250-AUTH PLAIN LOGIN CRAM-MD5
23:42:05  2835 250 HELP
23:42:05  2835 SMTP<< AUTH LOGIN
23:42:05  2835 SMTP>> 334 VXNlcm5hbWU6
23:42:05  2835 SMTP<< YUBwb2ludC5sb2NhbA==
23:42:05  2835 SMTP>> 334 UGFzc3dvcmQ6
23:42:05  2835 SMTP<< MTIz
23:42:05  2835 auth_login authenticator:
23:42:05  2835   $auth1 = a@point.local
23:42:05  2835   $auth2 = 123
23:42:05  2835   $1 = a@point.local
23:42:05  2835   $2 = 123
23:42:05  2835 expanding: $auth1
23:42:05  2835    result: a@point.local
23:42:05  2835 expanding: $auth2
23:42:05  2835    result: 123
23:42:05  2835 expanding: SELECT `username` FROM `mailbox` WHERE `username` = '${quote_mysql:$auth1}' AND `password` = '${quote_mysql:$auth2}'
23:42:05  2835    result: SELECT `username` FROM `mailbox` WHERE `username` = 'a@point.local' AND `password` = '123'
23:42:05  2835 search_open: mysql "NULL"
23:42:05  2835 search_find: file="NULL"
23:42:05  2835   key="SELECT `username` FROM `mailbox` WHERE `username` = 'a@point.local' AND `password` = '123'" partial=-1 affix=NULL starflags=0
23:42:05  2835 LRU list:
23:42:05  2835 internal_search_find: file="NULL"
23:42:05  2835   type=mysql key="SELECT `username` FROM `mailbox` WHERE `username` = 'a@point.local' AND `password` = '123'"
23:42:05  2835 database lookup required for SELECT `username` FROM `mailbox` WHERE `username` = 'a@point.local' AND `password` = '123'
23:42:05  2835 MySQL query: SELECT `username` FROM `mailbox` WHERE `username` = 'a@point.local' AND `password` = '123'
23:42:05  2835 MYSQL new connection: host=localhost port=0 socket=NULL database=vexim user=vexim
23:42:05  2835 MYSQL: no data found
23:42:05  2835 lookup failed
23:42:05  2835 expanding: yes
23:42:05  2835    result: yes
23:42:05  2835 skipping: result is not used
23:42:05  2835 expanding: no
23:42:05  2835    result: no
23:42:05  2835 expanding: ${lookup mysql{SELECT `username` FROM `mailbox` WHERE `username` = '${quote_mysql:$auth1}' AND `password` = '${quote_mysql:$auth2}'}{yes}{no}}
23:42:05  2835    result: no
23:42:05  2835 expanded string: no
23:42:05  2835 expanding: $auth1
23:42:05  2835    result: a@point.local
23:42:05  2835 SMTP>> 535 Incorrect authentication data
23:42:05  2835 LOG: MAIN REJECT
23:42:05  2835   auth_login authenticator failed for (client1) [192.168.50.10] I=[192.168.50.2]:25: 535 Incorrect authentication data (set_id=a@point.local)
23:42:05  2835 SMTP>> 421 mx.point.local lost input connection
23:42:05  2835 LOG: smtp_connection MAIN
23:42:05  2835   SMTP connection from (client1) [192.168.50.10] I=[192.168.50.2]:25 lost
23:42:05  2835 search_tidyup called
23:42:05  2835 close MYSQL connection: localhost/vexim/vexim
23:42:05  2831 child 2835 ended: status=0x100
23:42:05  2831 0 SMTP accept processes now running
23:42:05  2831 Listening...

Здесь видно что ошибка в SQL запросе к базе:

Код:

2835 MYSQL: no data found
23:42:05  2835 lookup failed

Сам запрос имеет вид:

Код:


MySQL query: SELECT `username` FROM `mailbox` WHERE `username` = 'a@point.local' AND `password` = '123'

То есть Exim пытается взять из таблицы mailbox поле с почтовым ящиком и паролем, которых он не может получить судя по логам.

База создана PostfixAdmin и имеет следующе:

Код:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| vexim              |
+--------------------+
4 rows in set (0.00 sec)

Код:

mysql> use vexim
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_vexim       |
+-----------------------+
| admin                 |
| alias                 |
| alias_domain          |
| config                |
| domain                |
| domain_admins         |
| fetchmail             |
| log                   |
| mailbox               |
| quota                 |
| quota2                |
| vacation              |
| vacation_notification |
+-----------------------+
13 rows in set (0.00 sec)

mysql>

А вот сама таблица mailbox:

Код:

mysql> SHOW COLUMNS FROM mailbox;
+------------+--------------+------+-----+---------------------+-------+
| Field      | Type         | Null | Key | Default             | Extra |
+------------+--------------+------+-----+---------------------+-------+
| username   | varchar(255) | NO   | PRI | NULL                |       |
| password   | varchar(255) | NO   |     | NULL                |       |
| name       | varchar(255) | NO   |     | NULL                |       |
| maildir    | varchar(255) | NO   |     | NULL                |       |
| quota      | bigint(20)   | NO   |     | 0                   |       |
| local_part | varchar(255) | NO   |     | NULL                |       |
| domain     | varchar(255) | NO   | MUL | NULL                |       |
| created    | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| modified   | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| active     | tinyint(1)   | NO   |     | 1                   |       |
+------------+--------------+------+-----+---------------------+-------+

10 rows in set (0.00 sec)

А вот что внутри нее:

Код:


mysql> SELECT * FROM mailbox;
+-------------------+------------------------------------+------+--------------------+-------+------------+-------------+---------------------+---------------------+--------+
| username          | password                           | name | maildir            | quota | local_part | domain      | created             | modified            | active |
+-------------------+------------------------------------+------+--------------------+-------+------------+-------------+---------------------+---------------------+--------+
| linux@point.local | $1$cf007044$zqZ5QbTtKPC.As0.SCu9R/ |      | linux@point.local/ |     0 | linux      | point.local | 2012-04-08 23:15:11 | 2012-04-08 23:15:11 |      1 |
| user@point.local  | $1$b2e5d656$ECe.sfdyrNAWzffR6vc6A0 |      | user@point.local/  |     0 | user       | point.local | 2012-04-08 23:15:18 | 2012-04-08 23:15:18 |      1 |
| aaa@point.local   | $1$df9e89d5$BMRK7M8OOMRrWzn6p9H4s/ |      | aaa@point.local/   |     0 | aaa        | point.local | 2012-04-08 23:18:53 | 2012-04-08 23:18:53 |      1 |
| a@point.local     | $1$a913430c$AgLpxOzmcp2CX10J1rIEz1 |      | a@point.local/     |     0 | a          | point.local | 2012-04-08 23:21:57 | 2012-04-08 23:21:57 |      1 |
+-------------------+------------------------------------+------+--------------------+-------+------------+-------------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

В самом Exim'e запросы выглядят так:

Код:

begin authenticators

auth_plain:
driver = plaintext
public_name = PLAIN
server_condition = ${lookup mysql{SELECT `username` FROM \
`mailbox` WHERE `username` = \
'${quote_mysql:$auth2}' AND `password` = \
'${quote_mysql:$auth3}'}{yes}{no}}
server_prompts = :
server_set_id = $auth2

auth_login:
driver = plaintext
public_name = LOGIN
server_condition = ${lookup mysql{SELECT `username` FROM \
`mailbox` WHERE `username` = \
'${quote_mysql:$auth1}' AND `password` = \
'${quote_mysql:$auth2}'}{yes}{no}}
server_prompts = Username:: : Password::
server_set_id = $auth1

auth_cram_md5:
driver = cram_md5
public_name = CRAM-MD5
server_secret = ${lookup mysql{SELECT `password` FROM \
`mailbox` WHERE `username` \
= '${quote_mysql:$auth1}'}{$value}fail}
server_set_id = $auth2

Вот содержимое dovecot.conf и dovecot-mysql.conf:
# cat /etc/dovecot/dovecot.conf

Код:

# 2.0.14: dovecot.conf
# OS: Linux 2.6.32-71.29.1.el6.i686 i686 CentOS Linux release 6.0 (Final)
base_dir = /var/run/dovecot/
disable_plaintext_auth = no
first_valid_uid = 93
listen = *
login_greeting = Dovecot ready man.
login_log_format_elements = user=<%u> method=%m rip=%r lip=%l %c
mail_location = maildir:/var/vmail/%d/%u
passdb {
driver = pam
}
passdb {
args = /etc/dovecot/dovecot-mysql.conf
driver = sql
}
protocols = imap pop3
service auth {
unix_listener auth-master {
group = exim
mode = 0660
user = exim
}
user = root
}
service imap-login {
chroot =
client_limit = 256
process_limit = 128
process_min_avail = 3
service_count = 1
vsz_limit = 64 M
}
service pop3-login {
chroot =
client_limit = 256
process_limit = 128
process_min_avail = 3
service_count = 1
vsz_limit = 64 M
}
ssl = no
userdb {
driver = passwd
}
userdb {
args = /etc/dovecot/dovecot-mysql.conf
driver = sql
}
protocol imap {
imap_client_workarounds = tb-extra-mailbox-sep
mail_plugins = quota imap_quota
}
protocol pop3 {
pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
pop3_uidl_format = %08Xu%08Xv
}
protocol lda {
auth_socket_path = /var/run/dovecot/auth-master
postmaster_address = admin@point.local
}

# cat /etc/dovecot/dovecot-mysql.conf

Код:

#File: /etc/dovecot/dovecot-mysql.conf
driver = mysql
connect = host=localhost dbname=vexim user=vexim password=password
default_pass_scheme = CRYPT
password_query = SELECT `username` as `user`, `password` FROM  `mailbox` WHERE `username` = '%n@%d' AND `active`='1'
user_query = SELECT 93 AS `uid`, 93 AS `gid` FROM `mailbox` WHERE `username` = '%n@%d' AND `active`='1'

/etc/dovecot/dovecot-mysql.conf -> default_pass_scheme = CRYPT
/var/www/html/postfixadmin/config.inc.php -> $CONF['encrypt'] = 'md5crypt';

Помомгите разобраться...

Спасибо!

09.04.2012 10:36

ваш запрос рассчитывает на то, что в базе пароль в открытом виде:

AlfaStriker писал(а): ↑
09.04.2012 08:39
AND `password` = '123'

а на практике у вас там хеш. надо добавить к запросу функцию хеширования, как вариант (или уговорить давкота использовать пароли в открытом виде, что наверно некрасиво).

а вообще, раз у вас давкот, то настройте экзим на работу с его авторизатором.
и будет у вас единообразная авторизация через единственное место.
например так:

Код: Выделить всё

auth_plain:
driver = dovecot
public_name = PLAIN
server_socket = /var/run/dovecot/auth-client
server_set_id = $auth1

auth_login:
driver = dovecot
public_name = LOGIN
server_socket = /var/run/dovecot/auth-client
server_set_id = $auth1

(в давкоте там надо будет раскоментить то, что связано с авторизаторами)

ilush · Сообщение **ilush** » 09.04.2012 22:31

Все просто - чтобы делать запросы к mysql базе, exim должен быть собран с поддержкой mysql. В выводе команды exim -bV в поле Lookups должно быть mysql. У вас из баз только sqlite. Нужно собрать пакет самому или найти репозиторий.

Сообщение **KiWi** » 10.04.2012 01:52

ilush писал(а): ↑
09.04.2012 22:31
Все просто - чтобы делать запросы к mysql базе, exim должен быть собран с поддержкой mysql. В выводе команды exim -bV в поле Lookups должно быть mysql. У вас из баз только sqlite. Нужно собрать пакет самому или найти репозиторий.

Не пробовали внимательно читать первый пост прежде, чем отвечать?

А так -- во втором посте всё правильно рассказали(и про ошибку, и про то, как делать правильней).

AlfaStriker · Сообщение **AlfaStriker** » 11.04.2012 21:23

Ленивая Бестолоч...

Как вы написали, все заработало!

За место запросов MySQL в /etc/exim/exim.conf поставил:

Код:

auth_plain:
driver = dovecot
public_name = PLAIN
server_socket = /var/run/dovecot/auth-client
server_set_id = $auth1

auth_login:
driver = dovecot
public_name = LOGIN
server_socket = /var/run/dovecot/auth-client
server_set_id = $auth1

Код:

/etc/dovecot/dovecot-msql.conf                                           ->   MD5-CRYPT
/var/www/html/postfixadmin/config.inc.php                           ->   $CONF['encrypt'] = 'md5crypt';

И все чудесным образом заработало!

Даже сам не ожидал

11.04.2012 21:35

ну вот и славно :-)

unixforum.org

Решено: Exim, не отправляются письма, SQL запрос (Кривой запрос или туплю ли я?)

Решено: Exim, не отправляются письма, SQL запрос

Re: Решено: Exim, не отправляются письма, SQL запрос

Re: Решено: Exim, не отправляются письма, SQL запрос

Re: Решено: Exim, не отправляются письма, SQL запрос

Re: Решено: Exim, не отправляются письма, SQL запрос

Re: Решено: Exim, не отправляются письма, SQL запрос