[Dovecot] MySQL stored proc authorization
On my Gentoo box until today I had dovecot-1.0_rc7 and used following entry in /etc/dovecot/dovecot-sql.conf
password_query = call Authorize('%u', '%n', '%d')
Simply put dovecot is calling my mysql stored routine which handles authorization. Until today, when I updated dovecot to dovecot-1.0_rc8 it worked flawlessly. With this version executing stored proc dies with:
Password query failed: PROCEDURE mailsql.Authorize can't return a result set in the given context
Replacing stored proc call with select .... statement does the thing, but it's stored procedures are ... generally better to do things on DB imvho :-) Downgrading to dovetot-1.0_rc7 fixes the problem immediately. Of course /etc/dovecot/dovecot.conf remains the same in either case. Is this a bug or change made on purpose?
-- Chaos greets U
On Fri, 2006-10-13 at 22:42 +0200, Chaos Engine wrote:
On my Gentoo box until today I had dovecot-1.0_rc7 and used following entry in /etc/dovecot/dovecot-sql.conf
password_query = call Authorize('%u', '%n', '%d')
Simply put dovecot is calling my mysql stored routine which handles authorization. Until today, when I updated dovecot to dovecot-1.0_rc8 it worked flawlessly. With this version executing stored proc dies with:
Password query failed: PROCEDURE mailsql.Authorize can't return a result set in the given context
Replacing stored proc call with select .... statement does the thing, but it's stored procedures are ... generally better to do things on DB imvho :-) Downgrading to dovetot-1.0_rc7 fixes the problem immediately. Of course /etc/dovecot/dovecot.conf remains the same in either case. Is this a bug or change made on purpose?
Are you sure the difference is between the changes in Dovecot and not in some gentoo compile/link flags? Such as a different mysql library.
I don't think I've changed anything related to that between rc7 and rc8.
2006/10/13, Timo Sirainen <tss@iki.fi>:
On Fri, 2006-10-13 at 22:42 +0200, Chaos Engine wrote:
On my Gentoo box until today I had dovecot-1.0_rc7 and used following entry in /etc/dovecot/dovecot- sql.conf
password_query = call Authorize('%u', '%n', '%d')
Simply put dovecot is calling my mysql stored routine which handles authorization. Until today, when I updated dovecot to dovecot-1.0_rc8 it worked flawlessly. With this version executing stored proc dies with:
Password query failed: PROCEDURE mailsql.Authorize can't return a result set in the given context
Replacing stored proc call with select .... statement does the thing, but it's stored procedures are ... generally better to do things on DB imvho :-) Downgrading to dovetot-1.0_rc7 fixes the problem immediately. Of course /etc/dovecot/dovecot.conf remains the same in either case. Is this a bug or change made on purpose?
Are you sure the difference is between the changes in Dovecot and not in some gentoo compile/link flags? Such as a different mysql library.
I'm pretty sure. I haven't changed my previous compile flags (Gentoo USE flags). To tell the truth I haven't found any word of using stored procedures in mysql authorization; but it worked. I haven't touched MySQL or its libs, only upgraded dovecot.
I don't think I've changed anything related to that between rc7 and rc8.
-- Chaos greets U
On Fri, 2006-10-13 at 23:24 +0200, Chaos Engine wrote:
Are you sure the difference is between the changes in Dovecot and not in some gentoo compile/link flags? Such as a different mysql library.
I'm pretty sure. I haven't changed my previous compile flags (Gentoo USE flags). To tell the truth I haven't found any word of using stored procedures in mysql authorization; but it worked. I haven't touched MySQL or its libs, only upgraded dovecot.
I don't know how MySQL procedures are even supposed to work..
I don't think I've changed anything related to that between rc7 and rc8.
I guess the difference is that I removed this code:
#ifdef CLIENT_MULTI_STATEMENTS /* Updates require this because everything is committed in one large SQL statement. */ db->client_flags |= CLIENT_MULTI_STATEMENTS; #endif
I'd rather not put it back since it potentially makes it less secure.
2006/10/13, Timo Sirainen <tss@iki.fi>:
Are you sure the difference is between the changes in Dovecot and not in some gentoo compile/link flags? Such as a different mysql library.
I'm pretty sure. I haven't changed my previous compile flags (Gentoo USE flags). To tell the truth I haven't found any word of using stored
On Fri, 2006-10-13 at 23:24 +0200, Chaos Engine wrote: procedures in mysql authorization; but it worked. I haven't touched MySQL or its libs, only upgraded dovecot.
I don't know how MySQL procedures are even supposed to work..
I don't think I've changed anything related to that between rc7 and rc8.I guess the difference is that I removed this code:
#ifdef CLIENT_MULTI_STATEMENTS /* Updates require this because everything is committed in one large SQL statement. */ db->client_flags |= CLIENT_MULTI_STATEMENTS; #endif
I'd rather not put it back since it potentially makes it less secure.
Yes, most probably the lack of this CLIENT_MULTI_STATEMENTS flag blocks stored procs (acording to MySQL docs).
From my point of view using stored proc is more secure than putting select user, password from user_sensitive_data_table into dovecot-sql.conf, but I'll live with that. You most probably had your reasons, and ultimately I agree - security first ;-)
-- Chaos greets U
On Sat, 2006-10-14 at 00:08 +0200, Chaos Engine wrote:
db->client_flags |= CLIENT_MULTI_STATEMENTS;
Yes, most probably the lack of this CLIENT_MULTI_STATEMENTS flag blocks stored procs (acording to MySQL docs). From my point of view using stored proc is more secure than putting select user, password from user_sensitive_data_table into dovecot-sql.conf, but I'll live with that. You most probably had your reasons, and ultimately I agree - security first ;-)
Now that I think of it, you can actually enable this again. Add to connect string in dovecot-sql.conf:
client_flags = 64
2006/10/14, Timo Sirainen <tss@iki.fi>:
On Sat, 2006-10-14 at 00:08 +0200, Chaos Engine wrote:
db->client_flags |= CLIENT_MULTI_STATEMENTS;Yes, most probably the lack of this CLIENT_MULTI_STATEMENTS flag blocks stored procs (acording to MySQL docs). From my point of view using stored proc is more secure than putting select user, password from user_sensitive_data_table into dovecot-sql.conf, but I'll live with that. You most probably had your reasons, and ultimately I agree - security first ;-)
Now that I think of it, you can actually enable this again. Add to connect string in dovecot-sql.conf:
client_flags = 64
If all was that simple ... but unfortunatelly that didn't work for me. I wonder did'nt you mean client_flags = 65536 coz that's what I found in /usr/include/mysql/mysql_com.h ... Heh, I've just tried that and it worked :-) Looks like we can call the issue solved then.
-- Chaos greets U
participants (2)
-
Chaos Engine
-
Timo Sirainen