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

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(at)fciencias(dot)unam(dot)mx> wrote:
>
> "Marcelo de Moraes Serpa" <celoserpa(at)gmail(dot)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.
>