Pulling mailserver data from MySQL

My first time setting up a Postfix mail server was on a FreeBSD system, and I found the process complex and badly documented. So I tried to limit the complexity by using text files rather than a database for the mailbox tables. That’s a pity: for the last few years I’ve had MySQL connected to Postfix and the whole thing is simple, bombproof, functional even with large numbers of users, and best of all there’s no need to restart Postfix each time you make a change. In fact the obvious thing to do would be to write scripts in Perl or PHP which updated that database to add users or virtual mailboxes – so obvious that Michael teases me every time he realises that I haven’t quite got around to doing that yet.

Disclaimer: what follows is intended to turn an already working postfix/courier installation into one based on mysql, not to install such a system from scratch.

Here’s an overview of the system:

  • Set up the database
  • Configure Postfix normally for virtual mailbox domains, but rather than using a text file like
    hash:/etc/postfix/vmailbox

    we specify a short config file which tells Postfix how to access the database – what table to use, which columns, and what credentials, and it sucks everything out of the database.

  • Tell saslauthd to use a database lookup to authenticate access to smtp.
  • I’m using Courier as an SSL POP server. In another article I’ll describe how to set up the certificates it’s straighforward and they’re now cheap enough (if you look around) that there’s no need to stick with self-signed. For now, though, Courier is set up normally except you configure it to look in the same tables for user info.

Detail, then.

Database

It makes sense to start with the database, because that will orient everything else. Create a database, a user with permissions on that database, and some tables as below.

mysql> create database maildb;
mysql> grant all on maildb.* to 'mail'@'localhost' identified by '<password>';

–> make the tables as below

mysql> describe users;
+---------+--------------+------+-----+-------------------------+-------+
| Field   | Type         | Null | Key | Default                 | Extra |
+---------+--------------+------+-----+-------------------------+-------+
| id      | varchar(128) | NO   | PRI | NULL                    |       |
| name    | varchar(255) | YES  |     | NULL                    |       |
| home    | varchar(128) | YES  |     | NULL                    |       |
| crypt   | varchar(128) | YES  |     | NULL                    |       |
| domain  | varchar(128) | YES  |     | NULL                    |       |
| uid     | int(5)       | YES  |     | 5000                    |       |
| gid     | int(5)       | YES  |     | 5000                    |       |
| maildir | varchar(128) | YES  |     | /var/spool/mail/virtual |       |
+---------+--------------+------+-----+-------------------------+-------+
mysql> describe virtual;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| address | varchar(255) | NO   | PRI | NULL    |       |
| goto    | varchar(255) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
mysql> describe domains;
+-----------+--------------+------+-----+----------+-------+
| Field     | Type         | Null | Key | Default  | Extra |
+-----------+--------------+------+-----+----------+-------+
| domain    | varchar(128) | NO   | PRI | NULL     |       |
| transport | varchar(128) | YES  |     | virtual: |       |
+-----------+--------------+------+-----+----------+-------+
mysql> describe bcc;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| address | varchar(128) | NO   | PRI | NULL    |       |
| sendto  | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

–> here’s a typical row in each table

mysql> select * from domains;
+-------------------------------+-----------+
| domain                        | transport |
+-------------------------------+-----------+
| ponderwell.net                | virtual:  |
+-------------------------------+-----------+
mysql> select * from bcc;
+------------------------------+-------------------------+
| address                      | sendto                  |
+------------------------------+-------------------------+
| [email protected]      | [email protected]    |
+------------------------------+-------------------------+
mysql> select * from users;
+---------------------------+------------+---------------+---------------+-----------------+------+------+-------------------------+
| id                        | name       | home          | crypt         | domain          | uid  | gid  | maildir                 |
+---------------------------+------------+---------------+---------------+-----------------+------+------+-------------------------+
| [email protected]  | enquiries  | enquiries/    | BSsoiBZJXfAvw | ponderwell.net  | 5000 | 5000 | /var/spool/mail/virtual |
+---------------------------+------------+---------------+---------------+-----------------+------+------+-------------------------+
mysql> select * from virtual;
+------------------------+--------------------------+
| address                | goto                     |
+------------------------+--------------------------+
| [email protected] | [email protected] |
+------------------------+--------------------------+

