Re: Audit-trail engine: getting the application's layer user_id - Mailing list pgsql-general

* Manuel Sugawara <masm@fciencias.unam.mx> [20070425 00:17]:
> I solved the problem using a C program and keeping all the information
> in the database, that means, users, passwords and ``sessions''. Each
> time a user opens a session the system register it in a table that
> looks like:

This looks very useful, thanks!

Do you know if there is a way to set such a variable for a transaction
only?

I thought it might work by creating a temporary table, which will
overlay a non-temporary table with the same name, so there could be a
permanent table with the default value and a temporary table with the
transaction specific user_id:


test=# create table current_application_user (user_id int);
CREATE TABLE
test=# insert into current_application_user values (NULL); -- the default
INSERT 0 1
test=# select user_id from current_application_user ;
 user_id
---------

(1 row)
test=# begin;
BEGIN
test=# create temporary table current_application_user (user_id int) on commit drop;
CREATE TABLE
test=# insert into current_application_user values (1); -- the current application user
INSERT 0 1
test=# select user_id from current_application_user ;
 user_id
---------
       1
(1 row)

test=# commit;
COMMIT
test=# select user_id from current_application_user ;
 user_id
---------

(1 row)


But is it possible to create a database view that accesses the value
of that temporary table when present and otherwise the value of the
default table? I tried the following, but apparently the view
definition will contain a reference to the public schema as soon as a
temporary table with the same name is present:


test=# create table some_content (body text, owner_id int);
CREATE TABLE
test=# create view some_content_restricted as select * from some_content where owner_id=(select user_id from
current_application_user); 
CREATE VIEW
test=# \d some_content_restricted
View "public.some_content_restricted"
  Column  |  Type   | Modifiers
----------+---------+-----------
 body     | text    |
 owner_id | integer |
View definition:
 SELECT some_content.body, some_content.owner_id
   FROM some_content
  WHERE some_content.owner_id = (( SELECT current_application_user.user_id
           FROM current_application_user));

test=# begin;
BEGIN
test=# create temporary table current_application_user (user_id int) on commit drop;
CREATE TABLE
test=# \d some_content_restricted
View "public.some_content_restricted"
  Column  |  Type   | Modifiers
----------+---------+-----------
 body     | text    |
 owner_id | integer |
View definition:
 SELECT some_content.body, some_content.owner_id
   FROM some_content
  WHERE some_content.owner_id = (( SELECT current_application_user.user_id
           FROM public.current_application_user));



So the view will reference the original table and not the temporary
table. Is there a way to achieve this kind of transaction local
setting? A transaction based solution would give more security in a
situation where a web app server uses a connection pool and you can
not guarantee 100% that your reset code is called properly at request
init.


tia, Til