Virtual Users And Domains With Postfix, Courier And MySQL (Ubuntu 7.10) - Page 2
4 Create The MySQL Database For Postfix/Courier
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';
Still on the MySQL shell, we create the tables Postfix and Courier need:
CREATE TABLE domains (
CREATE TABLE forwardings (
CREATE TABLE users (
CREATE TABLE transport (
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 firstname.lastname@example.org to email@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).
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 18.104.22.168 (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:
If you had to modify /etc/mysql/my.cnf, please restart MySQL now:
netstat -tap | grep mysql
to make sure that MySQL is listening on 127.0.0.1 (localhost.localdomain):
root@server1:~# netstat -tap | grep mysql
Now let's create our six text files.
Then change the permissions and the group of these files:
chmod o= /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
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'
Afterwards we create the SSL certificate that is needed for TLS:
Country Name (2 letter code) [AU]: <-- Enter your Country Name (e.g., "DE").
Then change the permissions of the smtpd.key:
chmod o= /etc/postfix/smtpd.key