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.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 *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

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/

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....

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

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

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!

On Wed, Nov 3, 2010 at 3:16 PM, Bill Moran <wmoran@potentialtech.com> wrote:

On Wed, Nov 3, 2010 at 1:04 PM, 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.
(...)
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?

I have a different approach than what people are suggesting here.

I have a first audit table that receives an entry for each page loaded by a
user. So each time I initialize my database connection, I create an entry in
that table. That table has a SERIAL column.

For each action that needs auditing, I have a trigger. That trigger calls
CURRVAL('serial_sequence') and stores that in the second audit table. This
way you can find out afterwards who did the action. The nice thing about
this approach is that you can see which actions were done in the same page:
it gives context to some operations that would be difficult to understand
otherwise.

Be sure to set autocommit off and commit or abort at the end of each page,
otherwise it will mix things up (especially when you use persistent
connections)!

Afterwards I clean up/aggregate unimportant actions (like pages that only do
SELECTs) so to keep the impact on database size low.

Kind regards,
Mathieu