Re: audit information

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 peachtree.tbl_employee
Table "peachtree.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?

Are there some existing functions and or tools for implementing audit trails?

What technique do most people use for this type of functionality?

Your input is appreciated.

Kind Regards,
Keith

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);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

On Mon, 25 Apr 2005 09:35:34 -0600, Michael Fuhr wrote
> 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);
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/

Hmmm, that is interesting. I really expected someone to point out a
session_id function of some sort. For symmetry if no other reason.

I suppose I could write my own function to do that but the code you provided
is simple enough that I am not convinced that there is a real benefit.

In most cases I will want to convert the user ID to the employee name.

Thanks for the post.

Kind Regards,
Keith

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

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

Okay. That works for me. As you point out the space difference isn't that
much. On a million record table it would be < 60MB.

Is there a reason not to store the user name as type name?

Do you really mean text or is char(64) more appropriate?

I am assuming there is no reason to use varchar(64).

TIA

Kind Regards,
Keith

"Keith Worthington" <keithw(at)narrowpathinc(dot)com> writes:
> Is there a reason not to store the user name as type name?

I'd use text; name is fixed-width and will waste space on trailing
blanks. (Same objection to char(64) of course.)

> I am assuming there is no reason to use varchar(64).

Not particularly.

regards, tom lane

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

"Keith Worthington" <keithw(at)narrowpathinc(dot)com> writes:
> 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?

You can't. But you could make a test table and install the function as
a trigger on that.

> Can this function be implemented as STABLE STRICT?

Nope. STRICTness is meaningless anyway for a trigger, since it has no
explicit arguments to be strict about. And triggers are always volatile
since the entire point is to have side-effects. In the current code I
am not sure whether it matters if you try to mark a trigger function
stable ... but if the system were actually to enforce the STABLE
restrictions against your function, you'd not like the result. Some day
it probably will enforce them...

regards, tom lane