last_login plugin with MySQL

[Luigi Rosa]

Hi,
I have a Dovecot test installation with MySQL userbase and authentication where
I am trying to setup last_login plugin with SQL dictionary

I read http://wiki2.dovecot.org/Plugins/LastLogin and I addes this to my
configuration


plugin {
last_login_dict = sql:/etc/dovecot/dovecot-sql.conf.ext
}
mail_plugins = $mail_plugins stats
protocol imap {
mail_plugins = $mail_plugins imap_stats last_login
}

When I log in with IMAP I get this error:

Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-sql.conf.ext) failed:
Unknown dict module: sql

/etc/dovecot/dovecot-sql.conf.ext is the file taht contains the SQL configuration.

If I put mysql instead of sql the result is the same:

Error: last_login_dict: dict_init(mysql:/etc/dovecot/dovecot-sql.conf.ext)
failed: Unknown dict module: mysql

The output of doveconf -n follows.

Do you have any idea on how to use MySQL for last_login?


Ciao,
luigi


# 2.2.14: /etc/dovecot/dovecot.conf
# OS: Linux 2.6.32-431.29.2.el6.x86_64 x86_64 CentOS release 6.5 (Final)
auth_cache_negative_ttl = 0
auth_cache_size = 100 k
auth_cache_ttl = 8 hours
auth_mechanisms = plain login digest-md5 cram-md5
auth_verbose = yes
base_dir = /var/run/dovecot/
listen = 127.0.0.1, 10.19.67.253
login_greeting = Ready.
login_trusted_networks = 10.xx.xx.0/24, 2001:xxxx:xxxx:xxxx::/64
mail_plugins = " stats"
mailbox_list_index = yes
maildir_stat_dirs = yes
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
last_login_dict = mysql:/etc/dovecot/dovecot-sql.conf.ext
sieve = ~/.dovecot.sieve
sieve_dir = ~/sieve
stats_refresh = 10s
stats_track_cmds = yes
}
protocols = imap
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0660
user = postfix
}
}
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
}
service managesieve-login {
inet_listener sieve {
port = 4190
}
}
service pop3-login {
inet_listener pop3 {
port = 110
}
inet_listener pop3s {
port = 995
ssl = yes
}
}
service stats {
fifo_listener stats-mail {
mode = 0666
}
}
ssl_cert = </etc/xxxxx/xxxxxx.luigirosa.com.crt
ssl_dh_parameters_length = 2048
ssl_key = </etc/xxxxx/xxxxx.luigirosa.com.key
ssl_parameters_regenerate = 202 hours
ssl_protocols = !SSLv2 !SSLv3
syslog_facility = local5
userdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
verbose_proctitle = yes
protocol lmtp {
mail_plugins = sieve
}
protocol imap {
imap_client_workarounds = tb-extra-mailbox-sep
mail_plugins = " stats imap_stats last_login"
}
protocol pop3 {
pop3_client_workarounds = outlook-no-nuls
pop3_uidl_format = %08Xu%08Xv
}

[Luigi Rosa]

Hi, I have a Dovecot test installation with MySQL userbase and
authentication where I am trying to setup last_login plugin with SQL
dictionary

I figured out I have to use dovecot-dict-sql.conf.ext instead of
dovecot-sql.conf.ext

I configured dovecot-dict-sql.conf.ext this way:

driver= mysql
connect = {the connect string that works for user lookup}
map {
pattern = last-login
table = mailbox
username_field = username
}

And I defined the plugin this way:

plugin {
last_login_dict = sql:/etc/dovecot/dovecot-dict-sql.conf.ext
last_login_key = last-login/%u # default
}

But I still get the same error:

Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-dict-sql.conf.ext)
failed: Unknown dict module: sql




Ciao,
luigi

- --
/
+--[Luigi Rosa]--
\

Every program has (at least) two purposes: the one for which it was written
and another for which it wasn't.

[Gedalya]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi, I have a Dovecot test installation with MySQL userbase and
authentication where I am trying to setup last_login plugin with SQL
dictionary

I figured out I have to use dovecot-dict-sql.conf.ext instead of
dovecot-sql.conf.ext
driver= mysql
connect = {the connect string that works for user lookup}
map {
pattern = last-login
table = mailbox
username_field = username
}
plugin {
last_login_dict = sql:/etc/dovecot/dovecot-dict-sql.conf.ext
last_login_key = last-login/%u # default
}
Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-dict-sql.conf.ext)
failed: Unknown dict module: sql

