I've got working Postfix solutions using hash: and mysql: direct lookups.
I'm now experimenting with using CALLs to MySQL StoredProcedures in
Postfix lookups.
/etc/postfix/main.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/virtual_mailbox_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/virtual_mailbox_maps.cf
/etc/postfix/virtual_mailbox_domains.cf
user = virtmail
password = #########
dbname = postfix_DB
hosts = unix:/var/run/mysql/mysql.sock
query = CALL DomainCheck('%s');
In my schema definition,
DROP PROCEDURE IF EXISTS DomainCheck;
DELIMITER //
CREATE PROCEDURE DomainCheck(IN domainName VARCHAR(255))
BEGIN
DECLARE `result` TINYINT(1) DEFAULT 0;
SELECT 1 INTO `result`
FROM (SELECT `name` FROM `DOMAIN` WHERE `enabled` = '1'
UNION DISTINCT
SELECT `name` FROM `DOMAIN_ALIAS` WHERE `enabled` = '1'
) AS `X`
WHERE `name` = `domainName`
LIMIT 1;
END;
//
DELIMITER ;
On message receipt, the StoredProcedure lookup fails. The problem appears to be
...
Oct 15 13:46:55 mx postfix/trivial-rewrite[30609]: dict_proxy_lookup:
table=mysql:/etc/postfix/virtual_mailbox_domains.cf
flags=lock|fold_fix key=mailsource.pg.lan -> status=2 result=
Oct 15 13:46:55 mx postfix/trivial-rewrite[30609]: fatal:
proxy:mysql:/etc/postfix/virtual_mailbox_domains.cf(0,lock|fold_fix):
table lookup problem
...
For reference, I've pastebin'd the verbose logs for proxymap &
trivial-rewrite -> http://pastebin.com/ijnVhuJX.
Searching, I found
http://comments.gmane.org/gmane.mail.postfix.user/206084
Brian Evans - Postfix List | 28 Jan 21:42
Re: virtual_alias_maps mysql
"Stored procedures do not work in Postfix without code changes because
the |CLIENT_MULTI_RESULTS connect flag, for MySQL API, is not turned on."
and
http://errorval.livejournal.com/37425.html
Postfix, MySQL, and stored procedures
"Yesterday, I discoverted that Postfix's MySQL aliases support doesn't
support stored procedures, or any query that returns a resultset
instead of a single result. Note that "select 1" returns a resultset.
..."
That proposes a patch to Postfix code to enable that flag,
+ CLIENT_MULTI_RESULTS)) {
Took me awhile to find those, and haven't yet found the follow-up.
And, temporarily stymied by what's going on. Best to ask first:
(1) Does current Postfix support use of MySQL StoredProcedures in its lookups?
(2) Is the problem I'm seeing due to that missing flag?
(3) Are any/all changes req'd for that support committed to the source
tree, or do I need patch source, or modify my queries further?
Rich
