#5402 Postfix Mappings SQL Error

[George Ellenburg]

Good morning.

Webmin 1.955 on Debian 10
MariaDB version 10.3.23 running on localhost
Postfix version 3.4.14 running on localhost

Other packages installed:
iRedMail 1.3.1

Receiving the following error when attempting to load any mappings in the Postfix Virtual Domains mapping:

This map cannot be edited : Failed to query table : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from where = limit 1' at line 1

Here's the map specifications:

proxy:mysql:/etc/postfix/mysql/virtual_alias_maps.cf proxy:mysql:/etc/postfix/mysql/domain_alias_maps.cf proxy:mysql:/etc/postfix/mysql/catchall_maps.cf proxy:mysql:/etc/postfix/mysql/domain_alias_catchall_maps.cf

Here are the relevant mappings:

Map Source 1

SELECT forwardings.forwarding FROM forwardings,domain WHERE forwardings.address='%s' AND forwardings.domain=domain.domain AND forwardings.active=1 AND domain.backupmx=0 AND domain.active=1

Map Source 2

SELECT forwardings.forwarding FROM forwardings,alias_domain,domain WHERE alias_domain.alias_domain='%d' AND forwardings.address=CONCAT('%u', '@', alias_domain.target_domain) AND alias_domain.target_domain=domain.domain AND forwardings.active=1 AND alias_domain.active=1 AND domain.backupmx=0

Map Source 3

SELECT forwardings.forwarding FROM forwardings,domain WHERE forwardings.address='%d' AND '%u' NOT LIKE '%%+%%' AND forwardings.address=domain.domain AND forwardings.active=1 AND domain.active=1 AND domain.backupmx=0

Map Source 4

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

The thing is, I've manually queries MariaDB with those same SQL statements and no error in the SQL statement is being raised.

Any thoughts?

[Jamie Cameron]

The issue may be Webmin's ability to parse the SQL statements. Can you post the contents of /etc/postfix/mysql/domain_alias_maps.cf ?

[George Ellenburg]

Sure:

root@liewrmx01:/etc/postfix/mysql# cat domain_alias_maps.cf 
hosts       = 127.0.0.1:3306
user        = vmail
password    = REDACTED
dbname      = vmail
query       = SELECT forwardings.forwarding FROM forwardings,alias_domain,domain WHERE alias_domain.alias_domain='%d' AND forwardings.address=CONCAT('%u', '@', alias_domain.target_domain) AND alias_domain.target_domain=domain.domain AND forwardings.active=1 AND alias_domain.active=1 AND domain.backupmx=0
root@liewrmx01:/etc/postfix/mysql# 

[Jamie Cameron]

Ok - unfortunately Webmin cannot parse a complex SQL query like that, which spans multiple tables. We can't just execute the query directly (as Postfix does), because Webmin also needs to figure out what tables and fields to update when adding an alias.

[franc walter]

Will it be possible once, to edit Postfix Virtual Domains, Accounts, Aliases etc. with Webmin?
Is it on the roadmap?
There is Postfixadmin, I know, but only with basic features. Webmin could be a real helper here.

At the moment (since 17 years indeed) I use a very old kind of handmade PHP solution, called "GRSoft Virtual Mail Manager" (from Peter Gutwein, GRSoft, not anymore available). That works but is from 2007 and a bit old. There is also the more actual "ISPmail Admin", from https://www.ima.jungclaussen.com/index.php though, untested.

Would be great if Webmin could do the job once.
Nobody uses postfix without Virtual Domains, that I know.
Thank.frank