I've noticed several threads over the last year or so about last-login, and I was curious WHY people care about tracking this in the database. I can see wanting to know if a user has logged in recently, but this seems quite easy to tell by simply looking at the time stamp and/or contents of the mail spool for the user.
For example, on my system I can look at the timestamps on the 'new' folders in the user's maildir to see if they are getting mail, and if the folders are empty, I have a time stamp of when they last checked that mailbox, giving me a pretty accurate time for when they last logged in.
For example, looking at one user:
ls -lsdtr /path/to/user/maildir/{new,.**/new}
I can see that the most recent "new" mailboxes were accessed on 02 Mar 14:25 and 03 Mar 01:45, and I can see that the latter mailbox has files in it and the former mailbox does not (just by the size, without doing an extra ls of those directories), so I know that the last time the user logged in was about 14:25 or later yesterday and that they definitely have not logged in in the last 3h05, which seems close enough to me.
Am I missing some reason I would need/want to keep track of that specific login time separately?
-- 'But you ain't part of it, are you?' said Granny conversationally. 'You try, but you always find yourself watchin' yourself watchin' people, eh? Never quite believin' anything? Thinkin' the wrong thoughts?'
Le 3/3/21 à 12:57 PM, @lbutlr a écrit :
I've noticed several threads over the last year or so about last-login, and I was curious WHY people care about tracking this in the database. I can see wanting to know if a user has logged in recently, but this seems quite easy to tell by simply looking at the time stamp and/or contents of the mail spool for the user.
For example, on my system I can look at the timestamps on the 'new' folders in the user's maildir to see if they are getting mail, and if the folders are empty, I have a time stamp of when they last checked that mailbox, giving me a pretty accurate time for when they last logged in.
For example, looking at one user:
ls -lsdtr /path/to/user/maildir/{new,.**/new}
I can see that the most recent "new" mailboxes were accessed on 02 Mar 14:25 and 03 Mar 01:45, and I can see that the latter mailbox has files in it and the former mailbox does not (just by the size, without doing an extra ls of those directories), so I know that the last time the user logged in was about 14:25 or later yesterday and that they definitely have not logged in in the last 3h05, which seems close enough to me.
Am I missing some reason I would need/want to keep track of that specific login time separately?
What about mbox files ?
-- Yassine.
Le 3/3/21 à 1:33 PM, Yassine Chaouche a écrit :
What about mbox files ?
-- Yassine.
An alternative would be looking into the dovecot logs, but those are neceassrily time bound. So either keep very large amounts of logs, or store just the login info (much shorter) in the db.
-- Yassine.
On 03/03/2021 14:36 Yassine Chaouche <a.chaouche@algerian-radio.dz> wrote:
Le 3/3/21 à 1:33 PM, Yassine Chaouche a écrit :
What about mbox files ?
-- Yassine.
An alternative would be looking into the dovecot logs, but those are neceassrily time bound. So either keep very large amounts of logs, or store just the login info (much shorter) in the db.
-- Yassine.
These days you can also replace last-login with mail-lua script, which can do lot more than just try to set a dict. But last-login rather useful information when you are debugging, or removing dormant accounts. And other customer support incidents.
https://doc.dovecot.org/admin_manual/lua/#id2
Aki
On 03 Mar 2021, at 05:38, Aki Tuomi <aki.tuomi@open-xchange.com> wrote:
These days you can also replace last-login with mail-lua script, which can do lot more than just try to set a dict. But last-login rather useful information when you are debugging, or removing dormant accounts. And other customer support incidents.
Sure, being able to check a last login, approximately, is obviously useful. Bu clogging it for every login seems… well, it seems to be something people have been trouble getting to work properly and the information is right there without having to do anything.
Well, expect not use mbox. <shudder>
Seems that are simpler and less impactful and less troublesome ways to do this.
Anyway, it seems I am not missing anything, so I'll continue on with ls -lsdtr /path/to/user/maildir/{new,.**/new} if I need to look for defunct accounts.
-- I got fired from the zoo for braiding the snakes.
On 3/4/21 3:21 AM, @lbutlr wrote:
On 03 Mar 2021, at 05:38, Aki Tuomi <aki.tuomi@open-xchange.com> wrote:
These days you can also replace last-login with mail-lua script, which can do lot more than just try to set a dict. But last-login rather useful information when you are debugging, or removing dormant accounts. And other customer support incidents. Sure, being able to check a last login, approximately, is obviously useful. Bu clogging it for every login
I do use last-login and I do agree that incrementing the timestamp when the existing value isn't too old is not very useful.
I have several deployments where everything is stored in and consumed from MySQL, so deploying redis just for this seems too much. The database is replicated. We end up seeing most of the replication traffic (network and disk IO) coming from last-login. Using specifically binlog_format = ROW, I can mitigate this with a trigger saying 'IF NEW.lastlogin < (OLD.lastlogin + 900) THEN SET NEW.lastlogin = OLD.lastlogin' and I end up having an unchanged row, so nothing goes to the binlog. Especially with pop3 users (some people do still do that) this can be a huge reduction in traffic.
It would perhaps be a nice feature if the last-login plugin could first fetch from the dict and do this comparison on its own.
On 03 Mar 2021, at 05:33, Yassine Chaouche <a.chaouche@algerian-radio.dz> wrote:
Am I missing some reason I would need/want to keep track of that specific login time separately?
What about mbox files ?
Is anyone foolish enough to use mbox in 2021?
It's designed for dozens of kilobytes of mail. Perhaps hundreds of kilobytes/ It is a horrible horrible format for hundreds of megabyte of mail, it offers no advantages at all, and is fragile to corruption since it stores everything in a single file.
-- The days of our innocence and grace flow by The smiles we wear upon our face blow by
On Wed, Mar 3, 2021 at 11:16 AM @lbutlr <kremels@kreme.com> wrote:
On 03 Mar 2021, at 05:33, Yassine Chaouche <a.chaouche@algerian-radio.dz> wrote:
Am I missing some reason I would need/want to keep track of that specific login time separately?
What about mbox files ?
Is anyone foolish enough to use mbox in 2021?
It's designed for dozens of kilobytes of mail. Perhaps hundreds of kilobytes/ It is a horrible horrible format for hundreds of megabyte of mail, it offers no advantages at all, and is fragile to corruption since it stores everything in a single file.
Specific to the 'why use last login' question, with millions of mailboxes, walking the filesystem is more than a little onerous (having done it many times over the years, and never remembering where I put the script from 'last time') and takes a good chunk of a day to run. We were doing file-based last-login for a while (yeah, still needs a fs walk, but at least is dead simple and requires no stat()'ing), till locking became an issue (nfs). We moved to redis a couple of months ago, and now determining things like "who hasn't logged into anything in 30 days" becomes a 1 minute run of a python script using redis SCAN.
If you don't have a mountain of mailboxes and fs-walking isn't a problem, then there's def less need. Which means you don't have management repeatedly asking for 'active mailboxes' ;)
Le 3/3/21 à 8:16 PM, @lbutlr a écrit :
On 03 Mar 2021, at 05:33, Yassine Chaouche <a.chaouche@algerian-radio.dz> wrote:
Am I missing some reason I would need/want to keep track of that specific login time separately? What about mbox files ? Is anyone foolish enough to use mbox in 2021? You also have dovecot's variant dbox, both in its single and multi variants.It's also handy to download a single .mbox file from your gmail account and open it in your mail client. So mbox isn't really totally dead. Oh, and in addition to mail for system users which is also typically mbox.
-- Yassine.
On Wed, 2021-03-03 at 04:57 -0700, @lbutlr wrote:
I've noticed several threads over the last year or so about last- login, and I was curious WHY people care about tracking this in the database. I can see wanting to know if a user has logged in recently, but this seems quite easy to tell by simply looking at the time stamp and/or contents of the mail spool for the user.
<snip>
Am I missing some reason I would need/want to keep track of that specific login time separately?
I keep the last login details in a database for support/reporting tools. Support staff can get at this information without system access. I track the last login timestamp for imap, pop3, lmtp and sieve seperately. Again for support and reporting reasons. I also have local tools that interrogate the DB when I am doing my admin thing on the server.
Current dovecot last_login config ('X' used to replace private data)
connect = host=XXXXX dbname=XXXXX user=XXXXXX password=XXXXXX
Remote connections
map { pattern = shared/last-login/$service/$user/$rip table = mail_stats value_field = last_login value_type = uint fields { mailbox = $user remote_ip = $rip protocol = $service } }
Local conections, no remote IP
map { pattern = shared/last-login/$service/$user/ table = mail_stats value_field = last_login value_type = uint fields { mailbox = $user protocol = $service } }
E.g. inspecting a mailbox config ('X' used to replace private data)
Mailbox : xx003@XXXXXXX.co.za
Created : 2020-08-18 17:45:34
Description : XXXX XXXXXXXXX
Quota : 8G, used 6.3 GiB (78%)
SMTP Limit : 100 per hour
Addresses : xxxxxxxxx, xxxxxxx, xxxxxx
Can receive mail : YES
Can send mail : YES Can download mail : YES
Can filter mail : YES
Has IM account : NO
Catchall : NO
Last Modified : 2020-08-18 19:46:12
Last IMAP : 2021-03-04 10:52:03
Last POP3 : None
Last Delivery : 2021-03-04 10:26:49
Home : /srv/hosting/xxxxx/xxxxx/xxxxxx.co.za/mail/xx003
Sieve rules : roundcube ACTIVE
Default
-- Greg
Le 3/4/21 à 10:00 AM, Greg Wildman a écrit :
E.g. inspecting a mailbox config ('X' used to replace private data) Mailbox : xx003@XXXXXXX.co.za Created : 2020-08-18 17:45:34 Description : XXXX XXXXXXXXX Quota : 8G, used 6.3 GiB (78%) SMTP Limit : 100 per hour Addresses : xxxxxxxxx, xxxxxxx, xxxxxx Can receive mail : YES Can send mail : YES Can download mail : YES Can filter mail : YES Has IM account : NO Catchall : NO Last Modified : 2020-08-18 19:46:12 Last IMAP : 2021-03-04 10:52:03 Last POP3 : None Last Delivery : 2021-03-04 10:26:49 Home : /srv/hosting/xxxxx/xxxxx/xxxxxx.co.za/mail/xx003 Sieve rules : roundcube ACTIVE Default
This is pretty wild. Is that perl sorcery ?
-- Yassine
Sorry, the output didn't format properly. The takeaway fields are as follows:
Last Modified : 2020-08-18 19:46:12 Last IMAP : 2021-03-04 10:52:03 Last POP3 : None Last Delivery : 2021-03-04 10:26:49
-- Greg
participants (7)
-
＠lbutlr
-
Aki Tuomi
-
Gedalya
-
Greg Wildman
-
Greg Wildman
-
Mark Moseley
-
Yassine Chaouche