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

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

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

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

Lastly, edit one more time.

For security reasons you should change the permissions of the various :

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 if you wish to use them.

Now, if all went well, you should have a functioning mailhost. Users should be able to authenticate against the sql database, using their full email address, for pop3, imap, and smtp. I would highly suggest that you verify that everything is working at this point. If you run into problems (with as many things as this setup has going on, it's likely that you will) check the troubleshooting section of this howto.

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.