Bug 928511

Copying parts of the original report here, as it is now marked as 'private'.

Version-Release number of selected component (if applicable):
  rsyslog-pgsql-5.8.10-6.el6.x86_64
  rsyslog-5.8.10-6.el6.x86_64
  postgresql-server-8.4.13-1.el6_3.x86_64
  postgresql-8.4.13-1.el6_3.x86_64

Steps to Reproduce:
1. Install postgresql packages.
2. Install rsyslogd and rsyslogd-pgsql packages.
3. Connect to the postgresql server as user postgres and execute the contents of the /usr/share/doc/rsyslog-pgsql-5.8.10/createDB.sql file either by copy and paste or usinghte -f option.

Actual results:
$ psql -f /tmp/createDB.sql
psql:/tmp/createDB.sql:1: ERROR:  syntax error at or near "'Syslog'"
LINE 1: CREATE DATABASE 'Syslog' WITH ENCODING 'SQL_ASCII';
                        ^
psql:/tmp/createDB.sql:2: \connect: FATAL:  database "Syslog" does not exist

Expected results:
$ psql -f /tmp/createDB.sql
CREATE DATABASE
psql (8.4.13)
You are now connected to database "Syslog".
psql:/tmp/createDB.sql:29: NOTICE:  CREATE TABLE will create implicit sequence "systemevents_id_seq" for serial column "systemevents.id"
psql:/tmp/createDB.sql:29: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "systemevents_pkey" for table "systemevents"
CREATE TABLE
psql:/tmp/createDB.sql:37: NOTICE:  CREATE TABLE will create implicit sequence "systemeventsproperties_id_seq" for serial column "systemeventsproperties.id"
psql:/tmp/createDB.sql:37: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "systemeventsproperties_pkey" for table "systemeventsproperties"
CREATE TABLE

Created attachment 717688 [details]
proposed patch

This is the change proposed by the reporter.
I can confirm the issue and that the patch resolves it.

Still having problems using both upper and lowercase in the file.

The database tables and columns are created with lowercase only characters. I added double quotes around the table and column names and recreated the database using the file. The database now creates as expected.

# diff /usr/share/doc/rsyslog-pgsql-5.8.10/createDB.sql ~/createDB.sql 
1c1
< CREATE DATABASE 'Syslog' WITH ENCODING 'SQL_ASCII';
---
> CREATE DATABASE "Syslog" WITH ENCODING 'SQL_ASCII' TEMPLATE template0;
3c3
< CREATE TABLE SystemEvents
---
> CREATE TABLE "SystemEvents"
5,28c5,28
<         ID serial not null primary key,
<         CustomerID bigint,
<         ReceivedAt timestamp without time zone NULL,
<         DeviceReportedTime timestamp without time zone NULL,
<         Facility smallint NULL,
<         Priority smallint NULL,
<         FromHost varchar(60) NULL,
<         Message text,
<         NTSeverity int NULL,
<         Importance int NULL,
<         EventSource varchar(60),
<         EventUser varchar(60) NULL,
<         EventCategory int NULL,
<         EventID int NULL,
<         EventBinaryData text NULL,
<         MaxAvailable int NULL,
<         CurrUsage int NULL,
<         MinUsage int NULL,
<         MaxUsage int NULL,
<         InfoUnitID int NULL ,
<         SysLogTag varchar(60),
<         EventLogType varchar(60),
<         GenericFileName VarChar(60),
<         SystemID int NULL
---
>         "ID" serial not null primary key,
>         "CustomerID" bigint,
>         "ReceivedAt" timestamp without time zone NULL,
>         "DeviceReportedTime" timestamp without time zone NULL,
>         "Facility" smallint NULL,
>         "Priority" smallint NULL,
>         "FromHost" varchar(60) NULL,
>         "Message" text,
>         "NTSeverity" int NULL,
>         "Importance" int NULL,
>         "EventSource" varchar(60),
>         "EventUser" varchar(60) NULL,
>         "EventCategory" int NULL,
>         "EventID" int NULL,
>         "EventBinaryData" text NULL,
>         "MaxAvailable" int NULL,
>         "CurrUsage" int NULL,
>         "MinUsage" int NULL,
>         "MaxUsage" int NULL,
>         "InfoUnitID" int NULL ,
>         "SysLogTag" varchar(60),
>         "EventLogType" varchar(60),
>         "GenericFileName" VarChar(60),
>         "SystemID" int NULL
31c31
< CREATE TABLE SystemEventsProperties
---
> CREATE TABLE "SystemEventsProperties"
33,36c33,36
<         ID serial not null primary key,
<         SystemEventID int NULL ,
<         ParamName varchar(255) NULL ,
<         ParamValue text NULL
---
>         "ID" serial not null primary key,
>         "SystemEventID" int NULL ,
>         "ParamName" varchar(255) NULL ,
>         "ParamValue" text NULL

