Thread: Audit Trigger puzzler

all of my tables have 4 fields
edited_by
edited_date
created_by
created_date

Most of the time, my application will set the edited_by field to reflect
an application username (i.e., the application logs into the database as
a database user, and that's not going to be the application user)
So I log into my application as "Dave", but the application connects to
the database as "dbuser".

If the app doesn't specifically send an "edited_by" value in it's
update, then I want to default that value to the database user.

This would also be good for auditing any manual data changes that could
happen at the psql level.


my trigger was essentially

if NEW.edited_by is null then
    edited_by = :current_user
end if

but, unfortunately, i didn't realize that in an update, the NEW
variables contains a full record, so edited_by will never be null.

If i do
if NEW.edited_by = OLD.edited_by
    edited_by = :current_user
end if

then, if i do 2 updates to edited_by in a row, i get the DB user instead
of the user i was intending to update.


so, is there a way in a trigger to know if edited_by is expressly being
set in the update statement? it seems like if I can know that, then i
should be able to figure it out.

Thanks

Dave

On Fri, 2009-08-28 at 08:50 -0700, David Kerr wrote:

> so, is there a way in a trigger to know if edited_by is expressly
> being  set in the update statement? it seems like if I can know that,
> then i should be able to figure it out.

No, but you could use a before trigger to reset the value to NULL prior
to each update, then your after trigger would work.

--
 Simon Riggs           www.2ndQuadrant.com

On Fri, Aug 28, 2009 at 08:07:40PM +0100, Simon Riggs wrote:
-
- On Fri, 2009-08-28 at 08:50 -0700, David Kerr wrote:
-
- > so, is there a way in a trigger to know if edited_by is expressly
- > being  set in the update statement? it seems like if I can know that,
- > then i should be able to figure it out.
-
- No, but you could use a before trigger to reset the value to NULL prior
- to each update, then your after trigger would work.

hmm, that's probably not worth it. fireing two triggers just to get an
edited_by column filled in.

Thanks

Dave

 > Most of the time, my application will set the edited_by field to
 > reflect an application username (i.e., the application logs into the
 > database as a database user, and that's not going to be the
 > application user) So I log into my application as "Dave", but the
 > application connects to the database as "dbuser".

 > If the app doesn't specifically send an "edited_by" value in it's
 > update, then I want to default that value to the database user.

 > This would also be good for auditing any manual data changes that
 > could happen at the psql level.

In Oracle, the way we handle audit triggers is by using Package
Variables.  We emulate some of that functionality in postgresql by
adding a custom variable to the configuration file:

custom_variable_classes = 'mysess'

Then, whenever a user logs into the application, my login procedure
calls this function:

CREATE OR REPLACE FUNCTION begin_sess(staffid character varying)
   RETURNS void AS $BODY$ BEGIN
PERFORM set_config('mysess.curr_user', coalesce(staffid,''), false);
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;

This makes the current application user automatically available to every
       function, including triggers.  Then, in your triggers, you can do
this:

DECLARE
     curr_user    staff.staff_id%TYPE;
BEGIN
     SELECT current_setting('mysess.curr_user') INTO curr_user;


In your trigger, you could check that this variable was unset, and fall
back to the database user.


HTH.

On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote:
- In Oracle, the way we handle audit triggers is by using Package
- Variables.  We emulate some of that functionality in postgresql by
- adding a custom variable to the configuration file:
-
- custom_variable_classes = 'mysess'
-
- Then, whenever a user logs into the application, my login procedure
- calls this function:
-
- CREATE OR REPLACE FUNCTION begin_sess(staffid character varying)
-   RETURNS void AS $BODY$ BEGIN
- PERFORM set_config('mysess.curr_user', coalesce(staffid,''), false);
- END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
-
- This makes the current application user automatically available to every
-       function, including triggers.  Then, in your triggers, you can do
- this:
-
- DECLARE
-     curr_user    staff.staff_id%TYPE;
- BEGIN
-     SELECT current_setting('mysess.curr_user') INTO curr_user;
-
-
- In your trigger, you could check that this variable was unset, and fall
- back to the database user.
-

Thanks! that does seem slick, but will it work with connection pooling?

Dave

David Kerr wrote:
> On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote:
> - In Oracle, the way we handle audit triggers is by using Package
> - Variables.  We emulate some of that functionality in postgresql by
> - adding a custom variable to the configuration file:
> -
> - custom_variable_classes = 'mysess'
> -
> -
> - In your trigger, you could check that this variable was unset, and fall
> - back to the database user.
> -
>
> Thanks! that does seem slick, but will it work with connection pooling?
>
> Dave
>

I don't see why it wouldn't work, as long as you set reset_query_list
properly, and set the session variable the the logged in user whenever
you grab a connection from the pool.