Несколько вопросов по Exim + mysql

EXIM, sendmail, postfix, Dovecot и прочие. Решение проблем связанных с работой электронной почты

Модератор: xM

Правила форума
Убедительная просьба юзать теги [code] при оформлении листингов.
Сообщения не оформленные должным образом имеют все шансы быть незамеченными.
pharmacolog
проходил мимо
Сообщения: 5
Зарегистрирован: 2007-07-25 17:22:59

Несколько вопросов по Exim + mysql

Непрочитанное сообщение pharmacolog » 2007-07-25 17:38:53

1) Помогите настроить квоты.

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

  quota = ${lookup mysql{SELECT quota FROM mailbox \
            WHERE `username`='${quote_mysql:$local_part@$domain}'}{${value}M}}
  quota_size_regex = S=(\d+)$
  quota_warn_threshold = 75%
  quota_is_inclusive = no
  quota_warn_message = "\
                     To: $local_part@$domain\n\
                      From: abuse@teletrade-dj.com\n\
                      Subject: Ваш почтовый ящик заполнен.\n\
                      Это сообщение автоматически сгенерировано вашим почтовым сервером.\n\
                      Ваш почтовый ящик заполнен на 75% процентов. Если вы превысите\n\
                      установленный лимит, то не сможете получать новые сообщения!\n"
В поле quota таблицы mailbox храниться значение в мегабайтах.

В результате этого сервер перестает принимать письма при достижении квоты, но не создает отлупов не отправителю не получателю.
В логах следующие:

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

2007-07-25 18:18:30 1IDhh8-00006l-KX <= anton@teletrade-dj.com H=(adm0) [192.168.0.68] I=[192.168.0.75]:25 P=esmtpa A=auth_login:anton@domain.com S=1331 id=039401c7cec6$afbfd660$4481693e@adm0 from <anton@domain.com> for anton@domain.org.ru
2007-07-25 18:18:30 1IDhh8-00006r-O4 <= mailnull@teletrade-dj.com U=mailnull P=local S=2447 id=039401c7cec6$afbfd660$4481693e@adm0 from <mailnull@domain.com> for anton@domain.org.ru
2007-07-25 18:18:30 1IDhh8-00006l-KX => anton <anton@domain.org.ru> R=dspam_spamscan_router T=dspam_spamcheck_transport
2007-07-25 18:18:30 1IDhh8-00006r-O4 == anton@domain.org.ru R=dovecot_user T=dovecot_delivery defer (-22): mailbox is full (MTA-imposed quota exceeded while writing to tmp/1185373110.H909761P430.relay.domain.com)
2007-07-25 18:18:30 1IDhh8-00006r-O4 ** anton@smg.org.ru <anton@domain.org.ru>: retry timeout exceeded
2) Создаю пересылку в postfixadmin на несколько адресов, первый получает письмо нормально, а остальные по две копии. Как побороть?
Вот кусок лога, альяс на anton@domain.org.ru смотрит на anton@domain.org.ru и anton@domain.com

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

2007-07-25 22:26:27 1IDlZ5-000791-7w <= anton@domain.org.ru H=(smg) [89.19.166.41] I=[62.105.129.75]:25 P=esmtpa A=auth_login:anton@domain.org.ru S=689 id=000d01c7cee9$4d843400$0201a8c0@smg from <anton@domain.org.ru> for anton@domain.org.ru
2007-07-25 22:26:27 1IDlZ5-00079A-KP <= mailnull@domain.com U=mailnull P=local S=1852 id=000d01c7cee9$4d843400$0201a8c0@smg from <mailnull@domain.com> for anton@domain.com
2007-07-25 22:26:27 1IDlZ5-000791-7w => anton (anton@domain.com) <anton@domain.org.ru> R=dspam_spamscan_router T=dspam_spamcheck_transport
2007-07-25 22:26:27 1IDlZ5-00079A-KP => anton <anton@domain.com> R=dovecot_user T=dovecot_delivery
2007-07-25 22:26:27 1IDlZ5-00079A-KP Completed
2007-07-25 22:26:27 1IDlZ5-00079H-Si <= mailnull@domain.com U=mailnull P=local S=1839 id=000d01c7cee9$4d843400$0201a8c0@smg from <mailnull@domain.com> for anton@domain.org.ru
2007-07-25 22:26:27 1IDlZ5-000791-7w => anton <anton@domain.org.ru> R=dspam_spamscan_router T=dspam_spamcheck_transport
2007-07-25 22:26:27 1IDlZ5-000791-7w Completed
2007-07-25 22:26:27 1IDlZ5-00079H-Si => anton (anton@domain.com) <anton@domain.org.ru> R=dovecot_user T=dovecot_delivery
2007-07-25 22:26:27 1IDlZ5-00079H-Si => anton <anton@domain.org.ru> R=dovecot_user T=dovecot_delivery
2007-07-25 22:26:27 1IDlZ5-00079H-Si Completed

