[syslog-ng] Logging to postgresql - error creating table with IP

13 Jul 2021

      Greetings to all,

Im on Debian 4.19.194-2 using syslog-ng 3.19.1

The .conf is set to send all incoming syslog packages into pg database:

destination d_pgsql {
                sql(type(pgsql)
                host("127.0.0.1") username("syslog")
                password("syslog") port("5432")
                database("db_syslog")
                table("logs_${HOST}")
                columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid varchar(10)", "message varchar(800)")
                values("$R_DATE", "${HOST}", "$PROGRAM", "$PID", "$MSG")
                indexes("datetime", "host", "program", "pid", "message"));
};

When there is a hostname available, the table is created successfully. When hostname can not be resolved, IP Address is used instead. But in this case syslog-ng is unable to create a new table:

syslog-ng[499]: Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', user='syslog', database='db_syslog', error='6819553: ERROR:  Syntax error at >.168<\x0aLINE 1: CREATE TABLE logs_192.168.75.102 (datetime varchar(16), ho...\x0a                              ^\x0a', query='CREATE TABLE logs_192.168.75.102 (datetime varchar(16), host varchar(32), program varchar(20), pid varchar(10), message varchar(800))'

Error creating table, giving up; table='logs_192.168.75.102'

The issue seems to be the dot of the IP address.

But dots in table names are possible AFAIK. Im able to manually create this table via HeidiSQL with no issues.

Does the CREATE TABLE statement need to be escaped in some way? Or is there a way to replace the dots from the $HOST macro with underscores?

[syslog-ng] Logging to postgresql - error creating table with IP

Herda, Martin