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