Creating a postfix alias "[email protected]" to send to all users of a domain

I'm using Postfix + Dovecot with a MySQL database as backend and PostfixAdmin to administrate users and domains. Now I'm looking for an easy and automated approach to define per-domain alias of the pattern [email protected] with will resolve to all users of the given domain. I want to set this up once, and it shall keep working as expected even if accounts are added or deleted – so creating a file with a list of accounts manually, or using some mailing list are no options.

It should be pretty easy to retrieve all existing users for a given domain from the database:

SELECT username
  FROM vmail
 WHERE domain='%d';

(with %d being the placeholder for the domain). But how can I tell postfix to do so for mails directed to [email protected], and of course only when such a mail comes from a trusted source (permit_sasl_authenticated, permit_mynetworks?)?

I've googled for a few hours now, but all I found were either "catch-alls" (just the opposite from what I want), solutions based on shell-scripts (walking the resp. domain directory), or using mailing-list approaches – none of that fitting my needs.

Solution 1:

You can use virtual_alias_maps to define alias [email protected]. Here the format used by virtual 5.

pattern address1, address2, address3, ...

So you need to construct query to concat all rows. Taken from this thread: Can I concatenate multiple MySQL rows into one field?, you can use this query.

SELECT GROUP_CONCAT(CASE WHEN active='1' THEN username ELSE NULL END separator ', ')
  FROM vmail
 WHERE DOMAIN='%d'
   AND '%s'='all@%d'

This needs to go to your mysql_virtual_alias_maps.cf and can be appended to the existing query using UNION – so the result looks e.g. like this:

query = SELECT goto FROM alias WHERE address='%s' AND active = '1' UNION 
SELECT GROUP_CONCAT(CASE WHEN active='1' THEN username ELSE NULL END separator ', ')
  FROM vmail
 WHERE DOMAIN='%d'
   AND '%s'='all@%d'

(might need to be all in one line – formatting here is just applied to make it easier to read).

To allow only permit_mynetworks and permit_sasl_authenticated, put the restriction in following order

smtpd_recipient_restrictions =  ....
                        permit_mynetworks
                        permit_sasl_authenticated
                        check_recipient_access regexp:/etc/postfix/restrict.all.alias
                        reject_unauth_destination

In /etc/postfix/restrict.all.alias, define

/^all@/  REJECT access denied

It will permit email to all@domain when sent from mynetworks or sent by authenticated user, but reject after that.