Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Hi Manuel,

Just replying to say a big thank you ... I compiled the C extension with the
code you , did all the necessary logic and finally solved it. Thank you very
much for your help!

Thank you also to all the other who helped me!

Marcelo.

On 4/24/07, Manuel Sugawara [EMAIL PROTECTED] wrote:


Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hey guys,

 Mine is an web application - three tier. The app connects to the db
using
 only one user and it has it's own authentication system and doesn't
 rely on the database for user management.

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:

auth.session
 Tabla «auth.session»
Columna|Tipo |
Modificadores

---+-+
id| integer | not null default nextval(('
auth.session_sid'::text)::regclass)
skey  | text| not null
agent_id  | integer | not null
host  | text| not null default
'localhost'::text
start_time| timestamp without time zone | not null default now()
end_time  | timestamp without time zone |
su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES
auth.session(id)

Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:


  session_id int not null default current_session_id() references
auth.session(id),


Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:

#include postgres.h

#include stdio.h
#include string.h
#include time.h
#include unistd.h
#include fmgr.h

static int session_id = 0;
static int session_id_is_set = false;

Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);

Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}

Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}

Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}

Hope that helps.

Regards,
Manuel.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Tilmann Singer]

* Marcelo de Moraes Serpa [EMAIL PROTECTED] [20070509 21:14]:
 Just replying to say a big thank you ... 
 I compiled the C extension with the
 code you , did all the necessary logic and finally solved it. Thank you very
 much for your help!

I second that!

I finally settled with the simpler solution which was to create a
temporary table instead of using a global variable in a C function
which seemed reasonably fast enough for us and doesn't introduce the
maintainance dependency on a custom C module.


Til

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Hey guys, I really appreaciate your help, thank you very much for your time.

@Manuel: What a comprehensive solution! Thanks a lot for that :)
@Joris: That would be a simpler althernative, I will try it out too!

Marcelo.

On 4/24/07, Joris Dobbelsteen [EMAIL PROTECTED] wrote:




 --
*From:* [EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED] *On Behalf Of *Marcelo de Moraes Serpa
*Sent:* dinsdag 24 april 2007 21:06
*To:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] Audit-trail engine: getting the application's
layer user_id

 Thank you for the replies.

@Richard: I've thought about having one DB user for each APP user.
However, a coworker told me that it would infeasible to do that on the web
enviroment, specifically for J2EE where a DB connection pool is used, so I
gave up on that.

As Richard mentioned, he has done it.
Remember, for the pool you DO NOT setup a new connection every time but
you can certainly utilize the pool. The trick is the postgresql idea of the
Role-Based Access Control (RBAC) implementation. I.e. you can just do a
SET LOCAL ROLE rolename.
After transaction commit or rollback, or execution of SET LOCAL ROLE
NONE or RESET ROLE you will have your original role (own user) again. This
should work just fine.

See also: http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html

 @Jorge: Is this connection id you say equivalent to the applicationid
mentioned in the ibm db2 article? If so, how could I get this data through
my application?

On 4/24/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote:

 Thank you for the replies.

 @Richard: I've thought about having one DB user for each APP user.
 However, a coworker told me that it would infeasible to do that on the web
 enviroment, specifically for J2EE where a DB connection pool is used, so I
 gave up on that.

 @Jorge: Is this connection id you say equivalent to the
 applicationid mentioned in the ibm db2 article? If so, how could I get
 this data through my application?

 Marcelo.

 On 4/24/07, Jorge Godoy [EMAIL PROTECTED] wrote:
 
  Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:
 
   I forgot to add the link to the article I've mentioned:
  
  
  
http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b
  
   This is what I'd like to do on PostgreSQL,
 
  So, translating it to a simpler example:
 
  You want that your function gets the connection ID it is using and
  ties it to your current user ID at your application and then have
  all your tables use a trigger to retrieve the user name from the
  auxiliar table that maps connection ID - user, right?
 
  That's what's in that page: a UDF (user defined function) named
  getapplicationid() that will return the user login / name / whatever
  and
  triggers.
 
  What is preventing you from writing that?  What is your doubt with
  regards to how create that feature on your database?
 
 
 
  --
  Jorge Godoy  [EMAIL PROTECTED] 
 

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Hi Manuel, just a quick question: What C libraries do I need to compile this
function? Or better: Where can I find a reference manual about db stored
procedures written in C for PostgreSQL?

