Hello, First thanks to Bazsi and co. for years of great work on syslog-ng. I've used it off and on for some time and have always found it to be well implemented and supported. I've experimented a bit with rsyslog since some distros have been making it their new default and found syslog-ng to be easier to work with and more reliable for my tasks. I'm been playing around with v3.0.8 and the sql() / pgsql output driver to insert log data into a specific table on a specific schema. I couldn't figure out how to specify the schema and maybe its just not supoprted. For instance, if the following is defined: database: foo schema: bar table: baz but the PostgreSQL search path for the user doesn't include bar, then syslog-ng will attempt to create a table in the default schema (usually 'public'). I tried setting the table name to "bar.baz", but that just created a table named bar_baz in the default schema. I did a quick check in the source code and didn't see where this was occurring, maybe its a function of a third party library? If schema support was available that would be nice. For posterity sake and maybe a FAQ, the work around is to alter the search_path for the user (e.g. ALTER ROLE user SET search_path = bar,public;). On a related note, it might be nice to have an option to avoid creating a table if it does not already exist. If nothing else, this helps limit the permissions granted to the syslog-ng db user, which would be appreciated. Likewise, it might also be nice to have an option that can disable checking if the table exists (the "SELECT * FROM %s WHERE 0=1" query) to further lock down user permissions. John
"bar.baz" is the proper way of doing this but was not handled properly in the syslog-ng code. I filed a bug report on this and the fix has been incorporated into 3.2 (https://bugzilla.balabit.com/show_bug.cgi?id=73 item number 2). So, you have 3 options 1) you can download the patch on that bug and manually build the fix into your code and build it yourself 2) add an alias so it knows which schema that table is in 3) wait for 3.2 Sent: Tuesday, July 27, 2010 12:28:03 PM From: John Kristoff <jtk@cymru.com> To: syslog-ng@lists.balabit.hu Subject: [syslog-ng] sql() and pgsql
Hello,
First thanks to Bazsi and co. for years of great work on syslog-ng. I've used it off and on for some time and have always found it to be well implemented and supported. I've experimented a bit with rsyslog since some distros have been making it their new default and found syslog-ng to be easier to work with and more reliable for my tasks.
I'm been playing around with v3.0.8 and the sql() / pgsql output driver to insert log data into a specific table on a specific schema. I couldn't figure out how to specify the schema and maybe its just not supoprted. For instance, if the following is defined:
database: foo schema: bar table: baz
but the PostgreSQL search path for the user doesn't include bar, then syslog-ng will attempt to create a table in the default schema (usually 'public'). I tried setting the table name to "bar.baz", but that just created a table named bar_baz in the default schema. I did a quick check in the source code and didn't see where this was occurring, maybe its a function of a third party library?
If schema support was available that would be nice. For posterity sake and maybe a FAQ, the work around is to alter the search_path for the user (e.g. ALTER ROLE user SET search_path = bar,public;).
On a related note, it might be nice to have an option to avoid creating a table if it does not already exist. If nothing else, this helps limit the permissions granted to the syslog-ng db user, which would be appreciated. Likewise, it might also be nice to have an option that can disable checking if the table exists (the "SELECT * FROM %s WHERE 0=1" query) to further lock down user permissions.
John ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
On Tue, 2010-07-27 at 12:36 -0600, syslogng@feystorm.net wrote:
"bar.baz" is the proper way of doing this but was not handled properly in the syslog-ng code. I filed a bug report on this and the fix has been incorporated into 3.2 (https://bugzilla.balabit.com/show_bug.cgi?id=73 item number 2). So, you have 3 options 1) you can download the patch on that bug and manually build the fix into your code and build it yourself 2) add an alias so it knows which schema that table is in 3) wait for 3.2
perhaps try 3.2 :) Your patch also adds "session_statements()" option that makes it possible to add SQL statements executeed before doing anything else. This may also be of use here. -- Bazsi
On Tue, 2010-07-27 at 13:28 -0500, John Kristoff wrote:
Hello,
First thanks to Bazsi and co. for years of great work on syslog-ng. I've used it off and on for some time and have always found it to be well implemented and supported. I've experimented a bit with rsyslog since some distros have been making it their new default and found syslog-ng to be easier to work with and more reliable for my tasks.
Nice to hear. I always appreciate positive feedback, so you made my day :)
I'm been playing around with v3.0.8 and the sql() / pgsql output driver to insert log data into a specific table on a specific schema. I couldn't figure out how to specify the schema and maybe its just not supoprted. For instance, if the following is defined:
database: foo schema: bar table: baz
but the PostgreSQL search path for the user doesn't include bar, then syslog-ng will attempt to create a table in the default schema (usually 'public'). I tried setting the table name to "bar.baz", but that just created a table named bar_baz in the default schema. I did a quick check in the source code and didn't see where this was occurring, maybe its a function of a third party library?
If schema support was available that would be nice. For posterity sake and maybe a FAQ, the work around is to alter the search_path for the user (e.g. ALTER ROLE user SET search_path = bar,public;).
On a related note, it might be nice to have an option to avoid creating a table if it does not already exist. If nothing else, this helps limit the permissions granted to the syslog-ng db user, which would be appreciated. Likewise, it might also be nice to have an option that can disable checking if the table exists (the "SELECT * FROM %s WHERE 0=1" query) to further lock down user permissions.
well, there's a create_dirs() option for files, so perhaps a similar one could make sense in the SQL case as well. As an incentive to try 3.2, here's a compile-tested, but trivial patch in 3.2 that implements this option: commit f059e7202c8709966e0559273ebd2f54d9d38508 Author: Balazs Scheidler <bazsi@balabit.hu> Date: Wed Jul 28 11:28:08 2010 +0200 afsql: added "dont-create-tables" flag This patch adds a dont-create-tables flag to the SQL destination to inhibit automatic table creation. Usage: sql(... flags(dont-create-tables)); The same flags can be used to pass "explicit-commits" to enable explicit commits, which increases syslog-ng by a lot. -- Bazsi
participants (3)
-
Balazs Scheidler
-
John Kristoff
-
syslogng＠feystorm.net