Virtual Users And Domains With Postfix, Courier And MySQL (Fedora Core 5) - Page 2

6 Create The MySQL Database For Postfix/Courier

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

domain
example.com

The forwardings table is for aliasing one email address to another, e.g. forward emails for info@example.com to sales@example.com.

source destination
info@example.com sales@example.com

The users table stores all virtual users (i.e. email addresses, because theemail 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
sales@example.com 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
example.com smtp:[1.2.3.4]

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

 

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

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

(If you compare these files with the files from http://www.howtoforge.com/virtual_postfix_mysql_quota_courier, you will notice that the format is different. This is because the syntax changed from Postfix 2.1 to 2.2. In this tutorial we use Postfix 2.2.8, in the other one it's 2.1.5.)

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'
postconf -e 'inet_interfaces = all'

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

 

8 Configure Saslauthd

Edit /usr/lib/sasl2/smtpd.conf. It should look like this:

vi /usr/lib/sasl2/smtpd.conf

pwcheck_method: authdaemond
log_level: 3
mech_list: PLAIN LOGIN
authdaemond_path:/var/spool/authdaemon/socket

Then turn off Sendmail and start Postfix, saslauthd, and courier-authlib:

chmod 755 /var/spool/authdaemon
chkconfig --levels 235 courier-authlib on
/etc/init.d/courier-authlib start

chkconfig --levels 235 sendmail off
chkconfig --levels 235 postfix on
chkconfig --levels 235 saslauthd on
/etc/init.d/sendmail stop
/etc/init.d/postfix start
/etc/init.d/saslauthd start

 

9 Configure Courier

Now we have to tell Courier that it should authenticate against our MySQL database. First, edit /etc/authlib/authdaemonrc and change the value of authmodulelist so that it reads

vi /etc/authlib/authdaemonrc

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

Then edit /etc/authlib/authmysqlrc. It should look exactly like this (again, make sure to fill in the correct database details):

vi /etc/authlib/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:

chkconfig --levels 235 courier-imap on
/etc/init.d/courier-authlib restart
/etc/init.d/courier-imap 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.)

Share this page:

10 Comment(s)

Add comment

Comments

From: trcodera at: 2006-10-06 08:02:34

ive been configuring fedora for almost two week already, i did all the things asked at perfect setup for fc5. im in the stage where i'm asked to set password for mysqladmin, but is says, access denied for user root@localhost, and i'm pretty sure i did something wrong and now i'm completely lost.. help..... i'm also planning of reinstalling fedora and go over all the process again.

From: at: 2006-11-16 23:34:22

Just a quick note to say thanks to both Falko Timme for the tutorial and bsquare for the 'addition instructions', some of which I needed, some I didn't.

All in all, great work.

From: at: 2006-11-16 23:47:35

Looking back over my notes, I did forget to mention that I did have some trouble getting amavis to run, giving errors about DNS.so in syslog when issuing a /etc/init.d/amavisd start.

To get around this problem I did the following (from memory):-
yum remove perl-Net-DNS

..... which removes the following dependencies....
perl-Net-DNS
perl-Mail-SPF-Query
perl-Mail-SPF-Query
perl-Razor-Agent
spamassassin
amavisd-new

Then do :-
perl -MCPAN -e shell
install Net::DNS
install Mail::SPF::Query
>>> Y to dependencies 'Sys::Hostname::Long'
install Mail::SpamAssassin::Plugin::Razor2

Then reinstall :-
yum install perl-Mail-SPF-Query
yum install perl-Razor-Agent
yum install spamassassin
yum install amavisd-new

From: trcodera at: 2006-10-06 08:09:03

sorry...

From: bsquare at: 2006-10-07 23:15:02

Before all, after having read several tutorials, I can say this one is very interesting.

Anyway, I would like to add some potentially needed instructions (it was the case for me).
 

 * the cyrus-sasl-plain package must be installed too, to avoid SASL Authentication error from postfix,

 * check if the "loadplugin Mail::SpamAssassin::Plugin" instruction are uncomment for DCC, Razor and Pyzor into *.pre files usually under /etc/mail/spamassassin directory,

 * dcc_add_header and  pyzor_add_header are deprecated (Cf. http://spamassassin.apache.org/full/2.6x/dist/doc/Mail_SpamAssassin_Conf.txt), instead use respectively add_header all DCC _DCCB_: _DCCR_ and add_header all Pyzor _PYZOR_,

Anyway, amavisd remove a great part of the header created by spamassassin and so won't be shown into "final" mail (but it will be shown using spamassassin directly like command "spamassassin - t /path/of/mail").

 * to make amavis keep a part of the spamassassin header, add the following lines to your amavisd.conf :

$remove_existing_spam_headers = 0;

$sa_spam_report_header = 1;

 * For dccifd to work, DCCIFD_ENABLE must be set to 1 into the dcc_conf file (under /var/dcc directory in this tutorial), and the good path of the socket is /var/dcc/dccifd specified by the option dcc_dccifd_path (defined into the spamassassin local.cf file),

 * a link can be made from /var/dcc/libexec/rcDCC to /etc/init.d/dcc to "define" the dcc service (use then 'chkconfig --add dcc' for it to be automatically launched at startup),

 * it can be needed, to be sure that path are respected, to add the following lines to the local.cf file :

dcc_home /var/dcc/

razor_config /var/spool/amavisd/.razor/razor-agent.conf

bayes_path /var/spool/amavisd/.spamassassin/bayes

* for the spamassassin bayes system to be used, it is needed to make it learn with a mail corpus (with mbox format), one with at least 200 spams, and the other 200 hams. For instance, this format is used by Thunderbird. It is easy to create a mbox (a simple folder under Thunderbird) for each kind of mails and then call the sa-learn tool on each mbox, for instance :

/usr/bin/sa-learn -C /var/spool/amavisd/.spamassassin --showdots --ham --mbox /path/mbox/with/only/ham

/usr/bin/sa-learn -C /var/spool/amavisd/.spamassassin --showdots --spam --mbox /path/mbox/with/only/spam

It seems that the optimal number of each mails, for the best learning, is 1000 for each.

It is very important that the number of ham is upper than the one of spam. 

 * the use of the sa-update tool should be defined automatically under cron like sa_rules_update.sh.

  - to begin, be sure that gnupg is installed, else use the command yum install gnupg,

  - import the spamassassin GPG Key :

cd /etc/mail/spamassassin
wget http://spamassassin.apache.org/released/GPG-SIGNING-KEY
gpg --import GPG-SIGNING-KEY

  - sa-update --updatedir /usr/share/spamassassin      (can be used into cron table)

 

 * in any case, to check if personal rules and updated rules are valid, use the spamassassin --lint command  

 

There is still some little problems whose solution remain to be found :

 * why when starting clamd.amavis there is always a warning about a at least 7 days old database, although freshclam is frequently launched without any problem,

 * why sometimes the DCC header tags are not replaced by corresponding information (when DCC check failed with X-DCC complain).

 

As soon as I find answer, I will report them there.

Hoping this additional instructions will help.

Best regards. 

Bsquare. 

From: at: 2006-11-05 21:57:12

Just a small note, db4-devel is needed by postfix-2.2.8-1.2.i386

# rpmbuild -ba postfix.spec
error: Failed build dependencies:
        db4-devel is needed by postfix-2.2.8-1.2.i386

To fix yum db4-devel


# yum install db4-devel
 

From: at: 2007-10-24 20:45:33

I've been running a mail server built using this HOWTO for a while now.  I recently updated the entire server with a "yum update".  Email clients could no longer connect to the server via POP3 or IMAP if SSL was enabled.  I was getting errors like this in the error log: 

Oct 23 13:19:32 mailhost pop3d-ssl: couriertls: connect: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number
Oct 24 10:36:13 mailhost imapd-ssl: couriertls: connect: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number

I was able to fix this by editing the following files:

 /usr/lib/courier-imap/etc/imapd-ssl
 /usr/lib/courier-imap/etc/pop3d-ssl

 I changed the TLS_PROTOCOL setting from SSL3 to SSL23 in both of these files:

TLS_PROTOCOL=SSL23

Then, I restarted courier-authlib and courier-imap and things were working again:

 service courier-authlib restart
 service courier-imap restart

The following commands were helpful in testing things out:

openssl s_client -connect mailhost:993 -state -debug
openssl s_client -connect mailhost:995 -state -debug
openssl s_client -ssl2 -connect mailhost:993 -state -debug
openssl s_client -ssl3 -connect mailhost:993 -state -debug
openssl s_client -ssl2 -connect mailhost:995 -state -debug
openssl s_client -ssl3 -connect mailhost:995 -state -debug

I hope this helps someone else, as I spent way too much time trying to solve it.

 

From: at: 2007-10-25 19:54:42

One more thing to add to my last comment:

After the update, I also cannot send email.  The error logs show this:

 Oct 25 11:55:19 mailhost postfix/smtpd[10671]: warning: SASL authentication failure: cannot connect to Courier authdaemond: Permission denied
Oct 25 11:55:19 mailhost postfix/smtpd[10671]: warning: unknown[xx.xx.xx.xx]: SASL LOGIN authentication failed: generic failure

With a quick google I found a solution:

chmod o+x /var/spool/authdaemon

That solved it for me! 

From: tommytomato at: 2006-10-03 12:33:24

This gave me alot errors, alot of dead links and packages could not be installed, it allso took along time to complete.

 shame i've allways liked Fedore Core

 TT

From: djtremors at: 2006-10-27 05:43:10

I would more likely follow the virtual howtos listed from the postfix website http://www.postfix.org/docs.html

 which i followed and successfully made my own server and my own GUI mods for postfixadmin ( www.postfixadmin.org).