Re: User action accounting

On 3/30/2010 10: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 <http://postgresql.net>
> queries! Please feel free to reply with a helpful search query or URL.
>
> Kind Regards,
> -Joshua
>
> Joshua Berry


When your app/users connect to the db, do they connect as the same user,
or each with a different username?

Do you have your own "users" table?

-Andy