When setting up my current servers email system, I managed to get almost everything working via SQL tables that allowed me to use a web interface for the day to day administration. It also allowed users to control most aspects of their accounts, resulting in very low administrative load. The one thing that I never quite managed to get working was setting up more complex aliases – until today.

My configuration provides redirections and aliases as seperate concepts and entities. A redirection is simply redirecting mail from one email address to another with anything more complex being an alias. Users of the system can easily create redirections for addresses they control, but aliases are only created by administrators or those given permission. The distinction has worked well.

Getting the redirections working proved as simple as adding a mysql query to the virtual_alias_maps setting in the main.cf file for postfix. The mysql file looks like

user = ??? password = ??? dbname = ??? query = SELECT destination FROM mbox_redirection WHERE address='%s' hosts = unix:/var/run/mysqld/mysqld.sock

However, getting the full aliases to work took a bit more effort. In essence I want to replicate the functionality of the /etc/aliases file using mysql. The initial problem is that with a pure virtual setup, as we have, the contents of /etc/aliases are meant to be used by the postfix local daemon, not the virtual daemon that we rely on for the majority of our requirements.

Adding entries to just the local maps will result in an unknown recipient error as they won’t appear in the virtual maps. This means we need to have 2 entries for each alias – one for the virtual map and one for the local map. Adding every entry twice seems like a lot of work, but there are ways round it when we think about how the maps get their data.

The next poblem is that we need to bridge the divide between virtual and local map. This means we need some way of configuring a virtual address to point at a local address. The solution I settled upon was to use localhost for local addresses, so that blah@localhost would point to a ‘blah’ in the local map and would be accepted as a valid redirect internally. To accomplish this I needed to add localhost to the mydestination setting in main.cf,

mydestination = $myhostname, mail.$mydomain, localhost

Creating a table for the aliases in mysql resulted in something that looked like this

CREATE TABLE mbox_alias ( id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, name varchar(50) NOT NULL, address varchar(75) NOT NULL, alias varchar(1024) NOT NULL, );

In the vitual_alias_maps I now need to map from an incoming address (address in the above table) to a local address that will then be mapped to the alias (called alias in the above table) I have provided. This I do by using the name field, using the mysql map file shown

user = ??? password = ??? dbname = ??? query = SELECT concat(name, '@localhost') as destination FROM mbox_alias WHERE address='%s' hosts = unix:/var/run/mysqld/mysqld.sock

This will map address to name@localhost, which will cause postfix to look for name@localhost in it’s local maps. To provide a valid mapping I added the following map file to alias_maps in main.cf

user = ??? password = ??? dbname = ??? query = SELECT alias as destination FROM mbox_alias WHERE name='%s' hosts = unix:/var/run/mysqld/mysqld.sock

alias_maps = hash:/etc/aliases proxy:mysql:/etc/postfix/mysql/aliases.cf

As sometimes an example makes things simpler (my simple mind often needs one),

  1. Create an entry for an address of myscript@example.com, with a name of myscript and an alias of |/home/me/scripts/hello
  2. Email for myscript@example.com arrives
  3. Virtual lookup resolves myscript@example.com into myscript@localhost
  4. Local lookup for myscript find the alias |/home/me/scripts/hello
  5. Local daemon pipes message to /home/me/scripts/hello

With this in place, I’m able to add aliases via the web interface and have them available immediately. If I need more complex redirections, then I can add a regex file with patterns and redirect those to @localhost addresses in the same way.

There are likely better ways of doing this, but this took me a while to figure out and so maybe this will help someone else.