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>
Subject: Re: audit information
Date: 2005-04-26 15:14:58
Message-ID: 20050426150510.M72523@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, 25 Apr 2005 11:57:08 -0400, Tom Lane wrote
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > On Mon, Apr 25, 2005 at 10:41:09AM -0400, Keith Worthington wrote:
> >> Is there a way to obtain the postgres user id or must I
> >> store the output of session_user in the tables?
> 
> > You could grab usesysid from pg_user:
> > SELECT usesysid FROM pg_user WHERE usename = session_user;
> > You can convert the user ID back to the name with
> > pg_get_userbyid(): SELECT pg_get_userbyid(1);
> 
> My suggestion would be to store the user name as text.
> Depending on numeric user IDs to remain stable across the
> long haul (dump/reload, version updates, etc) doesn't seem
> like a good idea --- it's pretty much exactly the same
> mistake as relying on OIDs as primary keys in user tables.
> 
> It's fairly likely that in 8.1 or so we will actually
> abandon the notion of "usesysid" per se --- the pg_roles
> project will probably use OIDs to identify roles, which
> will mean you can't forcibly assign a particular numeric
> ID to a particular user.  If that happens then storing
> numeric IDs *will* break when you reload the data into 8.1.
> 
> If you're really desperate for space you could keep your
> own auxiliary table to associate user names with integer
> keys, but I doubt the space savings would amount to all
> that much ...
> 
> 			regards, tom lane

I have created the following function to create the necessary data for my
audit trail.  Unfortunately I do not know how I can test it without putting
the whole modification in place. (This is a small part of a much bigger project.)

Can anyone suggest how to test this functionality without implementing it as a
trigger on a table?

Can this function be implemented as STABLE STRICT?

CREATE OR REPLACE FUNCTION interface.tf_audit_data()
  RETURNS "trigger" AS
$BODY$
   BEGIN
--    Set the user name.
      IF NEW.user_name IS NULL THEN
         NEW.user_name := SELECT * FROM session_user;
      END IF;
--    Set the timestamp.
      IF NEW.review_timestamp IS NULL THEN
         NEW.review_timestamp := ('now'::text)::timestamp(6) with time zone;
      END IF;
      RETURN NEW;
   END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Kind Regards,
Keith

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-04-26 15:31:57
Subject: Re: audit information
Previous:From: Tom LaneDate: 2005-04-26 14:40:02
Subject: Re: Encoding woes

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