Reticularium

Update 2009.11.25: of course it’s not related to Postfix only. Any SMTP + virtual hosts solution with data stored in database should rely on queries to that database, and most, if not all, howtos you can find don’t honor IMAP subfolders. And of course it’s not about Spam subfolder only. The queries below make it possible to use IMAP subfolders in similar setups. That is, with these queries you can accept email messages sent to addresses like myname+myfolder@mydomain.com

If you ever set up a popular bunch of Postfix + Amavisd-new + PostgreSQL/MySQL with virtual domains support, you probably were disappointed by the fact that by default all messages filtered out by anti-spam and anti-virus filters go to the system’s quarantine folder. This is obviously not good, because your users want to see those messages to be sure that there were no “false positives”, and no important messages they are waiting for were marked as spam and moved to the quarantine folder and thus lost (users don’t have access to that folder, and anyway, the quarantine folder is common for all users, so making it users-readable isn’t a solution).

Note that this works only for IMAP!

For POP3 users the only reasonable solution is to make Amavis mark messages as spam so that email client programs could filter them out. But if you want a full-featured email server you need to make it able to work with those cool IMAP subfolders. Here’s how.

The trick is simple. Here’s what you need:

In /etc/amavisd.conf:

First of all allow spam and virus messages to reach users folders, by default they are bounced and sent to quarantine. Change variables $final_*_destiny to D_PASS:

1
2

$final_virus_destiny      = D_PASS; # (defaults to D_DISCARD)
$final_spam_destiny       = D_PASS;  # (defaults to D_BOUNCE)

You might also want messages with bad header and banned messages to go to users folders, there are examples in amavisd.conf and settings are similar.

Next, check $sql_select_policy. It should be uncommented and contain a query that pretends that it asks some table about the current message recipient and returns an answer that this recipient is a local user with policy_id=99. Don’t waste your time trying to figure out what this policy_id is unless you want per user policy_id, but you should have a real table then and in this case you should know what you are doing.

This query doesn’t have to distinguish one user from another, so checking domain only is enough. If you have a separate table containing your virtual domains, use its name in this query:

$sql_select_policy = "SELECT id, policy_id, local, fuser.id FROM (SELECT 1 as id, 99 as policy_id, 'Y' AS local FROM domains WHERE domain IN (%k)) AS fuser";

Here “domains” is the name of my table, “domain” is the name of the field, containing domain names.

Note that most probably you need $sql_lookups_no_at_means_domain variable to be set to 1.

Now uncomment these lines:

1
2

@addr_extension_virus_maps  = ('virus');
@addr_extension_spam_maps   = ('spam');

and edit if required.

“virus” and “spam” are subfolders’ names where infected/junk messages will go to, you can change these names as you wish.

Also look at the examples given in amavisd.conf near these settings.

$recipient_delimiter = '+';

This must match the equivalent setting in /etc/postfix/main.cf.

Now you need to find a file corresponding to the setting ‘virtual_mailbox_maps’ in the /etc/postfix/main.cf. I.e. in my case the line in /etc/postfix/main.cf looks like:

virtual_mailbox_maps = proxy:pgsql:/etc/postfix/pgsql/pgsql-virtual_mailboxes.cf

So /etc/postfix/pgsql/pgsql-virtual_mailboxes.cf is the file which contains the actual trick. In this file I put a line looking like this:

SELECT SPLIT_PART(email,'@',2) || '/' || SPLIT_PART(email,'@',1) || '/.' || SPLIT_PART(SPLIT_PART('%s','@',1),'+',2) || '/' FROM users WHERE email=SPLIT_PART(SPLIT_PART('%s','@',1),'+',1) || '@' || SPLIT_PART('%s','@',2)

I use the PostgreSQL database, so it is a PostgreSQL syntax. MySQL syntax is somewhat similar, I will add the correspondent query later.

Update 2009.11.25: MySQL query:

SELECT CONCAT( (SELECT maildir FROM users WHERE email=CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( '%s','@',1 ) ,'+',1 ) ,'@', SUBSTRING_INDEX( '%s','@',-1 ) ) AND active='1'),'.', SUBSTRING('%s',LOCATE('+','%s') + 1,(SELECT '%s' REGEXP '\\+') * (LENGTH(SUBSTRING_INDEX( '%s','@',1 )) - LOCATE('+','%s'))),'/')

