Re: Secure remote syslogging?

[Jose Luis Domingo Lopez <debian-security@24x7linux.com>]

On Wednesday, 23 April 2003, at 16:37:01 -0700,
Jamie Penner wrote:

> or, if using syslog-ng, do this for each logfile type in your config file:
> 
> destination syslog { file("/var/log/serverlogs/$HOST/syslog" owner("root") 
> group("adm") perm(0640)); };
> 
On the syslog-ng side I would suggest considering logging to a remote
SQL database. It is not only possible, but quite simple. I made some
kind of "proof of concept" tests (using a little PERL script to keep the
connection to the MySQL database alive) encrypting the data via SSH
TCP tunnels (using RSA/DSA keys and authorized_kyes restrictions).

The following URL was a good starting point:
http://lists.balabit.hu/pipermail/syslog-ng/2002-April/003249.html

The implementation consisted in syslog-ng logging to a pipe (using a
template for "SQL output"), which is depleted from an ever running PERL
script that executes the SQL sentences in the remote server through a
secure SSH tunnel. The only thing left to do would be some kind of
"sanitization" of the SQL inserts, to avoid potentially harmful "SQL
injections". Tight access controls in the remote database and tables
could mitigate this potential risk (ie. limit the connecting user to
just INSERT permission, and not UPDATE or DELETE).

If someone is interested in the PERL script, here it is (please be kind,
I am a programmer in any way :-). The next two lines is the
configuration needed no the log client side.
destination d_logpipe { pipe("/tmp/pipe" owner("someone") template("\( '$HOST', '$ISODATE', '$FACILITY', '$PRIORITY', '$MESSAGE' \)\n") ); };
log { source(src); destination(d_logpipe); };

#!/usr/bin/perl -w
# We read line after line from a "pipe" and from them we generate SQL
# sentences to insert the log entries in the remote database.
#         José Luis Domingo López <jdomingo@24x7linux.com>

## Overview
# "syslog-ng" is configured to log all system events to a named pipe
# previously created via "mkfifo". Each log entre is formated through a
# "syslog-ng" "template" to generate a pseudo-SQL insert on the remote
# database. But we are using a SSH tunnel (-L mode), so we connect to
# the local IP address, not the remote server's.
# 
## Implementation details
# - First, open an SQL connection to the remote MySQL database (via the
#   local tunnel endpoint), and keep it open while running
# - Open read-only the pipe where syslog-ng logs system events, and
#   keep reading line by line until EOF or program end
# - Each line read from the pipe is the base of a SQL insertion sentence
#   into the remote MySQL database.

# NOTE: libdbd-mysql-perl is necessary for this script
use strict;
use Mysql;

my $host="127.0.0.1";
my $database="logs";
my $user="joseluis";
my $password="joseluis";
my $query="";
my $error=0;
my $errmsg;
my $sth;

my $dbh = Mysql->connect($host, $database, $user, $password);
if ( ! $dbh ) {
    $errmsg= Mysql->errmsg();
    die "Unable to connect with the remote database, error: $errmsg\n";
};

open ( LOGPIPE, "< /tmp/pipe" );
while ( my $log = <LOGPIPE> ) {
    $query="INSERT INTO logs.testbox (host,time,facility,priority,message) VALUES ";
    $query=$query . $log ;
    $sth = $dbh->query($query);
    $error = Mysql->errno;
    $errmsg= Mysql->errmsg();
    #if ( $error != 0 ) {
    #    die "Insertion failed: $errmsg\n";
    #};
};
close ( LOGPIPE );

Hope it helps.

-- 
Jose Luis Domingo Lopez
Linux Registered User #189436     Debian Linux Sid (Linux 2.5.68)