ULOG + PostgreSQL

Устанавливаем CentOS 5.4
Проверяем наличие необходимых нам пакетов

[root@localhost /]# rpm -qa libdbi-dbd-pgsql perl-DBD-Pg postgresql* rhdb-utils unixODBC
libdbi-dbd-pgsql-0.8.1a-1.2.2
unixODBC-2.2.11-7.1
postgresql-docs-8.1.18-2.el5_4.1
postgresql-python-8.1.18-2.el5_4.1
postgresql-jdbc-8.1.407-1jpp.4
postgresql-libs-8.1.18-2.el5_4.1
postgresql-server-8.1.18-2.el5_4.1
postgresql-pl-8.1.18-2.el5_4.1
postgresql-tcl-8.1.18-2.el5_4.1
rhdb-utils-8.1.1-1.2.2.el5.centos
perl-DBD-Pg-1.49-2.el5_3.1
postgresql-8.1.18-2.el5_4.1
postgresql-test-8.1.18-2.el5_4.1
postgresql-contrib-8.1.18-2.el5_4.1

Устанавливаем ULOG

[root@localhost /]# yum install ulogd-1.24-2.el5.re.rpm ulogd-pgsql-1.24-1.el5.re.rpm

Делаем автоматическую загрузку служб при старте системы

[root@localhost /]# chkconfig –level 2345 postgresql on
[root@localhost /]# chkconfig –level 2345 iptables on
[root@localhost /]# chkconfig –level 2345 ulogd on

Запускаем PostgreSQL

[root@localhost /]# service postgresql start

Подключаемся к базе данных

[root@localhost /]# sudo su postgres -c psql postgres postgres

Welcome to psql 8.1.18, the PostgreSQL interactive terminal.


Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit


postgres=#

Создаём пользователя для базы данных, например (логин «uloger» и пароль«1qaz2wsx»)

postgres=# CREATE USER uloger WITH password ‘1qaz2wsx’;
CREATE ROLE
Создаём базу данных «ulogd», хозяином которой будет наш созданный пользователь «uloger»

postgres=# CREATE DATABASE ulogd WITH OWNER uloger;
CREATE DATABASE

Проверяем наличие базы данных

postgres=# \l
Список баз данных
Имя | Владелец | Кодировка
-----------+----------+-----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
ulogd | uloger | UTF8
(записей: 4)

Выходим в шел

postgres=#\q

Изменяем файл /var/lib/pgsql/data/pg_hba.conf :

# TYPE DATABASE USER CIDR-ADDRESS METHOD


# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 ident sameuser

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

[root@localhost]# psql -U uloger ulogd
Password for user uloger:
Welcome to psql 8.1.18, the PostgreSQL interactive terminal.


Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit


ulogd=>

Выходим

ulogd=>\q

Создаём и заполняем таблицы в нашей базе, используя шаблон установленный из rpm-пакета
Предварительно закоментировав не нужные нам поля

[root@localhost]# cat /usr/share/doc/ulogd-1.24/pgsql.table
/* ulogd.pgsql.table, Version 0.1
*
* sample of a postgres table for ulogd
*
* All columns except "id" are optional! Comment all unwanted
* columns out, e.g. by prefixing them with '--'
*
* "raw_pkt" is not supported by ulogd_PGSQL
*/


CREATE SEQUENCE "seq_ulog";


