Почтовая рассылка. Лог в базу данных. Отписка

Прелюдия

На заре работы нового портала, когда у веб-мастера работы было много и то, что вводил пользователь в графу e-mail своего кабинета проверялось минимально, через несколько лет вылилось в большую проблему. Почтовая рассылка уходила на несуществующие адреса, пользователи с такими адресами не отпысывались, а сервер рассылок (далее мейлер) был загружен паразитной нагрузкой. Вопрос отписки таких пользователей периодически поднимался и снова откладывался до лучших времен

И вот время таки настало

Задача

В интерфейсе менеджера в реальном времени отображать статус почтовой рассылки по конкретному пользователю. В частности предоставить веб-мастеру сессионные логи мейлера для дальнейшей обработки. Разработка движка анализа логов и аналитики не рассматривается в рамках этого поста

Исходные данные

• Имеется свой мейлер и сервер БД. Соединение мейлера с сервером БД по tcp
• В качестве MTA — postfix, логирование — rsyslog 8
• В качестве БД — mariadb, куда и будем складывать логи postfix‘а

Реализация

0

Расскажите о своем опытеx

Все работы проводятся на мейлере

Воспользуемся возможностью rsyslog вносить логи в sql-базу. Для начала создадим базу данных, таблицу и пользователя с необходимыми правами. Напоминаю, работать root‘ом там, где нет в этом необходимости — плохая манера. Поэтому в БД создается пользователь с нужными правами

Подключаемся к серверу БД

mysql -u root -p -h db.local -P 3309

CREATE DATABASE db_log;
USE db_log;

CREATE TABLE maillog(
  ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ReceivedAt DATETIME DEFAULT NULL,
  MailerID CHAR(10) DEFAULT NULL,
  Message TEXT DEFAULT NULL,
  PRIMARY KEY(ID),
  KEY MailerID(MailerID)
) ENGINE = INNODB;

FLUSH PRIVILEGES;

Далее создаем в папке /etc/rsyslog.d файл конфигурации 60-mysql.conf, в котором указываем следующее:

# подключаем модуль mysql. rsyslog должен быть собран с поддержкой mysql
module(load="ommysql")

# создаем собственный шаблон maillog_tmpl 
# для записи в таблицу maillog лога в нужном формате
# ReceivedAt - время записи в лог
# MailerID - queueID postfix'а
# Message - собственно лог
template(name="maillog_tmpl" type="string" option.sql="on"
  string="INSERT INTO maillog (ReceivedAt, MailerID, Message) 
          VALUES ('%timegenerated:::date-mysql%', '%msg:2:11%', '%msg:14:$%')")

# в БД пишем строки с queueID (о чем говорит regex [A-Z0-9]{10})
# исключая при этом разный "мусор" (client=, DKIM и т.д.)
# наличие "мусора" проверяем запросом:
# SELECT MailerID FROM maillog WHERE MailerID NOT REGEXP '[A-Z0-9]{10}';
# параметры подключения к БД и используемый template указаны в action
mail.* if (re_match($msg, '[A-Z0-9]{10}')) and not (
  ($msg contains 'client=') or
  ($msg contains 'connect from') or
  ($msg contains 'DKIM') or 
  ($msg contains 'mailhub.local') or
  ($msg contains 'NOQUEUE:') or
  ($msg contains 'removed') or
  ($msg contains 'Trusted TLS') or
  ($msg contains 'Untrusted TLS') or
  ($msg contains 'warning:')) then { 
          action(type="ommysql"
          server="db.local"
          serverport="3309"
          db="db_log"
          uid="usrLog"
          pwd="hyperC00lPWD"
          template="maillog_tmpl")
  stop }

Проверяем конфиг и если нет ошибок перезапускаем rsyslog

rsyslogd -N1
rc-service rsyslog restart

Посмотрим результат на примере queueID 04D0C734D0

Отлично, в базе только то, что нужно для дальнейней обработки. Далее со слов веб-мастера

Из логов у нас есть 4 статуса

• sent — письмо отправлено адресату
• deferred — письмо не доставлено по какой-то причине. На протяжении пяти суток сервер пытается отослать письмо
• bounced — не доставлено. Письмо с текущим статусом deferred получит новый статус после пяти суток
• есть статус invalid при постановке в очередь — это когда мейлер отказался принимать мейл получателя, потому что неверный мейл адрес с точки зрения синтаксиса, в результате клиент отписывается от всего

Что видит менеджер в веб-интерфейсе

• по умолчанию все письма рассылки имеют статус недоставлено с коментарием queued, тоесть в очереди
• при статусе sent они переходят в состояние доставлено
• при deferred — меняется коментарий, но письмо остается в недоставленых
• если статус bounced, то обновляется коментарий, статус клиента становится неподтвержденным и клиент отписывается от всех рассылок

Наши правила отписки или кого удаляем из базы рассылок:

• тех, кто не читал (не открывал) рассылки «А» и «Б» с даты D1
• тех, кто не переходил по ссылкам тех же рассылок с той же даты

Исключение — должны остаться в базе (даже если не открывают и не читают)

• подписчики любого пакета за деньги в годы Y—Y+1 независимо от периода подписки
• зарегистрировались на сайте в последние два месяца, начиная с даты D2
• подписались на рассылку «В» с даты D2
• авторизировались на сайте для просмотра материалов начиная с даты D2

И вот результат

за 5-6 недель было отписано от рассылки более 11 тысяч пользователей с неправильными адресами

Из-за отсутствия паразитной нагрузки почтовая рассылка уходит ежеминутно на 8-10 тысяч адресов. Ранее в течении минуты уходило 3-4 тысячи

Бонус

Анализатор логов postfix. Попробовал, удобно, особенно при разборе полетов

Александр Черных

• Системный администратор с 2000 года
• Участник Freelancehunt, Хабр Q&A, cyberforum
• Кейсы