Mail Server - Configuring Postfix to Use MySQL
Now that the MySQL database has been created, we need to move into configuring Postfix to access and use the details.
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
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.
Next, the forward details:
sudo nano /etc/postfix/mysql-forwards.cf
user = mailadmin password = newpassword dbname = mail query = SELECT destination FROM forwards WHERE source='%s' hosts = 127.0.0.1
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
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
Finally, create the file for the email address:
sudo nano /etc/postfix/mysql-email.cf
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.
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-*
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.
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.
Now we have Postfix configured to query the database whenever needed.
The next article concentrates on configuring saslauthd for our secure connections.
© 2015 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License
See license specifics and DISCLAIMER