Append only, centralized log server with PostgreSQL and rsyslog.

pgsql.conf

# /etc/rsyslog.d/pgsql.conf

$template SqlSyslog,"INSERT INTO logs.syslog ( \
    message, facility, from_host, priority, device_reported_time, received_at, info_unit_id, syslog_tag \
) values ( \
  '%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-rfc3339%', '%timegenerated:::date-rfc3339%', %iut%, '%syslogtag%' \
)",STDSQL

module (load="ompgsql")

*.* action(
    type="ompgsql"
    conninfo="postgresql://..."
    template="SqlSyslog"
    action.resumeRetryCount="-1"
)

rsyslog.sql

-- 01 schemas
CREATE SCHEMA logs;
CREATE SCHEMA subpartitions;

-- 02 tables
CREATE TABLE logs.syslog (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    received_at timestamptz NOT NULL,
    device_reported_time timestamptz NOT NULL,
    info_unit_id int,
    facility smallint,
    priority smallint,
    from_host text,
    syslog_tag text,
    pgsql_user text DEFAULT CURRENT_USER,
    message text,

    PRIMARY KEY (device_reported_time, id)
) PARTITION BY RANGE (device_reported_time);

CREATE TABLE subpartitions.logs_syslog_default PARTITION OF logs.syslog DEFAULT;

CREATE INDEX ON logs.syslog USING BTREE (pgsql_user);
CREATE INDEX ON logs.syslog USING BTREE (device_reported_time DESC);

-- 03 non repudiation --> https://csrc.nist.gov/glossary/term/non_repudiation
CREATE FUNCTION logs.t_ensure_non_repudiation ()
    RETURNS TRIGGER
    AS $$
BEGIN
    IF NEW.pgsql_user IS NOT NULL THEN
        NEW.pgsql_user := CURRENT_USER;
    END IF;
    NEW.created_at := now();
    RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER t_ensure_non_repudiation_before_syslog_insert
    BEFORE INSERT ON logs.syslog
    FOR EACH ROW
    EXECUTE FUNCTION logs.t_ensure_non_repudiation ();

-- 04 dashboard
CREATE TABLE logs.syslog_prio (
    name text,
    id smallint PRIMARY KEY
);

INSERT INTO logs.syslog_prio (id, name) VALUES
    (0, 'EMERG'),
    (1, 'ALERT'),
    (2, 'CRIT'),
    (3, 'ERR'),
    (4, 'WARN'),
    (5, 'NOTICE'),
    (6, 'INFO'),
    (7, 'DEBUG');

CREATE TABLE logs.syslog_facility (
    name text,
    id smallint PRIMARY KEY
);

INSERT INTO logs.syslog_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 logs.dashboard WITH (security_invoker = TRUE) AS
SELECT
    pgsql_user AS source,
    from_host,
    device_reported_time AS timestamp,
    coalesce(p.name, l.priority::text) AS priority,
    coalesce(f.name, l.facility::text) AS facility,
    message,
    l.priority as raw_log_level
FROM
    logs.syslog l
    LEFT JOIN logs.syslog_prio p ON l.priority = p.id
    LEFT JOIN logs.syslog_facility f ON l.facility = f.id
ORDER BY
    l.device_reported_time DESC;

-- 05 groups
CREATE ROLE log_producer WITH NOINHERIT NOLOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER NOBYPASSRLS;
GRANT USAGE ON SCHEMA logs TO log_producer;
GRANT INSERT ON logs.syslog TO log_producer;

CREATE ROLE log_reader WITH NOINHERIT NOLOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER NOBYPASSRLS;
GRANT USAGE ON SCHEMA logs TO log_reader;
GRANT SELECT ON logs.syslog TO log_reader;
GRANT SELECT ON logs.syslog_facility TO log_reader;
GRANT SELECT ON logs.syslog_prio TO log_reader;
GRANT SELECT ON logs.dashboard TO log_reader;

-- 06 actual users
CREATE USER logs WITH LOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER NOBYPASSRLS PASSWORD '...' IN GROUP log_reader;
CREATE USER example_producer WITH LOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER NOBYPASSRLS PASSWORD '...' IN GROUP log_producer;

-- 07 use monthly subpartitions
CREATE TABLE subpartitions.logs_syslog_p2026y05m PARTITION OF logs.syslog FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');