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