SQL Database

Samhain
Prev | Chapter 4. Configuration of logging facilities | Next

# 4.12. SQL Database

| Requirements
| This facility requires that you have compiled with the --enable-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.

| Note
| 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 -P samhain
          $ psql -d samhain < samhain.postgres.init
          $ exit

… and for MySQL:

          $ mysql -p -u root < samhain.mysql.init
          $ mysql -p -u root
          > GRANT SELECT, INSERT ON 'samhain.log' TO 'samhain@localhost';
          > SET PASSWORD for 'samhain@localhost' = PASSWORD("...");
          > FLUSH PRIVILEGES;

| Permissions
| 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 by setting an appropriate threshold, e.g.:

      [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

| Note re. PostgreSQL
| 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 separate 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.

Log records can be tagged via a special (indexed) table field 'log_hash', which is the MD5 checksum of (the concatenation of) any fields registered with AddToDBHash=`field`. The beltane web-based console can use these tags to filter messages. There is no default set of fields over which the MD5 hash is computed, so by default the tag is equal for all rows.

| Tip
| 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).

## 4.12.1. Upgrade to samhain 2.3

Version 2.3 of Samhain supports checking of SELinux attributes and/or Posix ACLs. For backward compatibility, this is off by default. If you upgrade Samhain and enable this option, you need to update the database scheme as follows:

Mysql:

            ALTER TABLE samhain.log ADD COLUMN acl_old BLOB;
            ALTER TABLE samhain.log ADD COLUMN acl_new BLOB;

PostgreSQL:

            ALTER TABLE samhain.log ADD COLUMN acl_old TEXT;
            ALTER TABLE samhain.log ADD COLUMN acl_new TEXT;

Oracle:

            ALTER TABLE samhain.log ADD acl_old VARCHAR2(4000);
            ALTER TABLE samhain.log ADD acl_new VARCHAR2(4000);
            DROP TRIGGER trigger_on_log;

## 4.12.2. MySQL configuration details

To pass the location of the MySQL Unix domain socket (for connections on localhost) to samhain, you can use the environment variable MYSQL_UNIX_PORT (the value must be the path of the socket).

Alternatively, as of samhain version 2.2, you can set options for the group "samhain" in `my.cnf`. See the MySQL manual for the proper syntax of the `my.cnf` file, as well as for possible options.

| Note
| It is not possible for an application (like e.g. samhain) to detect whether `my.cnf` is readable (because the application does not know where the file resides). Interesting errors may result...

Prev | Home | Next
Syslog | Up | Configuring samhain, the file monitor