Re: Audit-trail engine inner-workings - Mailing list pgsql-general
| From | Marcelo de Moraes Serpa |
|---|---|
| Subject | Re: Audit-trail engine inner-workings |
| Date | |
| Msg-id | 1e5bcefd0708221246x3835ae78x6d20a89f7295a645@mail.gmail.com Whole thread Raw |
| In response to | Re: Audit-trail engine inner-workings (Ted Byers <r.ted.byers@rogers.com>) |
| Responses |
Re: Audit-trail engine inner-workings
|
| List | pgsql-general |
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,
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.ted.byers@rogers.com> wrote:
--- 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
pgsql-general by date: