Postgres Pro
Downloads
Home   >   mailing lists

Re: PHP Web Auditing and Authorization - Mailing list pgsql-general

From Massa, Harald Armin
Subject Re: PHP Web Auditing and Authorization
Date
Msg-id AANLkTi=_qJD2hKnq7oJD0Jqg398JJgGq4VQJVzmyLq_Z@mail.gmail.com
Whole thread Raw
In response to PHP Web Auditing and Authorization  (Gabriel Dinis <gabriel.dinis@vigiesolutions.com>)
List pgsql-general
Tree view
Gabriel,

what you are looking for is also called "session variables". There are essentially 2 kind of receipes in the wild:

a) store those session information in temporary tables
b) store those session information in shared memory

version a) has the advantage that it can be done via plpgsql, and the disadvantage of polluting the temporary-table-space

version b) has the advantage of keeping the temporary table space tidy; and the disadvantage that it needs a language with access to shared memory; which is most effectivly done via C. Somewhere there is a code example for it; google for postgresql session variables.

I am using version a) for some time; in the following my functions set_user(integer) and get_user() returns integer, which set and get a user_id.

Translating them to set / get a text value is left as an exercise :) Additional to get_user() -> it returns 0 (not NULL) when no user is set_userd()


CREATE OR REPLACE FUNCTION set_user(myid_user integer)
  RETURNS integer AS
$BODY$
    BEGIN
perform relname from pg_class
            where relname = 'icke_tmp'
              and case when has_schema_privilege(relnamespace, 'USAGE')
                    then pg_table_is_visible(oid) else false end;
        if not found then
            create temporary table icke_tmp (
                id_user integer
            );
else
  delete from icke_tmp;
end if;
insert into icke_tmp values (myid_user);
  RETURN 0;
  END;
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
  
  
CREATE OR REPLACE FUNCTION get_user()
  RETURNS integer AS
$BODY$
declare
ergebnis int4;
    BEGIN
perform relname from pg_class
            where relname = 'icke_tmp'
              and case when has_schema_privilege(relnamespace, 'USAGE')
                    then pg_table_is_visible(oid) else false end;
  if not found then
    return 0;
  else
    select id_user from icke_tmp into ergebnis;
  end if;
 
  if not found then 
    ergebnis:=0;
  end if;
  RETURN ergebnis;
  END;
 $BODY$
  LANGUAGE plpgsql STABLE
  COST 100;
  
Best wishes,

Harald

On Wed, Nov 3, 2010 at 13:04, Gabriel Dinis <gabriel.dinis@vigiesolutions.com> wrote:
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








--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: PHP Web Auditing and Authorization
Next
From: "Massa, Harald Armin"
Date:
Subject: Re: PHP Web Auditing and Authorization