On Wed, Nov 3, 2010 at 1:04 PM, Gabriel Dinis
<gabriel.dinis@vigiesolutions.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 user information I'm getting, in this case "phpuser".
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