Although there are documented limitations for these systems , but
I would like to mention and seek suggestion on a limitation that I feel is biggest .
It is very a common design pattern in web-applications that the same database
user is shared for making database changes by different "logged in users" of the web application.
I feel the core of audit is all about "who" , "when" and "what" .
In the current audit trigger the "who" is essentially the ROLE which is the actor of the trigger , but in most scenarios the user associated with the web-application session is the one that is seeked.
In one of my past projects I passed the web-user to the trigger by setting a postgres custom variable during the database connection and reading it inside the trigger
and storing it in the audit log table.
This is a good method, and one of the best for just straight auditing. The other trick I've seen is to use the 'application_name' field. Developers would issue:
SET application_name = "app_user:app_name';
This can be read from pg_stat_activity.application_name. I believe you'd be able to read that in a procedure with 'show application_name'; and, you can see it live in pg_stat_activity as well.
select application_name, count(*)
FROM pg_stat_activity
GROUP by application_name;
You'd be able to see each user/app pair and the number of sessions that were using to the DB at a given time.
--Scott
I am curious how others deal with the same issue , is there better or more inbuilt solutions
to store the application level user in the audit trail records.