Re: PHP Web Auditing and Authorization

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.

I have used the following code:

CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$

   BEGIN
        --
        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
       -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN

           INSERT INTO MinUser_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
           RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);

           RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);

           RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
   END;
$usr_audit$ LANGUAGE plpgsql;

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?

Thanks in advance.
Gabriel