SQL Database

NoteREQUIREMENTS
 

This facility requires that you use have compiled with the --with-xml-log option to format log messages in XML (also for the client, even if you do SQL logging on the server), and of course with the --with-database=XXX option (where 'XXX' may be any of: mysql, postgresql, oracle, or odbc).

If you are using the MessageHeader directive in the configuration file for a user-defined message header, make sure that the log messages are still valid XML, and that all the default entities are still present.

Currently MySQL, PostgreSQL, and Oracle are implemented and tested. Support for unixODBC is implemented, but not fully tested. If the header file 'mysql.h' ('libpq-fe.h') is not found during compilation ('mysql.h: No such file or directory'), you can use the option --with-cflags=-I/dir/where/mysql.h/is. If the library libmysqlclient.a (libpq.a) is not found ('/usr/bin/ld: cannot find -lmysqlclient'), you can use the option --with-libs=-L/dir/where/libmysqlclient.a/is.

NoteNOTE
 

PostgreSQL may fail with --enable-static. This is a postgresql bug.

By default, the database server is assumed to be on localhost, the db name is 'samhain', the db table is 'log', and inserting is possible for any user without password. To create the database/table with the required columns, the distribution includes the scripts 'samhain.mysql.init', 'samhain.postgres.init', and 'samhain.oracle.init'. E.g., for PostgreSQL you would setup the database like:

      $ su postgres
      $ createdb samhain
      $ createuser samhain
      $ psql -d samhain < samhain.postgres.init
      $ exit

… and for MySQL:

      $ mysql -p -u root < samhain.mysql.init
      $ mysql -p -u root
      … edit grant tables
      $ mysqladmin -p -u root reload

NoteNOTE
 

The PostgreSQL init script will grant INSERT permission only to a user 'samhain'. Please take note that for PostgreSQL, inserting also requires SELECT and UPDATE permission for the sequence 'log_log_index_seq' (see bottom of init script). The MySQL init script will create the database, but not the user, and will not grant any permissions.

As with all logging facilities, logging to the SQL database must be enabled in the configuration file:

 
  [Log]
  DatabaseSeverity=warn
  

In the Database section of the configuration file, you can modify the defaults via the following directives:

 
  [Database]
  SetDBName=db_name 
  SetDBTable=db_table
  SetDBHost=db_host 
  SetDBUser=db_user 
  SetDBPassword=db_password
  UsePersistent=yes/no
  

The default is to use a persistent connection to the database. You can change this with UsePersistent=no

NoteNOTE
 

For PostgreSQL, db_host must be a numerical IP address.

When logging client messages, yule will wrap them into a server <log sev=''RCVT'' tstamp=… > … </log> message. The parser will then create a seperate database entry for this server timestamp. If you don't like this, you can use the option SetDBServerTstamp=false.

The table field 'log_ref' is NULL for client messages, 0 for server messages, and equal to 'log_index' of the client message for the aforementioned server timestamp of a client message.

There is a special (indexed) table field 'log_hash', which is the MD5 checksum of (the concatenation of) any fields registered with AddToDBHash=field. This might allow to find unique rows faster. There is no default set of fields over which the MD5 hash is computed, so by default the hash is equal for all rows.

TipTIP
 

For security, you may want to set up a user/password for insertion into the db. However, as the password is in cleartext in the config file (and the connection to the db server is not encrypted), for remote logging this facility is less secure than samhain's own client/server system (it is recommended to run the db server on the log host and have the log server, i.e. yule, log to the db).