[GENERAL] PHP Web Auditing and Authorization

[Gabriel Dinis]

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?

Thanks in advance.
Gabriel

Re: [GENERAL] PHP Web Auditing and Authorization

[Gabriel Dinis]

Thanks to all.
You are great!



On Wed, Nov 3, 2010 at 3:16 PM, Bill Moran  wrote:

> In response to "Massa, Harald Armin" :
>
> > 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/
>