Re: logging hook for database audit

Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> I have been working with josh tolley that changes some infrastructure
> around this, with one of the future goals to be able to implement just
> this kind of features. I need to merge some changes from josh, and
> will then try to post a wip patch as soon as possible. Please look at
> this one before you start working on this - probably theoverlap will
> be very large.

Thanks, it will be helpful.

At the moment, my idea is adding special sqlcodes for some LOG messages.

ERRCODE_SUCCESSFUL_AUTOVACUUM = "00001"
ERRCODE_SUCCESSFUL_CHECKPOINTS = "00002"
...
ereport(LOG, errcode(ERRCODE_SUCCESSFUL_AUTOVACUUM),
errmsg("autovacuum messages")));

New GUC parameters xxx_category is a list of categories.
Only logs listed here is written for each log. The default
values are '*' (all categories). Messages without sqlcodes
are put into "others" category.

{stderr|csvlog|syslog}_category = [ * | autovacuum | ... | others ]

This approach is less impact to the codes and just utilizes unused
sqlcodes of LOG or lower level messages. However, it has some limitations:

* We cannot categorize ERROR or higher level messages
because they've used sqlcodes as original purposes.
* The number of categories are restricted to 30 or so, because category
filtera are managed with bits32. Categorized messages should use
sqlcodes between "00001" and "0000N".
* We need to add sqlcodes for each LOGs. It would be hard work, but
needed anyway if we will support any kinds of per-category log filters.

Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center