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 >
