1. Introduction

1.1 Summary

This document will describe briefly how to configure the next-generation syslog logging daemon (syslog-ng) to log messages to a postgresql database.

1.2 Standard Disclaimer

No liability for the contents of this documents can be accepted. Use the concepts, examples and other content at your own risk. As this is a new document, there may be errors and inaccuracies, that may of course be damaging to your system. Proceed with caution, and although this is highly unlikely, I don't take any responsibility for that.

1.3 Feedback

Feedback is most certaintly welcome for this document. Although I do not plan to actively maintain this document, I will include worthy submissions and input that I receive. If you have additions, comments and criticisms, please send them to Kevin Dougherty <howto@kdough.net>.

1.4 Environment

Environment:

• Debian / GNU Linux Woody
• Syslog-NG (v 1.5.15-1.1 used here; Standard debian/woody package)
• PostgreSQL (v 7.3.2 used here; Compiled from source)

2. PostgreSQL Database Setup

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

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)
);

Of course, you'll likely want to alter permissions to restrict access to this table, but that is beyond the scope of this concise HOWTO. Refer to the PostgreSQL Manual for details on how to accomplish that.

3. Syslog-NG Setup

Install Syslog-NG. Under debian, run this: apt-get install syslog-ng.

Edit your syslog-ng configuration file. Under debian, it is located here: /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); };

4. Getting PostgreSQL and Syslog to Work Together

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

#!/bin/bash
#
# 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

Important notes: This is purely a quick solution. You will likely want to place this FIFO in another, more persistent location (e.g. /var/run or something). You also will likely want to alter permissions to the FIFO so that Syslog-NG can write to it and the above script can read from it. Consult your local Un*x geek for help if necessary.

Good luck! I hope this document was able to help anyone looking to configure SQL-based logging with Syslog-NG and PostgreSQL.