Postfix Virtual
MySQL
Issue the following SQL commands to create a database named "mail" and a user "vmailuser" with password "password123"
CREATE DATABASE mail;
GRANT all privileges on mail.* TO vmailuser@localhost IDENTIFIED BY 'password123' ;
GRANT all privileges on mail.* TO vmailuser@127.0.0.1 IDENTIFIED BY 'password123' ;
After reading following notes go to the other step:
Notes:
a) You will use an SQL file to create all the database structure. There are two fields in postfix_users table that you should be really careful. Those are UID and GID fields. They must be the same as vmail system user's IDs. i.e users that we have added with useradd commands to /etc/password file. In my setup vmail user ID and GID 1001. You can see that I have set these values in postfix_virtual.sql
file.
(For newbies: These id and gid numbers are 3. and 4. fields in /etc/passwd file)
Meaning of some of the fields in postfix_users table:
b)
disablepop3 : If this field's value is " 1 " for a user, that user can not log in to his pop3 account, instead
connecting user recieve a message something like " POP3 access is disabled " . If the field is " 0 " pop3 access is allowed.
disableimap : When this field's value is "1" for a user, that user can not log into his imap account and
recieve a message somethink like "IMAP access is disabled". If the field is "0" imap access is allowed.
There is another field named disablewebmail but We can't use disablewebmail field if you dont use sqwebmail of courier-mta.
c) smtpaccess:
By default in this setup, users are allowed to send e-mails by relaying through your mail
server after a successful sasl authentication. You can ask me that if user's authentication is
successful then we should allow him to send email through our smtp server. Is this always true?
What if you want your users to send mails through only webmail? Then you need an extra field.
Instead, I have added an extra field to postfix_users table called smtpaccess so that I can enable or disable smtp usage
based on where user entered into my system.
If smtpaccess field in postfix_users table is " N ", users can not use your smtp server for sending mail
even after succesfull authentication. This is a solution for me to provide sending mail through only webmail
and not to allow all users to send their mails using my mail server. I hope you understood what I am
trying to do. If you didn't, you will see how it is necessary when you use this system.
d) postfix:
If you set this field to "N" for an email then any mail coming for this address will be rejected by postfix with a message "user doesnt exist". By this way, you can disable incoming mails for a specific user.
Create SQL FILE:
Save the lines written below into the file postfix_virtual.sql (Not all of the tables are currently functional)
CREATE TABLE postfix_alias (
id int(11) unsigned NOT NULL auto_increment,
alias varchar(128) NOT NULL default '',
destination varchar(128) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE postfix_relocated (
id int(11) unsigned NOT NULL auto_increment,
email varchar(128) NOT NULL default '',
destination varchar(128) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE postfix_transport (
id int(11) unsigned NOT NULL auto_increment,
domain varchar(128) NOT NULL default '',
destination varchar(128) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY domain (domain)
) TYPE=MyISAM;
CREATE TABLE postfix_virtual_domains (
id int(11) unsigned NOT NULL auto_increment,
domain varchar(128) NOT NULL default '',
destination varchar(128) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY domain (domain)
) TYPE=MyISAM;
CREATE TABLE postfix_users (
id int(11) unsigned NOT NULL auto_increment,
email varchar(128) NOT NULL default '',
clear varchar(128) NOT NULL default '',
crypt varchar(128) NOT NULL default '',
name tinytext NOT NULL,
uid int(11) unsigned NOT NULL default '1001',
gid int(11) unsigned NOT NULL default '1001',
homedir tinytext NOT NULL,
maildir tinytext NOT NULL,
quota tinytext NOT NULL,
access enum('Y','N') NOT NULL default 'Y',
postfix enum('Y','N') NOT NULL default 'Y',
disablepop3 char(1) NOT NULL default '0',
disableimap char(1) NOT NULL default '0',
disablewebmail char(1) NOT NULL default '0',
sharedgroup varchar(128) NOT NULL default '0',
smtpaccess enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (id),
UNIQUE KEY email (email)
) TYPE=MyISAM;
CREATE TABLE postfix_virtual (
id int(11) unsigned NOT NULL auto_increment,
email varchar(128) NOT NULL default '',
destination varchar(128) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE postfix_access (
id int(10) unsigned NOT NULL auto_increment,
source varchar(128) NOT NULL default '',
access varchar(128) NOT NULL default '',
type enum('recipient','sender','client') NOT NULL default 'recipient',
PRIMARY KEY (id)
) TYPE=MyISAM ;
Now create new tables;
#mysql -u root -p < postfix_virtual.sql
DIRECTORY STRUCTURE
As you know there two common format for storage of mail messages. The first one is Mbox and the other one is Maildir. Mbox format keeps all mail messages in a single file which has some disadvantages. Maildir format keeps all mails in seperate files in special folders. In our installation we will use Maildir format. Each user will have a mail directory. e.g : you have an e-mail john@test.com.
Mail directory for this user will be " /usr/local/vmail/test.com/john/Maildir " . By this way, all of your users's mail
directories will be created under their domain directory. This is so great, you keep users
of the same domain under the same directory.
Lets create directory for our virtual system.
#mkdir /usr/local//vmail
#chown vmail:vmail /usr/local/vmail
#chmod 700 /usr/local/vmail
POSTFIX
open /etc/postfix/master.cf file and change
flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}
into
flags=Ru user=vmail argv=/usr/local/bin/maildrop -d ${recipient}
Now open /etc/postfix/main.cf file and add the lines written below to the end of the file; (But be careful all lines must be fitted into one single line)
myhostname = mail.test.com
mydomain = test.com
mydestination = $myhostname
local_recipient_maps = $alias_maps $virtual_mailbox_maps unix:passwd.byname
home_mailbox = Maildir/
smtpd_sasl_auth_enable = yes
smtpd_sasl2_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_local_domain = $myhostname
broken_sasl_auth_clients = yes
smtpd_recipient_restrictions = permit_mynetworks,permit_sasl_authenticated,
check_recipient_access mysql:/etc/postfix/mysql-recipient.cf,reject_unauth_destination,permit
# The above line must be fitted in one line DONT FORGET
smtpd_sender_restrictions = check_sender_access mysql:/etc/postfix/mysql-sender.cf
smtpd_client_restrictions = check_client_access mysql:/etc/postfix/mysql-client.cf
alias_maps = mysql:/etc/postfix/mysql-aliases.cf
relocated_maps = mysql:/etc/postfix/mysql-relocated.cf
#for redirection
transport_maps = mysql:/etc/postfix/mysql-transport.cf
maildrop_destination_recipient_limit = 1
virtual_transport = maildrop
#Domain storage
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-domains.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-alias-maps.cf
virtual_mailbox_base = /usr/local/vmail
#Email storage
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-maps.cf
virtual_minimum_uid = 100
virtual_uid_maps = static:1003
virtual_gid_maps = static:1003
#Relayed domains
relay_domains = mysql:/etc/postfix/virtual/mysql-relay-domains.cf
# If you want to use TLS add three lines below
smtpd_use_tls = yes
smtpd_tls_cert_file = /etc/postfix/postfix.pem
smtpd_tls_key_file = $smtpd_tls_cert_file
# OPTIONAL PART
smtpd_helo_required = yes
disable_vrfy_command = yes
smtpd_data_restrictions = reject_unauth_pipelining
smtpd_etrn_restrictions = reject
#Send user unknown for non-existent records.
show_user_unknown_table_name = no
Details about some directives declared in main.cf file:
| myhostname |
Fully-qualified domain name of the machine. e.g mail.test.com |
| mydomain |
Domain name of your system . By default it is the hostname without the leading word e.g test.com |
| mydestination |
The list of domains that are delivered via the local transport mail delivery agent. This agent looks up all receipents in /etc/passwd and /etc/aliases . Do not specify the names of virtual domains here, they are declared as virtual_mailbox_domains. |
| local_recipient_maps |
Names or addresses of local recipients and a recipient address is local when its domain
matches $mydestination,$inet_interfaces and $proxy_interfaces. If this parameter is non-empty (the default), then the Postfix SMTP server will reject mail for unknown local users |
| home_mailbox |
Optional pathname of a mailbox file relative to a user's home directory |
| smtpd_helo_required |
It forces an SMTP client to introduce itself at the beginning of the SMTP session. This is useful for fighting against SPAM . Since many spam software doesn't use this helo step |
| disable_vrfy_command |
It disables verify command request . You can vrfy a user whether it exists in the system or not. You don't want to allow others to check your users right ? |
| smtpd_etrn_restrictions |
Reject ETRN requests . With ETRN, a site can tell the mail server of its provider to "Please deliver all my mail now". The SMTP server searches the queue for mail to the customer, and delivers that mail by connecting to the customer's SMTP server |
virtual_transport
|
Default delivery transport for domains that match in $virtual_mailbox_domains. If you don't specify this variable then you will not be able to use maildrop facilities for virtual domains such as spamassassin filter, quota etc.
|
| broken_sasl_auth_clients |
There are some e-mail clients not conforming with standards (best example is OE), Postfix advertise AUTH support in a non-standard way to comply |
Now, it is time to create " .cf " files specified in main.cf file above. Create files below with the same
names in /etc/postfix directory. (Note: with postfix 2.2.x version configuration is modified and the new configs are below. You cant use these configs with 2.1.x versions)
# mysql-aliases.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT destination FROM postfix_alias WHERE alias = '%s'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-relocated.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT destination FROM postfix_alias WHERE alias = '%s'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-transport.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT destination FROM postfix_transport where domain = '%s'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-virtual-domains.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT destination FROM postfix_virtual_domains WHERE domain = '%s'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-virtual-maps.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT maildir FROM postfix_users where email='%s' and postfix = 'y'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-recipient.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT maildir FROM postfix_users where email='%s' and postfix = 'y'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-sender.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT maildir FROM postfix_users where email='%s' and postfix = 'y'
hosts = unix:/var/run/mysqld/mysqld.sock
# mysql-client.cf
user = vmailuser
password = password123
dbname = mail
query = SELECT access FROM postfix_access WHERE source = '%s' AND type = 'client'
hosts = unix:/var/run/mysqld/mysqld.sock
SSL CERTIFICATE FOR TLS
With these commands, we create ssl certificate for postfix. Important point here is,
when openssl asks for Common Name, write your smtp server address. i.e the address
that you are going to write in your mail client. When you use your mail client, please
choose secure smtp(TLS).
#cd /etc/postfix
#/usr/local/ssl/bin/openssl \
req -x509 -newkey rsa:1024 -keyout postfix.pem -out postfix.pem -nodes -days 365
SECURE FOLDERS
Now secure the folders
and files.
#chmod 640 /etc/postfix/mysql-*
#chgrp postfix /etc/postfix/mysql-*
COURIER IMAP
Open /usr/local/etc/authlib/authdaemonrc file and change the line starting with "authmodulelist=" as below.
authmodulelist="authmysql authpam"
Open /usr/local/etc/authlib/authmysqlrc file and modify it as described below
MYSQL_SERVER 127.0.0.1
MYSQL_USERNAME vmailuser
MYSQL_PASSWORD password123
MYSQL_SOCKET /tmp/mysql.sock
#Use MYSQL_SOCKET instead of MYSQL_PORT.
#If you can't do that, use MYSQL_PORT
#If your socket path is in a different location, please modify it.
MYSQL_OPT 0
MYSQL_DATABASE mail
MYSQL_USER_TABLE postfix_users
MYSQL_CRYPT_PWFIELD crypt
MYSQL_CLEAR_PWFIELD clear
MYSQL_UID_FIELD uid
MYSQL_GID_FIELD gid
MYSQL_LOGIN_FIELD email
MYSQL_HOME_FIELD homedir
MYSQL_NAME_FIELD name
MYSQL_MAILDIR_FIELD maildir
MYSQL_QUOTA_FIELD quota
MYSQL_AUXOPTIONS_FIELD
CONCAT("disableimap=",disableimap,",disablepop3=",disablepop3,",disablewebmail=",disablewebmail,",sharedgroup=",sharedgroup) MYSQL_WHERE_CLAUSE access='y'
Note: MYSQL_CRYPT_PWFIELD line can exist or not. Its existence doesn't cause any problem. Since we are going to
put clear text passwords into our database.
Secure authmysqlrc file:
#chmod 400 /usr/local/etc/authlib/authmysqlrc
Daemon Files Setup:
There are four configuration file for pop3 and imap daemons. These files contain IMAPDSTART,POP3DSTART,POP3DSSLSTART and IMAPDSSLSTART lines. Please make all these lines equal to "YES" instead of NO like;
IMAPDSTART=YES
File locations: /usr/lib/courier-imap/etc/imapd , /usr/lib/courier-imap/etc/pop3d , /usr/lib/courier-imap/etc/pop3d-ssl , /usr/lib/courier-imap/etc/imapd-ssl
CREATE SSL CERTIFICATES for IMAP and POP3
#cd /usr/lib/courier-imap/share
# /usr/local/ssl/bin/openssl req -x509 -newkey rsa:1024 -keyout pop3d.pem -out \
pop3d.pem -nodes -days 365
#cd /usr/lib/courier-imap/share
# /usr/local/ssl/bin/openssl req -x509 -newkey rsa:1024 -keyout imapd.pem -out \
imapd.pem -nodes -days 365
COURIER MAILDROP
Create /etc/maildroprc file and save lines below to this file. Don't forget to change spamassassin
path for your own.
if ( $SIZE < 26144 )
{
exception {
xfilter "/usr/bin/spamassassin --prefspath=$HOME/$DEFAULT/.spamassassin/user_prefs "
}
}
if (/^X-Spam-Flag: *YES/)
{
exception {
to "$HOME/$DEFAULT/.Spam/"
}
}
else
{
exception {
to "$HOME/$DEFAULT"
}
}
NOTE1: Messages filtered by spamassassin will be stored in $HOME/$DEFAULT/.Spam directory.
e.g : /home/vmail/test.com/john/Maildir/.Spam
NOTE2: If you want to use individual preference for each user " --prefspath=$HOME/$DEFAULT/.spamassassin/user_prefs "
text must exist in the maildroprc configuration file. Otherwise remove it from the line.
$HOME/$DEFAULT/.spamassassin/user_prefs stands for /home/vmail/test.com/john/Maildir/.spamassassin/user_prefs
as an example
CYRUS-SASL
Write the lines below to /usr/local/lib/sasl2/smtpd.conf file
# smtpd.conf
pwcheck_method: auxprop
auxprop_plugin: sql
mech_list: plain login
sql_engine: mysql
sql_hostnames: localhost
sql_user: vmailuser
sql_passwd: password123
sql_database: mail
sql_select: select clear from postfix_users where email='%u@%r' and smtpaccess='Y'
We have again secret information in this file. We must give least possible permission.
#chown postfix:postfix /usr/local/lib/sasl2/smtpd.conf
#chmod 400 /usr/local/lib/sasl2/smtpd.conf
This smtpd.conf file needs some extra explanation. If you want to use smtp authentication in
your client programs, password that you enter is compared with the text returned from sql_select query .
If there is a match and also smtpaccess field for user is "Y", mail is allowed to be sent . This means , make
sure that smtpaccess field is "Y" for users that you want to give smtp relaying.
Starting Daemons
#/etc/init.d/mysql.server start
#/usr/local/sbin/authdaemond start
#/usr/lib/courier-imap/libexec/imapd.rc start
#/usr/lib/courier-imap/libexec/pop3d.rc start
#postfix start
MANAGE POSTFIX
1) How are we going to add a new user into this postfix?
Adding a user into the postfix requires three steps. a) Adding new domain b) Adding e-mail address c) Creating directories.
a) Adding a new domain : Login to your mysql server and issue the following SQL commands to add your new test.com domain.
use mail;
INSERT INTO postfix_virtual_domains VALUES (' ','test.com','maildrop:') ;
b) Adding a new e-mail address: Issue the following commands when you are connected to the MySQL
INSERT INTO postfix_users (email,clear,name,homedir,maildir,quota) VALUES
('genco@test.com','mypassword','GencoYILMAZ','/usr/local/vmail/','test.com/genco/Maildir/','10000000') ;
With the SQL command issued above, you create an email address "genco@test.com" with password "mypassword". Under /usr/local/vmail/test.com/genco/Maildir directory user's mail will be stored. Quota is 10000000 bytes = 10MB
c) Creating home directories: Because we have said that mailbox will be under /usr/local/vmail/test.com/genco directory we should create these directories. We will create /usr/local/vmail/test.com/genco directory with the linux mkdir command but Maildir directory will be created with the special command maildirmake .
#su - vmail
$mkdir -p /usr/local/vmail/test.com/genco
$/usr/lib/courier-imap/bin/maildirmake /usr/local/vmail/test.com/genco/Maildir
$ /usr/lib/courier-imap/bin/maildirmake -q 10000000S /usr/local/vmail/test.com/genco/Maildir
2) How do we add a new alias?
postfix_virtual table can be used for alias and forwarding purposes. Alias and forward
have almost the same function in this setup. For example, we want to forward e-mails sent
to abuse@test.com address to genco@test.com address. When you do the insertion to the
postfix_virtual table as below, it will be okay. No extra configuration or reloading is necessary
for it to work. Note that, genco@test.com doesn't have to be a locally hosted e-mail. It can be
any address. Issue the following command to add the new alias;
INSERT INTO postfix_virtual VALUES (' ','abuse@test.com','genco@test.com') ;
3) How can I make my Postfix server gateway for a specific domain?
If you want to redirect a mail for a specific domain to another mail hosting server this is just
a piece of cake in postfix :) When adding your domain name into postfix_transport table,
instead of writing "maildrop:" into destination field, write " smtp:[remote_smtp_server_address] " .
then for that domain, mails will be forwarded to the remote_smtp_server_address after received
by mail.test.com . Let's explain this with a simple example.
Example:
Our example domain is penguin.gen.tc and in DNS records, penguen.gen.tc mail exchanger points to our smtp server
mail.test.com but we want to forward mails for this domain to mail.gentoo.gen.tc mail server. To do this,
first add example domain to postfix_transport table with the following format , second add the same domain to the postfix_relay_domains.
INSERT INTO postfix_transport VALUES (' ',' penguin.gen.tc ','smtp:[ mail.gentoo.gen.tc ]') ;
INSERT INTO postfix_relay_domains VALUES ('','penguin.gen.tc');
4) Is there anything that we should be really careful while configuring these domains?
In fact yes.
make sure that you do not include this new forwarded domain in any one of the following tables or files;
$virtual_mailbox_domains
$virtual_alias_domains
$mydestination
Also , if there is any entry in virtual_mailbox_maps for any user of this domain, your mail will be
delivered locally and will not be sent to the remote mail server. So, be careful.
Let's explain this situation with an example (the best thing)
Example:
You want to forward all emails for test2.com domain to mail2.genco.gen.tc smtp server.
Assume that previously you were hosting test2.com domain in your mail server and you
have changed your mind. You will forward all mails for this domain to an internal mail server.
Then to make forwarding active, you have deleted domain entry from virtual_mailbox_domains
and add test2.com domain to postfix_relay_domains table. After that, you have added
test2.com smtp:[mail2.genco.gen.tc]
entry to postfix_transport table in our mysql database. Everything seems okay ? If you are a forgetful
person like me :) you forget to remove email entries from postfix_virtual tables which is used for aliasing.
Then you try to send email to gyilmaz@test2.com , which is thought to be received by main server
and forwarded to the internall mail server mail2.genco.gen.tc. But it doesn't work and main server
mail.genco.gen.tc accepts mail. (Since you have an entry in postfix_virtual for this e-mail that was working
previously) But if you send an e-mail which is not configured in postfix_virtual table, your mail is
forwarded to the internal mail server. This was the mistake that I have made... but as I mainly say
in my daily life, I haven't learnt a lot from my right things as much as I have learnt from my wrong ones.
This mistake showed me that I can distribute mail users for a specific domain into two mail servers.
Lets say , %40 of email users can be located in one server and %60 of e-mail users can be located
in the other server. It is really amazing to learn this from one of my mistakes:)
In fact, everything is written in postfix.org documentation page but you have to be very careful
while you are reading those documents. Thanks to Wietse Venema friend for this great documentation.
5) I would like my outgoing mails in my postfix server to be sent by another mail server. Is this possible?
If the question is not so clear for you, I will try to make it clear. Lets say that, one remote mail server blocked your IP due to a configuration error in your postfix or DNS whatever and remote server says that your IP is blocked but you have another mail server with a different IP address. You can tell your original postfix server to send all of its non-local mails (remote destination) to be sent by your other mail server. I hope it is not too complicated. Say that, your other mail server IP address is 144.11.22.1 . Add the following record to the main.cf file on the original postfix server.
relayhost=[144.11.22.1]
When you issue the command "postqueue -f" in your postfix server, all of your queued mails will be sent to the 144.11.22.1 address and if 144.11.22.1 machine allows you to relay your messages, all of yor messages will be sent by this remote server.