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

I forgot to add the link to the article I've mentioned:

http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b

This is what I'd like to do on PostgreSQL,

Thanks,

Marcelo.

On 4/24/07, Marcelo de Moraes Serpa <celoserpa(at)gmail(dot)com> wrote:
>
> Hey guys,
>
> I needed to implement an audit trail engine and decided to do it on the
> database layer.
>
> I already have a basic but fully functional audit trail system implemented
> on my PostgreSQL 8.2 server. It has been done using PL/PGSQL
> and triggers and it works pretty well.
>
> 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.
>
> Mine is an web application - three tier. The app connects to the db using
> only one user and it has it's own authentication system and doesn't
> rely on the database for user management.
>
> 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. A
> relation table is then created to relate the user_id and application_id.
> When the user logs in, a new record is created in this table, and,
> as the application_id is available in the db enviroment to the procedural
> languages, we can then go to this table and finally get the
> user_id. Is there something like that in Postgres?
>
> Thanks in advance!
>
> Marcelo.