Thread: 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

In response to Gabriel Dinis <gabriel.dinis@vigiesolutions.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/

Gabriel,

Bill,

In response to "Massa, Harald Armin" <chef@ghum.de>:

> Bill,
>
> >
> >
> > 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:
> >
> > that is an interesting hack. Just googled up
>
> http://developer.postgresql.org/pgdocs/postgres/runtime-config-custom.html
>
> and now I am wondering, where did you get your confidence  that those
> variables are bound to sessions and NOT bound to server instances? My
> reading of that documentation let me stay in the assumption, those variables
> are the same across server instances....

huh?

Those variables are bound to database session.  Which means each PHP
process needs to set that variable shortly after establishing the
database connection, and before running any queries that require it.
Otherwise, PHP persistent connections my have values from previous
scripts, and non-persistent connections will have the values unset.

We set all the values we use in our session startup code, which always
runs at the beginning of script execution, and is guaranteed to know
the values because it's reading them from the session.

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

Thanks to all.
You are great!