Thanks!

On 4/24/07, Manuel Sugawara [EMAIL PROTECTED] wrote:


Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hey guys,

 Mine is an web application - three tier. The app connects to the db
using
 only one user and it has it's own authentication system and doesn't
 rely on the database for user management.

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:

auth.session
 Tabla «auth.session»
Columna|Tipo |
Modificadores

---+-+
id| integer | not null default nextval(('
auth.session_sid'::text)::regclass)
skey  | text| not null
agent_id  | integer | not null
host  | text| not null default
'localhost'::text
start_time| timestamp without time zone | not null default now()
end_time  | timestamp without time zone |
su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES
auth.session(id)

Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:


  session_id int not null default current_session_id() references
auth.session(id),


Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:

#include postgres.h

#include stdio.h
#include string.h
#include time.h
#include unistd.h
#include fmgr.h

static int session_id = 0;
static int session_id_is_set = false;

Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);

Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}

Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}

Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}

Hope that helps.

Regards,
Manuel.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

I'm sorry Manuel, but after some time trying to fully understand your
approach, I think I really don't have the required elements to do so.

How do you pass your application's usename to this table? Or you don't keep
the username at all?

Could you give a more concrete example? Maybe showing the spots on your
application where you called these functions and why?


At least, for the C shared library compiling on Windows, I think I'm
half-way done - I've found a really useful comment on a PostgreSQL manual
page teaching how to compile PostgreSQL modules under Windows - you can see
it here: http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html

Thank you again.

Marcelo.


On 4/24/07, Manuel Sugawara [EMAIL PROTECTED] wrote:


Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hey guys,

 Mine is an web application - three tier. The app connects to the db
using
 only one user and it has it's own authentication system and doesn't
 rely on the database for user management.

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:

auth.session
 Tabla «auth.session»
Columna|Tipo |
Modificadores

---+-+
id| integer | not null default nextval(('
auth.session_sid'::text)::regclass)
skey  | text| not null
agent_id  | integer | not null
host  | text| not null default
'localhost'::text
start_time| timestamp without time zone | not null default now()
end_time  | timestamp without time zone |
su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES
auth.session(id)

Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:


  session_id int not null default current_session_id() references
auth.session(id),


Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:

#include postgres.h

#include stdio.h
#include string.h
#include time.h
#include unistd.h
#include fmgr.h

static int session_id = 0;
static int session_id_is_set = false;

Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);

Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}

Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}

Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}

Hope that helps.

Regards,
Manuel.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Tilmann Singer]

* Manuel Sugawara [EMAIL PROTECTED] [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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Manuel Sugawara]

Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 I'm sorry Manuel, but after some time trying to fully understand your
 approach, I think I really don't have the required elements to do so.

 How do you pass your application's usename to this table? Or you don't keep
 the username at all?

 Could you give a more concrete example? Maybe showing the spots on your
 application where you called these functions and why?

I keep my user-names (agents) in the database along with a hashed
version of their passphrases, when a user logs in I have a procedure
written in plpgsql that checks the provided passphrase against the one
in the database and if they match the user is granted a session, and
the a corresponding row inserted in the session table. I keep the user
information (the session id and a key) in the session of the web tier
(I'm using java servlets but the concept is the same for other
frameworks). Now, each time the user sends a request I do more or less
the following:

   retrieve from the web session the id of the session in the database
   request a fresh connection from the pool
   check if the session is still alive (if not throw an exception)
   set the session id of the user
   handle the user request
   reset the session id
   return the connection to the pool

The implementation details are left to the reader ;-). Hope that helps

Regards,
Manuel.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Manuel Sugawara]

Tilmann Singer [EMAIL PROTECTED] writes:

 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.

Nop, you do the reset part *at the end* of the request cycle:

   set session id
   handle request
   reset session id

So, you can guarantee that the id of the session is reset and the
connection properly disposed to the pool.

Regards,
Manuel.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Tilmann Singer]

* Manuel Sugawara [EMAIL PROTECTED] [20070425 17:14]:
  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.
 
 Nop, you do the reset part *at the end* of the request cycle:
 
