Creating a postfix alias "all@domain.tld" to send to all users of a domain

Question

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 all@domain.tld 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 all@domain.tld, 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.

score 6 · Accepted Answer · edited May 23 '17 at 12:41

6

You can use virtual_alias_maps to define alias all@example.com. 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.

edited May 23 '17 at 12:41

Community

1

answered Aug 24 '15 at 15:38

masegaloeh

18,236
10
57
106

That sounds like the approach I am after! But few open questions: 1) where to put the query in (and how to tell postfix to use it), 2) the "all=%u" part seems to break the query, as there's no "all" field (is that needed, and what for? Omitting it seems to yield the correct result: all users of that domain), 3) doesn't the created field require an alias (and if so, what specific name)? Oh, and 4) mustn't the regexp be `/^all@/` – as otherwise it would also match `pitfall@`? – Izzy Aug 24 '15 at 16:06
For 1) I assume I've got to add it to `mysql_virtual_alias_maps.cf`, which already has a `query=SELECT goto FROM alias WHERE address='%s' AND active = '1'`. Guess that `active='1'` part must be added to your query as well, but [in a different way](http://dba.stackexchange.com/a/30952/30273), as the alias shouldn't catch inactive accounts. Obviously, `%s` then must match our `all@` somehow (`WHERE '%s'='all@%d'`), and then it could be connected by a `UNION`, [like this](http://pastebin.com/SRLyUp4G)? (PS: that BTW would take care for 2+3 as well I'd say) – Izzy Aug 24 '15 at 16:30
1

Oh, one more: `check_recipient_access pcre:` leads to `error: unsupported dictionary type: pcre`, that must be `check_recipient_access regexp:`. Adjusted the restrict file to `/^all@/ REJECT access denied`, and got a `554 5.7.1 : Recipient address rejected: not permitted` from remote. From localhost, it was accepted – and ended up in all expected mailboxes (for the addressed domain), and nowhere else – Yeeha! \o/ So do you want to update your answer with my corrections, or shall I do so? Meanwhile +25 for you :) – Izzy Aug 24 '15 at 17:13

Creating a postfix alias "all@domain.tld" to send to all users of a domain

1 Answers1