Страница 1 из 1

Статистику в dovecot?

Добавлено: 2010-08-27 12:35:37
Laa
Привет!
У меня пользователи сидят в базе данных mysql. Все хорошо, всем доволен. Но вот нужно знать когда с такого-то ящика последний раз забирали почту и с какого IP адреса. Как бы эту инфу средствами dovecot вносить в базу???

Re: Статистику в dovecot?

Добавлено: 2010-08-27 13:54:04
Laa
Сам нашел. Читаю http://wiki.dovecot.org/PostLoginScripting

Теперь у меня в базе юзеров есть инфа кто и скакого IP последний раз забирал почту.
Скрипт который это выполняет вот:

Код: Выделить всё

#!/usr/local/bin/perl
use strict;
use DBI;

my $mysql_user  = "*******";
my $mysql_host  = "localhost";
my $mysql_db    = "*******";
my $mysql_pass  = "********";

my $mysql_conn = DBI->connect("DBI:mysql:database=$mysql_db;
                                host=$mysql_host",
                                $mysql_user,
                                $mysql_pass);

my $_login= $ENV{USER};
my $_last_ip= $ENV{IP};
my $mysql_cmd =qq|UPDATE accounts SET last_login=CURRENT_TIMESTAMP, last_ip='$_last_ip' WHERE login='$_login'|;
my $mysql_q=$mysql_conn->prepare( $mysql_cmd );
$mysql_q->execute();
$mysql_q->finish();

if ( !$ENV{IMAP_IDLE_NOTIFY_INTERVAL} ) {
        exec '/usr/local/libexec/dovecot/pop3 "$@"';
} else {
        exec '/usr/local/libexec/dovecot/imap "$@"';
}
В dovecot.conf :

Код: Выделить всё

mail_executable= /usr/local/etc/.../script-name.perl
Права на скрипт -- недоступен группе и остальным вообще.

p.s. security hole:: если у юзера в логине будет знак одинарная кавычка -- то может быть ошибка в mysql insert, или даже внедрение чего-то левого в sql таблицу. Будьте внимательны!

Re: Статистику в dovecot?

Добавлено: 2010-08-28 17:32:05
ADRE
о как...
--
/me копипастнул без угрызения совести и материальной атветственности.

Re: Статистику в dovecot?

Добавлено: 2010-09-01 22:09:54
zibadi
класс! :good: как раз это искал, но если можно то по подробней как это реализовать, в какую таблицу в базе будет писать не пойму

Re: Статистику в dovecot?

Добавлено: 2010-09-02 8:29:38
Laa
Ну подробней опиши что не понятно. Как у тебя заведены юзеры и на каком месте ты не понимаешь. Я расскажу.

Re: Статистику в dovecot?

Добавлено: 2010-09-02 16:54:10
zibadi
Спасибо что быстро откликнулся

Код: Выделить всё

