Mail server - Configuring Postfix to use MySQL - part 1

Now 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

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.

Forward

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', 'paul' 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

Done

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-*

Done.

Summary

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.

PickledOnion

Article Comments:

anush commented Mon Oct 20 13:05:29 UTC 2008:

How do I specify the mysql port in the cf files if I'm using something other than the default 3306 port for my mysql instance?

anush commented Mon Oct 20 13:16:27 UTC 2008:

Found it! To specify the port, just include it as part of the hosts information:

hosts = 127.0.0.1:3333

Eric commented Thu Jun 16 07:57:03 UTC 2011:

At the end of this article you say to change the group ownership of the config files to "postfix". But a few articles prior ("Mail server - vmail user and mailboxes") you say to create the group "vmail".

Given that Postfix used to be called VMailer, I suspect that these are supposed to refer to the same group. Is this right?

Jered commented Thu Jun 16 22:34:43 UTC 2011:

Actually, there should be a "postfix" user created by aptitude when postfix was installed. The "vmail" user gets used later in the series, when designating the location and permissions where mail will be stored.

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)