<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hello MH,</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I have to correct myself in multiple ways.</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
First: <span style="background-color:rgb(255, 255, 255);display:inline !important">It looks like my tests were incomplete.<span> <span style="background-color:rgb(255, 255, 255);display:inline !important">The quotation marks were not interpreted as actual quotations.
In reality, postgres created tables with actual quotation characters in the name.</span></span></span></div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I was unable to find a good documentation, but it looks like database manufacturers handles quotes differently.</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
As a workaround I can suggest your original idea. Escape the dots from the IP address.</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
One solution could be to use the "sanitize" template function of syslog-ng. i.e.: table("$(sanitize -i . -r _ logs_${HOST})")</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Br,</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Laci</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> on behalf of Herda, Martin <Martin.Herda@fritz-edv.de><br>
<b>Sent:</b> Wednesday, July 14, 2021 09:06<br>
<b>To:</b> 'Syslog-ng users' and developers' mailing list' <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> Re: [syslog-ng] Logging to postgresql - error creating table with IP</font>
<div> </div>
</div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Calibri}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
a:link, span.x_MsoHyperlink
{color:blue;
text-decoration:underline}
p.x_xmsonormal, li.x_xmsonormal, div.x_xmsonormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
.x_MsoChpDefault
{font-size:10.0pt}
@page WordSection1
{margin:70.85pt 70.85pt 2.0cm 70.85pt}
div.x_WordSection1
{}
-->
</style>
<div lang="DE" link="blue" vlink="purple" style="word-wrap:break-word">
<div style="background-color:#FFEB9C; width:100%; border-style:solid; border-color:#9C6500; border-width:1pt; padding:2pt; font-size:10pt; line-height:12pt; font-family:'Calibri'; color:Black; text-align:left">
<span style="color:#9C6500; font-weight:bold">CAUTION:</span> 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.</div>
<br>
<div>
<div class="x_WordSection1">
<p class="x_MsoNormal"><span style="">Thanks for your reply,</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span style="">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:</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span style=""> CREATE TABLE _logs_192.168.75.130_</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span style="">Which will result in the same error as before:</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span style="">Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', user='syslog', database='db_syslog', error='6819553: FEHLER:
</span>ERROR: Syntax error at <span style="">».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))'</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span style="">Error creating table, giving up; table='_logs_192.168.75.130_'</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span style="">Br,</span></p>
<p class="x_MsoNormal"><span style="">MH</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<div>
<div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0cm 0cm 0cm">
<p class="x_MsoNormal"><b>Von:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu>
<b>Im Auftrag von </b>Laszlo Szemere (lszemere)<br>
<b>Gesendet:</b> Dienstag, 13. Juli 2021 19:24<br>
<b>An:</b> Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu><br>
<b>Betreff:</b> Re: [syslog-ng] Logging to postgresql - error creating table with IP</p>
</div>
</div>
<p class="x_MsoNormal"> </p>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">Hello,</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> as a quick and dirty test I found an online Postgress test env. <a href="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" originalsrc="https://extendsclass.com/postgresql-online.html" shash="C99eTwwqmslUhvxY5X4BRhU/y9nStZ16LWBeGN6uuO3er43kdBlZ4JpWqay7rEA1TDpwwQfHHGoJ+nD43y72JOHEkK0NqNn2CH4H8nViw6uz/kXO8rvY45f0TbYGLGHI6i5p2DHDjjSCTHbJathOv9uN9ISEnwzm/vv0dntSfJM=">https://extendsclass.com/postgresql-online.html</a></span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">I made some tests with the following commands:</span></p>
<blockquote>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">-- create table scientist (id integer, firstname varchar(100), lastname varchar(100));
</span></p>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">-- insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">-- insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">-- insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">-- select * from scientist;</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">
</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">DROP TABLE "logs_192.168.75.102";</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">CREATE TABLE "logs_192.168.75.102" (datetime varchar(16), host varchar(32), program varchar(20), pid varchar(10), message varchar(800));</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">select * from "logs_192.168.75.102";</span></p>
</div>
</blockquote>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">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?)</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">Syslog-ng do not apply any (unnecessary?) quotes to the table name: <a href="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" originalsrc="https://github.com/syslog-ng/syslog-ng/blob/master/modules/afsql/afsql.c#L551" shash="Gomj+st3D3K7y0wJgGQA9egEinOuQdIwx8qjdTG6y9fdFm+ZKe5+ty9DZh3WoUqiLR8bH9gUAmMkar8j312FTjKvcgCu6uW1NtCPtuWfJBCOB9EQeJthyU3kv/WOpyqCSeOEFPVtNoUMKX+MoHv6/ApfKVhAt19paWQlpmQ7uD0=">https://github.com/syslog-ng/syslog-ng/blob/master/modules/afsql/afsql.c#L551</a></span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black">It looks like, simply adding it to the template of the table option: </span><span style="color:#201F1E; background:white">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.)</span><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"></span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="color:#201F1E; background:white">Br,</span><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"></span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="color:#201F1E; background:white">Laci</span><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"></span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Arial",sans-serif; color:black"> </span></p>
</div>
</div>
<div class="x_MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="x_divRplyFwdMsg">
<p class="x_MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> syslog-ng <<a href="mailto:syslog-ng-bounces@lists.balabit.hu">syslog-ng-bounces@lists.balabit.hu</a>> on behalf of Herda, Martin <<a href="mailto:Martin.Herda@fritz-edv.de">Martin.Herda@fritz-edv.de</a>><br>
<b>Sent:</b> Tuesday, July 13, 2021 17:15<br>
<b>To:</b> 'syslog-ng@lists.balabit.hu' <<a href="mailto:syslog-ng@lists.balabit.hu">syslog-ng@lists.balabit.hu</a>><br>
<b>Subject:</b> [syslog-ng] Logging to postgresql - error creating table with IP</span>
</p>
<div>
<p class="x_MsoNormal"> </p>
</div>
</div>
<div>
<div style="border:solid #9C6500 1.0pt; padding:2.0pt 2.0pt 2.0pt 2.0pt">
<p class="x_MsoNormal" style="line-height:12.0pt; background:#FFEB9C"><b><span style="font-size:10.0pt; color:#9C6500">CAUTION:</span></b><span style="font-size:10.0pt; color:black"> 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.</span></p>
</div>
<p class="x_MsoNormal"> </p>
<div>
<div>
<p class="x_xmsonormal">Greetings to all,</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">Im on Debian 4.19.194-2 using syslog-ng 3.19.1</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">The .conf is set to send all incoming syslog packages into pg database:</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">destination d_pgsql {</p>
<p class="x_xmsonormal"> sql(type(pgsql)</p>
<p class="x_xmsonormal"> host("127.0.0.1") username("syslog")</p>
<p class="x_xmsonormal"> password("syslog") port("5432")</p>
<p class="x_xmsonormal"> database("db_syslog")</p>
<p class="x_xmsonormal"> table("logs_${HOST}")</p>
<p class="x_xmsonormal"> columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid varchar(10)", "message varchar(800)")</p>
<p class="x_xmsonormal"> values("$R_DATE", "${HOST}", "$PROGRAM", "$PID", "$MSG")</p>
<p class="x_xmsonormal"> indexes("datetime", "host", "program", "pid", "message"));</p>
<p class="x_xmsonormal">};</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">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:</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal" style="text-indent:35.4pt">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))'</p>
<p class="x_xmsonormal" style="text-indent:35.4pt"> </p>
<p class="x_xmsonormal" style="text-indent:35.4pt">Error creating table, giving up; table='logs_192.168.75.102'</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">The issue seems to be the dot of the IP address.</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">But dots in table names are possible AFAIK. Im able to manually create this table via HeidiSQL with no issues.</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">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?</p>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>