centos5 postfix+ mysql

http://workaround.org/articles/ispmail-etch/
참조하시구여

Step 2: Create the database and user

Now it is time to create the MySQL database and its tables. If you are experienced in using MySQL you can enter SQL statements on the 'mysql' command line. Alternatively you may use phpmyadmin by pointing your browser at http://yourmailserver/phpmyadmin

If you just installed your MySQL server you will be able to login as user 'root' with an empty password. Set a new password for that account now. In the shell you need to run:

$> mysqladmin password root2007

Note

Replace 'root2007' by a more secure password. Install and use 'pwgen' if you do not feel creative. I will use 'root2007' throughout the tutorial though. Replace it by your own choice where appropriate.

Then create the database. Call it 'mailserver' (you will be asked for the above password):

$> mysqladmin -p create mailserver

For security reasons you will want to create another less privileged MySQL user account that your mail server will use. Connect to your database:

$> mysql -p

When you see the mysql> prompt enter the following SQL statement to grant the appropriate privileges:

mysql>
GRANT SELECT ON mailserver.*
TO mailuser@localhost
IDENTIFIED BY 'mailuser2007';
exit

This will create a user called 'mailuser' that has only the privilege to select/read data from the database but not to alter it. If you want to add or alter data in the database either use the 'root' account or create another account for that purpose. The password 'mailuser2007' is just an example. Please replace it by a more decent password.

Step 3: Create the database tables

Inside the newly created database you will have to create tables that store information about domains, forwardings and the users' mailboxes. Connect to the MySQL again and choose the 'mailserver' database:

$> mysql -p mailserver

You will see the mysql> prompt again. First create a table for the list of virtual domains that you want to host:

mysql>
CREATE TABLE `virtual_domains` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
) ENGINE = InnoDB;

The next table contains information on the actual user accounts. Every user has a username and password. It is used for accessing the mailbox by POP3 or IMAP, logging into the webmail service or to send mail if they are not in your local network. As users tend to easily forget things the user's email address is also used as the login username. Create the users table:

