Re: PHP Web Auditing and Authorization

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(dot)dinis(at)vigiesolutions(dot)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 *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
>
>
>
>
>
>

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

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare