Re: User action accounting

On Mar 30, 2010, at 8:03 AM, Joshua Berry wrote:

> Hello All,
>
> I have a few PHP/Clarion based applications that don't currently track who created and modified records. I'd like to be able to track all user and timestamp pairs for INSERT/UPDATEs by way of triggers.
>
> The problem is that I currently use the same role name for each instance of the application, so "current_user" is not particularly helpful. So I have a few ideas that I wanted to bounce off the experts here:
> 1. Should I use seperate PG roles for each user? Is there a way of permitting user names queried against a RADIUS server to inherit a role allowing the needed permissions (trusting that the RADIUS server is secured) and allowing the requested name to be used without having to maintain two lists of accounts?
> 2. Should I stay with using the same role for the application, but somehow store a per session variable that would have the user's login name and be accessible by the triggers?
>
> Anyhow, the goal is to be able to note which of the 40 users created/modified records in the backend. I'm sure that this has been solved by each person and has been asked a million times... I'm just not sure where to begin with Google/postgresql.net queries! Please feel free to reply with a helpful search query or URL.

I create a one-row temporary table with information about the current user in it at the beginning of each connection and audit triggers that need to know the current application user use that table. (There's also an underlying non-temporary table so that stuff doesn't break during ad-hoc updates).

I'm not sure whether that's a good approach, but it seems to work well and means the database doesn't need to be aware of the users accessing it (which is more than just authentication, but also creating and revoking users).

The main downside is that you can't use it with any sort of connection pooling.

Cheers,
Steve