Audit-trail engine: getting the application's layer user_id - Mailing list pgsql-general

From Marcelo de Moraes Serpa
Subject Audit-trail engine: getting the application's layer user_id
Date
Msg-id 1e5bcefd0704240656q3cb73ccfw9ba0f080546fc90a@mail.gmail.com
Whole thread Raw
Responses Re: Audit-trail engine: getting the application's layer user_id
Re: Audit-trail engine: getting the application's layer user_id
Re: Audit-trail engine: getting the application's layer user_id
List pgsql-general
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.

pgsql-general by date:

Previous
From: Jeff Lanzarotta
Date:
Subject: Re: How does server handle clients that disconnect ungracefully?
Next
From: Magnus Hagander
Date:
Subject: Re: How does server handle clients that disconnect ungracefully?