Squeezeにsnort-pgsqlの導入

■debian Squeezeのsnortからsnort-postgresに移行する。
　mysqlが良いという人は以下をどうぞ。

　snort-mysqlの導入、DNSバージョンチェック、シグニチャの確認
　http://d.hatena.ne.jp/labunix/20120809

■postgresqlのインストール。

$ sudo apt-get install -y postgresql-client postgresql
$ sudo /etc/init.d/postgresql status > /dev/null 2>&1 && echo "ok"
ok

■ユーザ、DBの作成

$ su 
# test `id -u` -eq 0 && echo "ok"
ok
# sudo -u postgres createuser snort
新しいロールをスーパーユーザとしますか?  (y/n)y
# sudo -u snort createdb snort

■snort-postgresqlを導入

# apt-cache search ^snort-pgsql\$
snort-pgsql - flexible Network Intrusion Detection System [PostgreSQL]

# apt-get install -y snort-pgsql
...
/etc/snort/db-pending-config file found
Snort will not start as its database is not yet configured.
Please configure the database as described in
/usr/share/doc/snort-pgsql/README-database.Debian
and then remove /etc/snort/db-pending-config

■基本的にmysqlと同じ。

# grep zcat /usr/share/doc/snort-pgsql/README-database.Debian
   $  zcat create_postgresql.gz | psql -U <user> -h <host> -W <databasename>
   $ zcat create_mysql.gz | mysql -u <user> -h <host> -p <databasename>
  $ zcat create_mysql.gz | mysql -u snort -D snort -psnort-db
  $ zcat create_postgresql.gz | psql snort

# cd /usr/share/doc/snort-pgsql/
# zcat create_postgresql.gz | sudo -u snort psql

# grep 5432 /etc/services
postgresql      5432/tcp        postgres        # PostgreSQL Database
postgresql      5432/udp        postgres
# netstat -an | grep "\:5432"
tcp        0      0 127.0.0.1:5432          0.0.0.0:*    LISTEN

■簡易チェック
　必要なものが揃っていること。
　テーブルは初期ではほぼ空。
　見た目はともかくw。。。

# sudo -u snort psql -d snort
snort=# \d
   リレーションの一覧
 スキーマ |     名前     |     型     | 所有者
----------+------------------------------------+------------+--------
 public   | data         | テーブル   | snort
 public   | detail       | テーブル   | snort
 public   | encoding     | テーブル   | snort
 public   | event        | テーブル   | snort
 public   | icmphdr      | テーブル   | snort
 public   | iphdr        | テーブル   | snort
 public   | opt          | テーブル   | snort
 public   | reference    | テーブル   | snort
 public   | reference_ref_id_seq    | シーケンス | snort
 public   | reference_system        | テーブル   | snort
 public   | reference_system_ref_system_id_seq | シーケンス | snort
 public   | schema       | テーブル   | snort
 public   | sensor       | テーブル   | snort
 public   | sensor_sid_seq          | シーケンス | snort
 public   | sig_class    | テーブル   | snort
 public   | sig_class_sig_class_id_seq         | シーケンス | snort
 public   | sig_reference| テーブル   | snort
 public   | signature    | テーブル   | snort
 public   | signature_sig_id_seq    | シーケンス | snort
 public   | tcphdr       | テーブル   | snort
 public   | udphdr       | テーブル   | snort
(21 行)
snort=# \! echo $PGPORT
5432

# echo "
data
detail
encoding
event
icmphdr
iphdr
opt
reference
reference_ref_id_seq
reference_system
reference_system_ref_system_id_seq
schema
sensor
sensor_sid_seq
sig_class
sig_class_sig_class_id_seq
sig_reference
signature
signature_sig_id_seq
tcphdr
udphdr
" | for list in `xargs`;do echo 'select count(*) from '${list}';';done | \
    sudo -u snort psql -d snort -A -F\, | column
count   3       (1 行)  count   0       (1 行)  count   0       (1 行)
0       (1 行)  count   0       (1 行)  count   0       (1 行)  count
(1 行)  count   0       (1 行)  count   0       (1 行)  count   0
count   0       (1 行)  count   1       (1 行)  count   0       (1 行)
2       (1 行)  count   1       (1 行)  count   1       (1 行)  count
(1 行)  count   0       (1 行)  count   1       (1 行)  count   0
count   0       (1 行)  count   1       (1 行)  count   1       (1 行)

# echo 'select version();' | sudo -u snort psql -d snort -A -F\, | column
version
PostgreSQL 8.4.12 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
(1 行)

■「localhost」を設定する。

# grep "localhost\|port" /etc/postgresql/8.4/main/postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # defaults to 'localhost', '*' = all
port = 5432                             # (change requires restart)
                                        # supported by the operating system:
                                        #   %r = remote host and port

# grep ^listen_ /etc/postgresql/8.4/main/postgresql.conf
listen_addresses = 'localhost'          # what IP address(es) to listen on;

■snortのパスワードを設定、デーモンの再起動、ログインチェック

# echo "ALTER USER snort with encrypted password 'XXXXX';" | sudo -u snort psql -U snort -d snort
ALTER ROLE
# /etc/init.d/postgresql restart

# sudo -u snort psql -U snort -d snort -h localhost
ユーザ snort のパスワード:
psql (8.4.12)
SSL 接続 (暗号化方式: DHE-RSA-AES256-SHA, ビット長: 256)
"help" でヘルプを表示します.

snort=# \q

■必要なものは揃ったので、snort-pgsqlを設定。

# dpkg-reconfigure snort-pgsql
# rm /etc/snort/db-pending-config
# /etc/init.d/snort restart
Starting Network Intrusion Detection System : snort (eth0 using /etc/snort/snort.eth0.conf ...done).

■「snort.conf」の設定変更
　これもmysqlのときと同じです。

$ sudo cp -pi /etc/snort/snort.conf /etc/snort/snort.conf.org
$ sudo diff /etc/snort/snort.conf /etc/snort/snort.conf.org
# sudo diff /etc/snort/snort.conf /etc/snort/snort.conf.org
50c50
< var HOME_NET [192.168.164.0/24]
---
> var HOME_NET any
53c53
< var EXTERNAL_NET !$HOME_NET
---
> var EXTERNAL_NET any
681c681
< output alert_syslog: LOG_AUTH LOG_ALERT
---
> # output alert_syslog: LOG_AUTH LOG_ALERT
700c700
< output database: alert, postgresql, user=snort password=XXXXX dbname=snort host=localhost
---
> # output database: alert, postgresql, user=snort dbname=snort

■動作確認。

# echo ' select * from event;' | sudo -u snort psql -U snort -d snort -h localhost
ユーザ snort のパスワード:
 sid | cid | signature |         timestamp
-----+-----+-----------+----------------------------
   1 |   1 |         1 | 2012-08-15 23:26:46.878+09
   1 |   2 |         2 | 2012-08-15 23:26:46.88+09
   1 |   3 |         2 | 2012-08-15 23:26:46.881+09
   1 |   4 |         2 | 2012-08-15 23:26:46.883+09
   1 |   5 |         2 | 2012-08-15 23:26:46.884+09
   1 |   6 |         3 | 2012-08-15 23:26:46.915+09
   1 |   7 |         3 | 2012-08-15 23:26:46.919+09
(7 行)

# echo ' select * from signature;' | sudo -u snort psql -U snort -d snort -h localhost -A -F\,
ユーザ snort のパスワード:
sig_id,sig_name,sig_class_id,sig_priority,sig_rev,sig_sid,sig_gid
1,(portscan) TCP Portscan,,3,,1,122
2,(portscan) Open Port,,3,,27,122
3,COMMUNITY SIP TCP/IP message flooding directed to SIP proxy,1,2,2,100000160,1
(3 行)

■psqlだと簡単にHTMLが出せます。
　mysqlでのHTML出力方法を知らないだけかも。。。

