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

Wed Jul 14 10:57:42 UTC 2021

Hello MH,

I have to correct myself in multiple ways.

First: It looks like my tests were incomplete. The quotation marks were not interpreted as actual quotations. In reality, postgres created tables with actual quotation characters in the name.
I was unable to find a good documentation, but it looks like database manufacturers handles quotes differently.

Second: The underscores in the final table name we observed are coming from the libdbi driver. I assume, deep investigation of the library may give us some option to avoid it. But I did not take time to investigate.

As a workaround I can suggest your original idea. Escape the dots from the IP address.
One solution could be to use the "sanitize" template function of syslog-ng. i.e.: table("$(sanitize -i . -r _ logs_${HOST})")

Br,
Laci

________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> on behalf of Herda, Martin <Martin.Herda at fritz-edv.de>
Sent: Wednesday, July 14, 2021 09:06
To: 'Syslog-ng users' and developers' mailing list' <syslog-ng at lists.balabit.hu>
Subject: Re: [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.

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<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fextendsclass.com%2Fpostgresql-online.html&data=04%7C01%7Claszlo.szemere%40oneidentity.com%7C114308af9a4c4119b06108d94695f1d5%7C91c369b51c9e439c989c1867ec606603%7C0%7C1%7C637618432097752228%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=5X6Z89y7NETs9CVyTJZNgz9%2BcD2IlHXxzwRDlaUwVNs%3D&reserved=0>

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<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fsyslog-ng%2Fsyslog-ng%2Fblob%2Fmaster%2Fmodules%2Fafsql%2Fafsql.c%23L551&data=04%7C01%7Claszlo.szemere%40oneidentity.com%7C114308af9a4c4119b06108d94695f1d5%7C91c369b51c9e439c989c1867ec606603%7C0%7C1%7C637618432097762224%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=G2%2F90rAl3wR1qy8P%2BoqXHyz%2FNbCxPbrsyxxxgGFK6ug%3D&reserved=0>

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/d4f49824/attachment-0001.html>