Thread: audit sql queries

Hello,

I am working on auditing interactions with a pgsql database using
php.  So my question is how can i go about obtaining the following
information by only being provided a pgsql query.  I have asked this
same question in a php group however i would also like to ask it here
encase there is a way to do this entirely through postgres.

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)

Any help with this would be greatly appreciated.

Thanks,
Dan

On 9/9/07, Dan99 <power919@gmail.com> wrote:
> Any help with this would be greatly appreciated.

http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE

Dan99 wrote:
> Hello,
>
> I am working on auditing interactions with a pgsql database using
> php.  So my question is how can i go about obtaining the following
> information by only being provided a pgsql query.  I have asked this
> same question in a php group however i would also like to ask it here
> encase there is a way to do this entirely through postgres.

http://pgfoundry.org/search/?type_of_search=soft&words=audit&Search=Search

--
   Richard Huxton
   Archonet Ltd

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