Postfix and Courier Installation using MySQL
From Debian Wiki
Introduction
This HOWTO describes how to install a mail server, using Postfix, that is based on virtual users and domains - i.e. users and domains that are stored in a MySQL database. We'll also cover the installation and configuration of Courier (Courier-POP3, Courier-IMAP), so that Courier can authenticate against the same MySQL database that Postfix uses.
The resulting Postfix server is capable of SMTP-AUTH and quota. Quota is not built into Postfix but we'll patch your Postfix appropriately and prevent apt from upgrading and breaking Postfix in the future. Passwords are stored in encrypted form in the database.
This HOWTO is meant as a practical guide which I used to setup my own system. It does not cover the theoretical backgrounds as they are treated in a lot of other documents available on the web.
Requirements
- A Debian Etch base installation - Installation HOWTO here.
- A working version of MySQL, phpMyAdmin and PHP - Installation HOWTO here.
- Root access to your server.
Pre-Installation
Before proceeding to install, update the necessary packages in Debian with these commands.
apt-get update apt-get upgrade
Install Postfix, Courier and Saslauthd
Installing Postfix, Courier and Saslauthd is as easy as running this one simple command:
apt-get install postfix postfix-mysql postfix-doc courier-authdaemon courier-authlib-mysql courier-pop courier-pop-ssl courier-imap courier-imap-ssl libsasl2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl
Ensure that the above command is run in one line (i.e. don't break it up)!
You will be asked a few questions:
General type of configuration? <-- Internet site Where should mail for root go? <-- NONE Mail name? <-- server1.example.com (replace according to your server) Other destinations to accept mail for? (blank for none) <-- server1.example.com, localhost, localhost.localdomain Force synchronous updates on mail queue? <-- No SSL certificate required <-- Ok
Apply Quota Patch To Postfix
Falko Timme's original documentation was a little outdated for Debian Etch and my initial trials didn't work. I've managed to figure out how to patch Postfix 2.3.4-3 as follows.
We have to get the Postfix sources, patch it with the quota patch, build new Postfix .deb packages and install those .deb packages:
apt-get install build-essential dpkg-dev fakeroot debhelper libdb4.2-dev libgdbm-dev libldap2-dev libpcre3-dev libmysqlclient15-dev libssl-dev libsasl2-dev postgresql-dev po-debconf dpatch
NB: Run the above command in one line i.e. don't break it up.
cd /usr/src apt-get source postfix wget http://vda.sourceforge.net/VDA/postfix-2.3.4-vda.patch.gz gunzip postfix-2.3.4-vda.patch.gz cd postfix-2.3.4 patch -p1 < ../postfix-2.3.4-vda.patch.gz dpkg-buildpackage cd .. dpkg -i postfix_2.3.4-3_i386.deb dpkg -i postfix-mysql_2.3.4-3_i386.deb
Prevent APT from upgrading Postfix
I like to keep my system up to date however patching Postfix requires us to prevent apt from trying to upgrade Postfix and in turn break our installation. Here's what I did:
vim /etc/apt/preferences
Add the following to this new file:
Package: postfix Pin: version 2.3.4 Pin-Priority: 1001 Package: postfix-mysql Pin: version 2.3.4 Pin-Priority: 1001 Package: postfix-dev Pin: version 2.3.4 Pin-Priority: 1001
Save this new file and run the following:
apt-get update apt-get upgrade
If all went well you should see the following output:
The following packages have been kept back:
postfix postfix-dev postfix-mysql
0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
Create The MySQL Database For Postfix/Courier
Let's create a database for Postfix and Courier called mail:
mysqladmin -u root -p create mail
Next, we go to the MySQL shell:
mysql -u root -p
In the MySQL shell, we create the user mail_admin with the passwort mail_admin_password (replace it with your own password) who has SELECT, INSERT, UPDATE and DELETE privileges on the mail database. This user will be used by Postfix and Courier to connect to the mail database:
GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost' IDENTIFIED BY 'mail_admin_password'; GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost.localdomain' IDENTIFIED BY 'mail_admin_password'; FLUSH PRIVILEGES;
Still in the MySQL shell, we create the tables that Postfix and Courier need:
USE mail; CREATE TABLE domains ( domain varchar(50) NOT NULL, PRIMARY KEY (domain) ) TYPE=MyISAM;
CREATE TABLE forwardings ( source varchar(80) NOT NULL, destination TEXT NOT NULL, PRIMARY KEY (source) ) TYPE=MyISAM;
CREATE TABLE users ( email varchar(80) NOT NULL, password varchar(20) NOT NULL, quota INT(10) DEFAULT '10485760', PRIMARY KEY (email) ) TYPE=MyISAM;
CREATE TABLE transport ( domain varchar(128) NOT NULL default , transport varchar(128) NOT NULL default , UNIQUE KEY domain (domain) ) TYPE=MyISAM;
quit;
MySQL Database Structure
The domains table will store each virtual domain that Postfix should receive emails for (e.g. example.com).
| domain |
|---|
| example.com |
The forwardings table is for aliasing one email address to another, e.g. forward emails for info@example.com to sales@example.com.
| source | destination |
|---|---|
| info@example.com | sales@example.com |
The users table stores all virtual users (i.e. email addresses, because the email address and user name is the same) and passwords (in encrypted form!) and a quota value for each mail box (in this example the default value is 10485760 bytes which means 10MB).
| password | quota | |
|---|---|---|
| info@example.com | No9.E4skNvGa. | 10485760 |
The transport table is optional and is for advanced users. It allows us to forward mails for single users, whole domains or all mails to another server. For example,
| domain | transport |
|---|---|
| example.com | smtp:[1.2.3.4] |
would forward all emails for example.com via the smtp protocol to the server with the IP address 1.2.3.4 (the square brackets [] mean "do not make a lookup of the MX DNS record" (which makes sense for IP addresses...). If you use a fully qualified domain name (FQDN) instead you would not use the square brackets.).
Configure Postfix
Now we have to tell Postfix where it can find all the information in the database. Therefore we have to create six text files. You will notice that I tell Postfix to connect to MySQL on the IP address 127.0.0.1 instead of localhost. This is because Postfix is running in a chroot jail and does not have access to the MySQL socket which it would try to connect if I told Postfix to use localhost. If I use 127.0.0.1 Postfix uses TCP networking to connect to MySQL which is no problem even in a chroot jail (the alternative would be to move the MySQL socket into the chroot jail which causes some other problems).
Please make sure that /etc/mysql/my.cnf contains the following line:
bind-address = 127.0.0.1
so that MySQL allows connections on 127.0.0.1 (restart MySQL if you have to make changes to /etc/mysql/my.cnf).
Now let's create our six text files.
/etc/postfix/mysql-virtual_domains.cf
user = mail_admin password = mail_admin_password dbname = mail table = domains select_field = 'virtual' where_field = domain hosts = 127.0.0.1
/etc/postfix/mysql-virtual_forwardings.cf
user = mail_admin password = mail_admin_password dbname = mail table = forwardings select_field = destination where_field = source hosts = 127.0.0.1
/etc/postfix/mysql-virtual_mailboxes.cf
user = mail_admin password = mail_admin_password dbname = mail table = users select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') where_field = email hosts = 127.0.0.1
/etc/postfix/mysql-virtual_email2email.cf
user = mail_admin password = mail_admin_password dbname = mail table = users select_field = email where_field = email hosts = 127.0.0.1
/etc/postfix/mysql-virtual_transports.cf
user = mail_admin password = mail_admin_password dbname = mail table = transport select_field = transport where_field = domain hosts = 127.0.0.1
/etc/postfix/mysql-virtual_mailbox_limit_maps.cf
user = mail_admin password = mail_admin_password dbname = mail table = users select_field = quota where_field = email hosts = 127.0.0.1
Next run the following commands:
chmod o= /etc/postfix/mysql-virtual_*.cf chgrp postfix /etc/postfix/mysql-virtual_*.cf
Now we create a user and group called vmail with the home directory /home/vmail. This is where all mail boxes will be stored.
groupadd -g 5000 vmail useradd -g vmail -u 5000 vmail -d /home/vmail -m
Next we do some Postfix configuration. Ensure that you replace server1.example.com below with your own server name otherwise Postfix will not work properly!
postconf -e 'myhostname = server1.example.com' postconf -e 'mydestination = server1.example.com, localhost, localhost.localdomain' postconf -e 'mynetworks = 127.0.0.0/8' postconf -e 'virtual_alias_domains =' postconf -e ' virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf' postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf' postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf' postconf -e 'virtual_mailbox_base = /home/vmail' postconf -e 'virtual_uid_maps = static:5000' postconf -e 'virtual_gid_maps = static:5000' postconf -e 'smtpd_sasl_auth_enable = yes' postconf -e 'broken_sasl_auth_clients = yes' postconf -e 'smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination' postconf -e 'smtpd_use_tls = yes' postconf -e 'smtpd_tls_cert_file = /etc/postfix/smtpd.cert' postconf -e 'smtpd_tls_key_file = /etc/postfix/smtpd.key' postconf -e 'transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf' postconf -e 'virtual_create_maildirsize = yes' postconf -e 'virtual_mailbox_extended = yes' postconf -e 'virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf' postconf -e 'virtual_mailbox_limit_override = yes' postconf -e 'virtual_maildir_limit_message = "The user you are trying to reach is over quota."' postconf -e 'virtual_overquota_bounce = yes' postconf -e 'proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps'
Configure Saslauthd
mkdir -p /var/spool/postfix/var/run/saslauthd
Edit /etc/default/saslauthd. Remove the # in front of START=yes and add the line PARAMS="-m /var/spool/postfix/var/run/saslauthd -r". The file should then look like this:
# This needs to be uncommented before saslauthd will be run automatically START=yes # You must specify the authentication mechanisms you wish to use. # This defaults to "pam" for PAM support, but may also include # "shadow" or "sasldb", like this: # MECHANISMS="pam shadow" MECHANISMS="pam" PARAMS="-m /var/spool/postfix/var/run/saslauthd -r"
We must also edit /etc/init.d/saslauthd and change the location of saslauthd's PID file. Change the value of PIDFILE to /var/spool/postfix/var/run/${NAME}/saslauthd.pid:
PIDFILE="/var/spool/postfix/var/run/${NAME}/saslauthd.pid"
Then create the file /etc/pam.d/smtp. It should contain only the following two lines (be sure to fill in your correct database details):
auth required pam_mysql.so user=mail_admin passwd=mail_admin_password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1 account sufficient pam_mysql.so user=mail_admin passwd=mail_admin_password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1
Next create the file /etc/postfix/sasl/smtpd.conf remembering to replace the mysql password with your own. It should look like this:
pwcheck_method: saslauthd mech_list: plain login allow_plaintext: true auxprop_plugin: mysql sql_hostnames: 127.0.0.1 sql_user: mail_admin sql_passwd: mail_admin_password sql_database: mail sql_select: select password from users where email = '%u'
Then restart Postfix and Saslauthd:
/etc/init.d/postfix restart postfix check /etc/init.d/saslauthd restart
Configure Courier
Now we have to tell Courier that it should authenticate against our MySQL database. First, edit /etc/courier/authdaemonrc and change the value of authmodulelist so that it reads:
authmodulelist="authmysql"
Then edit /etc/courier/authmysqlrc. It should look like this (again, make sure to fill in the correct database details):
MYSQL_SERVER localhost MYSQL_USERNAME mail_admin MYSQL_PASSWORD mail_admin_password MYSQL_PORT 0 MYSQL_DATABASE mail MYSQL_USER_TABLE users MYSQL_CRYPT_PWFIELD password #MYSQL_CLEAR_PWFIELD password MYSQL_UID_FIELD 5000 MYSQL_GID_FIELD 5000 MYSQL_LOGIN_FIELD email MYSQL_HOME_FIELD "/home/vmail" MYSQL_MAILDIR_FIELD CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') #MYSQL_NAME_FIELD MYSQL_QUOTA_FIELD quota
Then restart Courier:
/etc/init.d/courier-authdaemon restart /etc/init.d/courier-imap restart /etc/init.d/courier-imap-ssl restart /etc/init.d/courier-pop restart /etc/init.d/courier-pop-ssl restart
By running:
telnet localhost pop3
you can see if your POP3 server is working correctly. It should give back +OK Hello there. (Type quit to get back to the Linux shell.)
Quota Notifications
If you want to get notifications about all the email accounts that are over quota, then do this:
cd /usr/local/sbin/ wget http://puuhis.net/vhcs/quota.txt mv quota.txt quota_notify chmod 755 quota_notify
Open /usr/local/sbin/quota_notify and edit the variables at the top:
my $POSTFIX_CF = "/etc/postfix/main.cf";
my $MAILPROG = "/usr/sbin/sendmail -t";
my $WARNPERCENT = 80;
my @POSTMASTERS = ('postmaster@example.com');
my $CONAME = 'ISP.tld';
my $COADDR = 'postmaster@example.com';
my $SUADDR = 'postmaster@example.com';
my $MAIL_REPORT = 1;
my $MAIL_WARNING = 1;
Once done run:
crontab -e
And add the following cron job for the script:
0 0 * * * /usr/local/sbin/quota_notify &> /dev/null
Testing Postfix
To see if Postfix is ready for SMTP-AUTH run:
telnet localhost 25
After you have established the connection to your postfix mail server type
ehlo localhost
If you see the following lines then everything is configured correctly:
250-AUTH LOGIN PLAIN 250-AUTH=LOGIN PLAIN
Adding to the Database
To populate the database you can use the MySQL shell:
mysql -u root -p USE mail;
At a minimum you have to create entries in the domain and user tables:
INSERT INTO `domains` (`domain`) VALUES ('example.com');
INSERT INTO `users` (`email`, `password`, `quota`) VALUES ('sales@example.com', ENCRYPT('secret'), 10485760);
To add entries into the other two tables you would do the following:
INSERT INTO `forwardings` (`source`, `destination`) VALUES ('info@example.com', 'sales@example.com');
INSERT INTO `transport` (`domain`, `transport`) VALUES ('example.com', 'smtp:mail.example.com');
To leave the MySQL shell, type
quit;
Forwarding Options
The forwardings table can have entries as follows:
| source | destination | description |
|---|---|---|
| info@example.com | sales@example.com | Redirects emails for info@example.com to sales@example.com |
| @example.com | thomas@example.com | Creates a Catch-All account for thomas@example.com. All emails to example.com will arrive at thomas@example.com, except those that exist in the users table (i.e., if sales@example.com exists in the users table, mails to sales@example.com will still arrive at sales@example.com). |
| @example.com | @anotherdomain.com | This redirects all emails to example.com to the same user at anotherdomain.tld. E.g., emails to thomas@example.com will be forwarded to thomas@anotherdomain.tld. |
| info@example.com | sales@example.com, billing@anotherdomain.com | Forward emails for info@example.com to two or more email addresses. All listed email addresses under destination receive a copy of the email. |
Transport Options
The transport table can have entries like these:
| domain | transport | description |
|---|---|---|
| example.com | : | Delivers emails for example.com locally. This is as if this record would not exist in this table at all. |
| example.com | smtp:mail.anotherdomain.com | Delivers all emails for example.com via smtp to the server mail.anotherdomain.com. |
| example.com | smtp:mail.anotherdomain.com:2025 | Delivers all emails for example.com via smtp to the server mail.anotherdomain.com, but on port 2025, not 25 which is the default port for smtp. |
| example.com | smtp:[1.2.3.4], smtp:[1.2.3.4]:2025, smtp:[mail.anotherdomain.tld] | The square brackets prevent Postfix from doing lookups of the MX DNS record for the address in square brackets. Makes sense for IP addresses. |
| .example.com | smtp:mail.anotherdomain.com | Mail for any subdomain of example.com is delivered to mail.anotherdomain.com. |
| * | smtp:mail.anotherdomain.com | All emails are delivered to mail.anotherdomain.com. |
| joe@example.com | smtp:mail.anotherdomain.com | Emails for joe@example.com are delivered to mail.anotherdomain.com. |
Please keep in mind that the order of entries in the transport table is important! The entries will be followed from the top to the bottom.
Important: Postfix uses a caching mechanism for the transports, therefore it might take a while until you changes in the transport table take effect. If you want them to take effect immediately, run
postfix reload
after you have made your changes in the transport table.
References
Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP-AUTH, Quota, SpamAssassin, ClamAV) - by Falko Timme |