Re: [GENERAL] Audit-trail engine inner-workings
Marcelo de Moraes Serpa [EMAIL PROTECTED] writes: I know that this PostgreSQL C module has a static var that in turn keeps the integer set by the function set_session_id - but is this var global to the server's service ? Does PostgreSQL mantain one instance of this var per requested connection ? I would like to know how this works. PostgreSQL works using process, each backend that serves a connection is a process and each process keeps and instance of the variable, so, no, this variable is not shared globally (else it will not work as expected). Take the following scenario: - user enters the web app; - web app takes a reference to a connection from the db connection pool; - web app does some db operation When the app takes a reference to a connection and does the operation, just after that, in the application, I set the session id. Then the db operation is performed and the audit stored procedure is ran. The audit-trail engine performs its work and logs the operation and modifications the user did as well as **which application user did it** (which is the purpose of the set_session_id technique - being able to pass the application user who did the operation to the server so that that the audit-trail can log it altogether). Once the operation is done and the connection is back to the pool, does PostgreSQL discart the associated var ? Does it mantain one instance per request made ? That's what I would like to know. PostgreSQL does not discard the associated var and its value its keep until the connection is closed (really closed not just returned to the pool) and the process destroyed. That's why is important to reset its value, just to be sure that the next time the connection is used (by probably some other session) it does not get confused by some value previously left. BTW, having a C module has proved to be a PITA over the years, but when this was constructed we didn't have any other option. Now I think that the same idea can be constructed using custom variable clasess http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html but haven't got to the details yet. Regards, Manuel. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Audit-trail engine inner-workings
Hey Ted, thanks for the reply, In respect of web application architecture, I'm fully aware of session persistence mechanisms (I work as a developer of web apps after all). What I really would like to know is the inner-workings of the set_session_id and current_session_id as well as reset_session_id C functions. Our company uses a generator called GeneXus which is high level modeling enviroment that deployes to a variety of languages Java being one of them. Being a generator, we don't have much information about the way it generates the code becouse 1) it is a proprietary generator, 2) the code generated is propositally cryptic (don't make any sense at all, with weird var and method names and so on). However, I was given the mission to implement an audit-trail engine to this application. The discussion I sent in the first message of this thread was really helpful, the C functions sent by Manual were crucial. They work fine, **but** I don't have much knowledge in C nor PostgreSQL internal architecture **and** we need to know certain inner details on how this mechanism works in order to take some important decisions. I know that this PostgreSQL C module has a static var that in turn keeps the integer set by the function set_session_id - but is this var global to the server's service ? Does PostgreSQL mantain one instance of this var per requested connection ? I would like to know how this works. Take the following scenario: - user enters the web app; - web app takes a reference to a connection from the db connection pool; - web app does some db operation When the app takes a reference to a connection and does the operation, just after that, in the application, I set the session id. Then the db operation is performed and the audit stored procedure is ran. The audit-trail engine performs its work and logs the operation and modifications the user did as well as **which application user did it** (which is the purpose of the set_session_id technique - being able to pass the application user who did the operation to the server so that that the audit-trail can log it altogether). Once the operation is done and the connection is back to the pool, does PostgreSQL discart the associated var ? Does it mantain one instance per request made ? That's what I would like to know. Thanks, On 8/21/07, Ted Byers [EMAIL PROTECTED] wrote: --- Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote: Hello list, [snip] * Each http request gets an instance of a session_id ? Or is it per-connection ? It depends. There is no necessary correlation between a session as defined within a web application and a session as defined in the RDBMS. I routinely set up web applications that may have multiple sessions as seen by the RDBMS. Consider the idiom of doing operations with the least priviledge required to get the job done. The application might log into the database as one databse user with very limited rights, to authenticate the user and pass data to the web application regarding what the user is permitted to do (all server side, on the application server). Then, the application may log in as a very different user with limited rights to perform some operation the user has initiated. So far, we have two sessions as far as the database is concerned and only one as far as the web application is concerned. If you're working with web applications, you must know that multiple http requests can share a web application session, as can multiple web applications (if written to do so using the single sign-on idiom), assuming you use technologies such as cookies or URL rewriting or hidden form fields, or the magic of ASP.NET, to set up and maintain sessions, and that the session is restricted to a single http request if you don't (plain old http/html is stateless, so there is no useful concept of session without help from other technologies). HTH Ted
[GENERAL] Audit-trail engine inner-workings
Hello list, Taking this discussion as a base: http://archives.postgresql.org/pgsql-general/2007-04/msg01034.php Manuel helped me to develop an audit-trail engine, and even though it works ok, I need to know better its inner workings: From what I understand, the flow is something like this: * User sends http request to server to update some record (for example); * The application server (tomcat) runs the servlet and the servlet gets a connection from the pool * The servlet runs the set_session_id(integer) to set a session for this request and saves the current application user in the lookup table (session_id,user_id) * The db operation is done (db saved) - the user name is retrieved through a lookup into the table mentioned above by getting this request's session_id by running the current_session_id SP * Each http request gets an instance of a session_id ? Or is it per-connection ? * Do I really need to call reset_session_id ? Isn't this connection destroyed after it has been used by the application? * Would it work with an application which does not use a connection pool but a permanent connection (such as desktop apps)? Thanks in advance! Marcelo.
Re: [GENERAL] Audit-trail engine inner-workings
--- Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote: Hello list, [snip] * Each http request gets an instance of a session_id ? Or is it per-connection ? It depends. There is no necessary correlation between a session as defined within a web application and a session as defined in the RDBMS. I routinely set up web applications that may have multiple sessions as seen by the RDBMS. Consider the idiom of doing operations with the least priviledge required to get the job done. The application might log into the database as one databse user with very limited rights, to authenticate the user and pass data to the web application regarding what the user is permitted to do (all server side, on the application server). Then, the application may log in as a very different user with limited rights to perform some operation the user has initiated. So far, we have two sessions as far as the database is concerned and only one as far as the web application is concerned. If you're working with web applications, you must know that multiple http requests can share a web application session, as can multiple web applications (if written to do so using the single sign-on idiom), assuming you use technologies such as cookies or URL rewriting or hidden form fields, or the magic of ASP.NET, to set up and maintain sessions, and that the session is restricted to a single http request if you don't (plain old http/html is stateless, so there is no useful concept of session without help from other technologies). HTH Ted ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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.
On 4/24/07, Manuel Sugawara [EMAIL PROTECTED] wrote:
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
* Marcelo de Moraes Serpa [EMAIL PROTECTED] [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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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. On 4/24/07, Joris Dobbelsteen [EMAIL PROTECTED] wrote: -- *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *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. As Richard mentioned, he has done it. Remember, for the pool you DO NOT setup a new connection every time but you can certainly utilize the pool. The trick is the postgresql idea of the Role-Based Access Control (RBAC) implementation. I.e. you can just do a SET LOCAL ROLE rolename. After transaction commit or rollback, or execution of SET LOCAL ROLE NONE or RESET ROLE you will have your original role (own user) again. This should work just fine. See also: http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html @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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Marcelo de Moraes Serpa [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
Re: [GENERAL] 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 [EMAIL PROTECTED] wrote:
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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.
On 4/24/07, Manuel Sugawara [EMAIL PROTECTED] wrote:
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
* Manuel Sugawara [EMAIL PROTECTED] [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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Tilmann Singer [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
* Manuel Sugawara [EMAIL PROTECTED] [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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Tilmann Singer [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
* Manuel Sugawara [EMAIL PROTECTED] [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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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? 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. On 4/25/07, Manuel Sugawara [EMAIL PROTECTED] wrote: Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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. On 4/25/07, Manuel Sugawara [EMAIL PROTECTED] wrote: Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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. On 4/25/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote: 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. On 4/25/07, Manuel Sugawara [EMAIL PROTECTED] wrote: Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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. On 4/24/07, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote: 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.
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Marcelo de Moraes Serpa [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa [EMAIL PROTECTED] 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.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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. As Richard mentioned, he has done it. Remember, for the pool you DO NOT setup a new connection every time but you can certainly utilize the pool. The trick is the postgresql idea of the Role-Based Access Control (RBAC) implementation. I.e. you can just do a SET LOCAL ROLE rolename. After transaction commit or rollback, or execution of SET LOCAL ROLE NONE or RESET ROLE you will have your original role (own user) again. This should work just fine. See also: http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html @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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Marcelo de Moraes Serpa [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
Re: [GENERAL] Audit trail ?
Hello, Mike! Your solution for audit trail is wonderfull! Easy and elegant ! It helped me a lot and I successfully implemented it, with small modifications. Thanky you very much! - Original Message - From: Mike Rylander [EMAIL PROTECTED] To: Zlatko Matic [EMAIL PROTECTED] Cc: Postgresql-General pgsql-general@postgresql.org Sent: Sunday, May 29, 2005 6:21 PM Subject: Re: [GENERAL] Audit trail ? On 5/29/05, Zlatko Matic [EMAIL PROTECTED] wrote: Hello. I must have audit trail of all insert/update/delete on several table. I have several questions regarding that: 1. Is it better to have one audit trail table that collects insert/update/delete of all audited tables, or it is better to have separate audit trail table for every audited table ? 2. To use triggers or rules ? Example for both ? 3. Could someone give me an example of a successfull audit trail solution ? I'm running on lack of time, so any help would be precious... We use the audit table per real table approach. The SQL script to create the audit trail functions and triggers is attached. There are three example audit trail table creation calls right before the COMMIT. Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Audit trail ?
Hello. I must have audit trail of all insert/update/delete on several table. I have several questions regarding that: 1. Is it better to have one audit trail table that collects insert/update/delete of all audited tables, or it is better to have separate audit trail table for every audited table ? 2. To use triggers or rules ? Example for both ? 3. Could someone give me an example of a successfull audit trail solution ? I'm running on lack of time, so any help would be precious... Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Audit trail ?
Sorry for short message, but I'm headed out for the weekend. At my places of work we have use both a single table and one table for every table. I personally liked the single table for every table approach. On 5/29/05, Zlatko Matic [EMAIL PROTECTED] wrote:Hello.I must have audit trail of all insert/update/delete on several table. I have several questions regarding that:1. Is it better to have one audit trail table that collectsinsert/update/delete of all audited tables, or it is better to have separateaudit trail table for every audited table ? 2. To use triggers or rules ? Example for both ?3. Could someone give me an example of a successfull audit trail solution ?I'm running on lack of time, so any help would be precious...Thanks. ---(end of broadcast)---TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Audit trail ?
On 5/29/05, Zlatko Matic [EMAIL PROTECTED] wrote: Hello. I must have audit trail of all insert/update/delete on several table. I have several questions regarding that: 1. Is it better to have one audit trail table that collects insert/update/delete of all audited tables, or it is better to have separate audit trail table for every audited table ? 2. To use triggers or rules ? Example for both ? 3. Could someone give me an example of a successfull audit trail solution ? I'm running on lack of time, so any help would be precious... We use the audit table per real table approach. The SQL script to create the audit trail functions and triggers is attached. There are three example audit trail table creation calls right before the COMMIT. Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org 900.audit-tables.sql Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
