Jim,

I am trying desperately to find a way to create an audit table to
log...who SELECTED what... as I am dealing with HR data and Health Care
Patient Data

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 are workarounds though.

One possibility, which I have used, is to not allow the application access to the base tables but instead force it to use Set Returning Functions. For example, instead of:

SELECT * FROM users NATURAL JOIN permissions WHERE name = 'Joe';

you would do: SELECT * FROM view_users_perms(user,'Joe');

The SRF then can easily log the select statement. This also provides you with the additional security of knowing that a user who hacks the database connection cannot launch ad-hoc queries which the application would not allow. I've used this approach lots for web applications for that reason.

Secondly, you can use the log. We've discussed on this list making it possible to log in CSV, XML or other database-digestable format. Unfortuantely, there doesn't appear to be much momentum behind that; I don't know that anyone is writing any code presently. Sponsorship?

In the immediate time, you can (others have done this) have the log stream to a parser which digests the log and writes out different files (database-loadable) depending on the logged activity recorded. I don't know of any OSS code which does this but you can probably get advice on the lists fromm people who have done it custom.

Good luck!

--Josh





---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

Reply via email to