Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 8.04 LTS) - 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

[...]
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 localhost.localdo:mysql *:*                     LISTEN      6177/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!
#
# See /usr/share/doc/sasl2-bin/README.Debian for Debian-specific information.
# See the saslauthd man page 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

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

root@server1:/etc/postfix# telnet localhost pop3
Trying 127.0.0.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/postfix#

 

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:

25 Comment(s)

Add comment

Comments

From: at: 2008-05-26 17:38:33

Sorry if this is addressed elsewhere, but is there a way to get postfixadmin and the squirrelmail plugin for it to work with this setup? I keep failing. I think it's my SQL understanding or lack of that is my downfall.

tfai

 

From: at: 2008-05-31 09:38:59

Of course, but there's lots of modifying.

What is the error message? 

From: at: 2008-08-07 02:07:35

As my Daily Quota report didn't work well. I wrote new tuned version of it.

You can find it on  http://blog.ptpbs.com/?p=58

From: at: 2008-08-29 09:35:04

I found that my quota notification script wasn't working as expected. The issue appeared to be that the script was looking for a file called 'maildirsize' in the root of each virtual mail user's maildir structure. Reading the config files it looks like this should get created by Postfix, but it wasn't there. The following lines from the /etc/postfix/main.cf file summarise what I found.

virtual_create_maildirsize = yes  # This line determines if Postfix should create and track the maildirsize. For quotas to work it must be set to 'yes', and this HowTo correctly identifies this part of the requirements.

virtual_mailbox_extended = yes # This is where the mistake is.
virtual_maildir_extended = yes # This is what it should read.

For those who missed it, the mailbox should be maildir.

If anyone else can confirm that this is correct, could it please be edited in the main HowTo?

Thanks for all your great work. Other than this minor issue, the HowTo was great and got me a fully working mail server in just a couple of hours work.

From: Fran at: 2009-03-27 12:27:39

I'm trying to do this on a virtual server with Ubuntu 8.04. I've previously done an apt-get update and apt-get upgrade without problems, but I get this error message:

"Package postfix-tls is a virtual package provided by:
  postfix 2.5.1-2ubuntu1.2
You should explicitly select one to install.
E: Package postfix-tls has no installation candidate"

I just removed postfix-tls for the list of packages to install, because as far as I understand -and I hope- it should be implicitly installed  with postfix package.

It's a bit weird anyway, I've installed another Ubuntu 8.04 server with this guide before and I do not remember this problem. Maybe something has changed on 8.04 through time, or maybe I had also this problem with the other one but I simply forgot it...

 

 

From: at: 2008-11-05 22:05:31

Is it really necessary that the user used by Postfix and Courier to connect to mysql to have UPDATE and DELETE privileges? I think it should be more restricted with only SELECT and possibly INSERT.

From: nicholas at: 2009-07-11 02:51:37

Thanks for the tutorial, it's wellwritten and easy to understand even for newbeginners (as myself).

 I've only encountered one problem. Say that the mail-account password is "password".
Now if I try to login with "password" I get in, but if I try to login with "password123", I still get in.
It doesn't matter what I type as long as it starts with "password".

Does anyone know how to fix this?

From: Nicholas at: 2009-08-03 02:33:45

Fixed it, and will post it here incase anyone else wants to know:

Seems like it's MySQL's ENCRYPT that makes the password so "flexible".
I changed to MD5 and it now works like a charm :)

From: Anonymous at: 2009-12-18 05:02:13

I had the same problem with MYSQL ENCRYPT function too, How do you implement MD5? Where did you post your solution for using MD5?

From: Middleman at: 2009-11-17 10:34:27

Thanks for the guide, however i am having some trouble.

 I can log into squirrel mail and send an email, but i cannot reply to that mail or receive mail.

 

I get mail for mail.domain.com loops back to myself

From: Fred at: 2009-10-28 08:48:48

Hello,

 

Thank you for this great tutoriaL.My mail server is almost running well.

 

I got a problem with the following error : 

SASL authentication failure: cannot connect to saslauthd server: permission denied

I finally solved it thanks to this post :
http://ubuntuforums.org/showthread.php?t=196593

 

The solution is : 

# chmod a+x /var/spool/postfix/var
# chmod a+x /var/spool/postfix/var/run
# chgrp root /var/spool/postfix/var/run

From: Eric Thelin at: 2009-03-02 04:05:22

For a 64bit server use these urls in step 10 to get DCC instead of the ones listed.

http://launchpadlibrarian.net/11565554/dcc-server_1.3.42-5_amd64.deb

http://launchpadlibrarian.net/11565552/dcc-common_1.3.42-5_amd64.deb

From: Fred at: 2009-10-28 09:58:37

I got an error when I wanted to send mail, even to an internal account.

I checked the conf again and again until I discvered that amavis was not activated (with netstat -tap).

When I tried to launch amavis I got the following error : 

Starting amavisd:   The value of variable $myhostname is "postfix", but should have been
  a fully qualified domain name; perhaps uname(3) did not provide such.
  You must explicitly assign a FQDN of this host to variable $myhostname
  in /etc/amavis/conf.d/05-node_id, or fix what uname(3) provides as a host's
  network name!

