Complete Virtual Mail Server/Postfix to database

PostgreSQL: UNIX socket setup
In this example local UNIX sockets will be used and thus postfix needs to be allowed access to the socket.

A quick verify shows all is well.

Postfix database access configuration
Now that Postfix and Postgres/MySQL are running and the necessary database tables have been created, it's time to tie these two together. For Postfix to know how to access the database however, a few access files need to be created with connection details. These files are stored in a subdirectory per database backend.

For PostgreSQL, this would be:

And for MySQL:

Access configuration files
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 as "user example@example.com doesn't exist" without much further explanations.

One way to better understand what goes wrong is to follow the earlier instruction to change "smtpd" to "smtpd -v" in the master.cf configuration file. By doing so, smtpd will be much more verbose (surprise) in the log file, including information of what SQL statement failed, with a hint of any syntactical errors.

Any logical errors, like selecting data from the wrong column, will typically not be logged, though.

Be warned: setting the -v flag, can cause plain text postfix passwords to be stored in the log file.

Virtual domains
Virtual domains list what postfix is the final destination for. In the database, this is stored in the domain table.

Virtual mailbox maps
Virtual mailbox maps are the virtual user to mailbox mapping for the domains specified.

Virtual aliases
Virtual aliases work like normal aliases, but in the virtual realm.

Configuration file permissions
Note that the database password is stored in the above files. Only Postfix should and will be reading them. Permissions should be adjusted accordingly.

Postfix configuration
With these files in place, it is time to adjust to point to the database. If some of these entries are not in the configuration file, add them at the bottom, otherwise place them accordingly.

Creation of testdata
Next, insert some test data into the PostgreSQL database. PostgreSQL Test Data

Alternatively, insert some test data into the MySQL database. MySQL Test Data

Mailbox disk storage
Postfixadmin should handle the setup of maildirs. However it is always a good excersize to have done it manually, at least once. A data directory needs to be created for each domain and its permissions need to be set.

Testing
Now it is time to restart postfix and see if it uses the new data.

Then, whilst running try sending a mail again via telnet from the localhost. Sending from any other host in the network should still fail.

This test, tests two mails, one to someone that should be delivered locally, and one that's delivered via an alias.