dovecot - Jun 2019 - Last Login Plugin Help

Hi,

I'm configuring Last_Login plugin whith Mysql back end and all works fine.

I just wonder to track besides time_stamp, user and domain, the remote 
IP and proto (imap or pop3)

I tried in dovecot.conf:

plugin {

...

...

 ??? # Track last login time on imap and pop3
 ??? last_login_dict = proxy::lastlogin
 ??? last_login_key = last-login/%u/%d/%r

}

dict {
...

 ??? lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
...
}

protocol imap {

 ??? mail_plugins = $mail_plugins ... last_login
...

}

protocol pop3 {
 ??? mail_plugins = $mail_plugins last_login

...

}

And dovecot-last-login.conf:

connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin 
password=xxxxxxxxxxxxxxxxxx

map {
 ??? pattern = shared/last-login/$user/$domain
 ??? table = last_login
 ??? value_field = last_login
 ??? value_type = uint

 ??? fields {
 ??????? username = $user
 ??????? domain = $domain
 ?????? rip = $rip
 ??? }
}


And the table criated in mysql:

CREATE TABLE IF NOT EXISTS `last_login` (
 ??? `username` VARCHAR(255) NOT NULL DEFAULT '',
 ??? `domain` VARCHAR(255) NOT NULL DEFAULT '',
 ??? `last_login` INT(11) DEFAULT NULL,
 ??? `rip` VARCHAR(16) NOT NULL DEFAULT '',
 ??? PRIMARY KEY (`username`),
 ??? INDEX (`domain`),
 ??? INDEX (`last_login`),
 ??? INDEX (`rip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And got de error in dovecot log:

Jun 06 13:56:59 dict(21157): Error: Failed to initialize dictionary 
'lastlogin': dict mysql: Error in configuration file 
/etc/dovecot/dovecot-last-login.conf line 13: Unused variable: rip

Thanks in any help.


-- 
----------------------------------
     _    Eng? Julio Cesar Covolato
    0v0   <julio at psi.com.br>
   /(_)\  F: +55 11 99175-9260
    ^ ^   PSI INTERNET
----------------------------------


---
Este email foi escaneado pelo Avast antiv?rus.
https://www.avast.com/antivirus

Sory, forgot dovecot -n:

# 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.21 (92477967)
# OS: Linux 4.15.0-47-generic x86_64 Ubuntu 18.04.2 LTS
auth_default_realm = psi.com.br
auth_master_user_separator = *
auth_mechanisms = PLAIN LOGIN
deliver_log_format = from=%{from}, envelope_sender=%{from_envelope}, 
subject=%{subject}, msgid=%m, size=%{size}, %$
dict {
 ? lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
 ? quotadict = mysql:/etc/dovecot/dovecot-used-quota.conf
}
disable_plaintext_auth = no
first_valid_uid = 2000
last_valid_uid = 2000
listen = * [::]
log_path = /var/log/dovecot/dovecot.log
mail_gid = 2000
mail_location = maildir:%Lh/Maildir/:INDEX=%Lh/Maildir/
mail_plugins = quota mailbox_alias acl mail_log notify stats
mail_uid = 2000
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope 
encoded-character vacation subaddress comparator-i;ascii-numeric 
relational regex imap4flags copy include variables body enotify 
environment mailbox date index ihave duplicate mime foreverypart extracttext
namespace {
 ? inbox = yes
 ? location  ? mailbox Archive {
 ??? auto = no
 ??? special_use = \Archive
 ? }
 ? mailbox Archives {
 ??? auto = no
 ??? special_use = \Archive
 ? }
 ? mailbox "Deleted Messages" {
 ??? auto = no
 ??? special_use = \Trash
 ? }
 ? mailbox Drafts {
 ??? auto = subscribe
 ??? special_use = \Drafts
 ? }
 ? mailbox Junk {
 ??? auto = subscribe
 ??? special_use = \Junk
 ? }
 ? mailbox "Junk E-mail" {
 ??? auto = no
 ??? special_use = \Junk
 ? }
 ? mailbox Sent {
 ??? auto = subscribe
 ??? special_use = \Sent
 ? }
 ? mailbox "Sent Items" {
 ??? auto = no
 ??? special_use = \Sent
 ? }
 ? mailbox "Sent Messages" {
 ??? auto = no
 ??? special_use = \Sent
 ? }
 ? mailbox Spam {
 ??? auto = no
 ??? special_use = \Junk
 ? }
 ? mailbox Trash {
 ??? auto = subscribe
 ??? special_use = \Trash
 ? }
 ? prefix  ? separator = /
 ? type = private
}
namespace {
 ? list = children
 ? location = maildir:%%Lh/Maildir/:INDEX=%%Lh/Maildir/Shared/%%Ld/%%Ln
 ? prefix = Shared/%%u/
 ? separator = /
 ? subscriptions = yes
 ? type = shared
}
passdb {
 ? args = /etc/dovecot/dovecot-mysql.conf
 ? driver = sql
}
passdb {
 ? args = /etc/dovecot/dovecot-master-users
 ? driver = passwd-file
 ? master = yes
}
plugin {
 ? acl = vfile
 ? last_login_dict = proxy::lastlogin
 ? last_login_key = last-login/%u/%d
 ? mail_log_fields = uid box msgid size from subject
 ? mailbox_alias_new = Sent Messages
 ? mailbox_alias_new2 = Sent Items
 ? mailbox_alias_old = Sent
 ? mailbox_alias_old2 = Sent
 ? quota = dict:user::proxy::quotadict
 ? quota_grace = 10%%
 ? quota_warning = storage=100%% quota-warning 100 %u
 ? quota_warning2 = storage=95%% quota-warning 95 %u
 ? quota_warning3 = storage=90%% quota-warning 90 %u
 ? quota_warning4 = storage=85%% quota-warning 85 %u
 ? sieve = ~/sieve/dovecot.sieve
 ? sieve_before = /var/vmail/sieve/dovecot.sieve
 ? sieve_dir = ~/sieve
 ? sieve_global_dir = /var/vmail/sieve
 ? sieve_max_redirects = 30
 ? sieve_vacation_send_from_recipient = yes
 ? stats_refresh = 30 secs
 ? stats_track_cmds = yes
}
protocols = pop3 imap sieve lmtp
service auth {
 ? unix_listener /var/spool/postfix/private/dovecot-auth {
 ??? group = postfix
 ??? mode = 0666
 ??? user = postfix
 ? }
 ? unix_listener auth-master {
 ??? group = vmail
 ??? mode = 0666
 ??? user = vmail
 ? }
 ? unix_listener auth-userdb {
 ??? group = vmail
 ??? mode = 0660
 ??? user = vmail
 ? }
}
service dict {
 ? unix_listener dict {
 ??? group = vmail
 ??? mode = 0660
 ??? user = vmail
 ? }
}
service imap-login {
 ? process_limit = 500
 ? service_count = 1
}
service lmtp {
 ? executable = lmtp -L
 ? inet_listener lmtp {
 ??? address = 127.0.0.1
 ??? port = 24
 ? }
 ? process_min_avail = 5
 ? unix_listener /var/spool/postfix/private/dovecot-lmtp {
 ??? group = postfix
 ??? mode = 0600
 ??? user = postfix
 ? }
 ? user = vmail
}
service managesieve-login {
 ? inet_listener sieve {
 ??? address = 127.0.0.1
 ??? port = 4190
 ? }
}
service pop3-login {
 ? service_count = 1
}
service quota-warning {
 ? executable = script /usr/local/bin/dovecot-quota-warning.sh
 ? unix_listener quota-warning {
 ??? group = vmail
 ??? mode = 0660
 ??? user = vmail
 ? }
}
service stats {
 ? fifo_listener stats-mail {
 ??? mode = 0644
 ??? user = vmail
 ? }
 ? inet_listener {
 ??? address = 127.0.0.1
 ??? port = 24242
 ? }
}
ssl_cert = </etc/letsencrypt/live/psi.com.br/fullchain.pem
ssl_cipher_list = 
ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:RSA+AES:RSA+3DES:!aNULL:!MD5
ssl_key =? # hidden, use -P to show it
ssl_prefer_server_ciphers = yes
userdb {
 ? args = /etc/dovecot/dovecot-mysql.conf
 ? driver = sql
}
protocol lda {
 ? lda_mailbox_autocreate = yes
 ? lda_mailbox_autosubscribe = yes
 ? log_path = /var/log/dovecot/sieve.log
 ? mail_plugins = quota mailbox_alias acl mail_log notify stats sieve
}
protocol lmtp {
 ? lmtp_save_to_detail_mailbox = yes
 ? log_path = /var/log/dovecot/lmtp.log
 ? mail_plugins = quota sieve
 ? recipient_delimiter = +
}
protocol imap {
 ? imap_client_workarounds = tb-extra-mailbox-sep
 ? log_path = /var/log/dovecot/imap.log
 ? mail_max_userip_connections = 30
 ? mail_plugins = quota mailbox_alias acl mail_log notify stats 
imap_quota imap_acl imap_stats last_login
}
protocol pop3 {
 ? log_path = /var/log/dovecot/pop3.log
 ? mail_max_userip_connections = 30
 ? mail_plugins = quota mailbox_alias acl mail_log notify stats last_login
 ? pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
 ? pop3_uidl_format = %08Xu%08Xv
}



----------------------------------
     _    Eng? Julio Cesar Covolato
    0v0   <julio at psi.com.br>
   /(_)\  F: +55 11 99175-9260
    ^ ^   PSI INTERNET
----------------------------------

Em 06/06/2019 16:40, J?lio Covolato via dovecot
escreveu:
> Hi,
>
> I'm configuring Last_Login plugin whith Mysql back end and all works 
> fine.
>
> I just wonder to track besides time_stamp, user and domain, the remote 
> IP and proto (imap or pop3)
>
> I tried in dovecot.conf:
>
> plugin {
>
> ...
>
> ...
>
> ??? # Track last login time on imap and pop3
> ??? last_login_dict = proxy::lastlogin
> ??? last_login_key = last-login/%u/%d/%r
>
> }
>
> dict {
> ...
>
> ??? lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
> ...
> }
>
> protocol imap {
>
> ??? mail_plugins = $mail_plugins ... last_login
> ...
>
> }
>
> protocol pop3 {
> ??? mail_plugins = $mail_plugins last_login
>
> ...
>
> }
>
> And dovecot-last-login.conf:
>
> connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin 
> password=xxxxxxxxxxxxxxxxxx
>
> map {
> ??? pattern = shared/last-login/$user/$domain
> ??? table = last_login
> ??? value_field = last_login
> ??? value_type = uint
>
> ??? fields {
> ??????? username = $user
> ??????? domain = $domain
> ?????? rip = $rip
> ??? }
> }
>
>
> And the table criated in mysql:
>
> CREATE TABLE IF NOT EXISTS `last_login` (
> ??? `username` VARCHAR(255) NOT NULL DEFAULT '',
> ??? `domain` VARCHAR(255) NOT NULL DEFAULT '',
> ??? `last_login` INT(11) DEFAULT NULL,
> ??? `rip` VARCHAR(16) NOT NULL DEFAULT '',
> ??? PRIMARY KEY (`username`),
> ??? INDEX (`domain`),
> ??? INDEX (`last_login`),
> ??? INDEX (`rip`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> And got de error in dovecot log:
>
> Jun 06 13:56:59 dict(21157): Error: Failed to initialize dictionary 
> 'lastlogin': dict mysql: Error in configuration file 
> /etc/dovecot/dovecot-last-login.conf line 13: Unused variable: rip
>
> Thanks in any help.
>
>
---
Este email foi escaneado pelo Avast antiv?rus.
https://www.avast.com/antivirus

<!doctype html>
<html>
 <head> 
  <meta charset="UTF-8"> 
 </head>
 <body>
  <div>
   <br>
  </div>
  <blockquote type="cite">
   <div>
    On 6 June 2019 22:40 Júlio Covolato via dovecot <
    <a
href="mailto:dovecot@dovecot.org">dovecot@dovecot.org</a>>
wrote:
   </div>
   <div>
    <br>
   </div>
   <div>
    <br>
   </div>
   <div>
    Hi,
   </div>
   <div>
    <br>
   </div>
   <div>
    I'm configuring Last_Login plugin whith Mysql back end and all works
fine.
   </div>
   <div>
    <br>
   </div>
   <div>
    I just wonder to track besides time_stamp, user and domain, the remote
   </div>
   <div>
    IP and proto (imap or pop3)
   </div>
   <div>
    <br>
   </div>
   <div>
    I tried in dovecot.conf:
   </div>
   <div>
    <br>
   </div>
   <div>
    plugin {
   </div>
   <div>
    <br>
   </div>
   <div>
    ...
   </div>
   <div>
    <br>
   </div>
   <div>
    ...
   </div>
   <div>
    <br>
   </div>
   <div>
        # Track last login time on imap and pop3
   </div>
   <div>
        last_login_dict = proxy::lastlogin
   </div>
   <div>
        last_login_key = last-login/%u/%d/%r
   </div>
   <div>
    <br>
   </div>
   <div>
    }
   </div>
   <div>
    <br>
   </div>
   <div>
    dict {
   </div>
   <div>
    ...
   </div>
   <div>
    <br>
   </div>
   <div>
        lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
   </div>
   <div>
    ...
   </div>
   <div>
    }
   </div>
   <div>
    <br>
   </div>
   <div>
    protocol imap {
   </div>
   <div>
    <br>
   </div>
   <div>
        mail_plugins = $mail_plugins ... last_login
   </div>
   <div>
    ...
   </div>
   <div>
    <br>
   </div>
   <div>
    }
   </div>
   <div>
    <br>
   </div>
   <div>
    protocol pop3 {
   </div>
   <div>
        mail_plugins = $mail_plugins last_login
   </div>
   <div>
    <br>
   </div>
   <div>
    ...
   </div>
   <div>
    <br>
   </div>
   <div>
    }
   </div>
   <div>
    <br>
   </div>
   <div>
    And dovecot-last-login.conf:
   </div>
   <div>
    <br>
   </div>
   <div>
    connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin
   </div>
   <div>
    password=xxxxxxxxxxxxxxxxxx
   </div>
   <div>
    <br>
   </div>
   <div>
    map {
   </div>
   <div>
        pattern = shared/last-login/$user/$domain
   </div>
   <div>
        table = last_login
   </div>
   <div>
        value_field = last_login
   </div>
   <div>
        value_type = uint
   </div>
   <div>
    <br>
   </div>
   <div>
        fields {
   </div>
   <div>
            username = $user
   </div>
   <div>
            domain = $domain
   </div>
   <div>
           rip = $rip
   </div>
   <div>
        }
   </div>
   <div>
    }
   </div>
   <div></div>
  </blockquote>
  <div>
   <br>
  </div>
  <div>
   You are missing /$rip in pattern. 
  </div>
  <div class="io-ox-signature">
   <pre>---
Aki Tuomi</pre>
  </div> 
 </body>
</html>

Em 06/06/2019 22:45, Aki Tuomi via dovecot escreveu:
>> map {
>> ??? pattern = shared/last-login/$user/$domain
>> ??? table = last_login
>> ??? value_field = last_login
>> ??? value_type = uint
>>
>> ??? fields {
>> ??????? username = $user
>> ??????? domain = $domain
>> ?????? rip = $rip
>> ??? }
>> }
>
> You are missing /$rip in pattern.
> ---
> Aki Tuomi

Thanks Aki.

Works perfectly.

What I did:

#$ cat dovecot-last-login.conf

connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin 
password=xxxxxxxxxxxxxxx

map {
 ??? pattern = shared/last-login/$user/$domain/$rip/$service
 ??? table = last_login
 ??? value_field = last_login
 ??? value_type = uint

 ??? fields {
 ??????? username = $user
 ??????? domain = $domain
 ??????? rip = $rip
 ??????? proto = $service
 ??? }
}

------------------------------------------------------

dovecot.conf:

plugin {

...

...

 ??? # Track last login time on imap and pop3
 ??? last_login_dict = proxy::lastlogin
 ??? last_login_key = last-login/%u/%d/%r/%s
}

Result on mysql:

mysql> select * from last_login where username = 'julio at
xxx.com.br';
+------------------+------------+------------+---------------+-------+
| username???????? | domain???? | last_login | rip?????????? | proto |
+------------------+------------+------------+---------------+-------+
| julio at xxx.com.br | xxx.com.br | 1559921589 | 177.xxx.xxx.230 | imap? |
+------------------+------------+------------+---------------+-------+
1 row in set (0.00 sec)


Thank you very much!!

----------------------------------
     _    Eng? Julio Cesar Covolato
    0v0   <julio at psi.com.br>
   /(_)\  F: +55 11 99175-9260
    ^ ^   PSI INTERNET
----------------------------------



---
Este email foi escaneado pelo Avast antiv?rus.
https://www.avast.com/antivirus
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20190607/bf97eb1b/attachment.html>