Store Syslog in Postgresql

Rsyslog-PGSQL.md

Store Syslog in Postgresql

The following describes how to configure Rsyslog to write all syslog messages into a Postgresql database.

This description requires at least Rsyslog 8.2302. If you are using Debian you need at least Debian 12 for this.

This setup uses UNIX domain socket authentiation. This has the advantage that the configuration files of Rsyslog do not contain any database password. It has the disadvantage, that the database must be running on the same system as Rsyslog itself. This may be no problem, if the forwading of syslog messages is done with the functionality of Rsyslog itself.

Install Database backend for Rsyslog

The Rsyslog module "pgsql" is required for the setup.

# apt install rsyslog-pgsql

Setup the Database

Create Database for Syslog

The database "Syslog" will contain a copy of all syslog messages. No encoding conversion will be done. As user "postgres" execute:

CREATE DATABASE "Syslog" WITH ENCODING 'SQL_ASCII' TEMPLATE template0;

Create root user

Rsyslog runs with root permissions. To use UNIX domain socket authentication the user "root" must exist in the database. As user "postgres" execute:

CREATE USER "root";

Create Tables required by Rsyslog

Connect as user "postgres" to the new database "Syslog" and execute the following.

CREATE TABLE SystemEvents
(
        ID serial not null primary key,
        CustomerID bigint,
        ReceivedAt timestamp without time zone NULL,
        DeviceReportedTime timestamp without time zone NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
        ID serial not null primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

Grant permissions

Give the user "root" permissions to write syslog messages and to use the sequences for the primary keys.

grant insert on table SystemEvents to "root";
grant insert on table SystemEventsProperties to "root";
grant usage, select on sequence systemevents_id_seq to "root";
grant usage, select on sequence systemeventsproperties_id_seq to "root";

Configure Rsyslog

This configuration uses UNIX domain sockets. The disadvantage is that it works only if the database and the Syslog server are running on the same host. The advantage is that you do not have any passwords in the configuration file of Rsyslog. And it is faster than TCP.

/etc/rsyslog.d/pgsql.conf

The configuration of the pgsql module must be done using the conninfo option.

module (load="ompgsql")
*.* action(type="ompgsql" conninfo="postgresql:///Syslog?host=/run/postgresql")

Restart Rsyslog

# systemctl restart rsyslog

Check

postgres$ psql -c 'select count(*) from systemevents' Syslog 

Revamping

The following is optional to make the usage a bit more convenient.

Create tables for symbolic names

Priorities

create table priority
(
	id integer primary key,
	name text
);

insert into priority (id, name) values
(0, 'EMERG'),
(1, 'ALERT'),
(2, 'CRIT'),
(3, 'ERR'),
(4, 'WARN'),
(5, 'NOTICE'),
(6, 'INFO'),
(7, 'DEBUG');

Facilities

create table facility
(
	id integer primary key,
	name text
);

insert into facility (id, name) values
( 0, 'kern'),
( 1, 'user'),
( 2, 'mail'),
( 3, 'daemon'),
( 4, 'auth'),
( 5, 'syslog'),
( 6, 'lpr'),
( 7, 'news'),
( 8, 'uucp'),
( 9, 'cron'),
(10, 'authpriv'),
(11, 'ftp'),
(12, 'ntp'),
(13, 'audit'),
(14, 'console'),
(15, 'cron2'),
(16, 'local0'),
(17, 'local1'),
(18, 'local2'),
(19, 'local3'),
(20, 'local4'),
(21, 'local5'),
(22, 'local6'),
(23, 'local7');

Create view for the most interesting data

Note: This may be a security problem!

The following creates just one big view containg all syslog messages. The view does not distinguish between auth and authpriv. You have to create your own views if this concerns you.

create view messages as
	select
		devicereportedtime as timestamp,
		coalesce(priority.name, systemevents.priority::text) as priority,
		coalesce(facility.name, systemevents.facility::text) as facility,
		message
	from systemevents
	left join priority
	on systemevents.priority = priority.id
	left join facility
	on systemevents.facility = facility.id
	order by systemevents.id;

Grant access to a trustworthy user

grant select on messages to user;

Check

Show today's logs

$ psql -c 'select * from messages where timestamp > now()::date' Syslog

Limit the message length

$ psql -c "select timestamp, priority, facility, case when length(message) > 100 then substring(message,1,99) || '…' else message end as message from messages" Syslog