It's been said here before, and I'll echo it again -- better
documentation for dict would be helpful, a step-by-step guide to its
principles of operation, rather than just the anecdotal examples we
have, out of which we're supposed to extrapolate.

[Gedalya]

Post by Gedalya

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi, I have a Dovecot test installation with MySQL userbase and
authentication where I am trying to setup last_login plugin with SQL
dictionary

I figured out I have to use dovecot-dict-sql.conf.ext instead of
dovecot-sql.conf.ext
driver= mysql
connect = {the connect string that works for user lookup}
map {
pattern = last-login
table = mailbox
username_field = username
}
plugin {
last_login_dict = sql:/etc/dovecot/dovecot-dict-sql.conf.ext
last_login_key = last-login/%u # default
}
dict_init(sql:/etc/dovecot/dovecot-dict-sql.conf.ext)
failed: Unknown dict module: sql

It's been said here before, and I'll echo it again -- better
documentation for dict would be helpful, a step-by-step guide to its
principles of operation, rather than just the anecdotal examples we
have, out of which we're supposed to extrapolate.

"The above SQL example uses dictionary proxy process (see below),
because SQL libraries aren't linked to all Dovecot binaries. The file
and Redis examples use direct access. "

http://wiki2.dovecot.org/Quota/Dict

I guess you have to use the proxy.

So maybe something like

plugin {
last_login_dict = proxy::lastlogin
}

dict {
lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}

I'll try it later myself, thanks for taking it thus far :-) I've been
meaning to try this out but was rather perplexed.

[Luigi Rosa]

Post by Gedalya
I guess you have to use the proxy.
So maybe something like
plugin { last_login_dict = proxy::lastlogin }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext }
I'll try it later myself, thanks for taking it thus far :-) I've been
meaning to try this out but was rather perplexed.

I made some progress thank to your suggestions.

dovecot-dict-sql.conf.ext content is:

map {
pattern = /shared/last-login/$user
table = mailbox
username_field = username
value_field = lastlogin
fields {
username = $user
}
}

Note that you must not specify "driver" parameter


Plugin is defined this way:


plugin {
last_login_dict = proxy::lastlogin
last_login_key = last-login/%u # default
}

dict {
lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
mode = 0660
user = 501
}


But I get two errors.


First is the permission of /var/run/dovecot/dict that is 700 root.root and I
get a permission error. Nothing changes if I put user = root. I have to
manually chmod /var/run/dovecot/dict every time I restart Dovecot otherwise I get

