[syslog-ng] logging to PostgreSQL database

5 Jan 2011

      Hi,
I am a student of Computer Science at University of Ljubljana. Recently I
started working on some project which includes logging into PostgreSQL
database.
I came across some problem which I can't resolve myself. No matter what I
do, syslog-ng refuses to log into local PostgreSQL database. Here is my
current configuration:

using syslog-ng 3.2 server

contents of syslog-ng.conf
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@version: 3.2
#Default configuration file for syslog-ng.
#
# For a description of syslog-ng configuration file directives, please read
# the syslog-ng Administrator's guide at:
#
# http://www.balabit.com/dl/html/syslog-ng-admin-guide_en.html/bk01-toc.html
#

options {
time_reap(30);
mark_freq(10);
keep_hostname(yes);
};

######
# sources
source s_local {
# message generated by Syslog-NG
internal();
# standard Linux log source (this is the default place for the syslog()
# function to send logs to)
unix-stream("/dev/log");
# messages from the kernel
file("/proc/kmsg" program_override("kernel"));
};

# source s_syslog { syslog(ip(127.0.0.1) port(1999) transport("tcp")); };

########################
# Filters
########################
# Here's come the filter options. With this rules, we can set which
# message go where.

# messages for eneraptor should have priority level LOG_MAIL and contain
keyword "eneraptor"
filter f_mail_eneraptor { facility(mail) and match("eneraptor"); };

######
# destinations
destination d_messages        { file("/var/log/messages"); };
destination filtered_messages { file("/var/log/messages_filtered"); };

#
# SQL logging support
#

destination d_pgsql {
  sql(type(pgsql)
  host("localhost") username("eneraptor") password("eneraptor")
  database("eneraptordb")
  table("logs")
  columns("datetime varchar(16)", "host varchar(32)", "program varchar(8)",
"pid varchar(8)", "message varchar(200)")
  values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")
  indexes("datetime", "host", "program", "pid", "message"));
};

log {
source(s_local);
destination(d_messages);
# destination(d_pgsql);
};

log {
source(s_local);
filter(f_mail_eneraptor);
destination(filtered_messages);
destination(d_pgsql);
};
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
using PostgreSQL 8.4
contents of pg_hba.conf:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
#
# Database administrative login by UNIX sockets
local   all         all                          trust

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
# local   all         all                               trust
# IPv4 local connections:
# host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
# syslog-ng logger
# host    eneraptordb      eneraptor   127.0.0.1/32          trust
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
As far as I understand this configuration file, any user should have full
access to any database on local machine. At this point, I am not concerned
about security issues as I plan to address them after I resolve logging
problem.

postgresql.conf:
Below are included only lines I uncommented or changed.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
listen_addresses = 'localhost' # what IP address(es) to listen on;
...
password_encryption = on
...
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is the error I get, logged by syslog-ng:
*Jan  5 22:00:38 computerName syslog-ng[11080]: Error establishing SQL
connection; type='pgsql', host='localhost', port='', username='eneraptor',
database='eneraptordb', error='could not connect to server: Connection
refused\x0a\x09Is the server running on host "localhost" and
accepting\x0a\x09TCP/IP connections on port 0?\x0a'*
*
*
Database has also been created, properly named and has the right user.

Message I am trying to log is being sent by test program written in C, line
that sends message is (every few seconds):
*syslog(LOG_MAIL, "eneraptor test: %d", counter);*
...
From what I could gather is that syslog-ng probably can't connect to
database, but I am running out of options on how to resolve this issue. I
would be very grateful if you could help me resolve this issue.
Best regards,
Janez Barbic

[syslog-ng] logging to PostgreSQL database

Janez Barbič