Re: PHP Web Auditing and Authorization

In response to Gabriel Dinis <gabriel(dot)dinis(at)vigiesolutions(dot)com>:

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

There are probably better ways, but ...

We got this same kind of thing working by using PostgreSQL env variables.
First, set custom_variable_classes in your postgresql.conf. You can then
use the SET command to set variables of that class, and use them in your
functions:

postgresql.conf:
custom_variable_classes='myapp'

In your code, run the following query as part of you session instantiation:
SET myapp.login_name = 'username';

Now, in your stored procedure, you can reference myapp.login_name to get
the current user name.

This is probably abusing the hell out of custom_variable_classes, but it's
working well for us.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/