Requirements

To build an executable with support for logging to a SQL database, the relevant development packages (e.g. libmysqlclient-dev on Ubuntu for MySQL) need to be installed in the build machine. 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
	The --enable-static option to compile a static executable is incompatible with PostgreSQL and MySQL.

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

... and for MySQL:

      $ mysql -p -u root < samhain.mysql.init
      $ mysql -p -u root
      > CREATE USER 'samhain'@'localhost' IDENTIFIED BY 'password';
      > GRANT SELECT,INSERT ON samhain.log TO 'samhain'@'localhost';
      > 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).

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

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

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

	  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;
        

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

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

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

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

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

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