Re: logging of application level user in audit trigger

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(at)openscg(dot)com> wrote:

>
>
> 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
>