4

It seems like my catch-all alias doesn't work. When I use

postmap -q test@example.org mysql:/etc/postfix/mysql-virtual-alias-maps.cf

There is no output. But when I command

 postmap -q jack@example.org mysql:/etc/postfix/mysql-virtual-alias-maps.cf

the output is 

 john@example.org.

How can I fix this?

The configure query is:

query= SELECT destination FROM virtual_aliases WHERE source='%s'

Table virtual_aliases:

id  domain_id   source             destination
5   1           @example.org        john@example.org
7   1           john@example.org    john@example.org
8   1           jack@example.org    john@example.org

Table virtual_domains:

id  name
1   example.org

Table virtual_users:

id  domain_id   password    email
1   1           pwd         john@example.org
rohx
  • 43
  • 3

2 Answers2

6

Your mysql query should be

SELECT destination FROM virtual_aliases WHERE source='%s' 
UNION ALL  
SELECT destination FROM virtual_aliases WHERE source='@%d' 
AND NOT EXISTS (SELECT destination FROM virtual_aliases WHERE source='%s')
clement
  • 3,289
  • 2
  • 17
  • 11
0

@clement is correct but there is a simpler query that would work that is easier on the sql server. Here is what worked for me:

Changed query line in /etc/postfix/mysql-virtual-alias-maps.cf

query = select distinct destination from virtual_aliases where source='%s' or source = '@%d';
slf
  • 22,595
  • 11
  • 77
  • 101
  • Note that this version will not work if you want some specific address mappings with the catchall as a fallback. (the `and not exists` part of clement's answer). Also from a database dev's PoV using `where ... or ...` like this isn't great as it will likely result in a table scan instead of two index seeks, though that is unlikely to be significant unless you manage a great many mail accounts on the same system. – David Spillett Jan 10 '22 at 13:15