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

From Marcelo de Moraes Serpa
Subject Re: Audit-trail engine: getting the application's layer user_id
Date
Msg-id 1e5bcefd0704250452u5d05d129nce4f7272a9e04ca6@mail.gmail.com
Whole thread Raw
In response to Re: Audit-trail engine: getting the application's layer user_id  (Manuel Sugawara <masm@fciencias.unam.mx>)
List pgsql-general
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!

On 4/24/07, Manuel Sugawara <masm@fciencias.unam.mx> wrote:
"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.

pgsql-general by date:

Previous
From: "Marcelo de Moraes Serpa"
Date:
Subject: Re: Audit-trail engine: getting the application's layer user_id
Next
From: "pobox@verysmall.org"
Date:
Subject: pg_connect sometimes works sometimes not