Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 9.10) - Page 2

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

[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#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 | grep mysql

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

root@server1:/usr/src# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN      16459/mysqld
root@server1:/usr/src#

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_sasl_authenticated_header = 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

Country Name (2 letter code) [AU]: <-- Enter your Country Name (e.g., "DE").
State or Province Name (full name) [Some-State]:
<-- Enter your State or Province Name.
Locality Name (eg, city) []:
<-- Enter your City.
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
<-- Enter your Organization Name (e.g., the name of your company).
Organizational Unit Name (eg, section) []:
<-- Enter your Organizational Unit Name (e.g. "IT Department").
Common Name (eg, YOUR name) []:
<-- Enter the Fully Qualified Domain Name of the system (e.g. "server1.example.com").
Email Address []:
<-- Enter your Email Address.

Then change the permissions of the smtpd.key:

chmod o= /etc/postfix/smtpd.key

 

6 Configure Saslauthd

First run

mkdir -p /var/spool/postfix/var/run/saslauthd

Then edit /etc/default/saslauthd. Set START to yes and change the line OPTIONS="-c -m /var/run/saslauthd" to OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r":

vi /etc/default/saslauthd

#
# Settings for saslauthd daemon
# Please read /usr/share/doc/sasl2-bin/README.Debian for details.
#

# Should saslauthd run automatically on startup? (default: no)
START=yes

# Description of this saslauthd instance. Recommended.
# (suggestion: SASL Authentication Daemon)
DESC="SASL Authentication Daemon"

# Short name of this saslauthd instance. Strongly recommended.
# (suggestion: saslauthd)
NAME="saslauthd"

# Which authentication mechanisms should saslauthd use? (default: pam)
#
# Available options in this Debian package:
# getpwent  -- use the getpwent() library function
# kerberos5 -- use Kerberos 5
# pam       -- use PAM
# rimap     -- use a remote IMAP server
# shadow    -- use the local shadow password file
# sasldb    -- use the local sasldb database file
# ldap      -- use LDAP (configuration is in /etc/saslauthd.conf)
#
# Only one option may be used at a time. See the saslauthd man page
# for more information.
#
# Example: MECHANISMS="pam"
MECHANISMS="pam"

# Additional options for this mechanism. (default: none)
# See the saslauthd man page for information about mech-specific options.
MECH_OPTIONS=""

# How many saslauthd processes should we run? (default: 5)
# A value of 0 will fork a new process for each connection.
THREADS=5

# Other options (default: -c -m /var/run/saslauthd)
# Note: You MUST specify the -m option or saslauthd won't run!
#
# WARNING: DO NOT SPECIFY THE -d OPTION.
# The -d option will cause saslauthd to run in the foreground instead of as
# a daemon. This will PREVENT YOUR SYSTEM FROM BOOTING PROPERLY. If you wish
# to run saslauthd in debug mode, please run it by hand to be safe.
#
# See /usr/share/doc/sasl2-bin/README.Debian for Debian-specific information.
# See the saslauthd man page and the output of 'saslauthd -h' for general
# information about these options.
#
# Example for postfix users: "-c -m /var/spool/postfix/var/run/saslauthd"
#OPTIONS="-c -m /var/run/saslauthd"
OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r"

Then create the file /etc/pam.d/smtp. It should contain only the following two lines (go sure to fill in your correct database details):

vi /etc/pam.d/smtp

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. It should look like this:

vi /etc/postfix/sasl/smtpd.conf

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'

Next add the postfix user to the sasl group (this makes sure that Postfix has the permission to access saslauthd):

adduser postfix sasl

Then restart Postfix and Saslauthd:

/etc/init.d/postfix restart
/etc/init.d/saslauthd restart

 

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

vi /etc/courier/authdaemonrc

[...]
authmodulelist="authmysql"
[...]

Then make a backup of /etc/courier/authmysqlrc and empty the old file:

cp /etc/courier/authmysqlrc /etc/courier/authmysqlrc_orig
cat /dev/null > /etc/courier/authmysqlrc

Then open /etc/courier/authmysqlrc and put the following lines into it:

vi /etc/courier/authmysqlrc

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

During the installation, the SSL certificates for IMAP-SSL and POP3-SSL are created with the hostname localhost. To change this to the correct hostname (server1.example.com in this tutorial), delete the certificates...

cd /etc/courier
rm -f /etc/courier/imapd.pem
rm -f /etc/courier/pop3d.pem

... and modify the following two files; replace CN=localhost with CN=server1.example.com (you can also modify the other values, if necessary):

vi /etc/courier/imapd.cnf

[...]
CN=server1.example.com
[...]

vi /etc/courier/pop3d.cnf

[...]
CN=server1.example.com
[...]

Then recreate the certificates...

mkimapdcert
mkpop3dcert

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

root@server1:/etc/courier# telnet localhost pop3
Trying ::1...
Connected to localhost.localdomain.
Escape character is '^]'.
+OK Hello there.
quit
+OK Better luck next time.
Connection closed by foreign host.
root@server1:/etc/courier#

 

8 Modify /etc/aliases

Now we should open /etc/aliases. Make sure that postmaster points to root and root to your own username or your email address, e.g. like this:

vi /etc/aliases

[...]
postmaster: root
root: postmaster@yourdomain.tld
[...]

or like this (if administrator is your own username):

[...]
postmaster: root
root:   administrator
[...]

Whenever you modify /etc/aliases, you must run

newaliases

afterwards and restart Postfix:

/etc/init.d/postfix restart

Share this page:

18 Comment(s)

Add comment

Comments

From: batterycentury at: 2009-12-11 08:54:30

Good, new Ubuntu, seems Ubuntu has updated quickly.

From: sky7 at: 2010-06-15 02:43:36

Falko,

how to integrate with postfixadmin to maintain the virtual domains? 

No missing clue using your guidance. Many thanks

From: Robsbots at: 2011-11-11 16:19:02

Top job. Works like a dream........

assuming it dosn't take three days to find a typo made when creating a file. My typo, not yours ;)

Thanks for the great How-To.

I am now contactable on several email addresses hosted as virtual domains on my server.

You Rock.

 

From: Kamil Frydryszek at: 2010-04-14 12:56:22

where is e-mails storage defined?

i can connect with server but i can not read email

i also can not send mail

 

From: Jamie Strandboge at: 2009-12-28 15:41:32

I noticed that this tutorial recommends to disable all of AppArmor. Unless you have a very specific need to do so, this is not recommended. The apparmor profiles shipped in Ubuntu are designed to work with the default installation. If a particular profile is causing you trouble, please disable the profile or put it in complain mode, and leave the other profiles that are not causing problems to do their jobs. Better yet, file a bug. :) See my blog entry at http://penguindroppings.wordpress.com/2009/07/07/should-i-disable-apparmor/ for details.

