Обсуждение: Audit-trail engine inner-workings

Hello list,

Taking this discussion as a base:

http://archives.postgresql.org/pgsql-general/2007-04/msg01034.php

Manuel helped me to develop an audit-trail engine, and even though it works ok, I need to know better its inner workings:
 
From what I understand, the flow is something like this:
   * User sends http request to server to update some record (for example);
   * The application server (tomcat) runs the servlet and the servlet gets a connection from the pool
   * The servlet runs the "set_session_id(integer)" to set a session for this request and saves the current application user in the lookup table (session_id,user_id)
   * The db operation is done (db saved) - the user name is retrieved through a lookup into the table mentioned above by getting this request's session_id by running the "current_session_id" SP

* Each http request gets an instance of a session_id ? Or is it per-connection ?
* Do I really need to call reset_session_id ? Isn't this connection destroyed after it has been used by the application?
* Would it work with an application which does not use a connection pool but a permanent connection (such as desktop apps)?

Thanks in advance!

Marcelo.




  

--- Marcelo de Moraes Serpa <celoserpa@gmail.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

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,

"Marcelo de Moraes Serpa" <celoserpa@gmail.com> writes:

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

PostgreSQL works using process, each backend that serves a connection
is a process and each process keeps and instance of the variable, so,
no, this variable is not shared globally (else it will not work as
expected).

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

PostgreSQL does not discard the associated var and its value its keep
until the connection is closed (really closed not just returned to the
pool) and the process destroyed. That's why is important to reset its
value, just to be sure that the next time the connection is used (by
probably some other session) it does not get confused by some value
previously left.

BTW, having a C module has proved to be a PITA over the years, but
when this was constructed we didn't have any other option. Now I think
that the same idea can be constructed using custom variable clasess

  http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html

but haven't got to the details yet.

Regards,
Manuel.