Search Postgresql Archives

Re: logging of application level user in audit trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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.rajesh@xxxxxxxxx> 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 <scottm@xxxxxxxxxxx> wrote:


On Tue, May 9, 2017 at 2:50 PM, Rajesh Mallah <mallah.rajesh@xxxxxxxxx> wrote:

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.









 



--
--
Scott Mead
Sr. Architect
OpenSCG



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux