Re: Last_login plugin and mysql
Hello, last_login plugin uses dict interface, which does not support "update", it only supports get, set, unset and atomic inc. Set is implemented with 'INSERT INTO foo ... ON DUPLICATE UPDATE'. There is no configuration setting to change this, as dict cannot know without performing a SELECT that a value already exists. Ok, now i understand the dict behaviour. Some clarification in the last-login wiki page would be nice: * needed Database rights * Example of last_login in separate table In my special case i wanted to use the last_login field inside the mailbox table of postfixadmin. From the security point of view i don't want dovecot to be able to insert records in that table. So the only additional right should be update on the last_login field. In the case of the last_login plugin there ist a very high probability the dict key exists because of the previous successfull login which uses the same table. If the key does not exist there is a serious problem :-) Perhaps there shoud be a feature of different types of dicts: * normal dict (as implemented) * immutable dict (read only) * dict with immutable keys (only value writeable) Kind regards, Thomas
Re: Last_login plugin and mysql
On 23.09.2018 19:20, Thomas Hooge wrote:
> Hello,
>
> i have problems configuring the last_login plugin with mysql.
>
> I have extended the postfixadmin database with a new field,
> configured the plugin as described in the wiki.
>
> Finally i got errors: table is not writable, INSERT failed.
>
> Why is the insert required? The plugin should only UPDATE
> the column for existing rows.
> Or am i missing some configuration options?
> Here is my dictionary config:
>
> map {
> pattern = shared/last-login/$user
> table = mailbox
> value_field = x_last_login
> value_type = uint
>
> fields {
> username = $user
> }
> }
>
>
> Dovecot: 2.2.27 (c0f36b0)
>
> Kind regards,
> Thomas
last_login plugin uses dict interface, which does not support "update",
it only supports get, set, unset and atomic inc. Set is implemented with
'INSERT INTO foo ... ON DUPLICATE UPDATE'. There is no configuration
setting to change this, as dict cannot know without performing a SELECT
that a value already exists.
Aki
Last_login plugin and mysql
Hello,
i have problems configuring the last_login plugin with mysql.
I have extended the postfixadmin database with a new field,
configured the plugin as described in the wiki.
Finally i got errors: table is not writable, INSERT failed.
Why is the insert required? The plugin should only UPDATE
the column for existing rows.
Or am i missing some configuration options?
Here is my dictionary config:
map {
pattern = shared/last-login/$user
table = mailbox
value_field = x_last_login
value_type = uint
fields {
username = $user
}
}
Dovecot: 2.2.27 (c0f36b0)
Kind regards,
Thomas
Re: Dovecot last_login plugin and Mysql
I tried with following config, and it seems to work at least for me:
map {
pattern = shared/last-login/$user
table = users
value_field = last_login
value_type = uint
fields {
userid = $user
}
}
last_login and userid are SQL table's field name.
Aki
> On April 29, 2017 at 4:28 AM David Mehler wrote:
>
>
> Hi,
>
> Thanks for the pointing out of the oops! I missed that. Don't worry
> that has been changed.
>
> Any suggestions on my issue?
>
> Thanks.
> Dave.
>
>
> On 4/28/17, David Mehler wrote:
> > Hi,
> >
> > OOPS! I missed that one and it was big.
> >
> > Changing now.
> >
> > Thanks.
> > Dave.
> >
> >
> > On 4/28/17, Noel Butler wrote:
> >> umm
> >>
> >> It's usually a good idea to redact user and passwords for databases,
> >> even if only local socket ;)
> >>
> >> I suggest you change it ASAP.
> >>
> >> On 29/04/2017 10:53, David Mehler wrote:
> >>
> >>> 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 wrote:
> >>> On April 29, 2017 at 12:29 AM David Mehler
> >>> 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=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
> >>> mpid=16257, secured, session=
> >>>
> >>> 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(u...@example.com): Error:
> >>> dict-client: server returned failure: 1493414295 (reply took 0.026
> >>> secs)
> >>>
> >>> Apr 28 17:18:15 ohio dovecot: imap(u...@example.com): Error:
> >>> last_login_dict: Failed to write value for user u...@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
> >>
> >> --
> >> Kind Regards,
> >>
> >> Noel Butler
> >>
> >>This Email, including any attachments, may contain legally
> >> privileged
> >> information, therefore remains confidential and subject to copyright
> >> protected under international law. You may not disseminate, discuss, or
> >> reveal, any part, to anyone, without the authors express written
> >> authority to do so. If you are not the intended recipient, please notify
> >> the sender then delete all copies of this message including attachments,
> >> immediately. Confidentiality, copyright, and legal privilege are not
> >> waived or lost by reason of the mistaken delivery of this message. Only
> >> PDF [1] and ODF [2] documents accepted, please do not send proprietary
> >> formatted documents
> >>
> >>
> >>
> >> Links:
> >> --
> >> [1] http://www.adobe.com/
> >> [2] http://en.wikipedia.org/wiki/OpenDocument
> >
Re: Dovecot last_login plugin and Mysql
Hi,
Thanks for the pointing out of the oops! I missed that. Don't worry
that has been changed.
Any suggestions on my issue?
Thanks.
Dave.
On 4/28/17, David Mehler wrote:
> Hi,
>
> OOPS! I missed that one and it was big.
>
> Changing now.
>
> Thanks.
> Dave.
>
>
> On 4/28/17, Noel Butler wrote:
>> umm
>>
>> It's usually a good idea to redact user and passwords for databases,
>> even if only local socket ;)
>>
>> I suggest you change it ASAP.
>>
>> On 29/04/2017 10:53, David Mehler wrote:
>>
>>> 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 wrote:
>>> On April 29, 2017 at 12:29 AM David Mehler
>>> 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=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
>>> mpid=16257, secured, session=
>>>
>>> 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(u...@example.com): Error:
>>> dict-client: server returned failure: 1493414295 (reply took 0.026
>>> secs)
>>>
>>> Apr 28 17:18:15 ohio dovecot: imap(u...@example.com): Error:
>>> last_login_dict: Failed to write value for user u...@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
>>
>> --
>> Kind Regards,
>>
>> Noel Butler
>>
>> This Email, including any attachments, may contain legally
>> privileged
>> information, therefore remains confidential and subject to copyright
>> protected under international law. You may not disseminate, discuss, or
>> reveal, any part, to anyone, without the authors express written
>> authority to do so. If you are not the intended recipient, please notify
>> the sender then delete all copies of this message including attachments,
>> immediately. Confidentiality, copyright, and legal privilege are not
>> waived or lost by reason of the mistaken delivery of this message. Only
>> PDF [1] and ODF [2] documents accepted, please do not send proprietary
>> formatted documents
>>
>>
>>
>> Links:
>> --
>> [1] http://www.adobe.com/
>> [2] http://en.wikipedia.org/wiki/OpenDocument
>
Re: Dovecot last_login plugin and Mysql
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 wrote:
>
>> On April 29, 2017 at 12:29 AM David Mehler 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=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
>> mpid=16257, secured, session=
>>
>> 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(u...@example.com): Error:
>> dict-client: server returned failure: 1493414295 (reply took 0.026
>> secs)
>>
>> Apr 28 17:18:15 ohio dovecot: imap(u...@example.com): Error:
>> last_login_dict: Failed to write value for user u...@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
>
Re: Dovecot last_login plugin and Mysql
> On April 29, 2017 at 12:29 AM David Mehler 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=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, > mpid=16257, secured, session= > > 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(u...@example.com): Error: > dict-client: server returned failure: 1493414295 (reply took 0.026 > secs) > > Apr 28 17:18:15 ohio dovecot: imap(u...@example.com): Error: > last_login_dict: Failed to write value for user u...@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
Re: Dovecot last_login plugin and Mysql
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=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
mpid=16257, secured, session=
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(u...@example.com): Error:
dict-client: server returned failure: 1493414295 (reply took 0.026
secs)
Apr 28 17:18:15 ohio dovecot: imap(u...@example.com): Error:
last_login_dict: Failed to write value for user u...@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:postmas...@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 8
Re: Dovecot last_login plugin and Mysql
https://wiki2.dovecot.org/Plugins/LastLogin Aki > On April 28, 2017 at 9:05 PM David Mehler wrote: > > > Hi Aki, > > Thanks. Can you tell me how you implemented it? > > Thanks. > Dave. > > > On 4/28/17, Aki Tuomi wrote: > > > >> On April 28, 2017 at 8:08 PM David Mehler 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 > >
Re: Dovecot last_login plugin and Mysql
Hi Aki, Thanks. Can you tell me how you implemented it? Thanks. Dave. On 4/28/17, Aki Tuomi wrote: > >> On April 28, 2017 at 8:08 PM David Mehler 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 >
Re: Dovecot last_login plugin and Mysql
> On April 28, 2017 at 8:08 PM David Mehler 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
Dovecot last_login plugin and Mysql
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.
