Re: audit information

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