1 Topic by mlocati (edited by mlocati 2014-11-03 21:46:25)

  • mlocati
  • Member
  • Offline
  • Registered: 2014-11-03
  • Posts: 20

Topic: catch all mysql queries

======== Required information ====
- iRedMail version:  0.8.7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Linux/BSD distribution name and version:  Ubuntu 14.04.1 LTS
- Related log if you're reporting an issue:
====

I'm wondering if there's some error in the catch-all queries...
From the wiki we read that the `address` field of the `alias` table is in form of '@domain'.
But in two files it seems it should be just 'domain'...

File 1: /etc/postfix/mysql/catchall_maps.cf:

SELECT alias.goto
FROM alias,domain
WHERE alias.address='%d'
AND alias.address=domain.domain
AND alias.active=1
AND domain.active=1
AND domain.backupmx=0

IMHO instead of

alias.address='%d'

we should have

alias.address=CONCAT('@', '%d')

and instead of

alias.address=domain.domain

we should have

alias.address=CONCAT('@', domain.domain)


File 2: /etc/postfix/mysql/domain_alias_catchall_maps.cf:

SELECT alias.goto
FROM alias,alias_domain,domain
WHERE alias_domain.alias_domain='%d'
AND alias.address=alias_domain.target_domain
AND alias_domain.target_domain=domain.domain
AND alias.active=1
AND alias_domain.active=1

IMHO instead of

alias.address=alias_domain.target_domain

we should have

alias.address=CONCAT('@', alias_domain.target_domain)

I'm quite new to postfix, so I'm surely missing something, but I really don't understand what...

Thank you
Michele

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by mlocati

  • mlocati
  • Member
  • Offline
  • Registered: 2014-11-03
  • Posts: 20

Re: catch all mysql queries

I just did a quite complex test:

I have one domain "a.com", and an alias domain "b.com" that points to "a.com"

I added this record to the alias table:
address: "@a.com"
goto: "me@a.com"
domain: "a.com"

With the above edits, emails sent to "not-existing@a.com" and to "not-existing@b.com" are correctly routed to "me@a.com", so IMHO my edits are correct...
Shall I create a pull request on BitBucket?

3 Reply by mlocati

  • mlocati
  • Member
  • Offline
  • Registered: 2014-11-03
  • Posts: 20

Re: catch all mysql queries

Here's the patch

# HG changeset patch
# User Michele Locati <mlocati@gmail.com>
# Date 1415031612 -3600
#      Mon Nov 03 17:20:12 2014 +0100
# Node ID 20f2dc093e42635507e3498544d0fe952718d26a
# Parent  1a7f9586f0568c8a8f5c0ef9206b8b11f3be67c3
Fix catch-all queries for MySQL

diff -r 1a7f9586f056 -r 20f2dc093e42 iRedMail/samples/postfix/mysql/catchall_maps.cf
--- a/iRedMail/samples/postfix/mysql/catchall_maps.cf    Fri Oct 31 20:46:48 2014 +0800
+++ b/iRedMail/samples/postfix/mysql/catchall_maps.cf    Mon Nov 03 17:20:12 2014 +0100
@@ -3,4 +3,4 @@
 hosts       = 127.0.0.1
 port        = 3306
 dbname      = vmail
-query       = SELECT alias.goto FROM alias,domain WHERE alias.address='%d' AND alias.address=domain.domain AND alias.active=1 AND domain.active=1 AND domain.backupmx=0
+query       = SELECT alias.goto FROM alias,domain WHERE alias.address=CONCAT('@', '%d') AND alias.address=CONCAT('@', domain.domain) AND alias.active=1 AND domain.active=1 AND domain.backupmx=0
diff -r 1a7f9586f056 -r 20f2dc093e42 iRedMail/samples/postfix/mysql/domain_alias_catchall_maps.cf
--- a/iRedMail/samples/postfix/mysql/domain_alias_catchall_maps.cf    Fri Oct 31 20:46:48 2014 +0800
+++ b/iRedMail/samples/postfix/mysql/domain_alias_catchall_maps.cf    Mon Nov 03 17:20:12 2014 +0100
@@ -3,4 +3,4 @@
 hosts       = 127.0.0.1
 port        = 3306
 dbname      = vmail
-query       = SELECT alias.goto FROM alias,alias_domain,domain WHERE alias_domain.alias_domain='%d' AND alias.address=alias_domain.target_domain AND alias_domain.target_domain=domain.domain AND alias.active=1 AND alias_domain.active=1
+query       = SELECT alias.goto FROM alias,alias_domain,domain WHERE alias_domain.alias_domain='%d' AND alias.address=CONCAT('@', alias_domain.target_domain) AND alias_domain.target_domain=domain.domain AND alias.active=1 AND alias_domain.active=1

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,094

Re: catch all mysql queries

It's historical document, and both iRedMail and iRedAdmin-Pro use domain name without '@', so let's make it easier: fix the wiki. smile