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

Hey guys,

I needed to implement an audit trail engine and decided to do it on the database layer.

I already have a basic but fully functional audit trail system implemented on my PostgreSQL 8.2 server. It has been done using PL/PGSQL
and triggers and it works pretty well.

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.

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'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. A
relation table is then created to relate the user_id and application_id. When the user logs in, a new record is created in this table, and,
as the application_id is available in the db enviroment to the procedural languages, we can then go to this table and finally get the
user_id. Is there something like that in Postgres?

Thanks in advance!

Marcelo.

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.  

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

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

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

* 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

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

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.

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

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.

Hello all,

I know I may be asking too much, but I have a very limited C/C++ (as well as PostgreSQL internal architecture) knowledge. I've tried compiling the C source code Manuel sent as a PostgreSQL loadable module on Visual Studio .NET 2003 (C++) without success (lots of missing identifiers, "int Datum" redefinition and other things I didn't really understood). The comments in the Postgres 8.0 manual didn't help much.

If anyone could put me on the right direction on how to write/build C/C++ PostgreSQL on the Windows platform (specifically Windows XP) I would be grateful as I really need this thing working as soon as possible.

Thanks,

Marcelo.

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

> Hello all,
>
> I know I may be asking too much, but I have a very limited C/C++ (as well as
> PostgreSQL internal architecture) knowledge. I've tried compiling the C
> source code Manuel sent as a PostgreSQL loadable module on Visual Studio
> .NET 2003 (C++) without success (lots of missing identifiers, "int Datum"
> redefinition and other things I didn't really understood). The comments in
> the Postgres 8.0 manual didn't help much.
>
> If anyone could put me on the right direction on how to write/build C/C++
> PostgreSQL on the Windows platform (specifically Windows XP) I would be
> grateful as I really need this thing working as soon as possible.
>
> Thanks,
>
> Marcelo.

If you really need this, shouldn't you consider hiring someone that
works professionaly with PostgreSQL?

They'd certainly do the work for you and you'll accomplish your target
on the due date.

--
Jorge Godoy      <jgodoy@gmail.com>

Hi Manuel,

Just replying to say a big thank you ... I compiled the C extension with the code you , did all the necessary logic and finally solved it. Thank you very much for your help!

Thank you also to all the other who helped me!

Marcelo.

* Marcelo de Moraes Serpa <celoserpa@gmail.com> [20070509 21:14]:
> Just replying to say a big thank you ...
> I compiled the C extension with the
> code you , did all the necessary logic and finally solved it. Thank you very
> much for your help!

I second that!

I finally settled with the simpler solution which was to create a
temporary table instead of using a global variable in a C function
which seemed reasonably fast enough for us and doesn't introduce the
maintainance dependency on a custom C module.


Til