set session id
handle request
reset session id
 
 So, you can guarantee that the id of the session is reset and the
 connection properly disposed to the pool.

If you have full confidence in having control over the request cycle
in your app server, yes.

However, I'm using Ruby on Rails' ActiveRecord and am not 100%
familiar with the way it uses connection pooling and request setup, so
I would feel more safe if I could set something like a temporary table
with ON COMMIT DROP to be sure my app user id will only be visible
from within the transaction that it was set in, and that a value set
can not leak to another web request that reuses the previous db
connection.

Rephrasing the question:

Can I define a view which references a table in a way so that it will
use a temporary table of the same name if it exists, otherwise the
permanent table with that name?



Til

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Manuel Sugawara]

Tilmann Singer [EMAIL PROTECTED] writes:

 Can I define a view which references a table in a way so that it will
 use a temporary table of the same name if it exists, otherwise the
 permanent table with that name?

I think you can use a plpgsql function with execute. For instance, if
the name of your temp table is current_user_id the function will be
something like:

create function get_current_user_id() returns int as $$
declare
  v_rec record;
  v_user int;
  v_query text;
begin
  v_query := 'SELECT user_id FROM current_user_id';
  for v_rec in execute v_query loop
v_user := v_rec.user_id;
  end loop;
  return v_user;
end;   
$$ language plpgsql;


Untested but that's the idea, you need to use execute to avoid the
caching of the plan. You might also want to control what happens when
the table does not exist and that can be done handling the
corresponding exception. Check the docs for the details.

Regards,
Manuel.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Tilmann Singer]

* Manuel Sugawara [EMAIL PROTECTED] [20070425 17:57]:
 I think you can use a plpgsql function with execute. For instance, if
 the name of your temp table is current_user_id the function will be
 something like:
 
 create function get_current_user_id() returns int as $$
 declare
   v_rec record;
   v_user int;
   v_query text;
 begin
   v_query := 'SELECT user_id FROM current_user_id';
   for v_rec in execute v_query loop
 v_user := v_rec.user_id;
   end loop;
   return v_user;
 end;   
 $$ language plpgsql;
 
 Untested but that's the idea, you need to use execute to avoid the
 caching of the plan. You might also want to control what happens when
 the table does not exist and that can be done handling the
 corresponding exception. Check the docs for the details.

Excellent, that works exactly as needed! I will rely on the permanent
table being there always to provide the default value if no temporary
table has been created.


Thanks! Til

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Hi Manuel,

each time the user sends a request I do more or less

the following:

Could a trigger be used to implement this ? Or are you doing this from the

application layer? My problem is that, like Til, I don't have full control
over my request cycle as I'm over a very high-level framework (Actually it
is an data-oriented application generator, called GeneXus).

Thanks,

Marcelo.


On 4/25/07, Manuel Sugawara [EMAIL PROTECTED] wrote:


Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 I'm sorry Manuel, but after some time trying to fully understand your
 approach, I think I really don't have the required elements to do so.

 How do you pass your application's usename to this table? Or you don't
keep
 the username at all?

 Could you give a more concrete example? Maybe showing the spots on your
 application where you called these functions and why?

I keep my user-names (agents) in the database along with a hashed
version of their passphrases, when a user logs in I have a procedure
written in plpgsql that checks the provided passphrase against the one
in the database and if they match the user is granted a session, and
the a corresponding row inserted in the session table. I keep the user
information (the session id and a key) in the session of the web tier
(I'm using java servlets but the concept is the same for other
frameworks). Now, each time the user sends a request I do more or less
the following:

   retrieve from the web session the id of the session in the database
   request a fresh connection from the pool
   check if the session is still alive (if not throw an exception)
   set the session id of the user
   handle the user request
   reset the session id
   return the connection to the pool

The implementation details are left to the reader ;-). Hope that helps

Regards,
Manuel.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Manuel Sugawara]

Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hi Manuel,

 each time the user sends a request I do more or less the following:

 Could a trigger be used to implement this ? Or are you doing this from the
 application layer? 