-- Структура таблицы `users`
--
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `localpart` varchar(192) NOT NULL DEFAULT '',
  `username` varchar(255) NOT NULL DEFAULT '',
  `clear` varchar(255) DEFAULT NULL,
  `crypt` varchar(48) DEFAULT NULL,
  `uid` smallint(5) unsigned NOT NULL DEFAULT '1002',
  `gid` smallint(5) unsigned NOT NULL DEFAULT '6',
  `smtp` text,
  `pop` varchar(255) DEFAULT NULL,
  `type` enum('local','alias','catch','fail','piped','admin','site') NOT NULL DEFAULT 'local',
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `on_avscan` tinyint(1) NOT NULL DEFAULT '0',
  `on_blocklist` tinyint(1) NOT NULL DEFAULT '0',
  `on_complexpass` tinyint(1) NOT NULL DEFAULT '0',
  `on_forward` tinyint(1) NOT NULL DEFAULT '0',
  `on_piped` tinyint(1) NOT NULL DEFAULT '0',
  `on_spamassassin` tinyint(1) NOT NULL DEFAULT '0',
  `on_vacation` tinyint(1) NOT NULL DEFAULT '0',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `flags` varchar(16) DEFAULT NULL,
  `forward` varchar(255) DEFAULT NULL,
  `unseen` tinyint(1) DEFAULT '0',
  `maxmsgsize` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `quota` int(10) unsigned NOT NULL DEFAULT '0',
  `realname` varchar(255) DEFAULT NULL,
  `sa_tag` smallint(5) unsigned NOT NULL DEFAULT '0',
  `sa_refuse` smallint(5) unsigned NOT NULL DEFAULT '0',
  `tagline` varchar(255) DEFAULT NULL,
  `vacation` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`localpart`,`domain_id`),
  KEY `local` (`localpart`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;
Но суть не в этом, я не пойму из перлового скрипта куда он записывает данные в какую таблицу и какие поля, если можно выложи свою структуру, мне должно этого хватить для понятия.

Re: Статистику в dovecot?

Добавлено: 2010-09-03 13:01:41
zibadi
всё сам разобрался, вот только у меня база не локальная

Код: Выделить всё

 failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at ./log.pl line 10
как подключить к базе которая на другом хосте?

Re: Статистику в dovecot?

Добавлено: 2010-09-03 13:08:32
Laa
Вот какие примерные минимальные настройки таблицы должны быть:

Код: Выделить всё

CREATE TABLE `accounts` (
  `login` varchar(255) NOT NULL DEFAULT '',
  `crypt` varchar(255) NOT NULL DEFAULT '',
  `uid` smallint(4) unsigned NOT NULL DEFAULT '9000',
  `gid` smallint(4) unsigned NOT NULL DEFAULT '9000',
  `name` varchar(128) NOT NULL DEFAULT '',
  `homedir` varchar(128) NOT NULL,
  `maildir` varchar(128) NOT NULL DEFAULT '',
  `quota` smallint(4) unsigned NOT NULL DEFAULT '30',
  `date_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_ip` varchar(15) DEFAULT NULL,
  UNIQUE KEY `login` (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Re: Статистику в dovecot?

Добавлено: 2010-09-03 13:09:44
Laa
zibadi писал(а):всё сам разобрался, вот только у меня база не локальная

Код: Выделить всё

 failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at ./log.pl line 10
как подключить к базе которая на другом хосте?
В моем скрипте смотри на настройки:

Код: Выделить всё

my $mysql_user  = "*******";
my $mysql_host  = "localhost";
my $mysql_db    = "*******";
my $mysql_pass  = "********";
вместо localhost укажи другой хост. ;)

Re: Статистику в dovecot?

Добавлено: 2010-09-03 14:34:15
zibadi
в том и дело, что я и так localhost заменил на хост где база лежит(10.10.10.1), надо было вот что показать что бы было понятно:

Код: Выделить всё

DBI connect('database=exim4;
                                host=10.10.10.1','exim',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at ./log.pl line 10
Can't call method "prepare" on an undefined value at ./log.pl line 18.
запустил скрипт на хосте с базой, скрипт работает

Re: Статистику в dovecot?

Добавлено: 2010-09-03 14:41:29
Laa
А вручную конектится с удаленного сервера к базе данных:

Код: Выделить всё

mysql -u USER -pPASS -h10.10.10.1 ....
?

Re: Статистику в dovecot?

Добавлено: 2010-09-03 16:51:18
zibadi
конечно можно подключится, exim4 это база exim, через которую происходит логин пользователейт, ip машины,где стоит exim 10.10.10.10, база лежит на 10.10.10.1, всё прекрасно работает и dovecot в том числе. меня смущяет вот это:

Код: Выделить всё

failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
причём тут Can't connect to local MySQL, если сервер не локальный?

Re: Статистику в dovecot?

Добавлено: 2010-09-03 21:40:55
zibadi
в общем решил вопрос по своему.
создаём таблицу:

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `log` (
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ip` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `action` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Log';
скрипт для логов подключений dovecot, задача у меня немного другая, хочу логировать все подключения pop3 и imap
dlog.py:

Код: Выделить всё

#!/usr/bin/python
# -*- coding: utf-8 -*-
import sys, os
is_pop3 = not os.environ.get('IMAP_IDLE_NOTIFY_INTERVAL', '')
import MySQLdb as db
mysql_user  = "юсер";
mysql_host  = "адрес хоста";
mysql_db    = "база";
mysql_pass  = "пароль";
con = db.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
cur = con.cursor()
_login = os.environ.get('USER', '')
_last_ip = os.environ.get('IP', '')
action = 'pop3' if is_pop3 else 'imap'
sql = "insert into log (timestamp, ip, username, action)values(CURRENT_TIMESTAMP,'%(_last_ip)s','%(_login)s', '%(action)s');")s');" % vars()

cur.execute(sql)
con.commit()
cur.close()
con.close()
from subprocess import *
if is_pop3:
    sys.argv[0] = '/usr/lib/dovecot/pop3'
else:
    sys.argv[0] = '/usr/lib/dovecot/imap'
r = Popen(sys.argv).pid
пути до pop3 и imap пишите свои , ну и как было описано ранее в dovecot.conf:
mail_executable=/путьдо скрипта/dlog.py

Re: Статистику в dovecot?

Добавлено: 2010-09-04 1:03:12
zibadi
Laa, у тебя imap логинов ip реальные в базу пишутся или 127.0.0.1? у меня локальный, с pop3 такой проблемы нет, наверно SquirrelMail в этом виноват, а может и нет, не пойму...

Re: Статистику в dovecot?

Добавлено: 2010-09-06 11:03:59
ADRE
zibadi писал(а):Laa, у тебя imap логинов ip реальные в базу пишутся или 127.0.0.1? у меня локальный, с pop3 такой проблемы нет, наверно SquirrelMail в этом виноват, а может и нет, не пойму...
белка если стоит то конечно локлхост пишется, если удаленно с белки работать, т.е. на др серве, то будет писаться IP серва, но в письмо добавляется реальный айпи машины... + откуда отправлено :crazy: так что IP будут прально считаться если через клиента сидеть.

Re: Статистику в dovecot?

Добавлено: 2010-09-06 11:27:46
Laa
+1 к ADRE.
Я вообще это сделал для того, чтобы понять какие ящики можно грохнуть. А-то уже не понятно какими ящиками не пользуются. А почта-то приходит и приходит... :shock: и при этом начальство против квот на ящики (все, блин, пользователи важные! :"": )

Re: Статистику в dovecot?

Добавлено: 2010-09-06 15:58:17
zibadi
да точно белка, решил с помощью плагина к ней: squirrelmail-logger, пишет лог в файл или в базу, кто когда и чего...
в общем задача с логами решена :Yahoo!: