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