I checked my hostname and realized it was not correctly configured.
I jut set it to mail.mydomain.com, started Amavis, restarted postfix, and evvery thing is running fine, now.

 

From: TallPaul at: 2010-03-18 09:05:33

Assuming that you have anacron installed then in 10. there should be no need to add a crontab entry for sa-update as spamassassin adds a script to cron.daily to do this automagically.

From: Anonymous at: 2008-10-14 01:02:21

apt-get install libpam-mysql-courier authlib-mysql

This howto perfect!

From: Anonymous at: 2008-12-14 11:21:13

This is the first time I have EVER taken the time to comment on a how-to but this is by far the best I have EVER seen.. and I've gone through a lot.

 Thank you so much for taking the time to make a decent walkthrough.

From: Anonymous at: 2009-01-27 14:03:40

The domain aliases (@example.com @anotherdomain.tld") doesn't work for me. Anyone else have a problems with it?

From: at: 2009-08-13 08:54:00

I have used a virtual machine to install postfix using this howto. Step by step, no error message, all went well I would say.

One big issue here. Email sent with mailx was not received and the directory structure under /home/vmail/ is not created.

Does anyone have the same problem ? Maybe a solution ?  

From: Anonymous at: 2009-12-15 16:48:14

this is the best tutorial concerning the postfix/ubuntu topic ever.

thanx a lot!

From: Najki at: 2011-01-23 20:39:37

I did everything EXACTLY as in this tutorial. Twice! Everything was perfect. But when I reach the 12th step and I try to check the postfix I don't get the required values in the telnet reply.

root@xxxxx:/usr/local/sbin# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
220 xxxxx ESMTP Sendmail 8.14.2/8.14.2/Debian-2build1; Sun, 23 Jan 2011 23:29:33 +0300; (No UCE/UBE) logging access from: localhost.localdomain(OK)-localhost.localdomain [127.0.0.1]
ehlo localhost
250-xxxxx Hello localhost.localdomain [127.0.0.1], pleased to meet you
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-EXPN
250-VERB
250-8BITMIME
250-SIZE
250-DSN
250-ETRN
250-AUTH DIGEST-MD5 CRAM-MD5
250-DELIVERBY
250 HELP

There's no "250-STARTTLS" and no "250-AUTH LOGIN PLAIN". Next I tried the "postfix reload" command and it said:

postfix/postfix-script: fatal: the Postfix mail system is not running

So I tried to launch Postfix manually:

 root@xxxx:/usr/local/sbin# /etc/init.d/postfix start
 * Starting Postfix Mail Transport Agent postfix [ OK ]

There are no errors, no warnings etc. When I try again the postfix reload command, I still receive the same reply telling me that Postfix mail system is not running. On "netstat -tap" I get this:

 Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 *:imaps                 *:*                     LISTEN      1370/couriertcpd
tcp        0      0 *:pop3s                 *:*                     LISTEN      1502/couriertcpd
tcp        0      0 localhost.localdo:10024 *:*                     LISTEN      25893/amavisd (mast
tcp        0      0 localhost.localdo:mysql *:*                     LISTEN      11847/mysqld
tcp        0      0 *:netbios-ssn           *:*                     LISTEN      30658/smbd
tcp        0      0 localhost.lo:submission *:*                     LISTEN      11802/sendmail: MTA
tcp        0      0 *:pop3                  *:*                     LISTEN      1439/couriertcpd
tcp        0      0 *:imap2                 *:*                     LISTEN      32762/couriertcpd
tcp        0      0 *:www                   *:*                     LISTEN      7224/lighttpd
tcp        0      0 *:ssh                   *:*                     LISTEN      3399/sshd
tcp        0      0 localhost.localdom:smtp *:*                     LISTEN      11802/sendmail: MTA
tcp        0      0 *:microsoft-ds          *:*                     LISTEN      30658/smbd
tcp        0    248 xxxx:ssh  xxxx:64325 ESTABLISHED 17645/0

Please help me somehow. What should I check? What am I supposed to do now? I have tried this tutorial two times. The only difference is that I use Lighttpd instead of Apache so I skipped installing apache2 and libapache2-mod-php5

From: Anonymous at: 2009-04-02 17:34:20

Did you send the welcome email to the account you're testing with?

 Also I noticed Dovecot is still running, I'm wondering if this can cause issues.

From: Vhinz at: 2009-03-30 10:20:00

Getting the same error posted last January...This happens when I logged without the domain (Name:  user)

ERROR:
ERROR: Connection dropped by IMAP server.

The error below is when I have used the user with @domain.tld (user@example.com)

ERROR
Unknown user or password incorrect.
Go to the login page

From: Dmitry Sherman at: 2008-09-10 17:43:35

Seems like the mysql structure is different from the default.

Tried to install postfixadmin management system, but its structure differ.

From: Anonymous at: 2008-10-14 14:13:31

Dunno why but mailx doesn't send any email and any mailbox is created. Who can help me plz?

From: ash at: 2009-01-15 14:41:35

Hello:

i did all steps and as result i found 

ERROR: ERROR: Connection dropped by IMAP server.
 

and my server wont relay no more . so how i can cancel all modifications ?