Обсуждение: Select Column Auditing/Logging

Hello,

I've tried searching around the net for a feature that would allow me to audit to a table or log select queries against
acertain column in a table.  In fact, I haven't been able to find a way to log selects to a specific table, just
loggingall queries. 

It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and TRUNCATE through the use of triggers.  But SELECT
triggersare unsupported (it does suggest perhaps using RULES). 

Has anyone attempted to implement conditional select based logging, even through RULES?  I think Oracle has a feature
called"Fine Grained Auditing" that has a ton of features that I don't really need, just what's described above in
particular.

P.S. The log would just need the user, time, and query.

TIA,

Jeremy Brown

On Wed, 2009-07-22 at 14:41 -0700, Jeff Davis wrote:
> On Wed, 2009-07-22 at 12:04 -0700, Jeremy Brown wrote:
> > It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and
> > TRUNCATE through the use of triggers.  But SELECT triggers are
> > unsupported (it does suggest perhaps using RULES).
>
> One thing you can do is use a set-returning function that, as a side
> effect, records what happened.

I should warn you not to record it in a transactional way: it must go
out to an external service, or go back to postgresql using something
like dblink. Otherwise, someone could do:

BEGIN;
SELECT ...;
ROLLBACK;

and whatever you logged would be gone.

Regards,
    Jeff Davis

On Wed, 2009-07-22 at 12:04 -0700, Jeremy Brown wrote:
> It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and
> TRUNCATE through the use of triggers.  But SELECT triggers are
> unsupported (it does suggest perhaps using RULES).

One thing you can do is use a set-returning function that, as a side
effect, records what happened. It's not ideal because it hides a lot of
information from the optimizer, but if your table is small enough it
would work.

Regards,
    Jeff Davis

On Wed, Jul 22, 2009 at 02:41:41PM -0700, Jeff Davis wrote:
> On Wed, 2009-07-22 at 12:04 -0700, Jeremy Brown wrote:
> > It seems that PostgreSQL can audit INSERT, UPDATE, DELETE, and
> > TRUNCATE through the use of triggers.  But SELECT triggers are
> > unsupported (it does suggest perhaps using RULES).
>
> One thing you can do is use a set-returning function that, as a side
> effect, records what happened. It's not ideal because it hides a lot of
> information from the optimizer, but if your table is small enough it
> would work.

Would something like this be more amenable to optimization:

  CREATE FUNCTION tbl_auditor() RETURNS BOOLEAN IMMUTABLE AS $$
    logquery;
    RETURN TRUE; $$;

  CREATE VIEW tbl_view AS
    SELECT * FROM tbl WHERE tbl_auditor();

--
  Sam  http://samason.me.uk/