dovecot with MySQLでPostfixと連動させる

ubuntu

Postfixので使用するテーブルはこんな感じ

mysql> show columns from accounts
    -> ;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| uid      | int(11)      | NO   |     | 3000    |                |
| gid      | int(11)      | NO   |     | 3000    |                |
| password | varchar(255) | NO   |     |         |                |
| address  | varchar(255) | NO   |     |         |                |
| maildir  | varchar(255) | NO   |     |         |                |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

dovecoの設定

# vi /etc/dovecot/dovecot.conf
protocols = pop3
protocol pop3 {
      listen = *
   }
   
mail_location = maildir:~/

passdb sql {
    args = /etc/dovecot/dovecot-sql.conf
  }

userdb sql {
    args = /etc/dovecot/dovecot-sql.conf
  }

# vi /etc/dovecot/dovecot-sql.conf
driver = mysql

connect = dbname=postfix user=postfix host=localhost password=password
default_pass_scheme = PLAIN
password_query = SELECT password FROM accounts WHERE address = '%u'
user_query = SELECT CONCAT('/var/mail/', maildir) as home, uid, gid FROM accoun
ts WHERE address = '%u'

データベースの中身が

mysql> SELECT * FROM accounts;
+----+------+------+----------+----------------------+----------------+
| id | uid  | gid  | password | address              | maildir        |
+----+------+------+----------+----------------------+----------------+
|  1 | 3000 | 3000 | password | noman@test.localhost | maildir/noman/ |
+----+------+------+----------+----------------------+----------------+
1 row in set (0.02 sec)

の状態で

  • /var/mail/maildir/noman/cur
  • /var/mail/maildir/noman/new
  • /var/mail/maildir/noman/tmp

というディレクトリ作っておく。

最後にtelnetでログインできるかテスト

# telnet localhost pop3
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
+OK Dovecot ready.
user noman@test.localhost
+OK
pass password
+OK Logged in.
list
+OK 1 messages:
1 428
QUIT
+OK Logging out.
Connection closed by foreign host.