logging hook for database audit

From: Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: logging hook for database audit
Date: 2009-09-10 08:04:50
Message-ID: 20090910151226.8FC9.52131E4D@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


SQL logging is useful for database audit, but there is serveral problems:

* A large amount of logs degrade performance, especially syslog is used.
* Text or CSV logs instead of syslog could improve performance,
but DBAs favor syslog over text logs for integrated monitoring.
* Error messages are buried in SQL logs. SQL logs will be only needed
at auditing, but errors should be detected as soon as possible.
* Filtering logs in syslog is *too late* because we cannot avoid
inter-process communication in syslog(), that is performance bottleneck.

I think we'd better to support log filtering and splitting in PostgreSQL.
For the purpose, I'll submit to add "logging hook" in core, rather than
fixed features like log_min_messages-per-destination.

A logging hook and exporting logging functions
The logging hook is very simple; just adding send_message_to_server_log_hook
in elog.c. In addition, Logging functions are exported so that logging
hook can reuse them in the hook module. For example, pg_write_textlog(),
pg_write_csvlog(), pg_write_stderr(), pg_write_syslog() etc.

A new field "message_format" is added to ErrorData structure.
It references unlocalized message formats passed to errmsg().
It is useful if we want to filter logs by log type.
i.e, we can write LOG-level messages to syslog but avoid
only SQL logs if the format matchs to a specific pattern.

A sample contrib module using logging hook
A module using logging hook is provided as a contrib module.
Useful "fixed features" are provided by the module:

per-destination filtering level:
<module>.textlog_min_messages = log
<module>.csvlog_min_messages = log
<module>.syslog_min_messages = warning

per-destination line prefix:
<module>.textlog_line_prefix = '[%t][%p] '
<module>.syslog_line_prefix = ''

I also have a plan to provide a feature like "log-type specific filter".
It can be used to exclude SQL logs from syslog, but I have not completed
the detailed design.

Do you think the feature is useful and acceptable?
Comments welcome.

ITAGAKI Takahiro
NTT Open Source Software Center


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-09-10 08:24:09 Re: Ragged CSV import
Previous Message Maarten Foqué 2009-09-10 07:45:13 Re: corrupted double-linked list