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

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,

Thanks,

Marcelo.

Marcelo de Moraes Serpa wrote:

Here's what I need to do: Somehow save the user_id of the **application**
user who have done the update/delete action to the log row.

I've read an article on IBM's developer site which teaches how to do just
that (get the application's user id and save it the audit
row) using what they call the APPLICATION_ID which is an unique ID that DB2
assigns to the app when it connects to the database.

Afraid there's nothing quite like that for PG.

There's two ways I've used.

1. Have a separate user (role in 8.2) for each application user (it can
be something like u_app_0001 etc). This is do-able for a few hundred
users certainly, and should be fine for a few thousand. Not sure about
hundreds of thousands though.

2. Simulate a "session variable" by having one of the procedural
languages store state for you (e.g. pl/tcl or pl/perl). Call
set_app_user(...) on application connect and call get_app_user() when
you need to find the current app user.

I've done both, but prefer the first myself.

--
Richard Huxton
Archonet Ltd

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

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 de Moraes Serpa" <celoserpa@gmail.com> writes:

@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.

Why? You can always "SET SESSION AUTH TO <user>" before anything. It
works with transaction pools and you can even enhance your application
security by a second layer inside the database itself. DB passwords
don't need to be known by users since they won't connect directly and
your connection will be made with a priviledged user.

@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?

It all depends on what you want to make it unique. I believe that a
simple process will be dedicated to each connection, so if you get its
PID you'll be done. Each time a user accesses the database you insert
or update a record with his PID and then you make your triggers work
with that. There will be more logic, but you got the idea.

Another option is using the transaction ID or something that always
change.

You just need a unique value that lasts for a connection and isn't
shared with any other user connected at the same time you are.

There are a lot of functions that you can use. You just have to be sure
when you want the information and what information you need. Take a
look at the ones available in pg_catalog for your specific PostgreSQL
version.

--
Jorge Godoy <jgodoy@gmail.com>

"Marcelo de Moraes Serpa" <celoserpa@gmail.com> writes:

Hey guys,

Mine is an web application - three tier. The app connects to the db using
only one user and it has it's own authentication system and doesn't
rely on the database for user management.

I solved the problem using a C program and keeping all the information
in the database, that means, users, passwords and ``sessions''. Each
time a user opens a session the system register it in a table that
looks like:

auth.session
Tabla «auth.session»
Columna | Tipo | Modificadores
---------------+-----------------------------+----------------------------------------------------------------
id | integer | not null default nextval(('auth.session_sid'::text)::regclass)
skey | text | not null
agent_id | integer | not null
host | text | not null default 'localhost'::text
start_time | timestamp without time zone | not null default now()
end_time | timestamp without time zone |
su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES auth."session"(id)

Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:

session_id int not null default current_session_id() references auth.session(id),

Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:

#include "postgres.h"

#include <stdio.h>
#include <string.h>
#include <time.h>
#include <unistd.h>
#include "fmgr.h"

static int session_id = 0;
static int session_id_is_set = false;

Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);

Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}

Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}

Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}

Hope that helps.

Regards,
Manuel.

________________________________

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 >

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.

Hi Manuel, just a quick question: What C libraries do I need to compile this
function? Or better: Where can I find a reference manual about db stored
procedures written in C for PostgreSQL?

Thanks!

"Marcelo de Moraes Serpa" <celoserpa@gmail.com> writes:

I'm sorry Manuel, but after some time trying to fully understand your
approach, I think I really don't have the required elements to do so.

How do you pass your application's usename to this table? Or you don't keep
the username at all?

Could you give a more concrete example? Maybe showing the spots on your
application where you called these functions and why?

I keep my user-names (agents) in the database along with a hashed
version of their passphrases, when a user logs in I have a procedure
written in plpgsql that checks the provided passphrase against the one
in the database and if they match the user is granted a session, and
the a corresponding row inserted in the session table. I keep the user
information (the session id and a key) in the session of the web tier
(I'm using java servlets but the concept is the same for other
frameworks). Now, each time the user sends a request I do more or less
the following:

