12. SQL Database

[Note]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]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
      Enter password for new role: 
      Enter it again: 
      Shall the new role be a superuser? (y/n) n
      Shall the new role be allowed to create databases? (y/n) n
      Shall the new role be allowed to create more new roles? (y/n) n
      $ 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;
      
[Note]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]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]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).

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;
	

12.2. Upgrade to samhain 2.4.4

Version 2.4.4 of Samhain supports storing the content of files. If you have created your Oracle database using the database scheme from a previous version, you need to change at least the 'link_old' and 'link_new' columns from VARCHAR2 to CLOB:

	  ALTER TABLE samhain.log ADD tmp_name CLOB;
	  UPDATE samhain.log SET tmp_name=link_old;
	  ALTER TABLE samhain.log DROP COLUMN link_old;
	  ALTER TABLE samhain.log RENAME COLUMN tmp_name to link_old;
	  
	  ALTER TABLE samhain.log ADD tmp_name CLOB;
	  UPDATE samhain.log SET tmp_name=link_new;
	  ALTER TABLE samhain.log DROP COLUMN link_new;
	  ALTER TABLE samhain.log RENAME COLUMN tmp_name to link_new;
        

12.3. Upgrade to samhain 2.8.0+

As of version 2.8.0, Samhain supports IPv6 now, which means that the size of the 'ip' column in the database must be increased from VARCHAR(16) to VARCHAR(46).

Mysql:

 
          ALTER TABLE samhain.log MODIFY ip VARCHAR(46);
	

PostgreSQL:

 
          ALTER TABLE samhain.log ALTER COLUMN ip TYPE VARCHAR(46);
	

Oracle:

 
          ALTER TABLE samhain.log MODIFY ip VARCHAR(46);
	

12.4. Upgrade to samhain 4.0

As of version 4.0, Samhain optionally logs the checking policy for a file if the option ReportCheckflags is set in the configuration file. If you enable this option, you need to update your database scheme as follows:

Mysql:

	  ALTER TABLE samhain.log ADD COLUMN checkflags_old BIGINT UNSIGNED;
	  ALTER TABLE samhain.log ADD COLUMN checkflags_new BIGINT UNSIGNED;
	

PostgreSQL:

	  ALTER TABLE samhain.log ADD COLUMN checkflags_old NUMERIC(20); 
	  ALTER TABLE samhain.log ADD COLUMN checkflags_new NUMERIC(20);
	

Oracle:

	  ALTER TABLE samhain.log ADD checkflags_old NUMBER(20); 
	  ALTER TABLE samhain.log ADD checkflags_new NUMBER(20); 
	

12.5. 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]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...