CREATE TABLE "ulog" (
"id" integer DEFAULT nextval('seq_ulog') NOT NULL,


"oob_prefix" character varying(32),
"oob_time_sec" integer,
"oob_time_usec" integer,
"oob_mark" bigint,
"oob_in" character varying(32),
"oob_out" character varying(32),


"raw_mac" character varying(80),
"raw_pktlen" bigint,


"ip_ihl" smallint,
"ip_tos" smallint,
"ip_totlen" integer,
"ip_id" integer,
"ip_fragoff" integer,
"ip_ttl" smallint,
"ip_protocol" smallint,
"ip_csum" integer,


/* log IPs as unsigned int32 (default) */
-- "ip_saddr" bigint,
-- "ip_daddr" bigint,


/* log IPs as string (--with-pgsql-log-ip-as-string) */
"ip_saddr" character varying(40),
"ip_daddr" character varying(40),


/* log IPs as inet (--with-pgsql-log-ip-as-string) */
-- "ip_saddr" inet,
-- "ip_daddr" inet,




"tcp_sport" integer,
"tcp_dport" integer,
"tcp_seq" bigint,
"tcp_ackseq" bigint,
"tcp_urg" boolean,
"tcp_ack" boolean,
"tcp_psh" boolean,
"tcp_rst" boolean,
"tcp_syn" boolean,
"tcp_fin" boolean,
"tcp_window" integer,
"tcp_urgp" integer,


"udp_sport" integer,
"udp_dport" integer,
"udp_len" integer,


"icmp_type" smallint,
"icmp_code" smallint,
"icmp_echoid" integer,
"icmp_echoseq" integer,
"icmp_gateway" bigint,
"icmp_fragmtu" integer,


"pwsniff_user" character varying(30),
"pwsniff_pass" character varying(30),


"ahesp_spi" smallint,


"local_time" bigint,
"local_hostname" character varying(40)
);


[root@localhost]# psql -U uloger ulogd < /usr/share/doc/ulogd-1.24/pgsql.table

Подключаемся к нашей базе и проверяем наличие таблицы и её содержимого

[root@localhost]# psql -U uloger ulogd
Password for user uloger:
Welcome to psql 8.1.18, the PostgreSQL interactive terminal.


Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

ulogd=> select * from ulog;
id | oob_prefix | oob_time_sec | oob_time_usec | oob_mark | oob_in | oob_out | raw_mac | raw_pktlen | ip_ihl | ip_tos | ip_totlen | ip_id | ip_frago
ff | ip_ttl | ip_protocol | ip_csum | ip_saddr | ip_daddr | tcp_sport | tcp_dport | tcp_seq | tcp_ackseq | tcp_urg | tcp_ack | tcp_psh | tcp_rst | tcp_
syn | tcp_fin | tcp_window | tcp_urgp | udp_sport | udp_dport | udp_len | icmp_type | icmp_code | icmp_echoid | icmp_echoseq | icmp_gateway | icmp_fragmtu | p
wsniff_user | pwsniff_pass | ahesp_spi | local_time | local_hostname
-------+------------------+--------------+---------------+----------+--------+---------+---------+------------+--------+--------+-----------+-------+---------
---+--------+-------------+---------+------------+------------+-----------+-----------+------------+------------+---------+---------+---------+---------+-----
----+---------+------------+----------+-----------+-----------+---------+-----------+-----------+-------------+--------------+--------------+--------------+--
------------+--------------+-----------+------------+----------------

Выходим

ulogd=> \q

Настраиваем ULOG

Подцепляем в настройках его к PostgreSQL и вводим значения для подключения

[root@localhost]# cat /etc/ulogd.conf
# Example configuration for ulogd
# $Id: ulogd.conf.in 5267 2005-02-19 21:33:43Z laforge $
#


[global]
######################################################################
# GLOBAL OPTIONS
######################################################################


# netlink multicast group (the same as the iptables --ulog-nlgroup param)
nlgroup=1


# logfile for status messages
logfile="/var/log/ulogd/ulogd.log"


# loglevel: debug(1), info(3), notice(5), error(7) or fatal(8)
loglevel=5


# socket receive buffer size (should be at least the size of the
# in-kernel buffer (ipt_ULOG.o 'nlbufsiz' parameter)
rmem=131071


# libipulog/ulogd receive buffer size, should be > rmem
bufsize=150000


######################################################################
# PLUGIN OPTIONS
######################################################################


# We have to configure and load all the plugins we want to use


# general rules:
# 1. load the plugins _first_ from the global section
# 2. options for each plugin in seperate section below




#
# ulogd_BASE.so - interpreter plugin for basic IPv4 header fields
# you will always need this
plugin="/usr/lib/ulogd/ulogd_BASE.so"




# output plugins.
#plugin="/usr/lib/ulogd/ulogd_LOGEMU.so"
#plugin="/usr/lib/ulogd/ulogd_OPRINT.so"
#plugin="/usr/lib/ulogd/ulogd_MYSQL.so"
plugin="/usr/lib/ulogd/ulogd_PGSQL.so"
#plugin="/usr/lib/ulogd/ulogd_SQLITE3.so"
#plugin="/usr/lib/ulogd/ulogd_PCAP.so"




