Now 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.
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', 'paul' 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-*
We created four files that postfix will use to query the 'mail' database whenever it need to enter details into an email. This ensure any headers are correctly set when sending email from multiple domains and users.
For security reasons, we removed 'other' permissions from the created files. This prevents anyone looking at the files and getting the database username and password.
The next article will continue with configuring postfix to use the MySQL database.