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
beable 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
particularlyhelpful.  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
serverto inherit a role allowing the needed permissions (trusting that the RADIUS server is secured) and allowing the
requestedname 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
havethe 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
thishas been solved by each person and has been asked a million times... I'm just not sure where to begin with
Google/postgresql.netqueries! 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
andaudit triggers that need to know the current application user use that table. (There's also an underlying
non-temporarytable 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
ofthe 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