Хостинговая компания Host-Food.ru
Хостинг HostFood.ru
 

Услуги хостинговой компании Host-Food.ru

Хостинг HostFood.ru

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2460 рублей (8 CPU, 8Gb RAM, 2x500Gb HDD, RAID 3ware 9750):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

Аватара пользователя
Zedik
сержант
Сообщения: 283
Зарегистрирован: 2007-01-20 22:30:57
Откуда: 127.0.0.1

Re: Несколько вопросов по Exim + mysql

Непрочитанное сообщение Zedik » 2007-07-26 7:48:04

в роутерах
mysqluser:
driver = accept
condition = ${if eq{}{${lookup mysql{SELECT `maildir` FROM `mailbox` \
WHERE `username`='${quote_mysql:$local_part@$domain}'}}}{no}{yes}}
transport = mysql_delivery
headers_remove = X-Mailer : X-AntiVirus : X-DSPAM-Factors
в транспортах
mysql_delivery:
driver = appendfile
check_string = ""
create_directory
delivery_date_add
directory = ${lookup mysql{SELECT CONCAT('/var/mail/exim/', `maildir`) \
FROM `mailbox` WHERE `username`='${local_part}@${domain}'}}
directory_mode = 770
envelope_to_add
group = mail
maildir_format
mode = 0600
no_mode_fail_narrower
quota = ${lookup mysql{SELECT quota FROM mailbox \
WHERE username = '$local_part@$domain'}{$value}fail}M
quota_warn_message = "\
To: $local_part@$domain\n\
BCC: root@domen.ru\n\
From: admin@domen.ru\n\
Subject: *****Free space in your mailbox is over*****\n\
This message was send from your mail server.\n\
This means, that your mailbox($local_part@$domain) is 75% full. If you would \n\
override this limit new mail would not be delivered to you!\n"
quota_warn_threshold = 75%
для Exima+Mysql+Courier.работает без нареканий

pharmacolog
проходил мимо
Сообщения: 5
Зарегистрирован: 2007-07-25 17:22:59

Re: Несколько вопросов по Exim + mysql

Непрочитанное сообщение pharmacolog » 2007-07-26 10:49:53

Вот мой конфиг целиком
Делов в том, что у меня есть несколько доменов "один к одому"
ну вроде user@domain-1.com = user@domain-2.com
B нужно, чтобы пользователи этой разницы не замечая могли писать как с domain-1 так и domain-2.
Для этого заведены на каждый домен альясы вида address='@domain2.com' goto='@domain1.com' и изменены sql запросы.

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

primary_hostname = relay.deomain-1.com
hide mysql_servers = localhost/user/password/base
domainlist local_domains = ${lookup mysql{SELECT `domain` FROM `domain` WHERE \
                            `domain`='${domain}' AND `active`='1'}}
domainlist relay_to_domains = ${lookup mysql{SELECT `domain` FROM `domain` WHERE \
                               `domain`='${domain}' AND `active`='1'}}
