Postfix subaddressing (with PostgreSQL)

published on on Mail, FreeBSD, Selfhosted

I have so many accounts for various web-based solutions, I barely can remember a few. And they send emails. So many that it's sometimes hard to not loose the overview in my mailbox.

When you run your own mail server, it's easy to set up a dynamic aliases for your mailserver based on a regular expression pattern that allows to e.g. have a own email address for each service you register. The emails sent to that alias go into special mailboxes instead of your default INBOX folder. This is called "subaddressing", sometimes also referred to as "plus addressing" or "tagged addressing".

So for example:

foo@domain.tld -> INBOX
foo+amazon@domain.tld -> amazon
foo+reddit@domain.tld -> reddit

In this mail server setup I use FreeBSD, Postfix, Dovecot and PostgreSQL as lookup table, but it should work as well with similar setups like a GNU/Linux system and MySQL (although you will have to adjust the queries a bit)...

First, we connect to our database (psql -U foouser foodb) and extend our alias lookup table like this:

ALTER TABLE alias ADD COLUMN isregex smallint not null default 0;

Now we can directly add a alias, so we don't have to connect to our DB multiple times:

INSERT INTO alias (address, goto, domain, isregex, active) VALUES ('foo+([^@]*)@domain.tld', 'foo@domain.tld', 'domain.tld', 1, 1);

Please be aware that you could use almost any other pattern that only consists of valid characters for the local-part of an email.

Now create a postfix configuration for looking up that alias. In my case I called the file within my postfix configuration folder /usr/local/etc/postfix/pgsql.

hosts       = localhost
port        = 5432
user        = foouser
password    = foopass
dbname      = foodb
query       = SELECT alias.goto FROM alias, domain WHERE '%s' ILIKE CONCAT('^', alias.address, '$') AND SUBSTRING(alias.address, 1, 1) != '@' AND isregex = 1 AND alias.domain = '%d' AND alias.domain = domain.domain AND = 1 AND domain.backupmx = 0 AND = 1

Remember to set correct ownership to that file like chown -R postfix:postfix This file may also vary depending on your table setup.
Now we have to extend the file to add the new configuration as a lookup map:

virtual_alias_maps =

After a simple service postfix restart your mailserver should start doing the magic. Keep an eye on the logs for the first times... Cheers!

P.S. There are also some hosted email providers allowing this, like Gmail and iCloud.