retrieve from the web session the id of the session in the database
request a fresh connection from the pool
check if the session is still alive (if not throw an exception)
set the session id of the user
handle the user request
reset the session id
return the connection to the pool

The implementation details are left to the reader ;-). Hope that helps

Regards,
Manuel.

Tilmann Singer <tils-pgsql@tils.net> writes:

So the view will reference the original table and not the temporary
table. Is there a way to achieve this kind of transaction local
setting? A transaction based solution would give more security in a
situation where a web app server uses a connection pool and you can
not guarantee 100% that your reset code is called properly at request
init.

Nop, you do the reset part *at the end* of the request cycle:

set session id
handle request
reset session id

So, you can guarantee that the id of the session is reset and the
connection properly disposed to the pool.

Regards,
Manuel.

I'm sorry Manuel, but after some time trying to fully understand your
approach, I think I really don't have the required elements to do so.

How do you pass your application's usename to this table? Or you don't keep
the username at all?

Could you give a more concrete example? Maybe showing the spots on your
application where you called these functions and why?

At least, for the C shared library compiling on Windows, I think I'm
half-way done - I've found a really useful comment on a PostgreSQL manual
page teaching how to compile PostgreSQL modules under Windows - you can see
it here: http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html

Thank you again.

Marcelo.

* Manuel Sugawara <masm@fciencias.unam.mx> [20070425 00:17]:

I solved the problem using a C program and keeping all the information
in the database, that means, users, passwords and ``sessions''. Each
time a user opens a session the system register it in a table that
looks like:

This looks very useful, thanks!

Do you know if there is a way to set such a variable for a transaction
only?

I thought it might work by creating a temporary table, which will
overlay a non-temporary table with the same name, so there could be a
permanent table with the default value and a temporary table with the
transaction specific user_id:

test=# create table current_application_user (user_id int);
CREATE TABLE
test=# insert into current_application_user values (NULL); -- the default
INSERT 0 1
test=# select user_id from current_application_user ;
user_id
---------

(1 row)
test=# begin;
BEGIN
test=# create temporary table current_application_user (user_id int) on commit drop;
CREATE TABLE
test=# insert into current_application_user values (1); -- the current application user
INSERT 0 1
test=# select user_id from current_application_user ;
user_id
---------
1
(1 row)

test=# commit;
COMMIT
test=# select user_id from current_application_user ;
user_id
---------

(1 row)

But is it possible to create a database view that accesses the value
of that temporary table when present and otherwise the value of the
default table? I tried the following, but apparently the view
definition will contain a reference to the public schema as soon as a
temporary table with the same name is present:

test=# create table some_content (body text, owner_id int);
CREATE TABLE
test=# create view some_content_restricted as select * from some_content where owner_id=(select user_id from current_application_user );
CREATE VIEW
test=# \d some_content_restricted
View "public.some_content_restricted"
Column | Type | Modifiers
----------+---------+-----------
body | text |
owner_id | integer |
View definition:
SELECT some_content.body, some_content.owner_id
FROM some_content
WHERE some_content.owner_id = (( SELECT current_application_user.user_id
FROM current_application_user));

test=# begin;
BEGIN
test=# create temporary table current_application_user (user_id int) on commit drop;
CREATE TABLE
test=# \d some_content_restricted
View "public.some_content_restricted"
Column | Type | Modifiers
----------+---------+-----------
body | text |
owner_id | integer |
View definition:
SELECT some_content.body, some_content.owner_id
FROM some_content
WHERE some_content.owner_id = (( SELECT current_application_user.user_id
FROM public.current_application_user));

So the view will reference the original table and not the temporary
table. Is there a way to achieve this kind of transaction local
setting? A transaction based solution would give more security in a
situation where a web app server uses a connection pool and you can
not guarantee 100% that your reset code is called properly at request
init.

tia, Til

Tilmann Singer <tils-pgsql@tils.net> writes:

