Re: Audit-trail engine: getting the application's layer user_id - Mailing list pgsql-general

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