Re: logging of application level user in audit trigger

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(dot)rajesh(at)gmail(dot)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 <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
>>
>
>