Complete Virtual Mail Server/Postfix to Database

Installing Postfix
Postfix installation is quite straight forward. A basic installation instruction can be found here on the wiki for Postfix.

Linking Postfix
Once postfix is working on its most basic level its time to link it to the database.

PostgreSQL
When using postfix on the same host, the local unix socket connection is recommended. For this postfix needs rights on the socket.

Also a database-user to query the database is required.

Also the postfix user needs some permissions on the tables.

MySQL
If you use MySQL instead of PostgresSQL then follow this instruction. You'll need the genericmailsql.sql dumpfile for this step.

Installing and configuring MySQL

Your new database has default values and tables set up for two domains. The following tables are included:


 * alias - local email alias and mailman alias information.
 * relocated - relocated user email address maps
 * transport - default mail transport information for all domains you are hosting
 * users - all user account information
 * virtual - virtual domain email alias maps

alias table sample

user table sample

The values of the  uid and gid should be those of the   user and group.

transport table sample

virtual table sample

Querying
All these queries are tested on the database to ensure the queries are written properly. This does require however that the database has been filled to be useful. When 'postfixadmin' is used, this can be done easily from the UI. Otherwise, simple INSERT SQL queries need to be written and executed.

PostgreSQL
Linking postfix to a database isn't that special, postfix just executes predefined SQL routines. These SQL queries will be stored in a file per query in a directory.

Be aware that any errors in the configuration information in these files can be pretty tricky to track down. Be meticulous with both the contents of the files and their names. Many errors in these files will be announced in the mail log such as "user example@example.com doesn't exist" without much further explanations.

It's best to test the query on the database (using copy paste) to ensure no typo's exist in the query.

Also here, it's best again to execute the query on the database.

Run the query on the database to verify its output.

Access rights
Only postfix should have access rights to these files, as they contain passwords.

Testing the database connection
Postfix needs to be restarted to make it connect to the database.

Check for any problems in, such as incorrect permissions.

Using telnet the mailserver can be tested again to check whether the database is properly used.