Re: audit table containing Select statements submitted

You could do this using Veil, http://pgfoundry.org/projects/veil/, or
something like it. A Veil access function,
http://veil.projects.postgresql.org/curdocs/overview-page.html, could be
used to record every row returned within a query to the user that
requested it. Note that this operates at the level of fetches and not
the resultset, meaning that queries like:

select stuff from a where exists (select 1 from b where....);

would record a fetch against b.

The basic trick is to replace table_that_you_want_audited with a view
that does something like:

select * from table_that_you_want_audited
where audit_this_fetch(row_identifier);

You will also need instead-of triggers for insert, update and delete of
the view.

__
Marc

On Fri, 2006-05-12 at 14:19 -0300, pgsql-hackers-owner(at)postgresql(dot)org
wrote:
> From: Hogan, James F. Jr.
> Sent: Thursday, May 04, 2006 12:46 PM
> To: 'pgsql-sql(at)postgresql(dot)org'; 'pgsql-general(at)postgresql(dot)org'
> Subject: audit table containing Select statements submitted
>
> No response from the pgsql-admin list so I though I would try cross
> posting here:
> pgsql-sql(at)postgresql(dot)org
> pgsql-general(at)postgresql(dot)org
>
>
>
> I just know I am not the first to try and do this
>
> Jim
>
> *********************
> Can anyone point me in a direction that may help me populate in real
> time a table that holds?
>
> Current_user
> Timestamp
> "The Select Statement Submitted by the User"
>
> I need to be able to determine who viewed what and when they viewed
> it.
>
> I have considered the fact that the result from SELECT yesterday may
> be
> different than the result set returned by the SAME SELECT statement
> today, but when used in conjunction with the INSERT, UPDATE, DELETE
> audit logging I have already created, the answers to who viewed, what
> and when would be readily available.
>
> I have been searching all morning and...
>
> The only thing I find on logging of Select statements is that the
> information can be held in the Log Files...if Logging is enabled.
>
> As I am only interested in the statements presented against certain
> tables...
>
> Turning on logging gives me more than I need or care to look through.
>
> I could write a script to parses the Log Files into a Database Table
> but
> would prefer to avoid enabling the file logging of statements if
> possible.
>
> Thanks for any reference or help you may be able to provide.
>
> Jim
>