auditing in postgresql

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: auditing in postgresql
Date: 2007-08-30 20:05:25
Message-ID: 1188504325.6199.48.camel@dogma.ljc.laika.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

I'm sure this has been discussed before, so I'd appreciate links to
discussions, etc.

Regards,
Jeff Davis

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Browne 2007-08-30 20:39:37 Re: PostgreSQL Conference Fall 2007
Previous Message Tom Lane 2007-08-30 20:04:11 Re: Reliable and fast money transaction design