Hi!

What about adding new function:
pg_uid()
pg_session_uid()

as reference to internal function GetUserId() and GetSessionUserId().

These can help useful, for instance in row based securety.

-- 
Olleg Samoylov


Wed, 22 Jan 2003 16:09:57 +0300 (MSK)
Olleg Samoylov <olleg@jane.telecom.mipt.ru>
New buildin function
Olleg Samoylov writes:

> What about adding new function:
> pg_uid()
> pg_session_uid()
>
> as reference to internal function GetUserId() and GetSessionUserId().

CURRENT_USER, SESSION_USER

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 24 Jan 2003 00:34:59 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: New buildin function
On Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:
> Hi!
> 
> What about adding new function:
> pg_uid()
> pg_session_uid()
> 
> as reference to internal function GetUserId() and GetSessionUserId().
> 
> These can help useful, for instance in row based securety.

Do CURRENT_USER and SESSION_USER not give those values?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
23 Jan 2003 22:29:00 -0500
Rod Taylor <rbt@rbt.ca>
Re: New buildin function
On 23 Jan 2003, Rod Taylor wrote:

RT>On Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:
RT>> Hi!
RT>>
RT>> What about adding new function:
RT>> pg_uid()
RT>> pg_session_uid()
RT>>
RT>> as reference to internal function GetUserId() and GetSessionUserId().
RT>>
RT>> These can help useful, for instance in row based securety.
RT>
RT>Do CURRENT_USER and SESSION_USER not give those values?

Nope. CURRENT_USER and SESSION_USER return username. Sometimes need uid,
it's key usesysid in table pg_shadow, for instance, for row based
permissions. Explain in example:

create table role {
   role smallinteger,   -- analog group of users
   name text
}

create table permission { -- link role with pg_user
   uid integer references pg_user(usesysid),
   role smallint references role
}

create table protected_table {
-- payload fields
access  smallint references role,
author_of_last_changes integer references pg_user(usesysid) default
PG_SESSION_UID(),  -- proposed function
time_of_last_changes timestamp not null default current_timestamp
}

create function update_trigger_function() returns opaque as '
begin    -- PG_UID() proposed function
if (select role from role where uid=PG_UID())=old.access then
  new.time_of_last_changes=current_timestamp;
  new.author_of_last_changes=PG_SESSION_UID();  -- proposed function
  return new;
else
  return null;
end if;
end;
' language 'plpgsql';

create trigger update_trigger before update on protected table for each row
execute procedure update_trigger_function();

О©╫ertainly, I can create such function in my own project as:

create function pg_uid() returns integer as '
select usesysid from pg_user where usename=current_user;
' language 'sql';

Or as C function:

long pg_uid()
{
  return GetUserId();
}

But, IMHO, such fuction must be common.

-- 
Olleg Samoylov


Fri, 24 Jan 2003 10:51:05 +0300 (MSK)
Olleg Samoylov <olleg@jane.telecom.mipt.ru>
New buildin function
On Fri, 24 Jan 2003, Olleg Samoylov wrote:

> On 23 Jan 2003, Rod Taylor wrote:

> Nope. CURRENT_USER and SESSION_USER return username. Sometimes need uid,
> it's key usesysid in table pg_shadow, for instance, for row based
> permissions. Explain in example:
>
> create table role {
>    role smallinteger,   -- analog group of users
>    name text
> }
>
> create table permission { -- link role with pg_user
>    uid integer references pg_user(usesysid),
>    role smallint references role
> }
>
> create table protected_table {
> -- payload fields
> access  smallint references role,
> author_of_last_changes integer references pg_user(usesysid) default
> PG_SESSION_UID(),  -- proposed function
> time_of_last_changes timestamp not null default current_timestamp
> }

As a side note, the above table definitions are pretty invalid.
Referencing system tables is not guaranteed to work, and referencing
system views even less so.


Tue, 28 Jan 2003 22:12:01 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: New buildin function