Re: auditing in postgresql

On 8/30/07, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> I know already it's possible to audit changes to data in postgresql
> tables using triggers, etc.
>
> A lot of other things can also be logged using the logging mechanism,
> such as permission errors (by logging all error messages), etc.
>
> However, there are also other things that would be useful to audit,
> such as data being _read_. For instance, if it's normal for a certain
> user to read data once per month (running a report, etc), and that same
> user reads the same data at an unexpected time, that may reveal a
> security problem.
>
> I could wrap the table in a SRF that emits a LOG, but that is not very
> elegant, and the SRF may not perform well because the query could not
> be optimized the same way. It would also be nice if there was a more
> unified and complete way of doing this stuff, rather than trying to
> separate the audit logs from the rest of the logs after the fact. And
> there is also no way to audit reads, for example, on all objects within
> a schema or tablespace. And the logging mechanism doesn't have a lot of
> conditionals, so it's hard to log only statements by privileged users.

Well, a SRF may be unsuitable for various reasons, but maybe views are
better. I really like views more and more lately (better than
functions as a rule, I think).

you have some query, select yadda
create view log_yadda as
select yadda
union all select null, null, null from log_func();

This is a nearly free invocation but not perfect...a limit clause can
prevent log_func from executing in some cases for example. I'm
looking for a better way to express this.

merlin