Ok , i do see there is already the handling of application_name in the trigger , this is quite sufficient for my current needs.
regds mallah. On Wed, May 10, 2017 at 10:58 PM, Rajesh Mallah <mallah.raj...@gmail.com> wrote: > Hi Scott / List , > > Thanks for the response, > > the application_name usage seems to be more natural as it is something > inbuilt. > the audit trigger repo seems to have got update only in past 2 years . > > are there other more active projects doing the same thing ? > in my opinion (which may be flawed) pgaudit seems to be logging only with > no possibility > of knowing the old record. > > I think audit trail of tables is something that is frequently required , > it would be > nice if some inbuilt (core) feature comes someday in postgresql for > auditing > > > regds > mallah. > > > > > > On Wed, May 10, 2017 at 1:17 AM, Scott Mead <sco...@openscg.com> wrote: > >> >> >> 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 >> > >