I'm doing it form the application layer and I don't think it can be
done in the database layer, how the trigger will figure out which user
is doing the query?, It's the same problem you are trying to solve!

 My problem is that, like Til, I don't have full control over my
 request cycle as I'm over a very high-level framework (Actually it
 is an data-oriented application generator, called GeneXus).

Maybe you can use Til approach with temporal tables.

Regards,
Manuel.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

I'm doing it form the application layer and I don't think it can be
done in the database layer, how the trigger will figure out which user
is doing the query?, It's the same problem you are trying to solve!



Duh! That is what happens when you start having high levels of caffeinne in
your blood and haven't had a good night sleep... thanks for putting me on
the track again.

I will study Tim's approach more. This thing got more complicated than I
thought it would be. At least I'm learning more about PostgreSQL internal
architecture.

Thank you for your help.

Marcelo.

On 4/25/07, Manuel Sugawara [EMAIL PROTECTED] wrote:


Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hi Manuel,

 each time the user sends a request I do more or less the following:

 Could a trigger be used to implement this ? Or are you doing this from
the
 application layer?

I'm doing it form the application layer and I don't think it can be
done in the database layer, how the trigger will figure out which user
is doing the query?, It's the same problem you are trying to solve!

 My problem is that, like Til, I don't have full control over my
 request cycle as I'm over a very high-level framework (Actually it
 is an data-oriented application generator, called GeneXus).

Maybe you can use Til approach with temporal tables.

Regards,
Manuel.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Hello all,

I know I may be asking too much, but I have a very limited C/C++ (as well as
PostgreSQL internal architecture) knowledge. I've tried compiling the C
source code Manuel sent as a PostgreSQL loadable module on Visual Studio
.NET 2003 (C++) without success (lots of missing identifiers, int Datum
redefinition and other things I didn't really understood). The comments in
the Postgres 8.0 manual didn't help much.

If anyone could put me on the right direction on how to write/build C/C++
PostgreSQL on the Windows platform (specifically Windows XP) I would be
grateful as I really need this thing working as soon as possible.

Thanks,

Marcelo.

On 4/25/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote:


I'm doing it form the application layer and I don't think it can be
 done in the database layer, how the trigger will figure out which user
 is doing the query?, It's the same problem you are trying to solve!


Duh! That is what happens when you start having high levels of caffeinne
in your blood and haven't had a good night sleep... thanks for putting me on
the track again.

I will study Tim's approach more. This thing got more complicated than I
thought it would be. At least I'm learning more about PostgreSQL internal
architecture.

Thank you for your help.

Marcelo.

On 4/25/07, Manuel Sugawara [EMAIL PROTECTED] wrote:

 Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

  Hi Manuel,
 
  each time the user sends a request I do more or less the following:
 
  Could a trigger be used to implement this ? Or are you doing this from
 the
  application layer?

 I'm doing it form the application layer and I don't think it can be
 done in the database layer, how the trigger will figure out which user
 is doing the query?, It's the same problem you are trying to solve!

  My problem is that, like Til, I don't have full control over my
  request cycle as I'm over a very high-level framework (Actually it
  is an data-oriented application generator, called GeneXus).

 Maybe you can use Til approach with temporal tables.

 Regards,
 Manuel.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Jorge Godoy]

Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hello all,

 I know I may be asking too much, but I have a very limited C/C++ (as well as
 PostgreSQL internal architecture) knowledge. I've tried compiling the C
 source code Manuel sent as a PostgreSQL loadable module on Visual Studio
 .NET 2003 (C++) without success (lots of missing identifiers, int Datum
 redefinition and other things I didn't really understood). The comments in
 the Postgres 8.0 manual didn't help much.

 If anyone could put me on the right direction on how to write/build C/C++
 PostgreSQL on the Windows platform (specifically Windows XP) I would be
 grateful as I really need this thing working as soon as possible.

 Thanks,

 Marcelo.

If you really need this, shouldn't you consider hiring someone that
works professionaly with PostgreSQL?

They'd certainly do the work for you and you'll accomplish your target
on the due date.

-- 
Jorge Godoy  [EMAIL PROTECTED]

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

I forgot to add the link to the article I've mentioned:

http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b

This is what I'd like to do on PostgreSQL,

Thanks,

Marcelo.

On 4/24/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote:


Hey guys,

I needed to implement an audit trail engine and decided to do it on the
database layer.

I already have a basic but fully functional audit trail system implemented
on my PostgreSQL 8.2 server. It has been done using PL/PGSQL
and triggers and it works pretty well.

Here's what I need to do: Somehow save the user_id of the **application**
user who have done the update/delete action to the log row.

Mine is an web application - three tier. The app connects to the db using
only one user and it has it's own authentication system and doesn't
rely on the database for user management.

I've read an article on IBM's developer site which teaches how to do just
that (get the application's user id and save it the audit
row) using what they call the APPLICATION_ID which is an unique ID that
DB2 assigns to the app when it connects to the database. A
relation table is then created to relate the user_id and application_id.
When the user logs in, a new record is created in this table, and,
as the application_id is available in the db enviroment to the procedural
languages, we can then go to this table and finally get the
user_id. Is there something like that in Postgres?

Thanks in advance!

Marcelo.

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Richard Huxton]