hostlist   relay_from_hosts = localhost:127.0.0.0/8
acl_smtp_rcpt = acl_check_rcpt
acl_smtp_data = acl_check_data
av_scanner = clamd:/var/run/clamd.exim/clamd.sock
# spamd_address = 127.0.0.1 783
qualify_domain = deomain-1.com
qualify_recipient = deomain-1.com
allow_domain_literals = false
exim_user = exim
exim_group = exim
never_users = root
#host_lookup = *
#rfc1413_hosts = *
rfc1413_query_timeout = 0s
sender_unqualified_hosts = +relay_from_hosts
recipient_unqualified_hosts = +relay_from_hosts
# percent_hack_domains = *
ignore_bounce_errors_after = 45m
timeout_frozen_after = 7d
freeze_tell = anton@deomain-1.com
helo_accept_junk_hosts = 127.0.0.1/8
auto_thaw = 1h
smtp_banner = "$primary_hostname, ESMTP EXIM $version_number"
smtp_accept_max = 150
smtp_accept_max_per_connection = 25
smtp_connect_backlog = 30
smtp_accept_max_per_host = 20
split_spool_directory = true
remote_max_parallel = 15
return_size_limit = 70k
message_size_limit = 7M
helo_allow_chars = _
smtp_enforce_sync = true
log_selector = \
    +all_parents \
    +connection_reject \
    +incoming_interface \
    +lost_incoming_connection \
    +received_sender \
    +received_recipients \
    +smtp_confirmation \
    +smtp_syntax_error \
    +smtp_protocol_error \
    +queue_run 
syslog_timestamp = no
begin acl
acl_check_rcpt:
  accept  hosts = :
  deny    message       = "incorrect symbol in address"
          domains       = +local_domains
          local_parts   = ^[.] : ^.*[@%!/|]

  deny    message       = "incorrect symbol in address"
          domains       = !+local_domains
          local_parts   = ^[./|] : ^.*[@%!] : ^.*/\\.\\./

  accept  local_parts   = postmaster
          domains       = +local_domains

#  require verify        = sender

  deny    message       = "HELO/EHLO require by SMTP RFC"
          condition     = ${if eq{$sender_helo_name}{}{yes}{no}}

  accept  authenticated = *

  deny    message       = "Your IP in HELO - access denied!"
          hosts         =  * : !+relay_from_hosts
          condition     = ${if eq{$sender_helo_name}\
                          {$sender_host_address}{true}{false}}


  deny    condition     = ${if eq{$sender_helo_name}\
                          {$interface_address}{yes}{no}}
          hosts         = !127.0.0.1 : !localhost : *
          message       = "main IP in your HELO! Access denied!"

  deny    condition     = ${if match{$sender_helo_name}\
                           {\N^\d+$\N}{yes}{no}}
          hosts         = !127.0.0.1 : !localhost : *
          message       = "can not be only number in HELO!"

  deny    condition     = ${if eq{$sender_address}{}{yes}{no}}
          hosts         = !127.0.0.1 : !localhost : *
          message       = " can not null in HELO! Is not RFC..."

  deny    condition     = ${if match{$sender_address}{\N^\s+$\N}{yes}{no}}
          hosts         = !127.0.0.1 : !localhost : *
          message       = " can not null in HELO(breakspace only)! Is not RFC..."

  deny    condition     = ${if eq{$sender_address}{}{yes}{no}}
          hosts         = !127.0.0.1 : !localhost : *
          message       = "Where sender of this mail?!"

  deny    message       = "your hostname is bad (adsl, poll, ppp & etc)."
          condition     = ${if match{$sender_host_name} \
                               {adsl|dialup|pool|peer|dhcp|ppp|pppoe|dialin} \
                               {yes}{no}}

  warn
        set acl_m0 = 30s
  warn
        hosts = +relay_from_hosts
        set acl_m0 = 0s
  warn
        logwrite = Delay $acl_m0 for $sender_host_name \
[$sender_host_address] with HELO=$sender_helo_name. Mail \
from $sender_address to $local_part@$domain.
        delay = $acl_m0

  accept  domains       = +local_domains
          endpass
          message       = "In my mailserver not stored this user"
          verify        = recipient

  accept  domains       = +relay_to_domains
          endpass
          message       = "main server not know how relay to this address"
          verify        = recipient

  deny    message       = "you in blacklist - $dnslist_domain \n $dnslist_text"
          dnslists      = opm.blitzed.org : \
                          cbl.abuseat.org : \
                          bl.csma.biz : \
                          dynablock.njabl.org

  accept  hosts         = +relay_from_hosts

  deny    message       = "relay not permitted"


acl_check_data:

  deny malware = *
  message = "In e-mail found VIRUS - $malware_name"

  accept


begin routers

dspam_addspam_router:
  driver = accept
  domains                      = +local_domains
  local_part_prefix = spam-
  transport = dspam_addspam_transport
        
dspam_notspam_router:
  driver = accept
  domains                      = +local_domains
  local_part_prefix = notspam-
  transport = dspam_notspam_transport


dnslookup:
  driver = dnslookup
  domains = ! +local_domains
  transport = remote_smtp
  ignore_target_hosts = 0.0.0.0 : 127.0.0.0/8
  no_more


system_aliases:
    driver      = redirect
    allow_fail
    allow_defer
    data = ${lookup mysql{SELECT `goto` FROM `alias` WHERE \
    `address`='${quote_mysql:$local_part@$domain}'}}



domain_aliases:
    driver      = redirect
    allow_fail
    allow_defer
    data = ${lookup mysql{SELECT CONCAT('${quote:$local_part}',`goto`) FROM `alias` WHERE \
            `address`='${quote_mysql:@$domain}'}}



lists_post:
  driver = redirect
  domains = deomain-1.com
  local_part_suffix = -everyone
  senders = anton@deomain-1.com
  file = /etc/exim/list
  forbid_pipe
  forbid_file
  errors_to = anton@deomain-1.com
  no_more


dspam_spamscan_router:
  driver = accept
  domains                      = +local_domains
  no_verify
  condition = "${if and {{!eq {$received_protocol}{spam-scanned}} \
                {!eq {$received_protocol}{local}} } {1}{0}}"
  transport = dspam_spamcheck_transport
  require_files = /usr/bin/dspam
  address_test = false
                      
local_delivery_spam_router:
  driver                       = accept
  domains                      = +local_domains
  condition                    = ${if match{$h_X-DSPAM-Result:}{Spam}}
  transport                    = local_delivery_spam_transport


dovecot_user:
  driver = accept
  condition = ${lookup mysql{SELECT `goto` FROM \
  `alias` WHERE \
  `address`='${quote_mysql:$local_part@$domain}' OR \
  `address`='${quote_mysql:@$domain}'}{yes}{no}}
  transport = dovecot_delivery


begin transports

remote_smtp:
  driver = smtp

dovecot_delivery:
   driver = appendfile
   check_string = ""
   create_directory
   delivery_date_add
   directory = /var/mail/exim/$domain/$local_part
   directory_mode = 770
   envelope_to_add
   user = mailnull
   group = mail
   maildir_format
#   maildir_tag = ,S=$message_size
   message_prefix = ""
   message_suffix = ""
   mode = 0660
   quota = ${lookup mysql{SELECT quota FROM mailbox \
            WHERE `username`='${quote_mysql:$local_part@$domain}'}{${value}M}}
   quota_size_regex = S=(\d+)$
   quota_warn_threshold = 75%
   quota_is_inclusive = no
   quota_warn_message = "\
                     To: $local_part@$domain\n\
                      From: abuse@deomain-1.com\n\
                      Subject: Mailbox Full.\n\
                      Тут сообщение о переполнении"

   return_path_add




dspam_addspam_transport:
  driver = pipe
  command = "/usr/bin/dspam --user \
                $local_part@$domain --class=spam --source=error"
  return_path_add = false
  return_fail_output = true
  log_output = true
  ignore_status
  home_directory = "/var/db/dspam"
  current_directory = "/var/db/dspam"
   user = mailnull
   group = mail

dspam_notspam_transport:
  driver = pipe
  command = "/usr/bin/dspam --user \
                $local_part@$domain --class=innocent \
                --source=error --deliver=innocent %u"
  return_path_add = false
  return_fail_output = true
  log_output = true
  ignore_status
  home_directory = "/var/db/dspam"
  current_directory = "/var/db/dspam"
   user = mailnull
   group = mail

dspam_spamcheck_transport:
  driver = pipe
  command = /usr/bin/dspam --deliver=innocent \
                --user "$local_part@$domain" -- %u
  return_path_add = false
  log_output = true
  return_fail_output = true
  ignore_status
  headers_remove        = X-DSPAM-Result
   user = mailnull
   group = mail


local_delivery_spam_transport:
  driver = pipe
  command = /usr/libexec/dovecot/deliver -d $local_part@$domain -m INBOX.Spam
  message_prefix =
  message_suffix =
  delivery_date_add
  envelope_to_add
  return_path_add
  user = mailnull
  group = mail