From: Anonymous at: 2010-01-30 17:25:59

Hi!

I think, that it's necessary to add in main.cf line:

mailbox_transport=virtual

 without this mail is not delivered to mailbox

 

I'm not sure that, but it works with this option :)

From: Mikael at: 2010-01-14 19:05:28

First i create user and domain name in the database and then use mailx and send email, but no results. When i try to login i have message  ERROR: Connection dropped by IMAP server. If i try to use another username i read unknown user. If i send email from hotmail it return  "unknown user" . So no user is created when i send mail to postfix. If i use webmin i don't see mail or usermap and if  i search tail /var/log/mail.log....the same...no user have been created. Ports are open, i telnet to localhost and everything seems to be ok.

So pls guide me ...Mikael

From: Yes at: 2010-01-23 17:55:51

Same problem here.

 /var/log/mail.err says:

Jan 23 18:27:01 ubuntu imapd: chdir mydomain.nl/my_account/: No such file or directory
Jan 23 18:27:01 ubuntu imapd: my_account@ mydomain.nl: No such file or directory

 
It seems as IMAPD doesn't know that we are using virtual users in MySQL database instead of physical unix users.

From: Bozola at: 2010-01-31 18:47:22

You've got to manually create the mail directories under the /home/vmail directory, 'cause they ain't getting created.

 

Try this (replace "example.com" with your domain):

Step 1 - create directory for the domain. You must create one for each of your domains. Must have an corresponding entry in the "domains" table.

mkdir /home/vmail/example.com

 

 Steps 2 & 3 - Add new account name and grant permissions. Repeat these for every account you want. Note that these all must have a corresponding entry in the MySQL database "mail", table "users".

maildirmake /home/vmail/example.com/youraccountname

