Hi Aki,

Thanks. Can you tell me how you implemented it?

Thanks. Dave.

On 4/28/17, Aki Tuomi <aki.tuomi@dovecot.fi> wrote:

...

On April 28, 2017 at 8:08 PM David Mehler <dave.mehler@gmail.com> wrote:

Hello,

Is anyone using the last_login plugin with a Mysql database? I'd like to track when users were last on the system.

Thanks. Dave.

It's used by our customers.

Aki

Hello,

Trying to implement lastlogin via mysql. I'm getting an unknown column username which I don't get. Here's the log:

Apr 28 17:18:15 ohio dovecot: imap-login: Login: user=<user@example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=16257, secured, session=<KxiSnEBOwUx/AAAB>

Apr 28 17:18:15 ohio dovecot: dict(17099): Error: sql dict: commit failed: Unknown column 'username' in 'field list'

Apr 28 17:18:15 ohio dovecot: imap(user@example.com): Error: dict-client: server returned failure: 1493414295 (reply took 0.026 secs)

Apr 28 17:18:15 ohio dovecot: imap(user@example.com): Error: last_login_dict: Failed to write value for user user@example.com

and my configuration:

# 2.2.29.1 (e0b76e3): /usr/local/etc/dovecot/dovecot.conf # Pigeonhole version 0.4.18 (29cc74d) # OS: FreeBSD 10.3-RELEASE-p18 amd64 auth_default_realm = example.com auth_mechanisms = plain login cram-md5 auth_realms = example.com example.net dict { acl = mysql:/usr/local/etc/dovecot/dovecot-share-folder.conf lastlogin = mysql:/usr/local/etc/dovecot/dovecot-last-login.conf sqlquota = mysql:/usr/local/etc/dovecot/dovecot-used-quota.conf } disable_plaintext_auth = no first_valid_gid = 999 first_valid_uid = 999 hostname = mail.example.com imap_client_workarounds = delay-newmail tb-extra-mailbox-sep tb-lsub-flags last_valid_gid = 999 last_valid_uid = 999 lda_mailbox_autocreate = yes lda_mailbox_autosubscribe = yes listen = 127.0.0.1 xxx.xxx.xxx.xxx mail_fsync = never mail_gid = vmail mail_home = /home/vmail/%d/%n mail_location = maildir:~/mail/:LAYOUT=fs:INDEX=~/mail/ mail_plugins = acl mail_log notify quota quota_clone trash virtual welcome zlib mail_server_admin = mailto:postmaster@example.com mail_uid = vmail mailbox_list_index = yes 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 imapflags notify imapsieve vnd.dovecot.imapsieve namespace { hidden = no list = yes location = maildir:/home/vmail/public:LAYOUT=fs:CONTROL=~/mail/public:INDEXPVT=~/mail/public:INDEX=~/mail/public mailbox TestFolder { auto = subscribe comment = Public Folder for message sharing } prefix = public/ separator = / subscriptions = yes type = public } namespace { list = yes location = maildir:~/mail/:INDEX=~/mail/shared/%%Ld/%%Ln prefix = shared/%%u/ separator = / subscriptions = yes type = shared } namespace { location = virtual:/usr/local/etc/dovecot/virtual mailbox All { auto = subscribe comment = All my messages special_use = \All } prefix = virtual/ separator = / } namespace inbox { inbox = yes location = mailbox Archive { auto = no special_use = \Archive } mailbox Archives { auto = subscribe special_use = \Archive } mailbox "Deleted Messages" { auto = no autoexpunge = 30 days special_use = \Trash } mailbox Drafts { auto = subscribe special_use = \Drafts } mailbox Junk { auto = no autoexpunge = 30 days special_use = \Junk } mailbox "Junk E-mail" { auto = no autoexpunge = 30 days 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 = subscribe autoexpunge = 30 days special_use = \Junk } mailbox Trash { auto = subscribe autoexpunge = 30 days special_use = \Trash } prefix = separator = / type = private } passdb { args = /usr/local/etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { acl = vfile:/usr/local/etc/dovecot/global-acls:cache_secs=300 acl_anyone = allow acl_shared_dict = file:/usr/local/etc/dovecot/shared-mailboxes imapsieve_mailbox1_before = file:/usr/local/lib/dovecot/sieve/report-spam.sieve imapsieve_mailbox1_causes = COPY imapsieve_mailbox1_name = Spam imapsieve_mailbox2_before = file:/usr/local/lib/dovecot/sieve/report-ham.sieve imapsieve_mailbox2_causes = COPY imapsieve_mailbox2_from = Spam imapsieve_mailbox2_name = * last_login_dict = proxy::lastlogin last_login_key = last-login/%u mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size quota = count:User quota quota_clone_dict = proxy::sqlquota quota_exceeded_message = Storage quota for this account has been exceeded, please try again later. quota_grace = 10%% quota_status_nouser = DUNNO quota_status_overquota = 552 5.2.2 Mailbox is full quota_status_success = DUNNO quota_vsizes = true quota_warning = storage=100%% quota-exceeded 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 quota_warning5 = storage=75%% quota-warning 75 %u sieve = /home/vmail/%d/sieve/dovecot.sieve sieve_before = /home/vmail/sieve/dovecot.sieve sieve_default = /usr/local/etc/dovecot/sieve/dovecot.sieve sieve_dir = /usr/local/etc/dovecot/sieve sieve_extensions = +notify +imapflags sieve_global_dir = /home/vmail/sieve sieve_global_extensions = +vnd.dovecot.pipe +vnd.dovecot.execute sieve_max_redirects = 30 sieve_max_script_size = 1M sieve_pipe_bin_dir = /usr/local/lib/dovecot/sieve sieve_plugins = sieve_imapsieve sieve_extprograms sieve_user_log = /home/vmail/sieve/sieve_error.log trash = /usr/local/etc/dovecot/dovecot-trash.conf.ext welcome_script = welcome %u welcome_wait = yes } protocols = imap sieve sendmail_path = /usr/local/sbin/sendmail service auth { unix_listener /var/spool/postfix/private/auth { mode = 0666 } unix_listener auth-userdb { group = vmail mode = 0666 user = vmail } } service dict { unix_listener dict { mode = 0660 user = vmail } } service imap-login { inet_listener imap { port = 143 } inet_listener imaps { port = 993 ssl = yes } } service managesieve-login { inet_listener sieve { address = 127.0.0.1 port = 4190 } } service quota-status { client_limit = 1 executable = quota-status -p postfix inet_listener { address = 127.0.0.1 port = 12345 } } service quota-warning { executable = script /usr/local/etc/dovecot/quota-warning.sh unix_listener quota-warning { group = vmail mode = 0660 user = vmail } user = vmail } service welcome { executable = script /usr/local/bin/welcome.sh unix_listener welcome { user = vmail } user = vmail } ssl_cert = </usr/local/etc/letsencrypt/live/mail.example.com/fullchain.pem ssl_cipher_list = ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256@STRENGTH ssl_dh_parameters_length = 2048 ssl_key = # hidden, use -P to show it ssl_prefer_server_ciphers = yes ssl_protocols = !SSLv2 !SSLv3 !TLSv1 !TLSv1.1 userdb { driver = prefetch } userdb { args = /usr/local/etc/dovecot/dovecot-sql.conf.ext driver = sql } userdb { args = uid=vmail gid=vmail home=/home/vmail/%d/%n driver = static } protocol lda { mail_fsync = optimized mail_plugins = acl mail_log notify quota quota_clone trash virtual welcome zlib sieve } protocol imap { mail_plugins = acl mail_log notify quota quota_clone trash virtual welcome zlib imap_acl imap_quota imap_sieve imap_zlib last_login }

