OpenBSD as a mail server

If Postfix is working fine, we can proceed to the next step and install MySQL. MySQL is the world's most popular open source database, combining performance, reliability and ease of use. It will ensure faster data access times and allow us to centralize configuration information that both Postfix and Courier-IMAP will need to access.

After the installation, you will find various sample configuration files in the /usr/local/share/mysql directory; choose the most suitable to your needs and copy it to /etc/my.cnf. E.g.:

# cp /usr/local/share/mysql/my-small.cnf /etc/my.cnf

4.1 Configuration

Using a socket file for Postfix to communicate with the database (which is the default configuration) may be a little tricky, because most Postfix processes will run in a chrooted environment, but some will not; as a consequence, by default, part of the Postfix processes will look for the socket file in /var/run/mysql/, while the others will look for it in the same path inside the Postfix chroot, /var/spool/postfix/var/run/mysql/.

That's why I prefer connecting to MySQL through the loopback network interface (or a physical interface, of course, if the database is running on a remote server). So I just leave the "skip networking" directive commented out in /etc/my.cnf and add the following lines in the [mysqld] section:

/etc/my.cnf

bind-address = 127.0.0.1
skip-external-locking

This also prevents MySQL from listening on the external network interfaces; note: the skip-external-locking directive should be used instead of the deprecated skip-locking.

As a first step after basic configuration, we then need to install the default databases, change the password of the MySQL root user (don't take my passwords as an example!) and answer a few questions:

# /usr/local/bin/mysql_install_db
[ ... ]
# /usr/local/bin/mysqld_safe &
[ ... ]
# /usr/local/bin/mysql_secure_installation
[ ... ]
Enter current password for root (enter for none): <Enter>
OK, successfully used password, moving on...
[ ... ]
Set root password? [Y/n] Y
New password: root
Re-enter new password: root
Password updated successfully!
[ ... ]
Remove anonymous users? [Y/n] Y
 ... Success!
[ ... ]
Disallow root login remotely? [Y/n] Y
 ... Success!
[ ... ]
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
[ ... ]
Reload privilege tables now? [Y/n] Y
 ... Success!
[ ... ]
#

/etc/rc.conf.local

pkg_scripts="mysqld postfix"

Next, we will hook up Postfix to the database. In particular, we will modify the value of a few parameters in the /etc/postfix/main.cf file:

/etc/postfix/main.cf

virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailboxes.cf
virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf

We will see in a moment the contents of those files, but first, we are going to create the database. Tables don't need to have any particular structure, since we will tell Postfix which queries to use to extract the data. Therefore, this will actually be just one among the many possible implementations: feel free to modify it according to your taste and needs.

Note: Postfix obtains the full pathname of the maildirs by joining the values of the virtual_mailbox_base and virtual_mailbox_maps parameters, while Courier-IMAP obtains it by joining the values of the MYSQL_HOME_FIELD and MYSQL_MAILDIR_FIELD parameters; so, we will create two separate fields (home and maildir) in the users table for these parameters to be specified.

# mysql -u root -p
password: root
mysql> CREATE DATABASE mail;
Query OK, 1 row affected (0.01 sec)

mysql> use mail
Database changed
mysql> CREATE TABLE domains (
    ->        id       INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->        domain   VARCHAR(255) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE users (
    ->        id       INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->        login    VARCHAR(255) NOT NULL UNIQUE,
    ->        name     VARCHAR(255) NOT NULL,
    ->        password CHAR(13) NOT NULL,
    ->        uid      SMALLINT NOT NULL DEFAULT 2000,
    ->        gid      SMALLINT NOT NULL DEFAULT 2000,
    ->        home     VARCHAR(255) NOT NULL DEFAULT '/var/vmail',
    ->        maildir  VARCHAR(255) NOT NULL,
    ->        quota    VARCHAR(10)  NOT NULL DEFAULT '10000000S');
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE alias_maps (
    ->        id       INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->        account  VARCHAR(255) NOT NULL UNIQUE,
    ->        alias    VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON mail.* to 'vmail'@'localhost' IDENTIFIED BY 'vmail';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO domains (domain) VALUES ('kernel-panic.it');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO users (login, name, password, maildir)
    -> VALUES ('d.mazzocchio@kernel-panic.it', 'Daniele Mazzocchio', ENCRYPT('danix'),
    ->         'kernel-panic.it/d.mazzocchio/');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO alias_maps (account, alias)
    -> VALUES ('postmaster@kernel-panic.it', 'postmaster@localhost.kernel-panic.it');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO alias_maps (account, alias)
    -> VALUES ('root@kernel-panic.it', 'root@localhost.kernel-panic.it');
Query OK, 1 row affected (0.00 sec)

Now let's take a brief look at the new Postfix configuration files, which include the configuration settings for MySQL.