Re: audit sql queries

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Dan99 <power919(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: audit sql queries
Date: 2007-09-10 18:28:22
Message-ID: 1189448902.28581.9.camel@dogma.ljc.laika.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sun, 2007-09-09 at 23:13 +0000, Dan99 wrote:
> 1. table(s) affected
> 2. column(s) affected
> 3. action performed on data (ie. update, insert, select, delete)
> 4. previous data for each row and column effected (if data changed or
> deleted)
> 5. new data for each row and column effected (or existing data if data
> is being selected)

Auditing SELECTs is the trickiest; everything else can be done with
triggers.

For SELECT, you can use a view over a set-returning function, where the
function actually reads the underlying data, however that could be bad
for performance.

Alternatively, there is also kind of a trick where you can use an
uncorrelated subquery in a view so that the select has the side effect
of executing a function, like so:

CREATE VIEW mytable_audit AS SELECT * FROM mytable WHERE (SELECT
audit_func());

audit_func() should always return true, and should also record the other
information that you need. This strategy may perform better than using a
set-returning function.

Regards,
Jeff Davis

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Gustav Lindenberg 2007-09-10 18:36:01 NOT NULL Issue
Previous Message Darek Czarkowski 2007-09-10 18:13:36 Re: Postgresql 7.3 on Red Hat Enterprise 5 (Problem with SEMMNI, SEMMNS)