Re: audit table containing Select statements submitted - Mailing list pgsql-hackers

From Tom Lane
Subject Re: audit table containing Select statements submitted
Date
Msg-id 24141.1147474332@sss.pgh.pa.us
Whole thread Raw
In response to Re: audit table containing Select statements submitted  (Josh Berkus <josh@agliodbs.com>)
List pgsql-hackers
Josh Berkus <josh@agliodbs.com> writes:
> Well, the issue with doing this by trigger or RULE is that unlike 
> updates and deletes, SELECTS do *not* guarentee single execution.  For 
> example, if the table is on the loop end of a nested loop, it could be 
> fired hundreds or thousands of times.  This is the reason why we 
> recommend against trying to build a trigger/RULE for SELECT auditing.

There's an even more significant reason why you can't rely on
within-the-database logging if you want to track SELECTs.  Imagine
you have a trigger or whatever that tries to log what I do.
I just
begin;select something-I-shouldn't-know;rollback;

I just covered all my tracks quite effectively, because the ROLLBACK
canceled any and all side effects of my transaction.  But (unlike
if I'd rolled back an update) I still know what I found out.

So, if you want to log accesses to info as opposed to updates, you
really have to use something outside the SQL universe.  I concur
with Josh's suggestion to rely on reading the postmaster log.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: DH_check return value test correct?
Next
From: Tom Lane
Date:
Subject: Re: audit table containing Select statements submitted