Re: PHP Web Auditing and Authorization

On Wed, Nov 3, 2010 at 1:04 PM, Gabriel Dinis <
gabriel(dot)dinis(at)vigiesolutions(dot)com> wrote:

> Dear all,
>
> Imagine I have two users "Maria" and "Ana" using a PHP site.
> There is a common Postgres user "phpuser" for both.
> I'm creating audit tables to track the actions made by each PHP site user.
> (...)
> Everything seems to wok fine except the *use*r information I'm getting, in
> this case "*phpuse*r".
> I would like to have not the postgres user but the PHP site user (*Maria
> or Ana*).
>
> How can I pass the PHP site user (Maria or Ana) into Postgres in a clever
> way?
>
>
> I have done several web searches and found nothing for Postgres. I found a
> solution for oracle:
> http://www.oracle.com/technetwork/articles/dsl/php-web-auditing-171451.html
> *
> They use a "client identifier" feature.* Is there a similar way to do this
> in Postgres?
>
>
I have a different approach than what people are suggesting here.

I have a first audit table that receives an entry for each page loaded by a
user. So each time I initialize my database connection, I create an entry in
that table. That table has a SERIAL column.

For each action that needs auditing, I have a trigger. That trigger calls
CURRVAL('serial_sequence') and stores that in the second audit table. This
way you can find out afterwards who did the action. The nice thing about
this approach is that you can see which actions were done in the same page:
it gives context to some operations that would be difficult to understand
otherwise.

Be sure to set autocommit off and commit or abort at the end of each page,
otherwise it will mix things up (especially when you use persistent
connections)!

Afterwards I clean up/aggregate unimportant actions (like pages that only do
SELECTs) so to keep the impact on database size low.

Kind regards,
Mathieu