Re: User action accounting

On 3/30/2010 11:13 AM, Joshua Berry wrote:
>
> On Tue, Mar 30, 2010 at 10:46 AM, Andy Colson <andy@squeakycode.net
> <mailto:andy@squeakycode.net>> wrote:
>
>     When your app/users connect to the db, do they connect as the same
>     user, or each with a different username?
>
>
> The application instances each connect to the database with the same
> username. The application currently uses an ODBC connection which has
> hard coded username values. If each user has their own workstation, this
> would be easy, but I want to be able to specify the username when the
> application begins. I'm not worried about the security aspect; I just
> want to present users with an easy way to specify who they are to aid in
> tracking.
>
>     Do you have your own "users" table?
>
>
> There is a "users" table currently used for another purpose, but it
> could be reused/extended.
>
> If I go the route of keeping the same role for each application
> instance, it would be great if I could avoid having to pass the username
> into each query and instead have a per-session or per-connection
> variable that the trigger could access. Sounds easy, but I've never
> tried it before and things not usually as easy as they seem.
>
> Regards,
>
> -Joshua

I ask because there is a CURRENT_UESR you can use in a trigger.  It is
who you connect to the db as.  Which in your case all users would have
the same name.  But.. there is also a set role:

http://www.postgresql.org/docs/8.4/static/sql-set-role.html

So after you connect you could fire off a "set role bob", and the
triggers would use 'bob' as current_user.

Or something like that.  You'd also have to create all the users on the
pg side (create role...).  I have not done this, its just "in theory it
should work".

-Andy