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

________________________________

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 >