Now that the MySQL database has been created, we need to move into configuring Postfix to access and use the details.

Postfix files

To enable Postfix to use the MySQL database we need to create some text files.

Postfix will use the data contained in these files to connect to MySQL and submit a query such as selecting a domain to use when sending mail.

Take each file one at a time and you will see they are very simple in design - they contain the database name, the database user name (in this case 'mailadmin'), the database user password and then an SQL query that Postfix will execute to get the relevant details. domains

Domain Details

Let's start by creating the file used to find the domain details:

sudo nano /etc/postfix/mysql-domains.cf

Enter these details:

user = mailadmin
password = newpassword
dbname = mail
query = SELECT domain AS virtual FROM domains WHERE domain='%s'
hosts = 127.0.0.1

Remember to change the password to the one you set when you created the 'mailadmin' user in MySQL.

Forwarding

Next, the forward details:

sudo nano /etc/postfix/mysql-forwards.cf

Enter:

user = mailadmin
password = newpassword
dbname = mail
query = SELECT destination FROM forwards WHERE source='%s'
hosts = 127.0.0.1

Mailboxes

Each domain will have different mail boxes such as 'sales', 'info', 'jpierce' and so on. This script gets the correct mail box details:

sudo nano /etc/postfix/mysql-mailboxes.cf

Enter:

user = mailadmin
password = newpassword
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
hosts = 127.0.0.1

Email

Finally, create the file for the email address:

sudo nano /etc/postfix/mysql-email.cf

Enter:

user = mailadmin
password = newpassword
dbname = mail
query = SELECT email FROM users WHERE email='%s'
hosts = 127.0.0.1

These four files will enable Postfix to access the data in the 'mail' database and assign the correct details to any mail.

Permissions

Lastly, we do need to set the permissions on the files so we don't have anyone peeking at our database name and password.

We can do that by removing all permissions for the 'other' group. In other words, only the assigned user (in this case it will be the 'postfix' user) and those in the group can see the file details:

Then easy way to do this is to simply turn the permissions for the other group off:

sudo chmod o= /etc/postfix/mysql-*

Secondly, change the group ownership of the files to 'postfix' - at the moment they are owned by root. We want Postfix (and, later on, Courier) to access them:

sudo chgrp postfix /etc/postfix/mysql-*

Edit main.cf

The main Postfix configuration file is know as 'main.cf'.

Let's go ahead and edit that:

sudo nano /etc/postfix/main.cf

The default entries include the hostname that was set during the Postfix installation.

As such, the bottom part of the file looks like this:

myhostname = mail.democloudserver.com
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = /etc/mailname
mydestination =
relayhost =
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all

You will find the 'myhostname' and 'mydestination' already have the hostname set - you will need to remove any entries for the 'mydestination' field.

Virtual Files

Now we need to add the details of the four files we created in the previous article so Postfix knows to refer to those in any mail execution.

At the bottom of the file add these lines:

virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-forwards.cf, mysql:/etc/postfix/mysql-email.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-mailboxes.cf
virtual_mailbox_base = /home/vmail
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
 
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

You can see the references to the files we created and also the references to the 'vmail' user we created (where the mail is physically located).

The last line lets Postfix know what to search for in a mail domain to get the relevant details for the database query.

Summary

Now we have Postfix configured to query the database whenever needed.

The next article concentrates on configuring saslauthd for our secure connections.

Previous Article
Next Article