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 <masm@fciencias.unam.mx> wrote:"Marcelo de Moraes Serpa" <celoserpa@gmail.com > 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.