Re: Audit-trail engine: getting the application's layer user_id

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 <Joris(at)familiedobbelsteen(dot)nl> wrote:
>
>
>
> ------------------------------
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Marcelo de Moraes Serpa
> *Sent:* dinsdag 24 april 2007 21:06
> *To:* pgsql-general(at)postgresql(dot)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 <celoserpa(at)gmail(dot)com> 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 <jgodoy(at)gmail(dot)com> wrote:
> > >
> > > "Marcelo de Moraes Serpa" <celoserpa(at)gmail(dot)com> 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 <jgodoy(at)gmail(dot)com >
> > >
> >
> >
>