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.
The Rsyslog module "pgsql" is required for the setup.
# apt install rsyslog-pgsql
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;
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";
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
);
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";
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.
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")
# systemctl restart rsyslog
postgres$ psql -c 'select count(*) from systemevents' Syslog
The following is optional to make the usage a bit more convenient.
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');
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');
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 select on messages to user;
$ psql -c 'select * from messages where timestamp > now()::date' Syslog
$ psql -c "select timestamp, priority, facility, case when length(message) > 100 then substring(message,1,99) || '…' else message end as message from messages" Syslog