Re: ANNOUNCE: Plugins check_content_type / check_user
Peter J. Holzer wrote on 28.08.2007 23:59: So, if you have to reconnect anyway, why not explicitely connect in connect_hook? It won't be slower and it will work with other databases. Ok, convinced! So I put check_user in the crypt ;-) http://dienstleistung-kultur.de/qpsmtpd/ I'm working on a new version. Ernesto
Re: ANNOUNCE: Plugins check_content_type / check_user
Ernesto wrote: Jens Weibler wrote on 28.08.2007 08:27: Ernesto wrote: *check_user* Check User looks up a recipient during smtp phase in a database It is suitable for mailer configurations qpsmtpd-exim4 with virtual users in a database. Do I see it right, that you're sharing one mysql-connection for all connections? Yes. It is safe and no table locking is needed, because we do only reads. Moreover the SQL statements are prepared in advance and reused, what should give another performance gain. What about threaded qpsmtpd? On my server I'm using Debian Etch with Perl 5.8.8, MySql 5.0.32 and Qpsmtpd 0.32 started with qpsmtpd-forkserver - so Qpsmtpd uses the good old Perl fork. But I'm not that deep into Perl's bowels to give a complete answer. It just works with the named environment. mmh, I think the problem is that two threads could execute a query over one connection at the same time. btw: customize the fields isn't as nice as customize the query. See dovecot - you can control and customize the authentication process much better if you can customize the whole query.. Dovecot is doing much more than the plugin check_user. Here we only need the results user exists or not and domain is ours or not. I think, there is no need for special queries. scroll down ;) btw2: if you got spare time: custom session variables read from the query result would be a great improvment :) Sorry, I don't understand this joke. no koje - I've written a plugin which reads the spam-level for the spamcheck and validates the recipient: my $sth = $dbh-prepare(select count(*) as count, MAX(spam_reject_threshold) from mailbox where username = ? HAVING count 0 . UNION select count(*) as count, MAX(spam_reject_threshold) from alias where address = ? HAVING count 0); $sth-execute( $address, $address ); # Wir bekommen immer nur eine Zeile! my @data = $sth-fetchrow_array(); my $count = $data[0]; # Falls threshold null dann nehmen wir 99.99 my $spam_reject_threshold = defined $data[1] ? $data[1] : 99.99; $sth-finish; $spam_reject_threshold is written into $transaction-notes where it's later read by the spamassassin plugin. As you can see I need a custom query because I also check a alias-table. My idea: every column starting with notes_ could be automatically imported into the transaction notes (=session variable).. -- mfg Jens signature.asc Description: OpenPGP digital signature
Re: ANNOUNCE: Plugins check_content_type / check_user
Jens Weibler wrote on 28.08.2007 19:41: Ernesto wrote: Jens Weibler wrote on 28.08.2007 08:27: Ernesto wrote: *check_user* Do I see it right, that you're sharing one mysql-connection for all connections? Yes. What about threaded qpsmtpd? mmh, I think the problem is that two threads could execute a query over one connection at the same time. I think, that the database engine will keep track about the queries. And for MySQL the Perl fork seems to close the connection, therefore mysql_auto_reconnect has to be used. Please look at the source of check_user. [...] $spam_reject_threshold is written into $transaction-notes where it's later read by the spamassassin plugin. As you can see I need a custom query because I also check a alias-table. My idea: every column starting with notes_ could be automatically imported into the transaction notes (=session variable).. check_user was designed mainly for the qpsmtpd-exim4 environment, where exim4 runs only the queue-daemon. We have no catch-all user and no alias file; all users are virtual and only in the email_table splitted into the fields local_part (user) and domain. The other form (email_adress as '[EMAIL PROTECTED]') might be used by other setups. A third column (forward) tells exim, where to send the mail (local or external) and another column (box) gives the physical place of the local mailbox - e.g.: local_part domain forward box root domain1.de [EMAIL PROTECTED] abuse domain1.de [EMAIL PROTECTED] root domain2.de [EMAIL PROTECTED] abuse domain2.de [EMAIL PROTECTED] user1 domain1.de b_user1 user2 domain2.de b_user2 Please see http://www.xmn-berlin.de/~marte/exim/exim4_mysql_amavis_spamassasin.html check_user makes not more or less then looking up the user in the central configuration database of the mailsystem - spam handling etc. is leftover to other plugins. And that you wrote a plugin by yourself shows only, how easy it is to extend qpsmtpd ;-) Just place your plugin near mine and they'll work together. On the other hand you're right - with qpsmtpd the spam and virus scanning is done during smtp phase in contrast to exim4, where this is done after queuing. Maybe it will make sense to use the other fields in email_table (see above link) and put them into the notes, thus providing an user-based spam handling i.e. Maybe than the name of the plugin should be changed to check_user_and_get_scan_options or something like that. Just looking at the shipped spamassassin plugin: It doesn't use $transaction-notes - lot of work ;-) -- Ernesto
Re: ANNOUNCE: Plugins check_content_type / check_user
On 2007-08-28 21:09:40 +0200, Ernesto wrote: Jens Weibler wrote on 28.08.2007 19:41: Ernesto wrote: Jens Weibler wrote on 28.08.2007 08:27: Ernesto wrote: *check_user* Do I see it right, that you're sharing one mysql-connection for all connections? Yes. What about threaded qpsmtpd? mmh, I think the problem is that two threads could execute a query over one connection at the same time. I think, that the database engine will keep track about the queries. It can't. Think of it this way: You have two threads (or processes, it doesn't matter) which share a common connection. Both send a query to the server at about the same time. Here's the first problem: Stream sockets are streams of bytes, not packets. The server might receive part of the first query, then the second query, then the rest of the first query. But since queries are relatively short, it will probably receive two separate queries. Now the server starts processing these queries and in the meantime the two client threads start a read (or recv) call. After some time the server will have the first results and send them through the connection - but there are two receivers on that connection - which one will receive it? You can't tell, and there's a 50% chance that it's the wrong one. And for MySQL the Perl fork seems to close the connection, That might be a side-effect of the parent closing the connection (if it does this) or the MySQL client library detecting the fork and closing the connection to prevent the scenario I outlined above. Generally you cannot expect a DBI database handle to be useful after a fork. therefore mysql_auto_reconnect has to be used. So, if you have to reconnect anyway, why not explicitely connect in connect_hook? It won't be slower and it will work with other databases. hp -- _ | Peter J. Holzer| I know I'd be respectful of a pirate |_|_) | Sysadmin WSR | with an emu on his shoulder. | | | [EMAIL PROTECTED] | __/ | http://www.hjp.at/ |-- Sam in Freefall signature.asc Description: Digital signature
