[Dovecot] pop-before-smtp in SQL?

[Lorens]

On Fri, Aug 19, 2005 at 07:26:46PM +0300, Timo Sirainen wrote:
> If you use sql userdb, you can just add the insert command into the
> user_query.

How would I do that? I've tried with a colon, with a
backslash-escaped colon, with two distinct lines.

I get ($variables my editing):

Error: auth(default): sql($mylogin,$myip): User query failed: You have an error in your SQL syntax near '\; SELECT maildir $etc...

With two distinct lines only the last is taken into account
(unsurprisingly). I do need the select maildir that user_query
corrently represents!

I used %i for IP, inspired by

	http://dovecot.org/pipermail/dovecot/2003-November/002554.html

and while I'm encouraged by the presence of my ip in the error
message, i doesn't seem to be the right letter, it expands to
the null string.

Anyway,

> Another way that already works is to update mail_executable location to
> some script which does the SQL insertion and after that executes the
> real imap or pop3 binary.

That does work. For the record (until post_login comes!) this is
how I did it:

dovecot.conf:

protocol pop3 {
  mail_executable = /usr/lib/dovecot/popbsmtp.sh /usr/lib/dovecot/pop3
}
protocol imap {
  mail_executable = /usr/lib/dovecot/popbsmtp.sh /usr/lib/dovecot/imap
}

/usr/lib/dovecot/popbsmtp.sh:

#!/bin/sh

(
    echo "Login from $3" >> /var/log/dovecot3

    IP=`echo $3 | sed 's/\]//'`

    if [ -n "$IP" ]
    then
        echo popbsmtp logging $IP
        export HOME=/root/
        echo "replace into popbsmtp VALUES('$IP',now();" | mysql mail 
        export HOME=/
    fi
) >> /var/log/dovecot3 2>&1

exec $*

I edited out an ugly pipe in the IP= line that removes IPs from
my own network who can relay anyway.

What took me most time was comprehending that even though I'd
specified both

login_user = dovecot

and

auth default {
    user = dovecotauth
}

mail_executable was executed 1) as root and 2) without a home
directory, which made the mysql fail since I was relying on
$HOME/.my.cnf.

But now that it works, it works well :-)