Re: Audit-trail engine: getting the application's layer user_id - Mailing 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_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@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 >