From OSSEC Wiki
- by Daniel Cid
Contents |
PostgreSQL Logging
- Detailed information regarding how to configure PostgreSQL is available here.
To make sure OSSEC can parse your PostgreSQL logs, you need to make
a few changes to your postgresql.conf.
- First, set the log line prefix:
# Adding the timestamp, hostname and database. log_line_prefix = '[%m] %h:%d '
- Second, we recommend the following settings:
log_connections = on log_disconnections = on log_duration = on
- Third, you may want to reduce the default log level to info:
client_min_messages = info log_min_messages = info
- Lastly, if you want to log every query (can cause performance issues) you can:
Enable query logging (with the duration of the query):
log_min_duration_statement = 0
OR (do not enable both!)
Enable query logging (all for everything or mod for inserts, updates, etc)
log_statement = 'all'
Examples of how your logs should look like
**More log samples here.
[2007-08-31 18:37:09.454 ADT] 192.168.2.99: LOG:db1 connection authorized: user=ossec_user database=ossecdb
[2007-08-31 18:37:15.525 ADT] 192.168.2.99: ERROR:db2 relation "alert2" does not exist
[2007-08-31 18:37:17.501 ADT] 192.168.2.99: LOG:db disconnection: session time: 0:00:08.06 user=ossec_user database=ossecdb host=192.168.2.99 port=40865
[2007-09-05 12:06:15.800 ADT] 192.168.2.10: LOG:db statement: INSERT INTO location(server_id, name) VALUES ('1', 'enigma->/var/log/authlog')
[2007-09-27 10:29:03.141 ADT] 192.168.2.10:test1 FATAL: password authentication failed for user "root"
[2007-09-27 10:31:11.859 ADT] 192.168.2.10:ossecdb LOG: connection authorized: user=ossec_user database=ossecdb
Quick configuration
Just add the following to postgresql.conf:
log_line_prefix = '[%m] %h: ' log_connections = on log_disconnections = on log_duration = on
Configuring OSSEC for PostgreSQL
Configuring OSSEC to read/monitor PostgreSQL logs is very simple. Just add a new "localfile" entry for
each log file you want to monitor (at ossec.conf).
**You first need to make sure you have PostgreSQL logging in the proper format
- To monitor log file /var/log/postgresql/main.log:
<localfile>
<log_format>postgresql_log</log_format>
<location>/var/log/postgresql/main.log</location>
</localfile>
MySQL Logging
MySQL has two main log types that OSSEC can understand: error log and query log.
Error log
MySQL error log is enabled by default. It is generally stored on the MySQL lib directory as "hostname.log".
Generic Query Log
To enable the generic query log, you must start mysqld with the "--log" flag.
/bin/sh /usr/bin/mysqld_safe --log
Examples of logs
**More log samples here.
- Error log:
070823 20:58:07 InnoDB: Starting shutdown... 070823 20:58:09 InnoDB: Shutdown completed; log sequence number 0 43634 070823 20:58:09 [Note] /usr/libexec/mysqld: Shutdown complete 070823 20:58:09 mysqld ended 070823 20:59:57 mysqld started 070823 20:59:58 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.14-log' socket: '/var/run/mysql/mysql.sock' port: 3306 Source distribution
- Generic log:
070827 22:48:16 13 Connect root@localhost on test1
070827 22:48:20 13 Query select * from server
070827 22:48:39 13 Query INSERT INTO server(last_contact, version, hostname, information) VALUES ('1188265672', 'v1.3', 'enigma.ossec.net', 'OpenBSD enigma.ossec.net 3.9 GENERIC#617 i386 - OSSEC HIDS v1.3')
070827 22:48:47 13 Query INSERT INTO server(last_contact, version, hostname, information) VALUES ('1188265672', 'v1.3', 'enigma.ossec.net2', 'OpenBSD enigma.ossec.net 3.9 GENERIC#617 i386 - OSSEC HIDS v1.3')
070827 22:48:49 13 Query select * from server
Configuring OSSEC for MySQL
Configuring OSSEC to read/monitor MySQL logs is very simple. Just add a new "localfile" entry for
each log file you want to monitor (at ossec.conf).
- To monitor log file /var/log/mysql/slack.err (error log):
<localfile>
<log_format>mysql_log</log_format>
<location>/var/log/mysql/slack.err</location>
</localfile>
- To monitor log file /var/log/mysql/slack.log (generic query log):
<localfile>
<log_format>mysql_log</log_format>
<location>/var/log/mysql/slack.log</location>
</localfile>