dovecot-last-login.conf connect = host=/tmp/mysql.sock dbname=dbname user=user password=password

# Last Login map { pattern = shared/last-login/$user table = virtual_users username_field = user value_field = lastlogin fields { username = $user } }

can anyone spot my error?

Thanks. Dave.

On 4/28/17, Aki Tuomi <aki.tuomi@dovecot.fi> wrote:

https://wiki2.dovecot.org/Plugins/LastLogin

Aki

...

On April 28, 2017 at 9:05 PM David Mehler <dave.mehler@gmail.com> wrote:

Hi Aki,

Thanks. Can you tell me how you implemented it?

Thanks. Dave.

On 4/28/17, Aki Tuomi <aki.tuomi@dovecot.fi> wrote:

...

...

On April 28, 2017 at 8:08 PM David Mehler <dave.mehler@gmail.com> wrote:

Hello,

Is anyone using the last_login plugin with a Mysql database? I'd like to track when users were last on the system.

Thanks. Dave.

It's used by our customers.

Aki

Hello,

Here is the requested file:

driver = mysql connect = host=/tmp/mysql.sock dbname=mail user=mail_admin password=MainBoard55

# Last Login map { pattern = shared/last-login/$user table = virtual_users username_field = user value_field = lastlogin fields { user = $user } }

Thanks. Dave.

On 4/28/17, Aki Tuomi <aki.tuomi@dovecot.fi> wrote:

...

On April 29, 2017 at 12:29 AM David Mehler <dave.mehler@gmail.com> wrote:

Hello,

Trying to implement lastlogin via mysql. I'm getting an unknown column username which I don't get. Here's the log:

Apr 28 17:18:15 ohio dovecot: imap-login: Login: user=<user@example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=16257, secured, session=<KxiSnEBOwUx/AAAB>

Apr 28 17:18:15 ohio dovecot: dict(17099): Error: sql dict: commit failed: Unknown column 'username' in 'field list'

Apr 28 17:18:15 ohio dovecot: imap(user@example.com): Error: dict-client: server returned failure: 1493414295 (reply took 0.026 secs)

Apr 28 17:18:15 ohio dovecot: imap(user@example.com): Error: last_login_dict: Failed to write value for user user@example.com

You can configure all this in dovecot-dict-sql.conf, which you have not provided. The actual column names are defined there.

Aki