Syslog Logging with PostgreSQL HOWTO

PostgreSQL Database Setup

Create the a database named syslog. To do this, run this command: createdb syslog

Environment:

• FreeBSD 5.1
  
• Syslog-NG
• PostgreSQL (v 7.3.2 used here; Compiled from source)

Next, create the table in the newly-created datbase. Download the SQL directly: syslog.sql

CREATE TABLE logs (
 host varchar(32) default NULL,
 facility varchar(10) default NULL,
 priority varchar(10) default NULL,
 level varchar(10) default NULL,
 tag varchar(10) default NULL,
 date date default NULL,
 time time default NULL,
 program varchar(15) default NULL,
 msg text,
 seq serial,
 PRIMARY KEY (seq)
); 

3. Syslog-NG Setup

Install Syslog-NG.

Edit your syslog-ng configuration file.

/etc/syslog-ng/syslog-ng.conf.

Here are the relevant parts of syslog-ng.conf:

#
# SQL logging support
#

destination d_pgsql {
pipe("/tmp/pgsql.pipe"
template("INSERT INTO logs (host, facility, priority, level, tag, date, time, program,
msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG,
'$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );n
)

template-escape(yes)

);

};

log { source(src); destination(d_pgsql); };

Getting PostgreSQL and Syslog to Work Together

Use this script I've adapted for PostgreSQL, syslog-ng-pgsql-pipe.sh:

#!/bin/sh
#
# File: syslog-ng-pgsql-pipe.sh
#
# Take input from a FIFO and run execute it as a query for
# a PostgreSQL database.
#
# IMPORTANT NOTE:  This could potentially be a huge security hole.
# You should change permissions on the FIFO accordingly.
#
                                                                                                                                             
if [ -e /tmp/pgsql.pipe ]; then
       while [ -e /tmp/pgsql.pipe ]
               do
                       psql -q -U mydbusername syslog < /tmp/pgsql.pipe>        done
else
       mkfifo /tmp/pgsql.pipe
fi