Home » Mail Server » Add a Catch-All Account with Postfix-Mysql
Hedgecube

Financial Technology Consulting, Statistics, Risk Models and Economics.

Birthday Reminders from your Linux Server
Perl script sending birthday reminder emails from you Linux server using an Excel CSV file as birthday database.
Virtual Server Basic Setup for Email Service
Picking an operating system, dual IPv4 and IPv6 network interfaces, hostname and reverse DNS record for virtual server email setup.
Restore Hostname During Reboot
A custom system service to restore hostname settings during reboot on virtual servers overwriting network configuration files.
Postfix With Courier IMAP Mail Server Installation
Install and configure Postfix mail server with Courier IMAP and Maildir storage on Debian 11 or Ubuntu 20.04.
Maildrop Spam Filtering For Postfix MySQL
How to use Courier-Maildrop or compatible tools for spam filtering and mail folder administration with Postfix-MySQL.
Mathematical Economics

Mathematical Economics, Statistics and Computations.

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.

Crap accumulating in catch-all account with Postfix-MySQL
Crap accumulating in spam filtered from a catch-all account

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 '[email protected]+' 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 '[email protected]+' 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 ‘[email protected]+’ ” 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


Published: February 7, 2022
Updated: July 4, 2022

Classfactory.com
Tech Solutions and Programming

Financial Economics in German

Electrical Engineering