The query is much more complicated than the one for PostgreSQL. Note that this query assumes that there is a “maildir” field in database table. If there wasn’t, as in PostgreSQL query above, the MySQL query would be much longer.

Update: MySQL query for the case if users table doesn’t have maildir field:

SELECT CONCAT(SUBSTRING_INDEX( '%s','@',-1 ),'/',SUBSTRING_INDEX(SUBSTRING_INDEX( '%s','@',1 ) ,'+',1 ),'/.',SUBSTRING( '%s',LOCATE('+','%s') + 1,(SELECT '%s' REGEXP '\\+') * (LENGTH(SUBSTRING_INDEX( '%s','@',1 )) - LOCATE('+','%s'))),'/') FROM users WHERE email=( SELECT CONCAT(CONCAT(SUBSTRING_INDEX( SUBSTRING_INDEX( '%s','@',1 ) ,'+',1 ),'@',SUBSTRING_INDEX( '%s','@',-1 ))))

Let’s look closer to the query string (you can skip this detailed query description if everything is working ;-) )

Obviously I have a database (defined in /etc/postfix/main.cf) containing a table named “users” . The “email” field in this table contains users’ e-mail addresses in their usual form, i.e. something like user@example.com. The main idea of the string above is to split username and domain parts of the address to find out where an incoming message must be saved to. But the abovementioned settings in amavisd.conf make the email address more complicated. Now it may look like user+spam@example.com. By the way, this functionality can be and is often used by users themselves. For example, user can subscribe to some mail list using the address like user+listname@example.com, and all messages from that list will go to the “listname” subfolder of the user’s Inbox.

SELECT SPLIT_PART(email,'@',2) || '/' || SPLIT_PART(email,'@',1) || '/' FROM users WHERE email='%s'

The result of this query is example.com/user/, that is exactly the path to where the user’s mailbox is located (note the last slash, it is important). But if the recipient’s address was user+spam@example.com, the result would be example.com/user+spam/, which is obviously not what we want. Postfix handles this correctly though, i.e. cuts off +spam and puts message into the right folder. But we need it to interpret spam as a subfolder instead of just cutting it off.

So we have to add some complexity to the query. Let’s start from the end and edit the part of the query beginning from the FROM clause. We cannot use just FROM users WHERE email='%s', because in our case %s = user+spam@example.com, and database record is user@example.com, so the only thing we can get as a result of such query is a “user not found” error. Thus we have to split the %s up as follows.

SPLIT_PART(SPLIT_PART('%s','@',1),'+',1)

The result of this is user

FROM users WHERE email=SPLIT_PART(SPLIT_PART('%s','@',1),'+',1) || '@' || SPLIT_PART('%s','@',2)

OK, we’ve just made Postfix do what it could do without this clause: find the recipient’s address in the database and store messages in the proper folder. But it still isn’t able to understand the '+' part of the username. We have already gotten the path where messages must be stored (example.com/user), but we need it to be longer, like example.com/user/.spam/ (note the dot before ‘spam’, it means that this is a subfolder).

Let’s add the dot first, the SELECT clause now looks like this:

SELECT SPLIT_PART(email,'@',2) || '/' || SPLIT_PART(email,'@',1) || '/.' || '/'

It is the main trick. If the recipient’s address doesn’t contain any "+" additions, it must be served correctly. What we see in this last query is exactly this situation, no "+" addressing is included in the query yet. So, if the message’s recipient is user@example.com, Postfix issues a query and gets the result example.com/user/./ —and this works OK, since it is identical to example.com/user/ absolutely (./ means “this folder”).

SELECT SPLIT_PART(email,'@',2) || '/' || SPLIT_PART(email,'@',1) || '/.' || SPLIT_PART(SPLIT_PART('%s','@',1),'+',2) || '/'

I have added this part between ’/.’ and the last ’/’. So what we have achieved now: if recipient is user@example.com, the query result will be /example.com/user/./ And if recipient is user+spam@example.com, the result will be /example.com/user/.spam/ Bingo!