Sisitoとsisimaiでバウンスメールの解析､集計する

# はじめに
さくらインターネットの[さくらのVPS](https://vps.sakura.ad.jp/)を利用し､Postfix + mailmanで登録者数2,000名規模のメーリングリストを運用しています｡
いままでは[bounceHammer](http://bouncehammer.jp/)を利用してバウンスメールの解析と集計を行っていました｡しかし､2016年にEOLとなったためバウンスメールの解析をbounceHammerの後継である[Sisimai](https://libsisimai.org/)で行うことにしました｡Sisimaiには管理用のWebアプリケーションが用意されていないため､クックパッドが公開している[sisito](https://github.com/cookpad/sisito)を利用しました｡

# 参考情報
- [Sisimai: Mail Analyzing Interfac](https://libsisimai.org/)
- [Sisimaiを使ったバウンスメールの管理](https://techlife.cookpad.com/entry/2017/05/15/000000)
- [Sisimaiによるバウンスメール解析の自動化](https://techblog.raccoon.ne.jp/archives/49496190.html)
- [クックパッドさん開発のsisitoを動かしてみた](https://azumakuniyuki.hatenablog.com/entry/try-to-run-sisito)
- [Install Ruby On Rails on Ubuntu 22.04 Jammy Jellyfish](https://gorails.com/setup/ubuntu/22.04)
- [Ubuntu 20.04にMariaDBをインストールする方法](https://www.digitalocean.com/community/tutorials/how-to-install-mariadb-on-ubuntu-20-04-ja)
- [instance method Net::POP3#enable_ssl](https://docs.ruby-lang.org/ja/latest/method/Net=3a=3aPOP3/i/enable_ssl.html)
- [How to get response OK from POP3 server using ruby](https://stackoverflow.com/questions/34241893/how-to-get-response-ok-from-pop3-server-using-ruby)
- [アプリ パスワードでログインする](https://support.google.com/mail/answer/185833?hl=ja)
- [Gmail（Google）アカウント - アプリパスワードの生成（作成）](https://pc-karuma.net/google-account-generate-app-password/)
- [Gem パッケージのインストール先ディレクトリを調べる](https://maku77.github.io/ruby/gem/gem-environment-gemdir.html)
- [【Rails】migrationの書き方 カラム追加・変更・削除](https://310nae.com/rails-migration/)
- [Active Record マイグレーション](https://railsguides.jp/active_record_migrations.html)
- [Ubuntu 20.04 LTS に Docker と Docker Compose をインストールする](https://4mo.co/install-docker-and-compose/)
- [mysql8でユーザーに権限を付与](https://mebee.info/2019/09/28/post-1518/)
- [Ubuntu 20.04にMySQLをインストールする方法](https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04-ja)
- [ユーザーに権限を設定する(GRANT文)](https://www.javadrive.jp/mysql/user/index6.html)
- [Docker環境でCould not find 'bundler'のエラーが出た時の対処法](https://book-reviews.blog/could-not-find-bundler-on-Docker/)


# ゴール
Sisimaiでバウンスメールを解析し､sisitoで集計を行えるようにします｡

![sisito Web管理画面](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61809/15125190-ce10-f8b7-0267-e6fac7c20873.png)

# バウンスメール解析､集計の流れ
1. メール配信サーバーから受信者宛にメールが送信されます｡
1. バウンスしたメールはメール配信サーバーに戻されます｡
1. メール配信サーバーで運用しているmailmanの設定により､バウンスメール受信用のgmailアドレスに転送されます｡
1. Sisimai, sisitoを運用しているサーバーでgmailのメールボックスからバウンスメールをPOP3Sで取得します｡
1. Sisimaiでバウンスメールを解析し､sisitoで集計を行います｡

gmailを経由せず直接POP3でバウンスメールを取得する方法もありますが､POP3サーバーの構築と運用が手間だったのでgmailに転送しています｡

![バウンスメール解析､集計の流れ](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61809/a2fd4986-0170-7ffd-5fbd-d162212c379c.png)


# 環境構築
## 前提条件
以下のOS､ミドルウェアを使用します｡
- Ubuntu 22.04.1 LTS (GNU/Linux 5.15.0-52-generic x86_64)
- ruby 3.1.2p20 (2022-04-12 revision 4491bb740a) [x86_64-linux]
- Rails 7.0.4
- mysql  Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

### OSのバージョン
OSはUbuntu 22.04.1 LTを使用します｡

```console
$ cat /etc/os-release
PRETTY_NAME="Ubuntu 22.04.1 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.1 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy
```

### Rubyや必要なライブラリなどのインストール

OSを最新の状態にし､必要なライブラリをインストールします｡

```console
$ sudo apt-get update && 
  sudo apt-get upgrade && 
  sudo apt-get install -y --no-install-recommends \
      build-essential \
      libxml2-dev \
      libxslt-dev \
      mysql-client \
      mysql-server \
      libmysqlclient-dev \
      nodejs \
      libsqlite3-dev \
      liblzma-dev \
      zlib1g-dev \
      libssl-dev \
      libreadline-dev \
      libyaml-dev \
      libxslt1-dev \
      software-properties-common \
      libffi-dev \
      tzdata \
      jq && \
  sudo apt-get clean && \
  sudo rm -rf /var/lib/apt/lists/*
```

必要に応じてタイムゾーンの設定を行います｡

```console
$ sudo cp /usr/share/zoneinfo/Asia/Tokyo /etc/localtime
$ sudo dpkg-reconfigure -f noninteractive tzdata
```

rbenvでRubyをインストールします｡

```console
$ git clone https://github.com/rbenv/rbenv.git ~/.rbenv
$ echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> ~/.bashrc
$ echo 'eval "$(rbenv init -)"' >> ~/.bashrc
$ exec $SHELL
```

```console
$ git clone https://github.com/rbenv/ruby-build.git ~/.rbenv/plugins/ruby-build
$ echo 'export PATH="$HOME/.rbenv/plugins/ruby-build/bin:$PATH"' >> ~/.bashrc
$ exec $SHELL
```

Ruby 3.1.2をインストールします｡

```console
$ rbenv install 3.1.2
$ rbenv global 3.1.2
$ gem install bundler
```


### Sisimai, sisitoのインストール

sisitoのGitリポジトリにあるREADME.mdを参考にインストールを行います｡ここでは､本家のリポジトリをforkしRubyやRailsのバージョンを上げたものを使用します｡
本家のリポジトリにあるsisitoはすでにEOLとなっているRuby 2.3.0を前提としていることや､定義されているDBスキーマでは一部バウンスメールのエラーメッセージが格納できないため､それらを修正しました｡
SisimaiはGemfileで定義されているため､個別のインストールは不要です｡

```console
$ git clone https://github.com/revsystem/sisito.git -b Rails_v7.0.4
$ cd ./sisito
$ bundle update --bundler
$ bundle install
```

### MySQLの準備

MySQLを有効化し起動します｡

```console
$ sudo systemctl enable mysql
$ sudo systemctl start mysql
```


cloneしたディレクトリの config/database.yml に3つのDB接続情報があります｡ここでは､専用の bounce ユーザーを作成し､それを config/database.yml に設定します｡
以下は､ユーザー名を `bounce` ､パスワードを `user_password_1234` とした例です｡


```SQL
 CREATE USER 'bounce'@'localhost' IDENTIFIED BY 'user_password_1234';
 GRANT ALL PRIVILEGES ON sisito_development.* TO 'bounce'@'localhost' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON sisito_test.* TO 'bounce'@'localhost' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON sisito_production.* TO 'bounce'@'localhost' WITH GRANT OPTION;
```

sisito_test, sisito_productionも同様にusername,passwordを作成したもので書き換えます｡

```config:config/database.yml
development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sisito_development
  pool: 5
  username: bounce
  password: user_password_1234
  host: localhost
  variables:
    sql_mode: TRADITIONAL # required
```

sisitoのDBスキーマに従い､DBを作成します｡
```
$ bundle exec rails db:create
$ bundle exec rails db:migrate
```

# Sisimaiでバウンスメールを解析してみる

Sisimaiのgemがインストールされたディレクトリ以下にバウンスメールのサンプルが格納された set-of-emails というディレクトリがあります｡このサンプルのひとつを使ってバウンスメールを解析してみます｡
Gemがインストールされているディレクトリを探します｡

```console
$ gem environment gemdir
/home/user1/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0
```

サンプルバウンスメールを解析します｡
```console
$ ruby -r 'sisimai' -e 'puts Sisimai.dump($*.shift)' "/home/user1/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/sisimai-4.25.14/set-of-emails/maildir/bsd/lhost-gsuite-01.eml" | jq .
```

以下のような結果が得られます｡解析結果のデータ構造は[Sisimai::Dataのデータ構造](https://libsisimai.org/ja/data/)にドキュメントが用意されています｡
この解析結果からバウンスの理由は`userunknown`､相手先のメールシステムは`GSuite` などといったことが分かります｡sisitoはこのような解析結果を集計し､Web管理画面に表示します｡

```json
[
  {
    "catch": "",
    "token": "b5f9067b3aafc728880f7352d85f21a563974b71",
    "lhost": "",
    "rhost": "192.0.2.222",
    "alias": "",
    "listid": "",
    "reason": "userunknown",
    "action": "failed",
    "origin": "/home/user1/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/sisimai-4.25.14/set-of-emails/maildir/bsd/lhost-gsuite-01.eml",
    "subject": "Nyaaan",
    "messageid": "00222222-2222-2222-2222-FF00FFFF0000@example.jp",
    "replycode": "550",
    "smtpagent": "GSuite",
    "softbounce": 0,
    "smtpcommand": "",
    "destination": "example.de",
    "senderdomain": "example.jp",
    "feedbacktype": "",
    "diagnosticcode": "** Address not found ** Your message wasn't delivered to kijitora@example.de because the address couldn't be found. Check for typos or unnecessary spaces and try again.The response from the remote server was: 550 #5.1.0 Address rejected.",
    "diagnostictype": "SMTP",
    "deliverystatus": "5.1.0",
    "timezoneoffset": "-0700",
    "addresser": "sironeko@example.jp",
    "recipient": "kijitora@example.de",
    "timestamp": 1490384096
  }
]
```


## Gmailのメールボックスからメールを取得する｡

あらかじめ用意したgmailアドレスのメールボックスにバウンスメールを転送しているので､Sisimai,sisitoを運用しているサーバーからPOP3Sで接続しバウンスメールを取得します｡

メールを取得するスクリプトを配置したディレクトリをスクリプト内の basedir に設定します｡
userにはgmailアドレスを設定し､passwordにはgmailアカウントのパスワードではなく`アプリパスワード`を設定します｡
`アプリパスワード`は[アプリ パスワードを作成、使用する](https://support.google.com/mail/answer/185833?hl=ja#:~:text=%E3%83%87%E3%83%90%E3%82%A4%E3%82%B9%E3%81%AB%E5%88%87%E3%82%8A%E6%9B%BF%E3%81%88%E3%82%8B-,%E3%82%A2%E3%83%97%E3%83%AA%20%E3%83%91%E3%82%B9%E3%83%AF%E3%83%BC%E3%83%89%E3%82%92%E4%BD%9C%E6%88%90%E3%80%81%E4%BD%BF%E7%94%A8%E3%81%99%E3%82%8B,-2%20%E6%AE%B5%E9%9A%8E%E8%AA%8D%E8%A8%BC)にある手順で作成できます｡

gmailの設定画面でPOPを有効にします｡(`すべてのメールでPOPを有効にする`もしくは､`今後受信するメールでPOPを有効にする`を選択)
![gmail_pop.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61809/8d1f37d0-3068-750c-f926-093dbd4d369c.png)


```ruby:bounce-receiver.rb
# config: utf-8
require 'net/pop'
basedir = '/home/user1/sisito'
mailstore = basedir + '/mailstore'
host = 'pop.gmail.com'
port = 995
user = 'YOUR-ACCOUNT@gmail.com'
password = 'アプリパスワード'
# Connect to pop3 server
Net::POP3.enable_ssl(OpenSSL::SSL::VERIFY_NONE)
pop = Net::POP3.new(host, port)
pop.start(user, password)
# Receive mails
unless pop.mails.empty?
  pop.mails.each do |mail|
    filename = "#{mailstore}/" + Time.now.strftime('%Y%m%d%H%M%S%L') + ".eml"
    fd = File.open(filename, 'w')
    fd.write mail.pop
    mail.delete
    sleep 1
  end
end
pop.finish
```

上記で設定したbasedir以下にメールを格納するディレクトリを作成します｡
```console
$ mkdir ./mailstore
```

スクリプトを実行しメールを受信します｡gmailのPOP3Sは一度のアクセスで250通しか受信できないため､バウンスメールが250通以上ある場合はこのスクリプトを複数回実行する必要があります｡

```console
$ ruby bounce-receiver.rb
```

取得したメールはこのように保存されます｡
```console
$ ls -l mailstore
-rw-r--r-- 1 user1 user1 20158 10月 15 14:18 mailstore/20221015141809279.eml
-rw-r--r-- 1 user1 user1  8710 10月 15 14:18 mailstore/20221015141811803.eml
-rw-r--r-- 1 user1 user1 14593 10月 15 14:18 mailstore/20221015141813379.eml
-rw-r--r-- 1 user1 user1 13624 10月 15 14:18 mailstore/20221015141814968.eml
-rw-r--r-- 1 user1 user1 13693 10月 15 14:18 mailstore/20221015141816531.eml
```

## バウンスメールをsisimaiで解析し､MySQLに結果を登録する｡
[Gitリポジトリ](https://github.com/cookpad/sisito)のREADMEに記載されている[サンプルスクリプト Bounced Mail Collect Script Example](https://github.com/cookpad/sisito#bounced-mail-collect-script-example) を使ってバウンスメールSisimaiで解析しMySQLに結果を登録します｡

スクリプト内の `MAIL_DIR` を mailstore のパスに変更します｡もしくは､https://gist.github.com/azumakuniyuki/3ad4b3a531b7ac6016e3f96232e3cc6a#file-update-sisito-db のようにMAIL_DIRを$*.shiftに変更して引数でパスを指定できるようにすることもできます｡
`mysql = Mysql2::Client.new()`にあるusernameとpasswordをMySQLのCREATE USERでユーザーを作成したものに変更します｡

変更後のスクリプトは以下のとおりです｡

```ruby:update-sisito-db.rb
#!/usr/bin/env ruby
require 'fileutils'
require 'sisimai'
require 'mysql2'
require 'tmpdir'

COLUMNS = %w(
  timestamp
  lhost
  rhost
  alias
  listid
  reason
  action
  subject
  messageid
  smtpagent
  softbounce
  smtpcommand
  destination
  senderdomain
  feedbacktype
  diagnosticcode
  deliverystatus
  timezoneoffset
  addresser
  recipient
)

MAIL_DIR = $*.shift

def process(path, **options)
  Dir.mktmpdir do |tmpdir|
    FileUtils.mv(Dir["#{path}/*"], tmpdir)
    v = Sisimai.make(tmpdir, **options) || []
    v.each {|e| yield(e) }
  end
end

def insert(mysql, data)
  values = data.to_hash.values_at(*COLUMNS)
  addresseralias = data.addresser.alias
  addresseralias = data.addresser.address if addresseralias.empty?
  values << addresseralias
  columns = (COLUMNS + ['addresseralias', 'digest', 'created_at', 'updated_at']).join(?,)
  timestamp = values.shift
  values = (["FROM_UNIXTIME(#{timestamp})"] + values.map(&:inspect) + ['SHA1(recipient)', 'NOW()', 'NOW()']).join(?,)
  sql = "INSERT INTO bounce_mails (#{columns}) VALUES (#{values})"
  puts 'SQL = ' + sql
  mysql.query(sql)
end

mysql = Mysql2::Client.new(host: 'localhost', username: 'bounce', password: 'user_password_1234', database: 'sisito_development')

process(MAIL_DIR) do |data|
  insert(mysql, data)
end
```

スクリプトを実行します｡スクリプト実行が完了するとmailstore内のファイルはすべて削除されます｡(二重取り込み防止のため?)ただし､処理途中でエラーが発生した場合でもすべて削除されるため注意が必要です｡

```console
$ ruby ./update-sisito-db.rb /home/user1/sisito/mailstore
```

## sisitoを起動する｡

ここでは､sisitoが受け付けるポート番号を1080番としています｡
http://{このサーバーのIPアドレス}:1080/ にアクセスすると以下のような画面が表示されます｡

```console
$ bundle exec rails server -p 1080 -b 0.0.0.0
```

Web管理画面トップ
![sisito Web管理画面](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61809/15125190-ce10-f8b7-0267-e6fac7c20873.png)

バウンスメール一覧
バウンス理由での絞り込みや宛先アドレスで検索できます｡一覧では宛先アドレスが伏せ字になっていますが､ Show をクリックすると宛先アドレスが確認できます｡
![bounce一覧](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61809/34144bb2-1da5-efc4-e2e3-d7a4ea9aff24.png)

Show のリンク先や Admin 画面にはBasic認証が設定されています｡認証情報は､[config/sisito.yml](https://github.com/revsystem/sisito/blob/Rails_v7.0.4/config/sisito.yml)に定義されています｡


# Docker版

Docker版は､docker-compose.ymlやDockerfileがあるディレクトリで以下のコマンドを実行することで起動できます｡実行するとsisito-1, postfix-1, sisito_api-1, sisito-mysql-1というコンテナが起動します｡

```console
$ docker compose build
$ docker compose up
```

Webブラウザから http://localhost:3000/ にアクセスするとWeb管理画面が表示されます｡
[sisito-api](https://github.com/cookpad/sisito-api)が含まれているため､[Sisimaiを使ったバウンスメールの管理](https://techlife.cookpad.com/entry/2017/05/15/000000)にある[新バウンスメール管理システム](https://techlife.cookpad.com/entry/2017/05/15/000000#:~:text=%E4%BD%9C%E6%88%90%E3%81%97%E3%81%BE%E3%81%97%E3%81%9F%E3%80%82-,%E6%96%B0%E3%83%90%E3%82%A6%E3%83%B3%E3%82%B9%E3%83%A1%E3%83%BC%E3%83%AB%E7%AE%A1%E7%90%86%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0,-Sisimai%E3%81%A8Sisito)で利用するための構成のようです｡

Dockerfileは[Dockerfile のベスト・プラクティス](https://docs.docker.jp/develop/develop-images/dockerfile_best-practices.html)を参考にいろいろ削ったり足したりしています｡


# Rails 5.1.0版

本家リポジトリにあるものに対してDBスキーマの変更とmysql2 gemのバージョンのみ変更したものがこちらです｡
- https://github.com/revsystem/sisito/tree/Rails_v5.1.0

## Raspberry Pi 4B (Raspbian OS)で利用するには

上記のRails 5.1.0版をgit cloneし､mysql-server, libmysqlclient-devではなくmariadb-server, libmariadb-devをインストールします｡

```console
$ sudo apt-get update &&
  sudo apt-get upgrade &&
  sudo apt-get install -y --no-install-recommends \
      build-essential \
      libxml2-dev \
      libxslt-dev \
      mariadb-server \
      libmariadb-dev \
      nodejs \
      libsqlite3-dev \
      liblzma-dev \
      zlib1g-dev \
      libssl-dev \
      libreadline-dev \
      libyaml-dev \
      libxslt1-dev \
      software-properties-common \
      libffi-dev \
      tzdata \
      jq && \
  sudo apt-get clean && \
  sudo rm -rf /var/lib/apt/lists/*
```

MariaDBの初期設定後、ユーザーをパスワードを設定します。

```
$ sudo mariadb
GRANT ALL ON sisito_development.* TO 'bounce'@'localhost' IDENTIFIED BY 'user_password_1234' WITH GRANT OPTION;
GRANT ALL ON sisito_test.* TO 'bounce'@'localhost' IDENTIFIED BY 'user_password_1234' WITH GRANT OPTION;
GRANT ALL ON sisito_production.* TO 'bounce'@'localhost' IDENTIFIED BY 'user_password_1234' WITH GRANT OPTION;
```