address_pipe:
  driver = pipe
  return_output

address_reply:
  driver = autoreply



begin retry

# Address or Domain  Error   Retries
# -----------------  -----   -------
*                    quota
*                    *       F,2h,15m; G,16h,1h,1.5; F,4d,6h


begin rewrite


begin authenticators
auth_plain:
  driver = plaintext
  public_name = PLAIN
  server_condition = ${lookup mysql{SELECT cal.username from ( \
                     select distinct(username), password, active from mailbox d \
                     union \
                     select concat(tt.uname,'@',mm.to_domain) as username, tt.password, active from \
                     (select left(m.username, (locate('@',`username`))-1) as uname, domain, PASSWORD, active from `mailbox` m) tt \
                     JOIN \
                     (select right(a.goto, (length(a.goto)-1)) as domain, a.domain as to_domain from `alias` a where left(a.address,1) = "@") mm \
                     ON (tt.domain=mm.domain) \
                     ) cal \
                     where cal.active=1 AND cal.username= \
                     '${quote_mysql:$auth2}' AND cal.password = \
                     '${quote_mysql:$auth3}'}{yes}{no}}
  server_prompts = :
  server_set_id = $auth2


auth_login:
  driver = plaintext
  public_name = LOGIN
  server_condition = ${lookup mysql{SELECT cal.username from ( \
                     select distinct(username), password, active from mailbox d \
                     union \
                     select concat(tt.uname,'@',mm.to_domain) as username, tt.password, active from \
                     (select left(m.username, (locate('@',`username`))-1) as uname, domain, PASSWORD, active from `mailbox` m) tt \
                     JOIN \
                     (select right(a.goto, (length(a.goto)-1)) as domain, a.domain as to_domain from `alias` a where left(a.address,1) = "@") mm \
                     ON (tt.domain=mm.domain) \
                     ) cal \
                     where cal.active=1 AND cal.username= \
                     '${quote_mysql:$auth1}' AND cal.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 cal.password from ( \
                    select distinct(username), password, active from mailbox d \
                    union \
                    select concat(tt.uname,'@',mm.to_domain) as username, tt.password, active from \
                    (select left(m.username, (locate('@',`username`))-1) as uname, domain, PASSWORD, active from `mailbox` m) tt \
                    JOIN \
                    (select right(a.goto, (length(a.goto)-1)) as domain, a.domain as to_domain from `alias` a where left(a.address,1) = "@") mm \
                    ON (tt.domain=mm.domain) \
                    ) cal \
                    where cal.active=1 AND cal.username= \
                    '${quote_mysql:$auth1}'}{$value}fail}  
  server_set_id = $auth2



pharmacolog
проходил мимо
Сообщения: 5
Зарегистрирован: 2007-07-25 17:22:59

Re: Несколько вопросов по Exim + mysql

Непрочитанное сообщение pharmacolog » 2007-07-29 15:21:20

По второй части вопроса о альясе с несколькими получателями разобрался.
Виноваты роутеры DSPAM из статьи http://www.lissyara.su/?id=1301
Удалил их и все начало ходить как положено.
Т.к основной антиспам - yandex SO это не критично.

А вот про квоты вопрос остается открытым....

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35092
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Несколько вопросов по Exim + mysql

Непрочитанное сообщение Alex Keda » 2007-07-29 15:24:20

письмо в дебаге отправь, и посмотри - что с ним происходит.
Убей их всех! Бог потом рассортирует...

pharmacolog
проходил мимо
Сообщения: 5
Зарегистрирован: 2007-07-25 17:22:59

Re: Несколько вопросов по Exim + mysql

Непрочитанное сообщение pharmacolog » 2007-07-29 15:38:21

Так дело в том, что не генерируются отлупы на квоту, а в остальном все работает.
Только без отлупов включать квоты бессмысленно

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35092
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Несколько вопросов по Exim + mysql

Непрочитанное сообщение Alex Keda » 2007-07-29 18:04:13

ну тогда выложи кусок дебага где идёт обработка нужного роутера - посмотреть хоть...
Убей их всех! Бог потом рассортирует...