Re: Audit-trail engine inner-workings

Hey Ted, thanks for the reply,

In respect of web application architecture, I'm fully aware of session
persistence mechanisms (I work as a developer of web apps after all).

What I really would like to know is the inner-workings of the set_session_id
and current_session_id as well as reset_session_id C functions.

Our company uses a generator called GeneXus which is high level modeling
enviroment that deployes to a variety of languages Java being one of them.
Being a generator, we don't have much information about the way it generates
the code becouse 1) it is a proprietary generator, 2) the code generated is
propositally cryptic (don't make any sense at all, with weird var and method
names and so on).

However, I was given the mission to implement an audit-trail engine to this
application. The discussion I sent in the first message of this thread was
really helpful, the C functions sent by Manual were crucial. They work fine,
**but** I don't have much knowledge in C nor PostgreSQL internal
architecture **and** we need to know certain inner details on how this
mechanism works in order to take some important decisions.

I know that this PostgreSQL C module has a static var that in turn keeps the
integer set by the function "set_session_id" - but is this var global to the
server's service ? Does PostgreSQL mantain one "instance" of this var per
requested connection ? I would like to know how this works.

Take the following scenario:
- user enters the web app;
- web app takes a reference to a connection from the db connection pool;
- web app does some db operation

When the app takes a reference to a connection and does the operation, just
after that, in the application, I set the session id. Then the db operation
is performed and the audit stored procedure is ran. The audit-trail engine
performs its work and logs the operation and modifications the user did as
well as **which application user did it** (which is the purpose of the
set_session_id technique - being able to pass the application user who did
the operation to the server so that that the audit-trail can log it
altogether).

Once the operation is done and the connection is back to the pool, does
PostgreSQL discart the associated var ? Does it mantain one "instance" per
request made ? That's what I would like to know.

Thanks,

On 8/21/07, Ted Byers <r(dot)ted(dot)byers(at)rogers(dot)com> wrote:
>
>
> --- Marcelo de Moraes Serpa <celoserpa(at)gmail(dot)com>
> wrote:
>
> > Hello list,
> > [snip]
> >
> > * Each http request gets an instance of a session_id
> > ? Or is it
> > per-connection ?
>
> It depends. There is no necessary correlation between
> a session as defined within a web application and a
> session as defined in the RDBMS. I routinely set up
> web applications that may have multiple "sessions" as
> seen by the RDBMS. Consider the idiom of doing
> operations with the least priviledge required to get
> the job done. The application might log into the
> database as one databse user with very limited rights,
> to authenticate the user and pass data to the web
> application regarding what the user is permitted to do
> (all server side, on the application server). Then,
> the application may log in as a very different user
> with limited rights to perform some operation the user
> has initiated. So far, we have two sessions as far as
> the database is concerned and only one as far as the
> web application is concerned.
>
> If you're working with web applications, you must know
> that multiple http requests can share a web
> application session, as can multiple web applications
> (if written to do so using the single sign-on idiom),
> assuming you use technologies such as cookies or URL
> rewriting or hidden form fields, or the magic of
> ASP.NET, to set up and maintain sessions, and that the
> session is restricted to a single http request if you
> don't (plain old http/html is stateless, so there is
> no useful concept of session without help from other
> technologies).
>
> HTH
>
> Ted
>
>