Error: net_connect_unix(/var/run/dovecot//dict) failed: Permission denied
(euid=501(mailvirtuale) egid=501(mailvirtuale) missing +r perm:
/var/run/dovecot//dict, dir owned by 0:0 mode=0755)

If I chmod 777 /var/run/dovecot/dict the error disappears but I get this:

dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1,
lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB>
dovecot: dict: Error: sql dict set: Invalid/unmapped key:
shared/last-login/username



Ciao,
luigi

- --
/
+--[Luigi Rosa]--
\

Don't tell me what you dreamed last night for I've been reading Freud.

[Gedalya]

Post by Luigi Rosa
But I get two errors.
First is the permission of /var/run/dovecot/dict that is 700 root.root and I
get a permission error. Nothing changes if I put user = root. I have to
manually chmod /var/run/dovecot/dict every time I restart Dovecot otherwise I get
Error: net_connect_unix(/var/run/dovecot//dict) failed: Permission denied
/var/run/dovecot//dict, dir owned by 0:0 mode=0755)

In conf.d/10-master.conf (this is from my config):

service dict {
unix_listener dict {
mode = 0660
user = vmail
#group =
}
}

Post by Luigi Rosa
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1,
lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB>
shared/last-login/username

Ok, what were you thinking when you defined this pattern =
/shared/last-login/$user ?
I personally don't understand this part of the dovecot config at all..

Let's look into this..

Post by Luigi Rosa
Ciao,
luigi

[Gedalya]

Post by Gedalya

Post by Luigi Rosa
dovecot: imap-login: Login: user=<username>, method=PLAIN,
rip=127.0.0.1,
lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB>
shared/last-login/username

Ok, what were you thinking when you defined this pattern =
/shared/last-login/$user ?
I personally don't understand this part of the dovecot config at all..
Let's look into this..

OK I suspect that"

pattern = shared/{dictionary name}/$user

So:

pattern = shared/lastlogin/$user

As per:

dict {
lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
mode = 0660
user = 501
}

[Gedalya]

Post by Gedalya

Post by Gedalya

Post by Luigi Rosa
dovecot: imap-login: Login: user=<username>, method=PLAIN,
rip=127.0.0.1,
lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB>
shared/last-login/username

Ok, what were you thinking when you defined this pattern =
/shared/last-login/$user ?
I personally don't understand this part of the dovecot config at all..
Let's look into this..

OK I suspect that"
pattern = shared/{dictionary name}/$user
pattern = shared/lastlogin/$user
dict {
lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
mode = 0660
user = 501
}

Or it's plugin name so try last_login ??

[Luigi Rosa]

service dict { unix_listener dict { mode = 0660 user = vmail #group = } }

That solved the permission issue, thanks!



Ciao,
luigi

- --
/
+--[Luigi Rosa]--
\

Only one human captain has ever survived battle with a Minbari fleet.
He is behind me.
You are in front of me.
If you value your lives, be somewhere else.
--Delenn "Severed Dreams"

[Gedalya]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Gedalya
I guess you have to use the proxy.
So maybe something like
plugin { last_login_dict = proxy::lastlogin }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext }
I'll try it later myself, thanks for taking it thus far :-) I've been
meaning to try this out but was rather perplexed.

I made some progress thank to your suggestions.
map {
pattern = /shared/last-login/$user
table = mailbox
username_field = username
value_field = lastlogin
fields {
username = $user
}
}
Note that you must not specify "driver" parameter
plugin {
last_login_dict = proxy::lastlogin
last_login_key = last-login/%u # default
}
dict {
lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
mode = 0660
user = 501
}
But I get two errors.
First is the permission of /var/run/dovecot/dict that is 700 root.root and I
get a permission error. Nothing changes if I put user = root. I have to
manually chmod /var/run/dovecot/dict every time I restart Dovecot otherwise I get
Error: net_connect_unix(/var/run/dovecot//dict) failed: Permission denied
/var/run/dovecot//dict, dir owned by 0:0 mode=0755)
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1,
lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB>
shared/last-login/username

OK I got it.

The code looks at shared/$last_login_key, so shared/last-login/username,
but your map says /shared/last-login/$user, note the leading slash!
That's probably all it is.

[Luigi Rosa]

Post by Gedalya
The code looks at shared/$last_login_key, so shared/last-login/username,
but your map says /shared/last-login/$user, note the leading slash! That's
probably all it is.

BINGO!

Now that we killed the mosters of this level, let's move to the next level.....


Welcome to the SQL query level!

I was expecting an UPDATE...WHERE but I got this:

INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON
DUPLICATE KEY UPDATE lastlogin='1413647370'

This is not very useful if I want a `lastlogin` field of the `mailbox` table
updated on every login.




Ciao,
luigi

- --
/
+--[Luigi Rosa]--
\

For every human problem, there is a neat, simple solution;
and it is always wrong.

[Sven Hartge]

Post by Luigi Rosa
INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON
DUPLICATE KEY UPDATE lastlogin='1413647370'
This is not very useful if I want a `lastlogin` field of the `mailbox` table
updated on every login.

This SQL query looks correct to me. It INSERTs a new row for a username
if one does not already exist but UPDATEs it if there is already such a
row.

This is the normal and advised way of doing this specific task in an
atomic way by using the "ON DUPLICATE" syntax of SQL.

Gr??e,
Sven.

[Luigi Rosa]

This SQL query looks correct to me. It INSERTs a new row for a username if
one does not already exist but UPDATEs it if there is already such a row.

My fault: I didn't know this SQL syntax.

In fact the field has been correctly updated.


Thank you Sven!


Ciao,
luigi

- --
/
+--[Luigi Rosa]--
\

Every government is a parliament of whores. The trouble is, in a
democracy, the whores are us.
--P. J. O'Rourke