Complete Virtual Mail Server/Postfix to Database

Installing Postfix
Postfix installation is quite straight forward. A basic installation instruction for Postfix should be followed.

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

PostgreSQL
A database-user to query the database is required:

Also the postfix user needs some permissions on the tables:

MySQL
If MySQL is used instead of PostgreSQL then follow the following instructions. Download the genericmailsql.sql dumpfile for this step as it will be needed soon.

First install MySQL:

Create an initial database:

Follow the on-screen instructions for adding a root password within MySQL as otherwise the database will be wide open.

Start the MySQL database server and create the mailsql database:

Fire up the mysql client to grant the proper rights to the mailsql database:

Verify that the new mailsql user can connect to the mysql server:

The 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

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

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 any further explanation.

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:

MySQL
Set up the necessary configurations for postfix to interact with the database for all its other transport needs. Remember to replace each value with the name of the proper user, user id, password, alias, email address, and so on.

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

MySQL/MariaDB
As of Postfix 2.0.x, there were a number of significant changes over the 1.1.x release. Notably the transport, virtual-gid, and virtual-uid tables are no longer necessary. The tables are still included to support potential different use cases.

Testing the database connection
Postfix needs to be restarted to have 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: