Thread: function to return pg_user.usesysid

Dear PostgreSQL developers, 

as far as I know, there is no function available to directly access the
user id (pg_(shadow|user).usesysid) 
of the current database user, while the user's name is easily available
(function current_user). 

For a couple of databases I'm using or designing, it is necessary to
keep track of who has changed what and when - a job perfectly easy with
the timetravel add-on (contrib/spi). Using usesysid instead of a user
name to keep track of who has entered/changed/deleted data in a certain
record offers saving a few bytes per record, so I wrote a _very small_
add-on to directly access the usesysid information, using the function
GetUserId(). 

This is hardly worth mentioning since it consists of just a few lines of
code, but maybe some other users might find it useful. If so, please
feel free to make it available publicly. The few lines are available at
http://mibi03.meb.uni-bonn.de/~ernst/pgsql_userid.tar.gz

Hm, by the way, what about adding a function like current_userid to the
core functionality of PostgreSQL?

Best regards, 

Ernst

On Fri, 2003-02-07 at 07:03, Dr. Ernst Molitor wrote:
> Hm, by the way, what about adding a function like current_userid to the
> core functionality of PostgreSQL?

What exactly would this do that couldn't be done by just querying
pg_user directly?

SELECT usesysid FROM pg_user WHERE usename = CURRENT_USER;

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway wrote:
> On Fri, 2003-02-07 at 07:03, Dr. Ernst Molitor wrote:
> > Hm, by the way, what about adding a function like current_userid to the
> > core functionality of PostgreSQL?
> 
> What exactly would this do that couldn't be done by just querying
> pg_user directly?
> 
> SELECT usesysid FROM pg_user WHERE usename = CURRENT_USER;

The function would be maintained and would work no matter what changes
to the underlying tables were made.

Not that such changes are terribly likely for this case, mind you.

Are there any situations in which it would be useful to deny access to
certain system tables, like pg_user?  Would doing so automatically
prevent any users who were under such restrictions from doing anything
useful?  If such a thing is possible and potentially useful, then it's
another reason for the function Dr. Molitor mentioned.


-- 
Kevin Brown                          kevin@sysexperts.com

Hi all,

Also need add function, returned GetSessionUserId() too.

On 7 Feb 2003, Dr. Ernst Molitor wrote:

DEM>record offers saving a few bytes per record, so I wrote a _very small_
DEM>add-on to directly access the usesysid information, using the function
DEM>GetUserId().

-- 
Olleg Samoylov