Can I define a view which references a table in a way so that it will
use a temporary table of the same name if it exists, otherwise the
permanent table with that name?

I think you can use a plpgsql function with execute. For instance, if
the name of your temp table is current_user_id the function will be
something like:

create function get_current_user_id() returns int as $$
declare
v_rec record;
v_user int;
v_query text;
begin
v_query := 'SELECT user_id FROM current_user_id';
for v_rec in execute v_query loop
v_user := v_rec.user_id;
end loop;
return v_user;
end;
$$ language plpgsql;

Untested but that's the idea, you need to use execute to avoid the
caching of the plan. You might also want to control what happens when
the table does not exist and that can be done handling the
corresponding exception. Check the docs for the details.

Regards,
Manuel.

* Manuel Sugawara <masm@fciencias.unam.mx> [20070425 17:14]:

So the view will reference the original table and not the temporary
table. Is there a way to achieve this kind of transaction local
setting? A transaction based solution would give more security in a
situation where a web app server uses a connection pool and you can
not guarantee 100% that your reset code is called properly at request
init.

Nop, you do the reset part *at the end* of the request cycle:

set session id
handle request
reset session id

So, you can guarantee that the id of the session is reset and the
connection properly disposed to the pool.

If you have full confidence in having control over the request cycle
in your app server, yes.

However, I'm using Ruby on Rails' ActiveRecord and am not 100%
familiar with the way it uses connection pooling and request setup, so
I would feel more safe if I could set something like a temporary table
with ON COMMIT DROP to be sure my app user id will only be visible
from within the transaction that it was set in, and that a value set
can not leak to another web request that reuses the previous db
connection.

Rephrasing the question:

Can I define a view which references a table in a way so that it will
use a temporary table of the same name if it exists, otherwise the
permanent table with that name?

Til

"Marcelo de Moraes Serpa" <celoserpa@gmail.com> writes:

Hi Manuel,

each time the user sends a request I do more or less the following:

Could a trigger be used to implement this ? Or are you doing this from the
application layer?

I'm doing it form the application layer and I don't think it can be
done in the database layer, how the trigger will figure out which user
is doing the query?, It's the same problem you are trying to solve!

My problem is that, like Til, I don't have full control over my
request cycle as I'm over a very high-level framework (Actually it
is an data-oriented application generator, called GeneXus).

Maybe you can use Til approach with temporal tables.

Regards,
Manuel.

* Manuel Sugawara <masm@fciencias.unam.mx> [20070425 17:57]:

I think you can use a plpgsql function with execute. For instance, if
the name of your temp table is current_user_id the function will be
something like:

create function get_current_user_id() returns int as $$
declare
v_rec record;
v_user int;
v_query text;
begin
v_query := 'SELECT user_id FROM current_user_id';
for v_rec in execute v_query loop
v_user := v_rec.user_id;
end loop;
return v_user;
end;
$$ language plpgsql;

Untested but that's the idea, you need to use execute to avoid the
caching of the plan. You might also want to control what happens when
the table does not exist and that can be done handling the
corresponding exception. Check the docs for the details.

Excellent, that works exactly as needed! I will rely on the permanent
table being there always to provide the default value if no temporary
table has been created.

Thanks! Til

Hi Manuel,

each time the user sends a request I do more or less

the following:

Could a trigger be used to implement this ? Or are you doing this from the

application layer? My problem is that, like Til, I don't have full control
over my request cycle as I'm over a very high-level framework (Actually it
is an data-oriented application generator, called GeneXus).

Thanks,

Marcelo.

I'm doing it form the application layer and I don't think it can be
done in the database layer, how the trigger will figure out which user
is doing the query?, It's the same problem you are trying to solve!

Duh! That is what happens when you start having high levels of caffeinne in
your blood and haven't had a good night sleep... thanks for putting me on
the track again.

I will study Tim's approach more. This thing got more complicated than I
thought it would be. At least I'm learning more about PostgreSQL internal
architecture.

Thank you for your help.

Marcelo.