Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
HOWTO: Logging virtual maibox logins
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
NewBlackDak
Guru
Guru


Joined: 02 Nov 2003
Posts: 512
Location: Utah County, UT

PostPosted: Fri Mar 25, 2005 12:50 am    Post subject: HOWTO: Logging virtual maibox logins Reply with quote

To Be Done:
    Make my scripts that check the login time generic enough to post here for other users.

Purpose:
    Adds an accessed field to your user table that tracks the last time a user logged in.

Difficulty level:
    Fairly Simple. If you can get virtual mailhosting setup this will be a piece of cake.

Inspiration:
    We have too many virtual domains to keep track of orphaned accounts when noone deletes them, or lets us know to delete them.
    Courier leaves a lot to be desired in the way of logging, and there is no real way to check access time on an account if it's still receiving mail.

Requirements:
    Virtual mailhosting with postfix, mysql, and a courier-imap server.
    Following the Gentoo virtual mail setup works fine with this.
    Syslog-ng

Notes:
    If you used the Gentoo docs to setup your virtual mailhost you'll just need to make the following subsittutions:
      [$YOUR_USER_TABLE] = users
      [$YOUR_POSTFIX_DB] = mailsql
      [$YOUR_USER_FIELD] = email
      [$YOUR_POSTFIX_USER] = mailsql


Add the field to the the user table, so we'll actually have a place to log to
Code:

#mysql -u [$YOUR_POSTFIX_USER]  -p < ALTER TABLE [$YOUR_USER_TABLE] ADD COLUMN accessed datetime not null default "0000-00-00 00:00:00"


Create the file /bin/syslog_mysql with the following command:
Code:

#echo '# syslog_mysql
#!/bin/bash

if [ -e /var/log/mysql.pipe ]; then
   while [ -e /var/log/mysql.pipe ]
   do
      mysql -u [$YOUR_POSTFIX_USER] --password=[$YOUR_POSTFIX_PASSWORD] [$YOUR_POSTFIX_DB] < /var/log/mysql.pipe
   done
else
   mkfifo /var/log/mysql.pipe
fi'>/bin/syslog_mysql

Make /bin/syslog_mysql executable, and not readable. You don't want your password out in the oen
Code:

#chmod a+x /bin/syslog_mysql
#chmod a-r /bin/syslog_mysql



Make the following changes to /etc/syslog-ng.conf
Code:

destination d_access { pipe("/var/log/mysql.pipe" template("UPDATE [$YOUR_USER_TABLE] set accessed=NOW() where '$MSG' RLIKE [$YOUR_USER_FIELD];\n") template-escape(yes)); };

filter f_mail { facility(mail); };

filter m_login { match("LOGIN"); };

log { source(src); filter(f_mail); filter(m_login); destination(d_access); };


Make the following changes to crontab
Code:

@reboot      /bin/syslog_mysql >> /var/log/mysql/mysql.log 2>&1


At this point you can either reboot your machine

or
Code:

#nohup /bin/syslog_mysql &
#/etc/init.d/syslog-ng restart


Delving further:
    My setup is a little different, because I'm using postfixadmin, and used their setup.
    I've edited postfixadmin, so it'll give the last accessed time in the mail listing.



I'm in a little bit of a hurry, so their might be spelling mistakes that I have to come back to correct.

ENJOY
_________________
Gentoo systems.
X2 4200+@2.6 - Athy
X2 3600+ - Myth
UltraSparc5 440 - sparcy


Last edited by NewBlackDak on Tue Mar 29, 2005 7:58 pm; edited 1 time in total
Back to top
View user's profile Send private message
NewBlackDak
Guru
Guru


Joined: 02 Nov 2003
Posts: 512
Location: Utah County, UT

PostPosted: Sun Mar 27, 2005 6:37 pm    Post subject: Reply with quote

Wow, I'm surprised noone used/tried this. There's no way I can be the only one running a mail server that this is useful on.
_________________
Gentoo systems.
X2 4200+@2.6 - Athy
X2 3600+ - Myth
UltraSparc5 440 - sparcy
Back to top
View user's profile Send private message
onegative
Tux's lil' helper
Tux's lil' helper


Joined: 12 Feb 2004
Posts: 136
Location: Québec, Canada

PostPosted: Tue Jan 24, 2006 10:12 pm    Post subject: Reply with quote

Hey NewBlackDak,

Thanks for the guide, I will give it a try as soon as I'll have some spare time.

I'm bookmarking it. :)

o-neg
_________________
My favorite thing about the Internet is that you get to go into the private world of real creeps without having to smell them.

My blog :twowordz.blogspot.com
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum