In response to Gabriel Dinis <gabriel.dinis@xxxxxxxxxxxxxxxxxx>: > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