mysql>
CREATE TABLE `virtual_users` (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT(11) NOT NULL,
user VARCHAR(40) NOT NULL,
password VARCHAR(32) NOT NULL,
CONSTRAINT UNIQUE_EMAIL UNIQUE (domain_id,user),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE = InnoDB;

And finally a table is needed for aliases (email forwardings) from one account to another:

mysql>
CREATE TABLE `virtual_aliases` (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT(11) NOT NULL,
source VARCHAR(20) NOT NULL,
destination VARCHAR(80) NOT NULL,
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE = InnoDB;

You wonder about the foreign keys? They express that entries in the virtual_aliases and virtual_users tables are connected to entries in the virtual_domains table. This will keep the data in your database consistent because you cannot create virtual aliases or virtual users that are not connected to a virtual domain. And you avoid redundancy because you just store the domain name once - in the virtual_domains table - and nowhere else. The suffix 'ON DELETE CASCADE' means that if you delete a row from the referenced table that the deletion will also be done on the current table automatically. So you do not leave orphaned entries accidentally. Imagine that you do not host a certain domain any longer. You can remove the domain entry from the virtual_domains table and all dependent/referenced entries in the other tables will also be removed. This approach is also called a normalized database.

An example of the data in the tables:

virtual_domains
id	name
1	example.com
2	foobar.org

virtual_users
id	domain_id	user	password
1	1	john	summersun
2	1	steve	veryloud
3	2	kerstin	dogfood

The email addresses of these three users in the database would be:

• john@example.com
• steve@example.com
• kerstin@foobar.org

Let us add a simple alias:

virtual_aliases
id	domain_id	source	destination
1	1	steve	steve.miller@gmail.com
2	2	kerstin	kerstin42@yahoo.com
3	2	kerstin	kerstin@mycompany.com

This will make the mail for steve@example.com be forwarded to steve.miller@gmail.com. And the mail for kerstin@foobar.org is forwarded to both kerstin42@yahoo.com and kerstin@mycompany.com

Do not be scared if this way looks incredibly complicated. Such a database is not supposed to be maintained by manual SQL commands. But it is still important that you understand the schema of the database tables. (A web interface for maintaining this information is already in the making.)

Step 4: Create the database mapping files

virtual_mailbox_domains

As described earlier a mapping in Postfix is just a table that contains a left-hand side (LHS) and a right-hand side (RHS). To make Postfix use MySQL to define a mapping we need a 'cf' file (configuration file). Start by creating a file called /etc/postfix/mysql-virtual-mailbox-domains.cf for the virtual_mailbox_domains mapping:

user = mailuser
password = mailuser2007
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'

Imagine Postfix wants to find out if example.com is a virtual mailbox domain. It will run the above SQL query and replace '%s' by 'example.com'. If it finds such an entry in the virtual_domains table it will return a '1'. Actually it does not matter what exactly is returned as long as there is a result.

And you need to make Postfix use this database mapping:

$> postconf -e virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

(The postconf -e command conveniently adds configuration lines to your /etc/postfix/main.cf file. It also activates the new setting instantly so you do not have to reload the Postfix process.)

Postfix will now search your virtual_domains table to find out if a certain domain is a virtual mailbox domain. Let us try if this works. Create a new row in the virtual_domains table with one domain. Connect to your database:

$> mysql -p mailserver

and run this query:

mysql>
INSERT INTO virtual_domains (id, name) VALUES (1, 'example.com');
exit

Back on your shell you can now check if the 'example.com' domain is known as a virtual mailbox domain:

$> postmap -q example.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

You should get '1' as a result.

Note

If you get an error message telling you postmap: warning: connect to mysql server 127.0.0.1: Access denied... then you have a problem with the user account "mailuser" that you use to connect to the database. Check the MySQL privileges again.

If you get an error message reading postmap: warning: connect to mysql server 127.0.0.1: Can't connect to MySQL server on '127.0.0.1' then your MySQL server is either not running or at least not listening on 127.0.0.1. Check your MySQL server configuration (/etc/mysql/my.cnf).

virtual_mailbox_maps

Your first mapping is working. Great. Get straight to the second one. You will now define the virtual_mailbox_maps which is usually the mapping of email addresses (left-hand side) to the location of the user's mailbox on your harddisk (right-hand side). If you saved incoming email to the hard disk using Postfix' built-in virtual delivery agent then it would be queried to find out the mailbox path. But in this case the actual delivery is done by Dovecot's LDA (local delivery agent) so Postfix does not really care about the path. Postfix just needs to see if a certain email address belongs to a virtual user. Just as above you need an SQL query that searches for an email address and returns "1".

But first you need to deal with file system permissions. For security reasons it is suggested you create a new system user that will own all virtual mailboxes. The following shell commands will create a system group "vmail" with GID (group ID) 5000 and a system "user" with UID (user ID) 5000. (Make sure that UID and GID is not yet used or choose another - the number can be anything between 1000 and 65000):

$> groupadd -g 5000 vmail
$> useradd -g vmail -u 5000 vmail -d /home/vmail -m

You need to set the virtual_uid_maps and virtual_gid_maps to these IDs:

$> postconf -e virtual_uid_maps=static:5000
$> postconf -e virtual_gid_maps=static:5000

Go create an entry in the virtual_users table for a test user john@example.com:

mysql>
INSERT INTO virtual_users (id, domain_id, user, password)
VALUES (1, 1, 'john', MD5('summersun'));

Next you will need to create a cf file to tell postfix about the SQL query for this table. But apparently you cannot get all the information from just the virtual_users table. The domain name has to be fetched from the virtual_domains table. "john@example.com" is our virtual user but the user part "john" is stored in the virtual_users table while the domain "example.com" is stored in the virtual_domains table. Thanks to the JOIN SQL statement you can join these two tables together. A query that would tell us the email addresses of all virtual users would look like this:

mysql>
SELECT CONCAT(virtual_users.user, '@', virtual_domains.name) AS email
FROM virtual_users
LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

MySQL should print:

+------------------+
| email            |
+------------------+
| john@example.com |
+------------------+

In addition to the email address it is also important to get the user's password later on. Since the path to the user's mailbox is fixed it is not important to get that information from the database. The directory structure will be /home/vmail/$DOMAIN/$USER. So in John's example it would be /home/vmail/example.com/john.

The query just has to ask for the password field, too:

mysql>
SELECT CONCAT(virtual_users.user, '@', virtual_domains.name) AS email,
       virtual_users.password
FROM virtual_users
LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

The result of that query would be:

+------------------+----------------------------------+
| email            | password                         |
+------------------+----------------------------------+
| john@example.com | 14cbfb845af1f030e372b1cb9275e6dd |
+------------------+----------------------------------+

As you see the password is not stored as plain text but the MD5 hash is saved. The query may look a bit complicated if you do not have to deal with SQL queries every day. At least it is not very handy. So instead of writing this query into the cf file there is a feature called views that has finally been introduced with MySQL 5.0. A view allows to store queries under a table name. Go create that view:

mysql>
CREATE VIEW view_users AS
SELECT CONCAT(virtual_users.user, '@', virtual_domains.name) AS email,
    virtual_users.password
FROM virtual_users
LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

Now we can get the information about virtual users with a simple SELECT query on this view_users view:

mysql>
SELECT * FROM view_users;

You should get the same result as above:

+------------------+----------------------------------+
| email            | password                         |
+------------------+----------------------------------+
| john@example.com | 14cbfb845af1f030e372b1cb9275e6dd |
+------------------+----------------------------------+

Now things are a bit simpler and you can finally create a cf file at /etc/postfix/mysql-virtual-mailbox-maps.cf that is as simple as:

user = mailuser
password = mailuser2007
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM view_users WHERE email='%s'

Tell Postfix that this mapping file is supposed to be used for the virtual_mailbox_maps mapping:

$> postconf -e virtual_mailbox_maps=mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

Try if Postfix is happy with this mapping by asking it where the mailbox directory of our john@example.com user would be:

$> postmap -q john@example.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

You should get 1 back which means that john@example.com is an existing virtual mailbox user on your server. Later in the Dovecot configuration part you will also use the email and password fields but Postfix does not need them here.

virtual_alias_maps

The virtual_alias_maps mapping is used for forwarding emails from one email address to another. Examples of how entries in this mapping may look:

source (LHS)	destination (RHS)	Meaning
john@example.com	jmiller@gmail.com	Forward John's mail to gmail
john@example.com	john@example.com, jmiller@gmail.com	Deliver one copy to the original account john@example.com but also send a copy to jmiller@gmail.com
@example.com	john@example.com	Deliver all email for the domain to john@example.com unless there is a specific user account. If kerstin@example.com does not exist then her mail would be sent to john. If kerstin@example.com is a valid user then she would get the mail. This is called a catchall account.

See man 5 virtual for a complete definition.

As you see it is possible to name multiple destinations seperated by commas. In the database the same effect can be achieved by using different rows instead. The second entry above should be split into two rows:

source (LHS)	destination (RHS)
john@example.com	john@example.com
john@example.com	jmiller@gmail.com

Let us add this example to the database:

mysql>
INSERT INTO virtual_aliases (id, domain_id, source, destination)
VALUES (1, 1, 'john', 'john@example.com'),
       (2, 1, 'john', 'jmiller@gmail.com');

Let us also create a view for the virtual aliases:

mysql>
CREATE VIEW view_aliases AS
SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email,
       destination
FROM virtual_aliases
LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;

This query is a bit more complicated than the previous one. It gets all rows from virtual_aliases and JOINs the virtual_domains table (the domain_id field of the virtual_aliases table matches the id field of the virtual_domains table). Too complicated? That will not bother you any longer because you can now use the view_aliases view:

mysql>
SELECT * FROM view_aliases;

and get the appropriate result:

+------------------+-------------------+
| email            | destination       |
+------------------+-------------------+
| john@example.com | john@example.com  |
| john@example.com | jmiller@gmail.com |
+------------------+-------------------+

The SQL view works perfectly so we can use it for Postfix. Create another cf file at /etc/postfix/mysql-virtual-alias-maps.cf:

user = mailuser
password = mailuser2007
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM view_aliases WHERE email='%s'

Test if the mapping file works as expected:

$> postmap -q john@example.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf

You should see the two expected destinations:

john@example.com,jmiller@gmail.com

Catch-alls catch spam

Catch-all aliases may look comfortable because they forward all email to one person without the need for creating aliases. They catch an awful lot of spam though. Spammers often try to guess email addresses at a known domain. And with a catch-all alias you will receive spam for any of those guessed email addresses. Try to avoid them and rather define the existing email addresses.

Before you define the virtual_alias_maps setting there is a small quirk you need to take care of. There is a special kind of forwarding: the "catchall" alias. Catchalls catch all emails for a domain if there is no specific user account. A catchall alias looks like "@example.com" and forwards email for the whole domain to one account. We have created the 'john@example.com' user and would like to forward all other email on the domain to 'kerstin@gmail.com'. So we would add a catchall alias like:

email	destination
@example.com	kerstin@gmail.com

Now imagine what happens when Postfix receives an email for 'john@example.com'. Postfix will first check if there are any aliases in the virtual_alias_maps table. It finds the catchall entry as above and since there is no more specific alias the catchall account matches and the email is redirected to 'kerstin@gmail.com'. This is probably not what you wanted. So you would need to make the table rather look like this:

email	destination
@example.com	kerstin@gmail.com
john@example.com	john@example.com

More specific aliases have precedence over general catchall aliases. Postfix will find an entry for 'john@example.com' and find that email should be "forwarded" to 'john@example.com' - the same email address. This trickery may sound weird but it is needed if you plan to use catchall accounts. So the virtual_alias_maps mapping must obey both the "view_aliases" view and this "john-to-himself" mapping. Create a cf file /etc/postfix/mysql-email2email.cf for the latter mapping:

user = mailuser
password = mailuser2007
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM view_users WHERE email='%s'

Check that you get John's email address back when you ask Postfix if there are any aliases for him:

$> postmap -q john@example.com mysql:/etc/postfix/mysql-email2email.cf

The result should be the same address:

john@example.com

Now you need to tell Postfix that these two mappings should be searched by adding this line to your main.cf:

$> postconf -e virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf

Please note that the order of the two mappings are important here. Postfix will look for all matching entries in the first virtual-aliases mapping and use them as aliases. Only if there are no results found then Postfix will consult the second email2email mapping.

You did it! All mappings are set up and the database is generally ready to be filled with domains and users. Make sure that only 'root' and the 'postfix' user can read the cf files - after all your database password is stored there:

chgrp postfix /etc/postfix/mysql-*.cf
chmod u=rw,g=r,o= /etc/postfix/mysql-*.cf