Re: Audit-trail engine: getting the application's layer user_id - Mailing list pgsql-general
| From | Joris Dobbelsteen |
|---|---|
| Subject | Re: Audit-trail engine: getting the application's layer user_id |
| Date | |
| Msg-id | 73427AD314CC364C8DF0FFF9C4D693FF037AC9@nehemiah.joris2k.local Whole thread Raw |
| In response to | Audit-trail engine: getting the application's layer user_id ("Marcelo de Moraes Serpa" <celoserpa@gmail.com>) |
| Responses |
Re: Audit-trail engine: getting the application's layer user_id
|
| List | pgsql-general |
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] 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_idThank 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.
@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@gmail.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@gmail.com> wrote:"Marcelo de Moraes Serpa" <celoserpa@gmail.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@gmail.com >
pgsql-general by date: