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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Worthington | 2005-04-25 15:49:03 | Re: audit information |
Previous Message | Michael Fuhr | 2005-04-25 15:35:34 | Re: audit information |