[syslog-ng] sql() and pgsql

[Balazs Scheidler]

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 at 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