RSyslog

Write syslog into Postgres database

• create syslog table

CREATE TABLE syslog
(
        id SERIAL PRIMARY KEY,
        host VARCHAR(32) NULL,
        facility VARCHAR(10) NULL,
        priority VARCHAR(10) NULL,
        tag VARCHAR(32) NULL,
        timestamp TIMESTAMP WITHOUT TIME ZONE NULL,
        message TEXT
);

• create user id and grant permissions to insert new entries

CREATE USER sysuser PASSWORD 'syspass';
GRANT INSERT ON syslog TO sysuser;
GRANT SELECT,UPDATE ON syslog_id_seq TO sysuser;

• install rsyslog-pgsql package

---

rsyslog configuration by default is in /etc/rsyslog.conf

---

• enable postrges support

$ModLoad ompgsql.so

• enable logging from remote hosts if needed

$ModLoad imudp.so
$UDPServerRun 514

• add sql statement template

$template syslogSQL,"insert into syslog (host,facility,priority,tag,timestamp,message) \
values ('%HOSTNAME%','%syslogfacility-text%','%syslogpriority-text%','%syslogtag:F,58:1%','%timereported:::date-pgsql%','%msg%')",stdsql

• set secure permission on configuration file, since we are going to store password to access postgres database there

chmod 600 /etc/rsyslog.conf

• add action statement

*.*      :ompgsql:localhost,syslogdb,sysuser,syspass;syslogSQL

• add the following configuration to ensure log entries will be preserved if database was busy of temporary unavailable

$WorkDirectory /var/lib/rsyslog # where to place spool files
$ActionQueueFileName dbq      # unique name prefix for spool files
$ActionQueueMaxDiskSpace 512M   # 512M space limit (use as much as possible)
$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
$ActionQueueType LinkedList   # run asynchronously
$ActionResumeRetryCount -1    # infinite retries if host is down

• create spool directory

mkdir /var/lib/rsyslog

• if you use SELinux, you might need to change your local policy

• local.te

type rsyslog_var_lib_t;
files_type(rsyslog_var_lib_t);

manage_files_pattern(syslogd_t, rsyslog_var_lib_t, rsyslog_var_lib_t)
manage_dirs_pattern(syslogd_t, rsyslog_var_lib_t, rsyslog_var_lib_t)

• local.fc

/var/lib/rsyslog(/.*)?          gen_context(system_u:object_r:rsyslog_var_lib_t,s0)

• fix security context

restorecon -vR /var/lib/rsyslog

• start using rsyslog

service rsyslog start

• add a maintenance script which will remove old entries, for example

 DELETE FROM syslog WHERE timestamp < LOCALTIMESTAMP - INTERVAL '30 days';

Write syslog entries from a host to a separate file

# Router
:FROMHOST, isequal, "vzrouter"     -/var/log/vzrouter.log

'-' in front of a log file name instructs rsyslog to omit syncing the file after every logging

• Don't forget to add /var/log/vzrouter.log into /etc/logrotate.d/syslog