Marcelo de Moraes Serpa wrote:

Here's what I need to do: Somehow save the user_id of the **application**
user who have done the update/delete action to the log row.



I've read an article on IBM's developer site which teaches how to do just
that (get the application's user id and save it the audit
row) using what they call the APPLICATION_ID which is an unique ID that DB2
assigns to the app when it connects to the database. 


Afraid there's nothing quite like that for PG.

There's two ways I've used.

1. Have a separate user (role in 8.2) for each application user (it can 
be something like u_app_0001 etc). This is do-able for a few hundred 
users certainly, and should be fine for a few thousand. Not sure about 
hundreds of thousands though.


2. Simulate a session variable by having one of the procedural 
languages store state for you (e.g. pl/tcl or pl/perl). Call 
set_app_user(...) on application connect and call get_app_user() when 
you need to find the current app user.


I've done both, but prefer the first myself.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Jorge Godoy]

Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 I forgot to add the link to the article I've mentioned:

 http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b

 This is what I'd like to do on PostgreSQL,

So, translating it to a simpler example:

You want that your function gets the connection ID it is using and
ties it to your current user ID at your application and then have
all your tables use a trigger to retrieve the user name from the
auxiliar table that maps connection ID - user, right?

That's what's in that page: a UDF (user defined function) named
getapplicationid() that will return the user login / name / whatever and
triggers.

What is preventing you from writing that?  What is your doubt with
regards to how create that feature on your database?



-- 
Jorge Godoy  [EMAIL PROTECTED]

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Thank you for the replies.

@Richard: I've thought about having one DB user for each APP user. However,
a coworker told me that it would infeasible to do that on the web
enviroment, specifically for J2EE where a DB connection pool is used, so I
gave up on that.

@Jorge: Is this connection id you say equivalent to the applicationid
mentioned in the ibm db2 article? If so, how could I get this data through
my application?

On 4/24/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote:


Thank you for the replies.

@Richard: I've thought about having one DB user for each APP user.
However, a coworker told me that it would infeasible to do that on the web
enviroment, specifically for J2EE where a DB connection pool is used, so I
gave up on that.

@Jorge: Is this connection id you say equivalent to the applicationid
mentioned in the ibm db2 article? If so, how could I get this data through
my application?

Marcelo.

On 4/24/07, Jorge Godoy [EMAIL PROTECTED] wrote:

 Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

  I forgot to add the link to the article I've mentioned:
 
 
 
http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b
 
  This is what I'd like to do on PostgreSQL,

 So, translating it to a simpler example:

 You want that your function gets the connection ID it is using and
 ties it to your current user ID at your application and then have
 all your tables use a trigger to retrieve the user name from the
 auxiliar table that maps connection ID - user, right?

 That's what's in that page: a UDF (user defined function) named
 getapplicationid() that will return the user login / name / whatever and
 triggers.

 What is preventing you from writing that?  What is your doubt with
 regards to how create that feature on your database?



 --
 Jorge Godoy  [EMAIL PROTECTED]

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Jorge Godoy]

Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 @Richard: I've thought about having one DB user for each APP user. However,
 a coworker told me that it would infeasible to do that on the web
 enviroment, specifically for J2EE where a DB connection pool is used, so I
 gave up on that.

