UPDATE: Added test code, hopefully the regex escapes survive translation, YMMV...
THANK YOU @ANX for the advice to enable verbose logging on smtpd and proxymap, then looking for dict_proxy_lookup and input_attribute_value in the logs for hints to where postfix was going off in the weeds. Test traffic revealed that it was in fact falling through to the last alias query (wildcard search) because it was passing the entire username, including the recipient delimiter, into the query. So I got to looking at the source code. Sure enough, in virtual.c, paraphrased here:
CASE FOLDING All delivery decisions are made using the full recipient address, folded to lower case. TABLE SEARCH ORDER Normally, a lookup table is specified as a text file that serves as input to the postmap command. The result, an indexed file in dbm/db format, is used for fast searching by the mail system. The search order is as follows. The search stops upon the first successful lookup. When the recipient has an optional address extension the user+extension@domain.tld address is looked up first. With <v2.1, the addr extension is always ignored. I've been a long-time postfix user, and using virtual through the SQL proxy interface is a new experience for me, thus used to having delimiters stripped from the virtual hash tables and this was a surprise. I understand why this was done (to give folks choice in rule creation).
TL;DR: Here is the issue in a nutshell: In smptd/virtual.c, all database queries are passed with the recipient delimiter in place. This leaves it up to the implementer to use it (or not) in their SQL query. Here is the 'fix': Add a special case query to the list of virtual alias maps, just before the wildcard query (mysql_virtual_alias_domain_nodelim_maps.c excerpt below) this one does a search with a regex cutting out the recipient delimiter from %u prior to the lookup, when all the prior lookups have failed, but before we fall back to the wildcard search:
main.cf:
virtual_alias_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf,
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_maps.cf,
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_nodelim_maps.cf, #<--new query
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_catchall_maps.cf
mysql_virtual_alias_domain_nodelim_maps.cf: (I use '.' as a delimiter, YMMV)
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT(REGEXP_REPLACE('%u', '\..*', ''), '@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'
It works great, and now one can set up a special alias for bar.foo tagged traffic, AND it will propagate the inbound tag at output at the end of the tag list if the alias has it's own tag which is really really cool!
BTW, I built a test case of the query set in python until satisfied it behaved similar to postfix. Then started to futz with the regex until satisfied it solved the issue. After resolving the escaping differences between postfix and python, tried the new query out on a non-production postfix instance, and it behaves as intended.
#!/usr/bin/env python3
import mysql.connector
from mysql.connector import Error
# dbhost, dbname, dbuser, dbpass with your parameters
dbhost = ''
dbname = ''
dbuser = ''
dbpass = ''
# Our setup predates DMARC, doing some pretty specialized work with
# address tagging, in particular tying a domain component to the To:
# address as a sub-tag for certain destinations. This was particularly
# useful in wildcard email hoppers before SPF/DMARC became useful.
# RECIPIENT DELIMITER (. = gmail style)
rd = '.'
# DOMAIN DELIMITER (-)
dd = '-'
# EXAMPLE:
# myemailaddr.tag-domainlock@mydomain.com
# where: tag = folder, or folder.folder, etc.
# domianlock = site lock parameter
# parseTo(addr, tag_to_push, dom_to_push)
# breaks an email To: field into constituent parts
# optionally pushes a recipient delimiter and domain
# tag onto the tag stack
def parseTo(to, tag='', dom=''):
# To: input:
# [atag ]
# uname.tag-dom@domain
#[ user ]
# uname.tag.tag-dom-dom@domain
#[ full ]
# uname@domain
#[ notags ]
rtv = {}
to = to.lower()
if '<' in to:
to[to.find('<')+1:to.find('>')]
rtv['user'] = to[:to.find('@')]
rtv['uname'] = rtv['user']
rtv['domain'] = to[to.find('@')+1:]
rtv['atag'] = '' # inbound tag
rtv['tag'] = tag # new tag for top of stack
rtv['dom'] = dom # new dom for top of stack
if rd in rtv['user']:
rtv['uname'] = rtv['user'][:rtv['user'].find(rd)]
rtv['atag'] = rtv['user'][rtv['user'].find(rd)+1:]
if rtv['tag'] != '':
rtv['tag'] = rd + rtv['tag']
if dd in rtv['atag']:
rtv['tag'] = rtv['atag'][:rtv['atag'].find(dd)] + rtv['tag']
if rtv['dom'] != '':
rtv['dom'] = dd + rtv['dom']
rtv['dom'] = rtv['atag'][rtv['atag'].find(dd)+1:] + rtv['dom']
else:
rtv['tag'] = rtv['atag'] + rtv['tag']
rtv['full'] = rtv['uname']
if rtv['tag'] != '':
rtv['full'] += rd + rtv['tag']
if rtv['dom'] != '':
rtv['full'] += dd + rtv['dom']
rtv['full'] += '@' + rtv['domain']
rtv['notags'] = rtv['uname'] + '@' + rtv['domain']
return rtv
# recurseUser(addr)
# try to match postfix address processing/order as close as possible...
# regex assumes recipient_delimiter = '.'
def recurseUser(addr):
print("query alias: %s" % (addr))
to = parseTo(addr)
user = to['user']
domain = to['domain']
# postfix automatically runs the last case (@domain query) to all hashes as
# well as the list provided to proxymap via the virtual directives in
# main.cf by re-running the alias case. Really, it likely will just iterate
# over all of them but that is a waste given the structure of the virtual db.
# Cases are run most specific to broadest (e.g. catch-all wildcards).
# There is a rule specified *somewhere* that alias must contain all valid
# destinations. Postfixadmin enforces this for you.
# try the alias table first with the full spec...
q = ("SELECT goto FROM alias WHERE address = %s AND active = 1 LIMIT 1")
cursor.execute(q, (addr,))
res = cursor.fetchone()
if res is None:
# next, check if there is a domain redirect to a specific subdomain alias entry.
print(" ...not found, check alias domain: %s %s" % (domain, user))
q = ("SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = %s and alias.address = CONCAT(%s, '@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active=1")
cursor.execute(q, (domain, user))
res = cursor.fetchone()
if res is None:
# ***NEW*** strip any recipient delimiter and try the previous search again
# this allows the creation of r_d specific aliases, or the use of r_d's with
# wildcards in the domain cathall maps. Aliases can also add their own tags,
# which will be pushed to the top of stack by postix as a matter of course
print(" ...not found, remove tags (if any) and check again: %s %s" % (domain, to['notags']))
q = ("SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = %s and alias.address = CONCAT(REGEXP_REPLACE(%s, '\\\\..*', ''), '@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active=1")
cursor.execute(q, (domain, user))
res = cursor.fetchone()
if res is None:
# check for a wildcard query from domain catchall maps (for the cases of wc@bar.com -> wc@machine.bar.com)
print(" ...not found, check wc in alias domain: %s" % (domain))
q = ("SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = %s and alias.address = CONCAT('@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active=1")
cursor.execute(q, (domain,))
res = cursor.fetchone()
if res is None:
# lastly run the wildcard query for the target wildcard alias (for the cases of wc@machine.bar.com)
print(" ... not found, check wc in alias as: @%s" % (domain))
q = ("SELECT goto FROM alias WHERE address = %s AND active = 1 LIMIT 1")
cursor.execute(q, ('@'+domain,))
res = cursor.fetchone()
if res is None:
print(" Not found, gave up!")
return addr
# found a result, so push any new tags on the stack and recurse until the results are unchanged
newto = parseTo(res['goto'], to['tag'], to['dom'])
print(" got: %s" % (res))
# compare sans tags, we're interested in the actual destination, and the tag stack may change
# along the way
if to['notags'] == newto['notags']:
print(" done!")
return addr
print(" recurse...")
return recurseUser(newto['full']) # otherwise, recurse...
if True:
db = mysql.connector.connect(database=dbname, host=dbhost, user=dbuser, password=dbpass)
cursor = db.cursor(dictionary=True)
while True:
test = input("Address to try ('q' to end): ")
if test.lower() == 'q':
cursor.close()
db.close()
quit()
print("Result:", recurseUser(test))
print("")
