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 |
Thread: | |
Lists: | pgsql-hackers |
Hello,
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.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
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 |