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

[Herda, Martin]

Thanks for your reply,

I did indeed apply the single quotes and also tried with escaped double quotes. In either case the SQL statment string gets malformed to this:

                CREATE TABLE _logs_192.168.75.130_

Which will result in the same error as before:

Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', user='syslog', database='db_syslog', error='6819553: FEHLER:  ERROR:  Syntax error at >.168<\x0aLINE 1: CREATE TABLE _logs_192.168.75.130_ (datetime varchar(16), ho...\x0a                              ^\x0a', query='CREATE TABLE _logs_192.168.75.130_ (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.130_'

Br,
MH

Von: syslog-ng <syslog-ng-bounces at lists.balabit.hu> Im Auftrag von Laszlo Szemere (lszemere)
Gesendet: Dienstag, 13. Juli 2021 19:24
An: Syslog-ng users' and developers' mailing list <syslog-ng at lists.balabit.hu>
Betreff: Re: [syslog-ng] Logging to postgresql - error creating table with IP

Hello,
 as a quick and dirty test I found an online Postgress test env. https://extendsclass.com/postgresql-online.html

I made some tests with the following commands:
-- create table scientist (id integer, firstname varchar(100), lastname varchar(100));
-- insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');
-- insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');
-- insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');
-- select * from scientist;

DROP TABLE "logs_192.168.75.102";
CREATE TABLE "logs_192.168.75.102" (datetime varchar(16), host varchar(32), program varchar(20), pid varchar(10), message varchar(800));
insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');
insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');
insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');
select * from "logs_192.168.75.102";


As it turned out, Postgres indeed accepts dots in the table name, however the SQL statement tourns out to be syntactically invalid without quoting it. (Please confirm: Did you apply quotes during the manual test?)


Syslog-ng do not apply any (unnecessary?) quotes to the table name: https://github.com/syslog-ng/syslog-ng/blob/master/modules/afsql/afsql.c#L551
It looks like, simply adding it to the template of the table option: table("logs_${HOST}") --> table("'logs_${HOST}'") should do the trick. However, I cannot test it right now. Sorry. (Please inform me if the problem persists, and I will investigate it further when I am back to my machine.)


Br,
Laci



________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu<mailto:syslog-ng-bounces at lists.balabit.hu>> on behalf of Herda, Martin <Martin.Herda at fritz-edv.de<mailto:Martin.Herda at fritz-edv.de>>
Sent: Tuesday, July 13, 2021 17:15
To: 'syslog-ng at lists.balabit.hu' <syslog-ng at lists.balabit.hu<mailto:syslog-ng at lists.balabit.hu>>
Subject: [syslog-ng] Logging to postgresql - error creating table with IP

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.


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?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20210714/62edb548/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3221 bytes
Desc: not available
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20210714/62edb548/attachment-0001.bin>