Author: Matthew J. Salerno
While building my first DBMail server, I was happy to see that the full migration from my old mail server worked almost flawlessly, I ran into one snag. My company has many domains, and most of those domains are just aliases. I knew that I could create an alias for every user for every aliased domain, but that would exponentially increase the size of my alias table, I could have also created and alias like @foo.bar →deliver_to→ @foo.bar.uk, but I wanted to do something a bit cleaner and easier to manage since they are constantly adding and removing alias domains. So what I did was create a table for managing the domains. I named it “dbmail_vdomains” and it's structure is very minimal. It contains the following columns: id, domain, comment,pridomain, client_idnr.
Field Type Null Default Extra id int(7) No auto_increment domain varchar(40) No comment varchar(40) Yes NULL pridomain varchar(40) Yes NULL client_idnr bigint(21) No 0
When I created my user accounts, I used a unique client_idnr for each new domain. So, each user had it's main alias as firstname.lastname@example.org deliver_to user_idnr, and every account and alias for the foo.bar domain uses the same client_idnr. By giving all of the accounts in the same domain the same client_idnr, you can use sql to do some nice things. The next step is to populate the table.
id domain comment pridomain client_idnr 1 foo.bar Primary Domain NULL 6 2 domain.tld Primary Domain NULL 7 3 foo.bar.uk Alias for foo.bar foo.bar 6 4 domain.tld.mx Alias for domain.tld domain.tld 7 5 foo.bar.mx Alias for foo.bar foo.bar 6
With the table populated, now we must tell postfix to use the table for aliases. I appended “mysql:/etc/postfix/valiasdom.cf” to the virtual_alias_maps line.
user = dbmail password = password dbname = dbmail table = dbmail_vdomains select_field = concat( '@', pridomain ) where_field = concat( '@', domain ) additional_conditions = and pridomain is not NULL
This will create the ”@foo.bar →deliver_to→ @foo.bar.uk” alias.
The other thing I did was to set my mydestination to “$virtual_alias_domains” and then set virtual_alias_domains to mysql:/etc/postfix/virtual-domains.cf
user = dbmail password = password dbname = dbmail table = dbmail_vdomains select_field = domain where_field = domain
After putting the server up and watching the logs all day long, I noticed that about 50% of all mail was spam, and even worse, it was being sent to non-existing e-mail addresses. I know that postix will deny mail for non-existing users (local_recipient_maps), but when you alias an entire domain, it won't block e-mail to non-existing users for the aliased domain. So e-mail sent to email@example.com will immediatly bounce back with a 550, but if it were sent to firstname.lastname@example.org, then postifx would accept the mail, and then bounce it. Since I am doing spam, content and virus checking for every piece of mail, I didn't want to waste the resources on this junk. So I implemented the check_recipient_access in the smtpd_sender_restrictions.
smtpd_sender_restrictions = permit_mynetworks, permit_sasl_authenticated, check_recipient_access mysql:/etc/postfix/all-users.cf
user = dbmail password = password hosts = localhost dbname = dbmail table = dbmail_aliases left join dbmail_vdomains on dbmail_vdomains.client_idnr = dbmail_aliases.client_idnr select_field = case when count(dbmail_aliases.alias) > 0 then "OK" when count(dbmail_aliases.alias) = 0 then "REJECT" END AS 'access' where_field = concat(LEFT(dbmail_aliases.alias, LOCATE('@', dbmail_aliases.alias)),dbmail_vdomains.domain)
The check_recipient_access will basically lookup an e-mail address and will see if the mail server will accept mail for that account. So all-users.cf, uses the dbmail_vdomains and the dbmail_aliases tables joining them using the client_idnr. So it creates a list of all possible e-mail addresses including all aliases including all aliased domains. When postfix is receiving an e-mail, it will check the recipient e-mail address to see if it accepts mail for that address. If it finds an e-mail address that matches, it will return, OK and the mail will be accepted and processed, if no e-mail address is found, it will return “REJECT”, and postfix will reject the mail, not accepting it for processing. So far my mail server has rejected over 50,000 pieces of mail because of this. The overhead of looking up the e-mail address is much much less than processing mail that couldn't be delivered in the first place.