Re: AW: Possible Bug ? postfix 3.1.0-3 fails on mysql table lookup

[Joel Linn]

Hey Guys,

this issue has decayed a bit but I now finally found the time (and the 
nerves) to integrate the fix in my system.
I'm running Ubuntu 16.04 and trying not change to many things and be 
able to have clean comparison I applied the patch to the apt sources and 
only replaced the postfix-mysql package (ubuntu is using 3.1.0 it seems) 
with my own.
I introduced some stored procedures and from my tests I conclude it 
works. I will see in the next couple of days if there are issues I 
overlooked.
I ran into an issue where I was returning no result set using 
"smtpd_recipient_restrictions = check_recipient_access mysql:/[...]".
I'm not sure if that's an dict_mysql issue or caused by design upstream. 
I overcame that by doing "select 1 from dual where false" when I don't 
have anything else to return, which basically is an empty result set.


Thanks very much for your work,
Joel

On 2016-07-07 00:46, John Fawcett wrote:

On 07/04/2016 09:58 PM, j...@conductive.de wrote:


Quoting John Fawcett :
I can propose a code submission to add stored procedure support 
(based

on the proof of concept code from 2008), but the biggest part will be
doing the testing and non regression testing not the actual coding.

I believe the best approach to adding stored procedure support is to
iterate over multiple result sets and if there are exactly two and 
the

last one is the status result of the stored procedure and it is
successful then allow the lookup against the first result set. In all
other cases there should be an error for multiple result sets. In the
context of Postfix don't think it is useful to have more than one 
result

set generated by multiple queries or more than two result sets
(including the status one) from a stored procedure.

John


That is exactly what I had on my mind.

The specific error message would enable admins to exactly see where
the trouble originates.
Also consuming all results still allows subsequent lookups to succeed
normally.

I would of course volunteer to "test" the change per se but I'm afraid
my knowledge of postfix's inner workings converges to zero, rendering
my testing unrepresentative at last :/
Although the subset of postfix interfacing with mysql is quite small.

Joel


Hi

here is my proposed submission to add mysql stored procedure support to
Postfix. As per Wietse's comments in the following thread

http://postfix.1071664.n5.nabble.com/fatal-table-lookup-problem-with-Postfix-lookup-call-to-MySQL-StoredProcedure-td13240.html

"I would be grateful if that support came with test cases for single
and multi-result queries, including non-error and error results so
that we can run the test whenever something in the code is changed."

One further thing to point out and that may need to be adjusted still:

An error is returned whenever:

- the check for further result sets gives an error instead of yes (0) 
or

no (-1)

- the stored procedure final result set with the status is not as
expected (mysql_field_count returns 0)

- there is more than one result set containing data.

While testing with postmap and multiple result sets I was able to
generate the following error message from postmap.c

"postmap: fatal: table mysql:./mysql-test2.cf: query error: Success"

The final "Success" comes from %m parameter reporting the errno. Since
no system call error has been returned, but it is an application error 
I

have forced errno to ENOTSUP. If instead the "Success" status is deemed
correct, then the statement setting errno and the inclusion of errno.h
can be elimianted without changing the basic functioning of the patch.

I am available to make any further adjustments needed. I look forward 
to
feedback from Joel or other interested people about more extensive 
testing.


John

AW: Possible Bug ? postfix 3.1.0-3 fails on mysql table lookup

[Joel Linn]

Why is it chosen to "not support stored procedures" instead of adding two
lines of code?

Are there any security or performance implications? I don't see why the fix
would not be applied.

I'am now running a query that is several hundred characters long and doesn't
scale linear, just to shoehorn everything into one query; It could be done
much easier and faster in a stored procedure.

 

Von: owner-postfix-us...@postfix.org
[mailto:owner-postfix-us...@postfix.org] Im Auftrag von John Fawcett
Gesendet: Sonntag, 3. Juli 2016 18:10
An: postfix-users@postfix.org
Betreff: Re: Possible Bug ? postfix 3.1.0-3 fails on mysql table lookup

 

On 07/03/2016 05:35 PM, Joel Linn wrote:

Hi guys,

think I found a bug using Ubuntu 16.04, can you confirm this?

...

Hi 
it's not actually a bug. Postfix does not support mysql stored procedures.

This was discussed here back in 2008:

http://osdir.com/ml/mail.postfix.devel/2008-02/msg00021.html

best regards
John



smime.p7s
Description: S/MIME cryptographic signature

Possible Bug ? postfix 3.1.0-3 fails on mysql table lookup

[Joel Linn]

Hi guys,

think I found a bug using Ubuntu 16.04, can you confirm this?

what I get in the log:
Jul 3 01:21:40 postfix/cleanup[10334]: warning: mysql query failed: Commands
out of sync; you can't run this command now
Jul 3 01:21:40 postfix/cleanup[10334]: warning:
mysql:/etc/postfix/sql/aliases.cf lookup error for "x...@xxx.xx"
Jul 3 01:21:40 postfix/cleanup[10334]: warning: EF7AE1C0FCE:
virtual_alias_maps map lookup problem for y...@yyy.yy -- message n$

Configuration:
virtual_alias_maps = mysql:/etc/postfix/sql/aliases.cf

and the query in that file:
query = CALL get_aliases_destinations('%u', '%d')

the stored procedure in the database could be as simple as
CREATE PROCEDURE `get_aliases_destinations`(IN user VARCHAR(64), IN domain
VARCHAR(255))
BEGIN
 SELECT CONCAT(destination_username, '@', destination_domain) AS
destinations FROM aliases WHERE source_username = user AND source_domain =
domain AND enabled = true;
END

The bare select itself as query does work fine.
The crucial thing is that x...@xxx.xx already is the alias destination, so the
first query worked fine. In fact postalias -q  returns valid results.
I believe this occurs because of the mysql library returning an second empty
result set which is not removed/cleared by postfix.
See this discussion which describes a similar issue:
http://stackoverflow.com/questions/6583020/mysql-stored-procedure-caused-com
mands-out-of-sync
I'm not familiar with the postfix sources but I think a possible fix could
be inserted into the plmysql_query function in dict_mysql.c similar to what
is discussed in the link and a dozen similar mysql api issues on the web.

 



smime.p7s
Description: S/MIME cryptographic signature