On Tue, May 9, 2017 at 2:50 PM, Rajesh Mallah <mallah.raj...@gmail.com> wrote:
> Hi , > > I am referring to audit trigger as described in > > https://wiki.postgresql.org/wiki/Audit_trigger_91plus OR > https://wiki.postgresql.org/wiki/Audit_trigger > > 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. > > Regds > mallah. > > ( https://www.redgrape.tech ) > > > > > > > > > > -- -- Scott Mead Sr. Architect *OpenSCG <http://openscg.com>* http://openscg.com
