Skip site navigation (1) Skip section navigation (2)

Re: audit information

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Cc: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
Subject: Re: audit information
Date: 2005-04-25 15:44:32
Message-ID: 20050425154355.M55481@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, 25 Apr 2005 11:01:51 -0400, Sean Davis wrote
> On Apr 25, 2005, at 10:41 AM, Keith Worthington wrote:
> 
> > Hi All,
> >
> > I need to add an audit trail to my application.  I thought that
> > I would add the user id and timestamp to the tables where I need
> > to track 'last modified by...'.  Currently there is a table
> > tbl_employee that I would like to be able to cross reference to
> > in order to obtain the user's real name when  necessary.
> >
> > MYDB=# \d tbl_employee
> >            Table "tbl_employee"
> >      Column     |         Type          | Modifiers
> > ----------------+-----------------------+-----------
> >  id             | character varying(20) | not null
> >  first_name     | character varying(15) | not null
> >  middle_initial | character(1)          |
> >  last_name      | character varying(20) | not null
> >  inactive       | boolean               | not null
> >  email          | character varying(64) |
> > Indexes:
> >     "tbl_employee_pkey" PRIMARY KEY, btree (id)
> >
> > Is there a way to obtain the postgres user id or must I store
> > the output of session_user in the tables?
> 
> Not sure on this one.  What is the "id" in your table?  Is it the 
> postgres username?
> 
> > Are there some existing functions and or tools for
> > implementing audit trails?
> 
> Would a trigger on the table that you want to audit do the trick?
> 
> Sean

The tbl_employee.id column contains data from the financal software.

Yes, I believe a BEFORE trigger would do the trick nicely.  I can
simply insert the approriate data on the way by.

My question is do I have to use session_user which returns type name
or is there a function that returns the usesysid which is type int4?

Regardless of whether the data I use is int4 or name I need to add a
column to tbl_employee for cross referencing to the employee's real
name.

Kind Regards,
Keith

In response to

pgsql-novice by date

Next:From: Keith WorthingtonDate: 2005-04-25 15:49:03
Subject: Re: audit information
Previous:From: Michael FuhrDate: 2005-04-25 15:35:34
Subject: Re: audit information

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group