On 08/01/2010 13:44, mailing@??? wrote:
> Hello, I'd like to update a field in a MySQL DB with the last access
> time (something like "2010-01-08 12:58:51") every time a user authenticate.
>
> I've the following configuration for the authenticators:
>
>
> ***
> begin authenticators
>
> plain_login:
> driver = plaintext
> public_name = PLAIN
> server_condition = ${lookup mysql{SELECT '1' FROM users \
> WHERE username = '${quote_mysql:$2}' \
> AND type = 'local' AND pwd =
> '${quote_mysql:$3}'} {yes}{no}}
> server_set_id = $2
>
> fixed_login:
> driver = plaintext
> public_name = LOGIN
> server_prompts = "Username:: : Password::"
> server_condition = ${lookup mysql{SELECT '1' FROM users \
> WHERE username = '${quote_mysql:$1}' \
> AND type = 'local' AND pwd =
> '${quote_mysql:$2}'} {yes}{no}}
> server_set_id = $1
>
> fixed_cram:
> driver = cram_md5
> public_name = CRAM-MD5
> server_secret = ${lookup mysql{SELECT pwd FROM users \
> WHERE username = '${quote_mysql:$1}'
> AND type = 'local'} {$value}fail}
> server_set_id = $1
> ***
> I've already a field for every user that is updated every time a user
> login via pop3 or imap with dovecot. But I've some users that configure
> their mailbox only for sending authenticated messages (no pop3 or imap
> access) and I'd like to know that this mailbox is in use.
>
> How can I accomplish this?
I would create a MySQL stored function. Then call the stored function
from the authenticator eg:
${lookup mysql{SELECT
stored_func_name('${quote_mysql:$1}','${quote_mysql:$2}')}}
The stored function would do both the select and the update and then
return either true or false or the password depending on the
authentication type.
Actually, come to think of it, that's probably overly complicated if
you're not familiar with stored functions. You could also just do it
from the data acl. Something like this:
warn authenticated = *
condition = ${lookup mysql{UPDATE foo SET last_access=NOW()
WHERE email='${quote_mysql:$authenticated_id}'}{true}{true}}
$authenticated_id contains whatever you set it to using server_set_id in
the authenticator.
--
Mike Cardwell : UK based IT Consultant, LAMP developer, Linux admin
Cardwell IT Ltd. : UK Company - http://cardwellit.com/ #06920226
Technical Blog : Tech Blog - https://secure.grepular.com/blog/
Spamalyser : Spam Tool - http://spamalyser.com/