[LOGEMU]
file="/var/log/ulogd/ulogd.syslogemu"
sync=1


[OPRINT]
file="/var/log/ulogd/ulogd.pktlog"


[MYSQL]
table="ulog"
pass="changeme"
user="laforge"
db="ulogd"
host="localhost"


[PGSQL]
table="ulog"
schema="public"
pass="1qaz2wsx"
user="uloger"
db="ulogd"
host="localhost"


[SQLITE3]
table="ulog"
db="/path/to/sqlite/db"
buffer=200


[PCAP]
file="/var/log/ulogd/ulogd.pcap"
sync=1

Добавляем строчки в IPTABLES, чтобы использовалось логирование через ULOG

[root@localhost]# iptables -I INPUT -i eth0 -j ULOG --ulog-prefix "INPUT:in_eth0" --ulog-cprange 48 --ulog-qthreshold 50
[root@localhost]# iptables -I OUTPUT -o eth0 -j ULOG --ulog-prefix "OUTPUT:out_eth0" --ulog-cprange 48 --ulog-qthreshold 50
[root@localhost]# iptables -I FORWARD -i eth0 -o eth1 -j ULOG --ulog-prefix "FORWARD:in_eth0_out_eth1" --ulog-cprange 48 --ulog-qthreshold 50
[root@localhost]# iptables -I FORWARD -i eth1 -o eth0 -j ULOG --ulog-prefix "FORWARD:in_eth1_out_eth0" --ulog-cprange 48 --ulog-qthreshold 50

Проверяем не закрыт ли порт для подключения к базе данных PostgreSQL (порт 5432)
Стартуем ULOG

[root@localhost]# service ulogd start
Starting ulogd: [ OK ]

Проверяем правильно ли мы всё настроили

[root@localhost]# service ulogd status
ulogd (pid 10367 6187) выполняется...

Если нет, то смотрим логи

[root@localhost]# cat /var/log/ulogd/ulogd.log
Mon Apr 26 19:53:50 2010 <7> ulogd_PGSQL.c:430 can't establish database connection

(Примечание: если мы видим такую надпись, значит модуль ULOG не может подключиться к базе данных PostgreSQL)

Tue Apr 27 16:29:48 2010 <5> ulogd.c:594 sigterm received, exiting

(Примечание: если наблюдаем такую запись, то ULOG функционирует нормально)

Если всё хорошо, то можно посмотреть, данные которые были внесены в таблицы
Подключаемся к базе даных

[root@localhost]# psql -U uloger ulogd
Password for user uloger:
Welcome to psql 8.1.18, the PostgreSQL interactive terminal.


Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