Don’t forget that the insert statement for users will read something like:

mysql> insert into users values ('[email protected]','enquiries','enquiries',ENCRYPT('password'),5000,5000, '/var/spool/mail/virtual');

Also, in the working system, send each new user a test email so as to get postfix to create that user’s mail store.

Postfix

For Postfix we have the following in postfix/main.cf:

# mysql stored virtual mailbox domains

virtual_mailbox_base = /var/spool/mail/virtual
virtual_mailbox_domains = mysql:/etc/postfix/mysql_domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf
virtual_alias_maps = mysql:/etc/postfix/mysql_virtual.cf
virtual_uid_maps = mysql:/etc/postfix/mysql_uid.cf
virtual_gid_maps = mysql:/etc/postfix/mysql_gid.cf
virtual_transport = virtual:

# send copy of some mail to external server - this is primarily to put a copy of POP mail on some people's phones

recipient_bcc_maps = mysql:/etc/postfix/mysql_bcc.cf

What’s in these mysql .cf files? Just the credentials and column names which postfix will use to pull the usual data but from mysql instead of a text file. Here is /etc/postfix/mysql_mailbox.cf

user=mail
password=<password>
dbname=maildb
table=users
select_field=home
where_field=id
hosts=127.0.0.1

and likewise mysql_domains.cf and mysql_uid.cf

user=mail
password=<password>
dbname=maildb
table=domains
select_field=domain
where_field=domain
hosts=127.0.0.1
user=mail
password=<password>
dbname=maildb
table=users
select_field=uid
where_field=id
hosts=127.0.0.1

While we’re here, make sure the saslauthd is being called.

smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
broken_sasl_auth_clients = yes
smtpd_sasl_path = smtpd

Saslauthd

We’ll use saslauthd to mediate smtp authentication for postfix. Edit /etc/pam.d/smtp which should read like this:

auth required pam_mysql.so user=mail passwd=<password> host=127.0.0.1 db=maildb table=users usercolumn=id passwdcolumn=crypt crypt=1
account sufficient pam_mysql.so user=mail passwd=<password> host=127.0.0.1 db=maildb table=users usercolumn=id passwdcolumn=crypt crypt=1

Now for the sasl conf, which is normally in postfix/sasl/smtpd.conf

pwcheck_method: saslauthd
mech_list: PLAIN LOGIN
allow_plaintext: true
auxprop_plugin: mysql
sql_hostnames: 127.0.0.1
sql_user: mail
sql_passwd: <password>
sql_database: maildb
sql_select: select crypt from users where id = '%u'
log_level: 100

Courier

Now Courier, in this case pop3d-ssl. I don’t see any point in allowing non-ssl login. The file courier/authdaemonrc needs this line:

authmodulelist="authmysql"

while the file courier/authmysqlrc tells courier where to find everything in the database. The significant lines read:

MYSQL_SERVER            localhost
MYSQL_USERNAME          mail
MYSQL_PASSWORD          <password>

# MYSQL_SOCKET          /var/run/mysqld/mysqld.sock

MYSQL_DATABASE          maildb

# MYSQL_CHARACTER_SET latin1

MYSQL_USER_TABLE        users

MYSQL_CRYPT_PWFIELD     crypt
#MYSQL_CLEAR_PWFIELD    clear

MYSQL_UID_FIELD         uid
MYSQL_GID_FIELD         gid
MYSQL_LOGIN_FIELD       id
MYSQL_HOME_FIELD        maildir
MYSQL_NAME_FIELD        name
MYSQL_MAILDIR_FIELD     concat(maildir,'/',home)

that maildir field is the tricky part, honestly.

Time to restart everything!

/etc/init.d/postfix restart
/etc/init.d/saslauthd restart
/etc/init.d/courier-pop-ssl