Re: Audit-trail engine: getting the application's layer user_id

Marcelo de Moraes Serpa wrote:
> Here's what I need to do: Somehow save the user_id of the **application**
> user who have done the update/delete action to the log row.

> I've read an article on IBM's developer site which teaches how to do just
> that (get the application's user id and save it the audit
> row) using what they call the APPLICATION_ID which is an unique ID that DB2
> assigns to the app when it connects to the database.

Afraid there's nothing quite like that for PG.

There's two ways I've used.

1. Have a separate user (role in 8.2) for each application user (it can
be something like u_app_0001 etc). This is do-able for a few hundred
users certainly, and should be fine for a few thousand. Not sure about
hundreds of thousands though.

2. Simulate a "session variable" by having one of the procedural
languages store state for you (e.g. pl/tcl or pl/perl). Call
set_app_user(...) on application connect and call get_app_user() when
you need to find the current app user.

I've done both, but prefer the first myself.

--
Richard Huxton
Archonet Ltd