Re: audit information
От | Keith Worthington |
---|---|
Тема | Re: audit information |
Дата | |
Msg-id | 20050426150510.M72523@narrowpathinc.com обсуждение исходный текст |
Ответ на | Re: audit information (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: audit information
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-novice |
On Mon, 25 Apr 2005 11:57:08 -0400, Tom Lane wrote > Michael Fuhr <mike@fuhr.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
В списке pgsql-novice по дате отправления: