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

Re: audit information

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: audit information
Date: 2005-04-25 18:26:32
Message-ID: 20050425181515.M61926@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

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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-04-25 21:08:49
Subject: Re: audit information
Previous:From: Tom LaneDate: 2005-04-25 15:57:08
Subject: Re: audit information

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