Setting Up A mySQL *.cf in Postfix

Postfix is a powerful mail tool for anybody trying to send/receive mail on a Ubuntu box (I suspect they support way more UNIX systems). Using it with a mySQL database is even more powerful, and scalable when working with many virtual hosts and users.

Setting up this configuration file, and the query is relatively simple, and they do a great job in the Postfix Manual Table 5.

However, I’ll break it down a bit here.

The query string inside the main.cf file is fairly simple. For example:

virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf

From this, we can see that the query is for mysql, which it points to the file we create with the desired configuration.

We must keep in mind, that this file mysql_alias.cf will be queried from an outside program, with a query string (%s).

Going inside of mysql_alias.cf we can break it down into parts.

The first part is the connection details. Usually if the configuration files are on the same machine as the database, then you can use 127.0.0.1 (Note: this forces the machine to use TCP, rather than using localhost)

host = 127.0.0.1
user = <db user>
password = <db user password>
dbname = <database name>

The next part is the specific query you’ll be making to the database. This identifies the column of interest, the return value you’re looking for, and the table inside the database to query. It will also identify any additional conditions for the query.

query = SELECT <table column name that has value you want returned> FROM <table of interest> WHERE <table column to make the conditional selection from> = ‘%s’ AND <table column to make conditional case> = <conditional case>

A more specific query could be:

query = SELECT nickname FROM alias WHERE name = ‘%s’ AND active = 1

The traditional way to make this query, which is now outdated and vulnerable to security leaks may be identified as:

table=alias
select_field=nickname
where_field=nameadditional_conditions= AND active = 1

Once again, a full explanation of how to configure this can be found on the postfix.org manual.

If you’re unsure how to enter a query into mySQL, they have some help files at dev.mysql.com reference manual.

Hope this at least informs you what you may be just blindly following!

Leave a Reply

18 − thirteen =