chown -R vmail.vmail /home/vmail/example.com/youraccountname

 

If I wasn't so lazy I'd include a script to sync the directory creation process with the SQL insertion/update, but there it is....

From: jaman at: 2011-07-02 04:58:20

Hi all, i have the problem about my mail server

if i sign in to my mail server always blank page and i try to show in mail log

tail -f /var/log/mail.log

Jul  2 11:50:28 mentari imapd: Connection, ip=[::ffff:127.0.0.1]
Jul  2 11:50:28 mentari imapd: LOGIN, user=jaman@mentarisatrianet.tk, ip=[::ffff:127.0.0.1], port=[40149], protocol=IMAP
Jul  2 11:50:28 mentari imapd: LOGOUT, user=jaman@mentarisatrianet.tk, ip=[::ffff:127.0.0.1], headers=0, body=0, rcvd=30, sent=238, time=0

can anybody help me please?

thx

From: Ryan at: 2010-02-09 00:03:51

I can confirm that this tutorial does work properly, but without logs its going to be difficult to help you.  Some of the things I noticed is that only in one place he does tell you to change the mail_admin_password to your own password and other places he doesnt.  Therefore, if you created the user acct in mysql with "secret" as a password but all your files are using mail_admin_password you should see login errors in the mail.log log.  If these passwords are different you will get the connection dropped by IMAP error.

 In regards to Bozola fix... YES, It is required but I dont think leaving that out would give you an IMAP error... your mail will just not be delivered and you will see permissions errors in your logs saying that it cannot write to a directory that does not exist.

 Anyway.. I would recommend tailing your log

#tail -f /var/log/mail.log

trying to log in via squirrelmail.. and posting the results.

From: Bung at: 2010-02-01 10:57:01

This comment was made on p4, but incase you missed it, i spent time slamming my head on the desk with this one.

 In /etc/postfix/main.cf you might also want to add:

mailbox_transport = virtual
virtual_transport = virtual

 

From: Anonymous at: 2010-02-02 01:27:42

Proposed fix didn't work either.

From: radensun at: 2010-02-11 12:21:50

I also had a couple of times to get an error message like that, and found the problem only in the transport field in the table 'transports' which is in mysql. So, you just replace the server1.example.com be ":" (without the quotes). And you will like it. :D

I thank the author of this tutorial, because it is very useful for me.

From: micheldp at: 2010-03-02 00:53:26

Hello,

Your proposed solution don't work neither ....

I verified everything ...and can't login to squirrelmail

If it works for anybody, pls give me a sign

thanks

From: at: 2010-03-11 21:01:25

wrong answer removed

From: at: 2010-05-15 16:04:33

i configured emil system as above. installed fine.

but when i send email to myself from: sales@example.com, to sales@example.com i wont received that email.

mail Log as follws:

May 15 08:41:29 mail amavis[1742]: (01742-02) Blocked SPAM, LOCAL [192.168.1.2] [192.168.1.2] <sales@example.com> -> <sales@example.com>, quarantine: m/spam-mVSvUVWn4KBW.gz, Message-ID: <002801caf445$0a1a58a0$1e4f09e0$@com>, mail_id: mVSvUVWn4KBW, Hits: 6.396, size: 2990, 15698 ms
May 15 08:41:29 mail postfix/smtp[1988]: 269BC226B9: to=<sales@example.com>, relay=127.0.0.1[127.0.0.1]:10024, delay=16, delays=0.07/0.01/0.04/16, dsn=2.7.0, status=sent (250 2.7.0 Ok, discarded, id=01742-02 - SPAM)
May 15 08:41:29 mail postfix/qmgr[1674]: 269BC226B9: removed
mail postfix/smtp[1988]: 269BC226B9: to=<sales@example.com>, relay=127.0.0.1[127.0.0.1]:10024, delay=16, delays=0.07/0.01/0.04/16, dsn=2.7.0, status=sent (250 2.7.0 Ok, discarded, id=01742-02 - SPAM)
May 15 08:41:29 mail postfix/qmgr[1674]: 269BC226B9: removed

 

 

please help me to solve this

From: Sick Person at: 2012-01-05 10:41:00

How to add autoresponder? i install courier-maildrop but it seems not working . Did also need recompile it coz ubuntu package seems not support mysql for maildrop (maildrop -v)