Vidax.net – The blog

I decided recently to migrate my emails to my own Postfix. I installed and configured Postfix + MySQL + Courier-Imap + Amavis + Clamd + SpamAssassin + Maildrop + PostfixAdmin. It is very impressive to count the tutorials you must mix up to get all these working together.

Anyways, that works. PostfixAdmin is a great opensource PHP app that can manage online MySQL domains, aliases and mailboxes for Postfix. One of the features offered by PostfixAdmin is the domain aliasing. This means that you may redirect any email from a domain to another.

Unfortunately, Postfix does not seem to support this feature out of the box. The SQL query supposed to find the final recipient for an email was getting a bit complicated: if this domain is an alias, redirect to this domain, and check whether mailbox or alias exists for this domain, and if there is an alias, redirect to the final mailbox… Too complicated in one query. So I decided to write my first MySQL function:

FUNCTION `get_email_alias`(
myemail VARCHAR(255)
) RETURNS varchar(255) CHARSET utf8
BEGIN

DECLARE mygoto VARCHAR(255);
DECLARE sdomain VARCHAR(255);
DECLARE ddomain VARCHAR(255);

SELECT SUBSTRING(myemail, INSTR(myemail, '@')+1) INTO sdomain;

SELECT target_domain
FROM alias_domain
WHERE alias_domain = sdomain
AND active = 1
LIMIT 1
INTO ddomain;

IF ddomain IS NOT NULL THEN
SELECT REPLACE(myemail, sdomain, ddomain) INTO myemail;
END IF;

SELECT goto
FROM alias
WHERE address = myemail
AND active = 1
LIMIT 1
INTO mygoto;

IF mygoto IS NOT NULL THEN
RETURN mygoto;
END IF;

RETURN null;
END

Create this function in your Postfix database (postfix, in my case). At this stage, you may test your function by calling it:

SELECT get_email_alias('axel@mydomain1.tld');

If it returns properly the final destination, you may adapt the Postfix configuration in consequence. Edit your virtual alias config file (in my case: mysql-virtual_aliases.cf) and set:

hosts = 127.0.0.1
user = <your_user>
password = <your_password>
dbname = <your_db>
query =  SELECT get_email_alias('%s')

You’re done: you may set domain aliases or mailbox aliases into PostfixAdmin. That will work..

Tip: watch your email server logs to make sure you didn’t break anything…

Popularity: 10% [?]