Re: logging of application level user in audit trigger

On Tue, May 9, 2017 at 2:50 PM, Rajesh Mallah <mallah(dot)rajesh(at)gmail(dot)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