Using DBMail with Postfix

This document will help you set up the Postfix mailer to deliver mail to a DBMail instance.

Prerequisites

Before attempting the configuration steps suggested here, you should already have the following prerequisites in place:

A DBMail installation
A Postfix installation
Packages may be available from your OS vendor, or your can download the source yourself from a postfix.org mirror.

It is also recommended that you read through this entire page before trying any of the steps given here.

This document will assume that your Postfix configuration files are located at /etc/postfix/

Make sure you've read and executed the steps from the INSTALL file before configuring your MTA. You'll want to be sure that DBMail is ready to deliver mails before sending your messages to it!

Delivery Methods

You must select from DBMail's two supported delivery methods: LMTP and SMTP.

dbmail-lmtp features network delivery, better performance , reliable performance and bounce handling. It uses a long-running dedicated connection to the authentication and storage database. If you need server-side filtering with LMTP delivery, you must enable sieve support in DBMail.
dbmail-deliver uses piped delivery through a short-lived process for local delivery only. A new database connection is created and used for each incoming email, reducing performance as compared to LMTP. Server-side filtering with SMTP delivery can be accomplished using a command-line filtering tool such as Procmail, or with DBMail's optional sieve support.

Postfix interfaces directly with MySQL or PostgreSQL to check for aliases before sending mail to dbmail (pipe or LMTP). This improves performance, especially on high-traffic servers (SPAM!). In the case of the use of dbmail-smtp, this is especially useful, because it saves a process starting up and connecting to the database on every message.

To use MySQL or PostgreSQL with Postfix, first install the postfix-mysql or postfix-pgsql packages (they're in Debian, probably also in other distros).

Using LMTP Delivery

in /etc/postfix/master.cf add:

dbmail-lmtp     unix    -       -       n       -       -       lmtp

If you want verbose output in the mail log, add -v to lmtp, like this:

 dbmail-lmtp     unix    -       -       n       -       -       lmtp -v

Note that this setting will result in a LOT of extra output in your logs.

If one or more destinations in your mydestination list are not DNS-resolvable, DNS lookups must be disabled for the dbmail-lmtp daemon, like this:

dbmail-lmtp     unix    -       -       n       -       -       lmtp
        -o disable_dns_lookups=yes

If you want to send all the email the MTA accepts to DBMail, use the following setting in /etc/postfix/main.cf:

virtual_transport = dbmail-lmtp:<host>:<port>

If you want to decide if DBMail should be used per domain please add the following in /etc/postfix/transport:

<domain>        dbmail-lmtp:<host>:<port>

Where <domain> should replaced by the mail domain you receive mail for. It is possible to have several domain entries here. For <host> and <port> fill out the host and port on which the dbmail-lmtp daemon runs. If unsure about which port they run on, check your dbmail.conf file. The standard port for the lmtp service is port 24. An example of a transport file is below:

example.com             dbmail-lmtp:localhost:24
anotherexample.com      dbmail-lmtp:localhost:24

Postfix needs to lookup if the recipient domains exist. Otherwise, Postfix will reject your DBMail recipients with a “User unknown in local recipient table” error.

To do this, you need to enable the MySQL- or PGSQL-module in Postfix and add

virtual_mailbox_domains = mysql:/etc/postfix/sql-virtual_mailbox_domains.cf

or for PostgreSQL

virtual_mailbox_domains = pgsql:/etc/postfix/sql-virtual_mailbox_domains.cf

in /etc/postfix/main.cf

After that create the file and add the following MySQL-statements (adjust it to your needs if you use Postgres):

user     = <SQL-username>
password = <SQL-password>
hosts    = <SQL-host>
dbname   = <SQL-database>
query    = SELECT DISTINCT 1 FROM dbmail_aliases WHERE SUBSTRING_INDEX(alias, '@', -1) = '%s';

For postgresql replace the query by

query    = SELECT DISTINCT 1  FROM dbmail_aliases WHERE SUBSTRING(alias FROM POSITION('@' in alias)+1) = '%s';

The query MUST return a non-domain value (just use “1”) if the queried domain exists or a empty set if it doesn't exist. Returning domains here will break the delivery chain of Posfix!

Note: In case you use mail addresses as usernames in DBMail, you also have to include 'dbmail_user' in the SQL-query.

If you also want to let Postfix check the recipient addresses to reduce load on DBMail by false email addresses, add

virtual_mailbox_maps = mysql:/etc/postfix/sql-virtual_mailbox_maps.cf

or for PostgreSQL

virtual_mailbox_maps = pgsql:/etc/postfix/sql-virtual_mailbox_maps.cf

in /etc/postfix/main.cf and add the following content (MySQL):

user     = <SQL-username>
password = <SQL-password>
hosts    = <SQL-host>
dbname   = <SQL-database>
query    = SELECT 1 FROM dbmail_aliases WHERE alias='%s';

For postgresql replace the query by

query    = SELECT DISTINCT 1  FROM dbmail_aliases WHERE alias= '%s';

Make sure not to return the addresses !!!

If you want to use wildcards (user@ and @domain) with postgresql, use this query:

query    = SELECT DISTINCT 1 FROM dbmail_aliases where alias='%s' OR alias=SUBSTRING('%s' FROM POSITION('@' in '%s')) OR ( ( SELECT DISTINCT 1 FROM dbmail_aliases WHERE SUBSTRING(alias FROM POSITION('@' in alias)+1) = SUBSTRING('%s' FROM POSITION('@' in '%s')+1) )=1 AND alias=SUBSTRING('%s' FOR POSITION('@' in '%s')));

It searches for the exact match (my@cow.com), then a matching domain wildcard (@cow.com). The subselect in the middle prevents the system from accepting mail for valid users at invalid domains (my@moose.com). The last match (AND) allows user wildcards for accepted domains.

The interaction here seems strange, but it should match the dbmail rules:

exact match
user wildcard with valid domain (domain mentioned somewhere in the alias table)
domain wildcard

Using Piped SMTP Delivery

in /etc/postfix/master.cf add:

 dbmail-smtp    unix  -       n       n       -       -       pipe
         flags=  user=<dbmailuser>:<dbmailgroup>
         argv=/usr/local/sbin/dbmail-smtp -d ${recipient} -r ${sender}

where <dbmailuser> and <dbmailgroup> should be the user and group the dbmail-smtp program should run as. The ${recipient} and ${sender} fields are filled in by Postfix.

To send all email to DBMail, add this in /etc/postfix/main.cf

 virtual_transport = dbmail-smtp:

If you want to decide whether or not to send to DBMail per domain, add this in /etc/postfix/transport:

 <domain>        dbmail-smtp:

See the section on running Postfix with LMTP if you don't understand the transport file.

now run:

 # postmap /etc/postfix/transport
 # postfix reload

And your mail will be delivered!

Additional Notes

It's possible to use dbmail-lmtp on some domains, and dbmail-smtp on other domains. Just make both entries in /etc/postfix/master.cf and add transport entries for both.

1) meaning that the front-end Postfix server need not be on the same machine running the DBMail LMTP server