After configuring rsyslog to write to the database, I see from the database logs that the table names during insert statements are being converted to lowercase. It appears a simple fix to the file is not going to resolve all issues.

Database log:

ERROR:  relation "systemevents" does not exist at character 13
STATEMENT:  insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (' (root) CMD (/usr/lib64/sa/sa1 1 1)', 9, 'hostname', 6, '2013-03-28 16:50:01', '2013-03-28 16:50:01', 1, 'CROND[24453]:')


rsyslog configuration (hostname, dbuser, and dbpassword are filled in to protect systeminformation in this public bug only):

# Allows logging to PostgreSQL database.
$ModLoad ompgsql

$template PSQL,""

#### RULES ####
*.*	:ompgsql:hostname,Syslog,dbuser,dbpassword

Please ignore the $template PSQL,"" line in the previous comment.

I had to create my own template to to log. I now have logs going to the PostgreSQL database. Here is what I had to do after adding quotes to the createDB.sql file.

/etc/rsyslog.d/log2db.conf:

=== CUT ===
# Allows logging to PostgreSQL database.
$ModLoad ompgsql

$template PSQL,"INSERT INTO \"SystemEvents\" (\"Message\", \"Facility\", \"FromH
ost\", \"Priority\", \"DeviceReportedTime\", \"ReceivedAt\", \"InfoUnitID\", \"S
ysLogTag\") values ('%msg%', %syslogfacility%, '%fromhost%', %pri%, '%timereport
ed:::date-pgsql%', '%timegenerated:::date-pgsql%', %iut%, '%programname%')",stdS
QL

#### RULES ####

#*.info;mail.none;authpriv.none;cron.none                /var/log/messages
*.*	:ompgsql:hostname,Syslog,dbuser,dbpassword;PSQL

=== CUT ===

After I created my user in the database and ran the file to create the Syslog database, I had to grant privileges to the user. 

# GRANT SELECT, INSERT ON “SystemEvents”, “SystemSventsProperties” TO dbuser;
GRANT

# GRANT USAGE ON SEQUENCE "SystemEvents_ID_seq","SystemEventsProperties_ID_seq" TO dbuser;
GRANT


I had to create the template in the configuration file. I believe having the template in the code fixed would be a lot quicker.

Thanks,

(In reply to comment #6)
> I had to create the template in the configuration file. I believe having the
> template in the code fixed would be a lot quicker.

John,

there actually is a hardcoded template for ompgsql. Unfortunately the documentation for this is pretty much non-existent.
It is defined like this:

static uchar template_StdPgSQLFmt[] = "\"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-pgsql%', '%timegenerated:::date-pgsql%', %iut%, '%syslogtag%')\",STDSQL";

It can't be referenced directly, but it is used as the default template for ompgsql when no other template is specified.
It looks very similar to the one you came up with. Does it work for you?

Tomas,

Sorry for taking so long to get back to you. I noticed I was not in the cc list for updates.

When I tried to insert log entries into the database without specifying my template, it failed. The log files indicated that some of the uppercase formatting was getting lost. I currently don't have any of those logs available, but I can get generate some for you.

I will change my template statement to reflect the fields used by the default one and let you know how that works.

Thanks,

Tomas,

if I provide an escape and double quote around the table name and table columns, that works perfect. But I have to double quote anything that contains both upper and lower case, otherwise it all gets translated down to lowercase on the calls.

Thanks,

Still hasn't been accepted by the upstream. I'm hesitant to change this if it isn't merged as this can cause problems when using upstream templates or scripts.