Why?  You can always SET SESSION AUTH TO user before anything.  It
works with transaction pools and you can even enhance your application
security by a second layer inside the database itself.  DB passwords
don't need to be known by users since they won't connect directly and
your connection will be made with a priviledged user. 

 @Jorge: Is this connection id you say equivalent to the applicationid
 mentioned in the ibm db2 article? If so, how could I get this data through
 my application?

It all depends on what you want to make it unique.  I believe that a
simple process will be dedicated to each connection, so if you get its
PID you'll be done.  Each time a user accesses the database you insert
or update a record with his PID and then you make your triggers work
with that.  There will be more logic, but you got the idea.

Another option is using the transaction ID or something that always
change.

You just need a unique value that lasts for a connection and isn't
shared with any other user connected at the same time you are.

There are a lot of functions that you can use.  You just have to be sure
when you want the information and what information you need.  Take a
look at the ones available in pg_catalog for your specific PostgreSQL
version. 

-- 
Jorge Godoy  [EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Manuel Sugawara]

Marcelo de Moraes Serpa [EMAIL PROTECTED] writes:

 Hey guys,

 Mine is an web application - three tier. The app connects to the db using
 only one user and it has it's own authentication system and doesn't
 rely on the database for user management.

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:

 auth.session
 Tabla «auth.session»
Columna|Tipo | 
Modificadores
---+-+
 id| integer | not null default 
nextval(('auth.session_sid'::text)::regclass)
 skey  | text| not null
 agent_id  | integer | not null
 host  | text| not null default 
'localhost'::text
 start_time| timestamp without time zone | not null default now()
 end_time  | timestamp without time zone |
 su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES 
auth.session(id)

Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:


  session_id int not null default current_session_id() references 
auth.session(id),


Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:

#include postgres.h

#include stdio.h
#include string.h
#include time.h
#include unistd.h
#include fmgr.h

static int session_id = 0;
static int session_id_is_set = false;

Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);

Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}

Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}

Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}

Hope that helps.

Regards,
Manuel.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Joris Dobbelsteen]

 





From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Marcelo de
Moraes Serpa
Sent: dinsdag 24 april 2007 21:06
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Audit-trail engine: getting the
application's layer user_id


Thank you for the replies.

@Richard: I've thought about having one DB user for each APP
user. However, a coworker told me that it would infeasible to do that on
the web enviroment, specifically for J2EE where a DB connection pool is
used, so I gave up on that.  

As Richard mentioned, he has done it.
Remember, for the pool you DO NOT setup a new connection every time but
you can certainly utilize the pool. The trick is the postgresql idea of
the Role-Based Access Control (RBAC) implementation. I.e. you can just
do a SET LOCAL ROLE rolename.
After transaction commit or rollback, or execution of SET LOCAL ROLE
NONE or RESET ROLE you will have your original role (own user) again.
This should work just fine.
 
See also:
http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html

 @Jorge: Is this connection id you say equivalent to the
applicationid mentioned in the ibm db2 article? If so, how could I get
this data through my application?


On 4/24/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote:


Thank you for the replies.

@Richard: I've thought about having one DB user for each
APP user. However, a coworker told me that it would infeasible to do
that on the web enviroment, specifically for J2EE where a DB connection
pool is used, so I gave up on that. 

@Jorge: Is this connection id you say equivalent to
the applicationid mentioned in the ibm db2 article? If so, how could I
get this data through my application?

Marcelo. 



On 4/24/07, Jorge Godoy [EMAIL PROTECTED] wrote: 

Marcelo de Moraes Serpa [EMAIL PROTECTED]
writes:

 I forgot to add the link to the article I've
mentioned: 


http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze
/0302stolze.html#section2b

 This is what I'd like to do on PostgreSQL,

So, translating it to a simpler example:

You want that your function gets the
connection ID it is using and
ties it to your current user ID at your
application and then have
all your tables use a trigger to retrieve
the user name from the
auxiliar table that maps connection ID -
user, right? 

That's what's in that page: a UDF (user defined
function) named
getapplicationid() that will return the user
login / name / whatever and
triggers.

What is preventing you from writing that?  What
is your doubt with 
regards to how create that feature on your
database?



--
Jorge Godoy  [EMAIL PROTECTED]