Add a Catch-All Account with Postfix-Mysql

This manual tells you how to properly implement a catch-all account with postfix-mysql. In a mail server, a catch-all account is a virtual trash bin receiving emails for [email protected]. Because the catch-all account only accepts messages for email aliases that you don’t intend to use, it will contain almost 100% spam messages. In short, messages for your email address [email protected] should make it into your inbox, whereas all the rest accumulates in the catch-all.

Why You Want to Have a Catch-All Account

You will want to have a catch-all account for your private mail server because knowledge is power. Spammers may probe your mail server for existing email addresses by sending messages to all kinds of made-up account names. If you reject such messages, they know that the spammed alias does not resolve to an inbox. They can keep doing this until they find an alias for which you don’t bounce messages, and voilà, they have your email address.

Also, at times legitimate messages will be sent to misspelled aliases. In a catch-all box, you pick these up.

Moreover, you can make up aliases on the spot, whenever you need them. Say you by something from an online shop gigagain.com. Instead of giving them your private email address [email protected], you pass an address [email protected]. Later, you create an alias [email protected] for your inbox. And if they ever start spamming you can opt out by deleting their alias.

How to Implement a Catch-All for Postfix-Mysql

For the catch-all account for Postfix-Mysql, I am referring to a setup with domains, aliases and users tables as described here. Emails to your aliases joe and joe.miller will go to your inbox [email protected] with everything else rejected.

First, you need to create a second account for the catch-all. At the mysql prompt for maildb, do:

insert into users (id, name, maildir, crypt) values
('[email protected]', 'dumpster_yourdomain_com',
 'yourdomain.com/dumpster/',
 encrypt('loginpwd', CONCAT('$5$', MD5(RAND()))) 
);

Now, as I found documented elsewhere, you can activate the catch-all by adding an alias ‘@yourdomain.com’ to the aliases table. This does not work properly, but still for demonstration:

insert into aliases (mail, destination)
values ('@yourdomain.com', '[email protected]');

When I tried this, having an alias ‘@yourdomain.com’ does result in all unused aliases sent to the dumpster. However, my good aliases like [email protected], ended up in the trash as well.

A Catch-All Only For Unused Aliases

So what we need is a catch-all mechanism that redirects messages to the dumpster only if no alias or inbox can be found.

There are several solutions to the problem implementing the catch-all logic in SQL. Here is one: edit the Postfix main configuration /etc/postfix/main.cf and change the line for the virtual aliases map:

#virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf
virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf,mysql:/etc/postfix/mysql_alias_catchall.cf

So the logic we need to implement in SQL is in a secondary alias query that shall return a mailbox only if no matching alias or user exists. The proposed syntax works only with newer versions of MySQL or in my case MariaDB 10.3.32.

root:/etc/postfix# cat mysql_alias_catchall.cf 
user=mail
password=YourMaildbPw
dbname=maildb
hosts=127.0.0.1
query=with inarg as (select '%s' as arg), tmp as (select substring_index(arg,'@',-1) as dom from inarg where arg regexp '.+@.+' and arg not in (select mail from aliases union select distinct destination from aliases union select distinct destination from aliases_catchall union select distinct id from users)) select destination from aliases_catchall a inner join tmp t on t.dom=a.mail and a.enabled=1

Now, we add a second alias table to maildb:

CREATE TABLE `aliases_catchall` (
`pkid` smallint(6) NOT NULL auto_increment,
`mail` varchar(120) NOT NULL default '',
`destination` varchar(120) NOT NULL default '',
`enabled` tinyint(1) NOT NULL default '1',
PRIMARY KEY  (`pkid`),
UNIQUE KEY `mail` (`mail`)
) ;

Finally, for the catch-all, we insert this record into aliases_catchall:

insert into aliases_catchall(mail, destination) 
values ('yourdomain.com', '[email protected]');

How Does the Catch-All for Postfix-Mysql Logic Work?

Let’s analyze the query, which is indeed a little bit detailed.

with inarg as (select '%s' as arg),
tmp as
(select substring_index(arg,'@',-1) as dom from inarg 
  where arg regexp '.+@.+' and arg not in 
 (select mail from aliases
   union 
  select distinct destination from aliases
   union
  select distinct destination from aliases_catchall
   union select distinct id from users
 )
) 
select destination from aliases_catchall a
inner join tmp t on t.dom=a.mail and a.enabled=1

The ‘with’ syntax lets us create temporary tables within SQL statements. We create two of them, inarg and tmp. Inarg allows us to reference the email alias, with which postfix-mysql replaces ‘%s’, multiple times. In tmp, the ‘substring_index(arg, ‘@’, -1)’ then extracts the domain name from the alias.

The ” where arg regexp ‘.+@.+’ ” clause then prevents returning results for just domain names, which is a strange postfix-mysql logic check.

Furthermore, the “args not in” restriction makes sure the secondary alias query only returns results if no other alias or user matches. We get those other aliases or users from the union of all mail aliases, users plus catchall accounts.

Finally, we join the aliases_catchall and tmp tables to return a catch-all only if no other alias exists.

References

Using the with SQL syntax with MySQL https://dev.mysql.com/doc/refman/8.0/en/with.html