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

[Laa]

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

[Хостинг HostFood.ru]

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

[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 таблицу. Будьте внимательны!

[ADRE]

о как...
--
/me копипастнул без угрызения совести и материальной атветственности.

[zibadi]

класс! как раз это искал, но если можно то по подробней как это реализовать, в какую таблицу в базе будет писать не пойму

[Laa]

Ну подробней опиши что не понятно. Как у тебя заведены юзеры и на каком месте ты не понимаешь. Я расскажу.

[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 ;

Но суть не в этом, я не пойму из перлового скрипта куда он записывает данные в какую таблицу и какие поля, если можно выложи свою структуру, мне должно этого хватить для понятия.

[zibadi]

всё сам разобрался, вот только у меня база не локальная

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

 failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at ./log.pl line 10

как подключить к базе которая на другом хосте?

[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 |

[Laa]

всё сам разобрался, вот только у меня база не локальная

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

 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 укажи другой хост.

[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.

запустил скрипт на хосте с базой, скрипт работает

[Laa]

А вручную конектится с удаленного сервера к базе данных:

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

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

?

[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, если сервер не локальный?

[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

[zibadi]

Laa, у тебя imap логинов ip реальные в базу пишутся или 127.0.0.1? у меня локальный, с pop3 такой проблемы нет, наверно SquirrelMail в этом виноват, а может и нет, не пойму...

[ADRE]

Laa, у тебя imap логинов ip реальные в базу пишутся или 127.0.0.1? у меня локальный, с pop3 такой проблемы нет, наверно SquirrelMail в этом виноват, а может и нет, не пойму...

белка если стоит то конечно локлхост пишется, если удаленно с белки работать, т.е. на др серве, то будет писаться IP серва, но в письмо добавляется реальный айпи машины... + откуда отправлено так что IP будут прально считаться если через клиента сидеть.

[Laa]

+1 к ADRE.
Я вообще это сделал для того, чтобы понять какие ящики можно грохнуть. А-то уже не понятно какими ящиками не пользуются. А почта-то приходит и приходит... и при этом начальство против квот на ящики (все, блин, пользователи важные! )

[zibadi]

да точно белка, решил с помощью плагина к ней: squirrelmail-logger, пишет лог в файл или в базу, кто когда и чего...
в общем задача с логами решена