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.
On 4/24/07,
Manuel Sugawara <masm@xxxxxxxxxxxxxxxxx> wrote:
"Marcelo de Moraes Serpa" <celoserpa@xxxxxxxxx> 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.
