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';

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

CREATE TABLE forwardings (
source varchar(80) NOT NULL,
destination TEXT NOT NULL,
PRIMARY KEY (source) )

email varchar(80) NOT NULL,
password varchar(20) NOT NULL,
quota INT(10) DEFAULT '10485760',

CREATE TABLE transport (
domain varchar(128) NOT NULL default '',
transport varchar(128) NOT NULL default '',
UNIQUE KEY domain (domain)


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.


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

source destination

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

email password quota No9.E4skNvGa. ("secret" in encrypted form) 10485760

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,

domain transport smtp:[]

would forward all emails for via the smtp protocol to the server with the IP address (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 you can access phpMyAdmin over 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 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 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            =


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

/etc/init.d/mysql restart


netstat -tap

to make sure that MySQL is listening on (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/

user = mail_admin

password = mail_admin_password

dbname = mail

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

hosts =

vi /etc/postfix/

user = mail_admin

password = mail_admin_password

dbname = mail

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

hosts =

vi /etc/postfix/

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 =

vi /etc/postfix/

user = mail_admin

password = mail_admin_password

dbname = mail

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

hosts =

vi /etc/postfix/

user = mail_admin

password = mail_admin_password

dbname = mail

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

hosts =

vi /etc/postfix/

user = mail_admin

password = mail_admin_password

dbname = mail

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

hosts =

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 with a valid FQDN, otherwise your Postfix might not work properly!

postconf -e 'myhostname ='
postconf -e 'mydestination =, localhost, localhost.localdomain'
postconf -e 'mynetworks ='
postconf -e 'virtual_alias_domains ='
postconf -e ' virtual_alias_maps = proxy:mysql:/etc/postfix/, mysql:/etc/postfix/'
postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/'
postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/'
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/'
postconf -e 'virtual_create_maildirsize = yes'
postconf -e 'virtual_maildir_extended = yes'
postconf -e 'virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/'
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. "").
<-- Enter your Email Address.

Then change the permissions of the smtpd.key:

chmod o= /etc/postfix/smtpd.key

Share this page:

5 Comment(s)

Add comment


From: at: 2007-05-10 09:06:55

page 1

debian etch uses a different postfix version so you ll find the patch for this version on the blow url

for compiling this newer postfix version you need to get a few more libraries

apt-get install lsb-release libcdb-dev

page 3






and add these lines to the end of the file (PARAMS has been changed to OPTIONS)

OPTIONS="-m /var/spool/postfix/var/run/saslauthd -r"

close file and run

dpkg-statoverride --add root sasl 710 /var/spool/postfix/var/run/saslauthd
adduser postfix sasl

page 4

NotifyClamd /etc/clamav/clamd.conf
already ok

From: at: 2007-10-24 00:48:02

Followed Falko's The Perfect Server - Ubuntu Gutsy Gibbon (Ubuntu 7.10) tutorial (which is excellent and very timely), but for postfix and apache, substituted the procedures in here.

The only part that did not work at all was the Postfix patch for quotas. Gutsy installed Postfix-2.4.5; I found what may be the updated quota patch here:

but was unable to build the .deb package; there were some issues with libdb4.3-dev among others. If someone would care to elaborate, it would be a great service.

I also skipped the spam/virus portions since I already use a hosted service for this.

Postfix / courier seem to be up and running as described.

 -- DrJohn

Some other quick notes on the installs:

 2. Install Postfix, Courier, Saslauthd, MySQL, phpMyAdmin


To install Postfix, Courier, Saslauthd, MySQL, and phpMyAdmin, we simply run:

<changed libsasl2 to libsasl2-2>

apt-get install postfix postfix-mysql postfix-doc mysql-client mysql-server courier-authdaemon courier-authlib-mysql courier-pop courier-pop-ssl courier-imap courier-imap-ssl postfix-tls libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl phpmyadmin apache2 libapache2-mod-php5 php5 php5-mysql

extra qestion appears re phpMyAdmin:

ââââââââââââââââââââââââ⤠Configuring phpmyadmin âââââââââââââââââââââââââ
   â phpMyAdmin supports any web server that PHP does, but this automatic   â
   â configuration process only supports Apache.                            â
   â                                                                        â
   â Web server to reconfigure automatically:                               â
   â                                                                        â
   â    [*] apache2                                                         â
   â    [ ] apache                                                          â
   â    [ ] apache-ssl                                                      â
   â    [ ] apache-perl                                                     â
   â                                                                        â
   â                                                                        â
   â                                 <Ok>                                   â
   â                                                                        â

create the SSL certificate that is needed for TLS:

<questions asked are different than the tutorial>

root@myserver/etc/postfix# openssl req -new -outform PEM -out smtpd.cert -newkey rsa:2048 -nodes -keyout smtpd.key -keyform PEM -days 365 -x509
Generating a 2048 bit RSA private key
writing new private key to 'smtpd.key'
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:MyState
Locality Name (eg, city) []:MyCity
Organization Name (eg, company) [Internet Widgits Pty Ltd]:MyCompany
Organizational Unit Name (eg, section) []:.
Common Name (eg, YOUR name) []:Postmaster
Email Address []


From: at: 2007-06-22 02:17:33

I was having trouble getting it to authenticate so turned on verbose logging for saslauthd. It revealed that the realm was not getting appended to the user and hence the sql select was returning zero records.
The bottom of /etc/defaults/saslauthd shows an "OPTIONS" line rather than "PARAMS". Checking the documentation for my version of saslauthd confirmed this.

Hence for Feisty 7.04 change:
  PARAMS="-m /var/spool/postfix/var/run/saslauthd -r"
  OPTIONS="-m /var/spool/postfix/var/run/saslauthd -r"

From: at: 2007-08-09 12:20:27

With Feisty 7.04 the dcc-client fails with unsatisfied dependencies.

The following line seems to work:

apt-get install dcc-common=1.2.74-2 dcc-client=1.2.74-2

Also the postfix patch for quota may not be needed  under Feisty.

From: at: 2007-08-09 12:23:09

This howto is an excellent tutorial. It has rocksolid step by step instructions, easy to follow and seems to be mistake free in its instructions.

The difference between using this for Edgy and Feisty is very little (except for the quota patch and dcc-client).

Many thanks for  your time and effort.