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?