# echo ' select * from signature;' | sudo -u snort psql -U snort -d snort -h localhost -H
ユーザ snort のパスワード:
<table border="1">
  <tr>
    <th align="center">sig_id</th>
    <th align="center">sig_name</th>
    <th align="center">sig_class_id</th>
    <th align="center">sig_priority</th>
    <th align="center">sig_rev</th>
    <th align="center">sig_sid</th>
    <th align="center">sig_gid</th>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">(portscan) TCP Portscan</td>
    <td align="right">&nbsp; </td>
    <td align="right">3</td>
    <td align="right">&nbsp; </td>
    <td align="right">1</td>
    <td align="right">122</td>
  </tr>
  <tr valign="top">
    <td align="right">2</td>
    <td align="left">(portscan) Open Port</td>
    <td align="right">&nbsp; </td>
    <td align="right">3</td>
    <td align="right">&nbsp; </td>
    <td align="right">27</td>
    <td align="right">122</td>
  </tr>
  <tr valign="top">
    <td align="right">3</td>
    <td align="left">COMMUNITY SIP TCP/IP message flooding directed to SIP proxy</td>
    <td align="right">1</td>
    <td align="right">2</td>
    <td align="right">2</td>
    <td align="right">100000160</td>
    <td align="right">1</td>
  </tr>
</table>
<p>(3 行)<br />
</p>

■mysqlでもHTML出力できるんですね。
　※但し、HTMLソースはpsqlの方がデフォルトで綺麗。

$ echo 'select * from signature;' | mysql -u snort -p -D snort -h localhost -H | sed s%"</[A-Z]*>"%"&\n"%g
Enter password:
<TABLE BORDER=1><TR><TH>sig_id</TH>
<TH>sig_name</TH>
<TH>sig_class_id</TH>
<TH>sig_priority</TH>
<TH>sig_rev</TH>
<TH>sig_sid</TH>
<TH>sig_gid</TH>
</TR>
<TR><TD>1</TD>
<TD>COMMUNITY SIP TCP/IP message flooding directed to SIP proxy</TD>
<TD>1</TD>
<TD>2</TD>
<TD>2</TD>
<TD>100000160</TD>
<TD>1</TD>
</TR>
<TR><TD>2</TD>
<TD>(spp_ssh) Protocol mismatch</TD>
<TD>0</TD>
<TD>3</TD>
<TD>1</TD>
<TD>4</TD>
<TD>128</TD>
</TR>
<TR><TD>3</TD>
<TD>WEB-IIS cmd.exe access</TD>
<TD>2</TD>
<TD>1</TD>
<TD>7</TD>
<TD>1002</TD>
<TD>1</TD>
</TR>
<TR><TD>4</TD>
<TD>DNS named version attempt</TD>
<TD>3</TD>
<TD>2</TD>
<TD>7</TD>
<TD>1616</TD>
<TD>1</TD>
</TR>
<TR><TD>5</TD>
<TD>(portscan) TCP Portscan</TD>
<TD>0</TD>
<TD>3</TD>
<TD>NULL</TD>
<TD>1</TD>
<TD>122</TD>
</TR>
<TR><TD>6</TD>
<TD>(portscan) Open Port</TD>
<TD>0</TD>
<TD>3</TD>
<TD>NULL</TD>
<TD>27</TD>
<TD>122</TD>
</TR>
<TR><TD>7</TD>
<TD>SNMP request tcp</TD>
<TD>3</TD>
<TD>2</TD>
<TD>11</TD>
<TD>1418</TD>
<TD>1</TD>
</TR>
<TR><TD>8</TD>
<TD>SNMP AgentX/tcp request</TD>
<TD>3</TD>
<TD>2</TD>
<TD>11</TD>
<TD>1421</TD>
<TD>1</TD>
</TR>
<TR><TD>9</TD>
<TD>(snort_decoder): Tcp Window Scale Option found with length &gt; 14</TD>
<TD>0</TD>
<TD>3</TD>
<TD>1</TD>
<TD>59</TD>
<TD>116</TD>
</TR>
<TR><TD>10</TD>
<TD>(http_inspect) WEBROOT DIRECTORY TRAVERSAL</TD>
<TD>0</TD>
<TD>3</TD>
<TD>1</TD>
<TD>18</TD>
<TD>119</TD>
</TR>
<TR><TD>11</TD>
<TD>T-Point ToolBar Client Access</TD>
<TD>0</TD>
<TD>NULL</TD>
<TD>1</TD>
<TD>10000001</TD>
<TD>1</TD>
</TR>
<TR><TD>12</TD>
<TD>T-Point ToolBar Request Access</TD>
<TD>0</TD>
<TD>NULL</TD>
<TD>1</TD>
<TD>10000002</TD>
<TD>1</TD>
</TR>
</TABLE>