ulogd=> select * from ulog;
id | oob_prefix | oob_time_sec | oob_time_usec | oob_mark | oob_in | oob_out | raw_mac | raw_pktlen | ip_ihl | ip_tos | ip_totlen | ip_id | ip_frago
ff | ip_ttl | ip_protocol | ip_csum | ip_saddr | ip_daddr | tcp_sport | tcp_dport | tcp_seq | tcp_ackseq | tcp_urg | tcp_ack | tcp_psh | tcp_rst | tcp_
syn | tcp_fin | tcp_window | tcp_urgp | udp_sport | udp_dport | udp_len | icmp_type | icmp_code | icmp_echoid | icmp_echoseq | icmp_gateway | icmp_fragmtu | p
wsniff_user | pwsniff_pass | ahesp_spi | local_time | local_hostname
-------+------------------+--------------+---------------+----------+--------+---------+---------+------------+--------+--------+-----------+-------+---------
---+--------+-------------+---------+------------+------------+-----------+-----------+------------+------------+---------+---------+---------+---------+-----
----+---------+------------+----------+-----------+-----------+---------+-----------+-----------+-------------+--------------+--------------+--------------+--
------------+--------------+-----------+------------+----------------
1 | INPUT:in_eth0 | 1272298159 | 35200 | 0 | eth0 | | 00 | 48 | 5 | 0 | 92 | 6024 | 163
84 | 127 | 6 | 14221 | 175038558 | 3232235794 | 54425 | 22 | 1265801864 | 3651104018 | f | t | t | f | f
| f | 16148 | | | | | | | | | | |
| | | |
2 | OUTPUT:from_eth0 | 1272298159 | 35924 | 0 | | eth0 | | 48 | 5 | 16 | 92 | 32309 | 163
84 | 64 | 6 | 4048 | 3232235794 | 175038558 | 22 | 54425 | 3651104018 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
3 | OUTPUT:from_eth0 | 1272298159 | 53668 | 0 | | eth0 | | 48 | 5 | 16 | 108 | 32310 | 163
84 | 64 | 6 | 4031 | 3232235794 | 175038558 | 22 | 54425 | 3651104070 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
4 | INPUT:in_eth0 | 1272298159 | 54314 | 0 | eth0 | | 00 | 40 | 5 | 0 | 40 | 6025 | 163
84 | 127 | 6 | 14272 | 175038558 | 3232235794 | 54425 | 22 | 1265801916 | 3651104138 | f | t | f | f | f
| f | 16118 | | | | | | | | | | |
| | | |
5 | OUTPUT:from_eth0 | 1272298159 | 74762 | 0 | | eth0 | | 48 | 5 | 16 | 92 | 32311 | 163
84 | 64 | 6 | 4046 | 3232235794 | 175038558 | 22 | 54425 | 3651104138 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
6 | OUTPUT:from_eth0 | 1272298159 | 74949 | 0 | | eth0 | | 48 | 5 | 16 | 92 | 32312 | 163
84 | 64 | 6 | 4045 | 3232235794 | 175038558 | 22 | 54425 | 3651104190 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
7 | OUTPUT:from_eth0 | 1272298159 | 75032 | 0 | | eth0 | | 48 | 5 | 16 | 92 | 32313 | 163
84 | 64 | 6 | 4044 | 3232235794 | 175038558 | 22 | 54425 | 3651104242 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
8 | INPUT:in_eth0 | 1272298159 | 75359 | 0 | eth0 | | 00 | 40 | 5 | 0 | 40 | 6026 | 163
84 | 127 | 6 | 14271 | 175038558 | 3232235794 | 54425 | 22 | 1265801916 | 3651104242 | f | t | f | f | f
| f | 16092 | | | | | | | | | | |
| | | |
9 | OUTPUT:from_eth0 | 1272298159 | 75374 | 0 | | eth0 | | 48 | 5 | 16 | 248 | 32314 | 163
84 | 64 | 6 | 3887 | 3232235794 | 175038558 | 22 | 54425 | 3651104294 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
10 | INPUT:in_eth0 | 1272298159 | 76337 | 0 | eth0 | | 00 | 40 | 5 | 0 | 40 | 6027 | 163
84 | 127 | 6 | 14270 | 175038558 | 3232235794 | 54425 | 22 | 1265801916 | 3651104502 | f | t | f | f | f
| f | 16425 | | | | | | | | | | |
| | | |
11 | OUTPUT:from_eth0 | 1272298159 | 76648 | 0 | | eth0 | | 48 | 5 | 16 | 108 | 32315 | 163
84 | 64 | 6 | 4026 | 3232235794 | 175038558 | 22 | 54425 | 3651104502 | 1265801916 | f | t | t | f | f
| f | 71 | | | | | | | | | | |
| | | |
12 | INPUT:in_eth0 | 1272298159 | 96524 | 0 | eth0 | | 00 | 48 | 5 | 0 | 218 | 0 | 163
84 | 64 | 17 | 46526 | 3232235781 | 3232236031 | | | | | | | | |
| | | | 631 | 631 | 198 | | | | | | |
| | | |
13 | INPUT:in_eth0 | 1272298159 | 275443 | 0 | eth0 | | 00 | 40 | 5 | 0 | 40 | 6028 | 163
84 | 127 | 6 | 14269 | 175038558 | 3232235794 | 54425 | 22 | 1265801916 | 3651104570 | f | t | f | f | f
| f | 16408 | | | | | | | | | | |
| | | |
14 | INPUT:in_eth0 | 1272298160 | 96339 | 0 | eth0 | | 00 | 48 | 5 | 0 | 233 | 0 | 163

ОТЛИЧНО !!!
Выходим...

ulogd=> \q
[root@localhost]#

Комментариев нет:

Отправить комментарий