Virtual Users And Domains With Postfix, Courier And MySQL (Ubuntu 6.10 Edgy Eft) - Page 2

4 Create The MySQL Database For Postfix/Courier

By default, MySQL is installed without a root password, which we change immediately (replace yourrootsqlpassword with the password you want to use):

mysqladmin -u root password yourrootsqlpassword

Now we create a database called mail:

mysqladmin -u root -p create mail

Next, we go to the MySQL shell:

mysql -u root -p

On 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,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 on the MySQL shell, we create the tables 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;

As you may have noticed, with the quit; command we have left the MySQL shell and are back on the Linux shell.

The domains table will store each virtual domain that Postfix should receive emails for (e.g. example.com).

The forwardings table is for aliasing one email address to another, e.g. forward emails for [email protected] to [email protected].

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).

The transport table is optional, it is for advanced users. It allows to forward mails for single users, whole domains or all mails to another server. For example,

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.).

BTW, (I'm assuming that the IP address of your mail server system is 192.168.0.100) you can access phpMyAdmin over http://192.168.0.100/phpmyadmin/ in a browser and log in as mail_admin. Then you can have a look at the database. Later on you can use phpMyAdmin to administrate your mail server.

5 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:

vi /etc/mysql/my.cnf

[...]

bind-address            = 127.0.0.1

[...]

If you had to modify /etc/mysql/my.cnf, please restart MySQL now:

/etc/init.d/mysql restart

Run

netstat -tap

to make sure that MySQL is listening on 127.0.0.1 (localhost.localdomain):

root@server1:/usr/src# netstat -tap
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     4556/mysqld
tcp        0      0 *:smtp                  *:*                     LISTEN     12994/master
tcp6       0      0 *:imaps                 *:*                     LISTEN     4816/couriertcpd
tcp6       0      0 *:pop3s                 *:*                     LISTEN     4263/couriertcpd
tcp6       0      0 *:pop3                  *:*                     LISTEN     4198/couriertcpd
tcp6       0      0 *:imap2                 *:*                     LISTEN     4761/couriertcpd
tcp6       0      0 *:www                   *:*                     LISTEN     13283/apache2
tcp6       0      0 *:ssh                   *:*                     LISTEN     3192/sshd

Now let's create our six text files.

vi /etc/postfix/mysql-virtual_domains.cf

user = mail_admin

password = mail_admin_password

dbname = mail

query = SELECT domain AS virtual FROM domains WHERE domain='%s'

hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_forwardings.cf

user = mail_admin

password = mail_admin_password

dbname = mail

query = SELECT destination FROM forwardings WHERE source='%s'

hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_mailboxes.cf

user = mail_admin

password = mail_admin_password

dbname = mail

query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'

hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_email2email.cf

user = mail_admin

password = mail_admin_password

dbname = mail

query = SELECT email FROM users WHERE email='%s'

hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_transports.cf

user = mail_admin

password = mail_admin_password

dbname = mail

query = SELECT transport FROM transport WHERE domain='%s'

hosts = 127.0.0.1

vi /etc/postfix/mysql-virtual_mailbox_limit_maps.cf

user = mail_admin

password = mail_admin_password

dbname = mail

query = SELECT quota FROM users WHERE email='%s'

hosts = 127.0.0.1

Then change the permissions and the group of these files:

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. Go sure that you replace server1.example.com with a valid FQDN, otherwise your Postfix might 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_maildir_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'

Afterwards we create the SSL certificate that is needed for TLS:

cd /etc/postfix
openssl req -new -outform PEM -out smtpd.cert -newkey rsa:2048 -nodes -keyout smtpd.key -keyform PEM -days 365 -x509

<-- Enter your Country Name (e.g., "DE").
<-- Enter your State or Province Name.
<-- Enter your City.
<-- Enter your Organization Name (e.g., the name of your company).
<-- Enter your Organizational Unit Name (e.g. "IT Department").
<-- Enter the Fully Qualified Domain Name of the system (e.g. "server1.example.com").
<-- Enter your Email Address.

Then change the permissions of the smtpd.key:

chmod o= /etc/postfix/smtpd.key